태그 보관물: MySQL

MySQL 이벤트 스케줄러를 이용한 자료정리

개요

테이블에 자료가 쌓이다 보면 오래된 자료를 주기적으로 정리해야 할 경우가 있습니다. 로그같은 개념으로 계속 누적이 되거나 저장된지 오래되어 의미가 없어진 자료등이 그렇습니다. 관리가 필요한 자료를 확인해서 관리자가 수동으로 관리할 수도 있습니다. 하지만 그것보다는 자동화하는 것이 더 좋은 선택일 것 입니다. MySQL 이벤트 스케줄러 를 사용해서 원하는 작업을 주기적으로 실행할 수 있습니다.

MySQL 이벤트 스케줄러

MySQL 이벤트 스케줄러는 원하는 작업을 지정된 규칙에 따라 실행할 수 있는 기능입니다. 자세한 내용은 다음의 링크에서 확인하실 수 있습니다.

https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

새벽 2시에 특정 프로시저를 수행하는 이벤트 생성 소스는 다음과 같습니다.

CREATE EVENT `system_log_delete_event`
	ON SCHEDULE
		EVERY '2' DAY_HOUR STARTS '2020-07-17 11:46:26'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT '30일 지난 로그 자료 삭제'
	DO call system_log_delete()

핵심은 스케줄과 수행할 명령을 지정하는 부분 입니다. 위 소스에서는 매일 2시에 system_log_delete 프로시저를 호출하도록 설정했습니다.

자세한 생성 문법은 다음 링크를 참조하시면 됩니다.

https://dev.mysql.com/doc/refman/5.7/en/create-event.html

시작과 끝을 지정할 수 있고 끝나면 이벤트 자체를 삭제할 수 도 있습니다. 만일 이벤트 스케줄러가 실행되지 않는다면 SET GLOBAL event_scheduler = ON; 명령을 수행하면 됩니다.

이번글에서는 MySQL 이벤트 스케줄러에 대해 알아보았습니다.

MySQL 프로시저 변수값에 따른 where 및 order by 변경

개요

MySQL 프로시저내에서 인수로 전달받은 값에 따라 where 및 order by 부분을 달리하고 싶은 경우가 있습니다. Dynamic SQL로 가능하지만 이 글에서는 case 문을 이용한 방법을 살펴보겠습니다.

where 절

프로시저의 인수로 전달받은 값에 따라 where 절의 조건을 변경하는 방법입니다. 회사ID 가 0이면 모든 회사를 보여주고 회사ID가 0 보다 크면 해당하는 자료만 보여주는 쿼리를 보겠습니다. _company_id 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    1 = 1
    and case when _company_id > 0 then company_id = _company_id else 1 = 1 end

7행을 보면 and 뒤쪽에 case 문을 이용해서 조건을 변경한 것을 알 수 있습니다.

order by

프로시저 인수로 특정한 필드의 정렬 조건을 넘겨받는 경우가 있습니다. 토글 형태로 오름차순과 내림차순이 번갈아 가면서 order by 부분에 적용되어야 합니다. 정렬을 적용해야 하는 필드가 적으면 조건으로 나누어 처리할 수 있습니다. 하지만 필드의 개수가 많아지면 조건으로 나누어 작업하는 것이 조합이 많아지게 되어 불가능 합니다. where 절과 마찬가지로 case 문을 이용해서 order by 부분의 필드와 정렬기준을 변경할 수 있습니다.

회사이름과 직원수 필드로 정렬하는 쿼리문을 예로 들어보겠습니다. _company_name_sort_order, _employee_sort_order 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    company_id > 0
order by
    case when lower(_company_name_sort_order) = 'a' then company_name end asc
    , case when lower(_company_name_sort_order) = 'd' then company_name end desc
    , case when lower(_employee_sort_order) = 'a' then employee end asc
    , case when lower(_employee_sort_order) = 'd' then employee end desc

8-11행을 보면 case 문으로 전달된 변수의 값으로 정렬기준을 변경한 것을 알 수 있습니다. 쿼리문이 이상하게 보이는 것 같지만 이런 방식으로 프로시저의 크기를 줄일 수 있습니다.

이상으로 MySQL 프로시저 변수값에 따른 where 및 order by 변경 방법에 대해 알아보았습니다.