근원e♡수진♡라라 일상생활

MySQL Event

MySQL 5.1버전 부터 Event 가 가능합니다. 이벤트는 특정 시간 마다 정해진 일을 할 수 있도록 이벤트를 지정하는 것입니다. 예를 들어 통계 작업을 할때 매번 DB를 접속을 한 후에 값을 불러와 연산을 하고 그 결과를 DB에 다시 입력하도록 프로그램을 작성하고 그 프로그램을 *nix 의 Crond 에 등록을 하거나 Windows 의 이벤트 스케쥴러에 등록을 했습니다. 하지만 MySQL Event 는 이벤트를 직접 MySQL 내부에서 처리 하도록 할 수 있습니다.

기본 적인 문법에 대해서 보도록 하겠습니다.
CREATE EVENT [이벤트 이름] ON SCHEDULE [스케쥴:시간:간격] [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] DO [이벤트 구문] END

위와 같이 이벤트를 동작 시킬 수 있습니다. 이벤트는 기본적으로 OFF로 설정이 되어 있습니다. (옵션을 지정하지 않았을 경우 )


SHOW VARIABLES LIKE '%event%'; 를 통해 이벤트 스케쥴러가 활성화가 되어 있는지 확인 을 할 수 있습니다. event_scheduler 의 값이 없거나 OFF로 되어 있으면 전자는 지원하지 않고 , 후자는 이벤트 스케쥴러가 동작을 하지 않고 있는 것입니다.

이벤트 스케쥴러를 우선적으로 활성화를 합니다 (지원하지 않는 버전일 경우에 MySQL 을 5.1 이상 버전으로 업그레이드를 해주세요)

서버가 실행될때 매번 이벤트 스케쥴러를 실행하고자 할때는 my.ini 에 아래 구문을 추가해 주세요
event_scheduler = On

일회성 또는 테스트용으로 사용하시려면 아래와 같이 SET 으로 현재 설정 상태를 변경하시면 됩니다.

위와 같이 설정하시면 이벤트 스케쥴러가 정상적으로 동작을 할 것입니다.

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE '%event%';

위와 같이 설정을 하셨으면 이제 부터 이벤트 스케쥴러 사용법에대에 본격적으로 다루어 보겠습니다.

일단 이벤트를 사용할 사용자에 대한 권한을 지정해야 합니다. root로 사용하는 사례는 별로 없기때문에 MySQL 사용자에 대한 이벤트 사용권한을 주도록 하겠습니다.

이벤트 스케쥴러란 이벤트 즉 정해진 루틴에 따라 시작을 하기때문에 설정 에 대한 옵션을 먼저 보겠습니다.

-- 이벤트 부여
-- 사용자 user 에 모든 데이터 베이스와 테이블에 이벤트 권한을 부여
GRANT EVENT ON *.* TO user;
-- 사용자 user 에게 database_name 하위에 모든 테이블에 대한 이벤트 권한을 부여
GRANT EVENT ON database_name.* TO user;

-- 이벤트 취소
-- 사용자 user에 모든 데이터 베이스에 대한 이벤트 권한을 제거한다
REVOKE EVENT ON *.* FROM user;
-- 사용자 user에 데이터 베이스 database_name 하위에 모든 테이블에 대한 권한을 제거 한다.
REVOKE EVENT ON database_name.* FROM user;

CREATE EVENT
이벤트를 직접 생성해 보도록 하겠습니다.

DROP EVENT IF EXISTS testevent;
DELIMITER $
-- 이벤트 testevet 를 생성합니다.
CREATE EVENT testevent
-- 지금 현재 시간(CURRENT_TIMESTAMP) 후(+) 1일(INTERVAL 1 DAY) 이 지나면 수행합니다.
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
-- 이벤트가 완료된(수행) 후 이벤트를 보전합니다. 
ON COMPLETION PRESERVE
DO
BEGIN
	-- 이벤트 구문을 시작합니다.
	INSERT INTO TriggerTest (`seq`, `name`) VALUES (null, 'This is not a reale news');
	-- 이 구문은 특정 테이블에 1일에 한번씩 ROW를 삽입합니다.
END$
DELIMITER ;
-- 위 생성된 이벤트는 1회성 이벤트로 1번 수행 후 다시 수행하지 않습니다
-- 이벤트가 종료가 되면 해당 이벤트는 information_schema.EVENTS 에서 삭제가 됩니다.
-- 하지만 ON COMPLETION PRESERVE 를 추가하면 이벤트가 보전이 됩니다.


DROP EVENT IF EXISTS testevent;
DELIMITER $
-- 이벤트 testevet 를 생성합니다.
CREATE EVENT testevent
-- 지금 부터 2일에 한번씩 이벤트를 수행합니다.
ON SCHEDULE EVERY 2 DAY

-- 반복적 이벤트는 아래와 같은 시간 단위를 사용할 수 있습니다. 
-- YEAR : 년
-- QUARTER : 분기
-- MONTH : 월
-- WEEK : 주
-- DAY : 일
-- HOUR : 시간
-- MINUTE : 분
-- SECOND : 초
-- 등을 사용 할 수 있습니다. 자세한 INTERVAL 구문은 아래 다시 다루도록 하겠습니다.

DO
BEGIN
	-- 동작할 이벤트 구문을 넣습니다.
END$
DELIMITER ;

이벤트를 지정할 때 사용되는 (+/-) INTERVAL [값] [단위] 표현에 대해서 알아보겠습니다. 이벤트를 지정할 때는 지금 시점 부터 또는 특정 시점부터 언제까지 또는 얼마만에 동작을 하겠다는 표현을 합니다. 그 표현은 아래 표에서 참고 하시면 됩니다.

단위	사용 방법
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

date + INTERVAL [시간표기] 단위
date - INTERVAL [시간표기] 단위
위 와 같은 방법으로 사용합니다. 예를 들어 1시간 후면 INTERVAL 1 HOUR 가 됩니다.
그럼 언제 부터 언제 까지 이벤트를 수행하려면 어떻게 할까요?
아래와 같이 하면 됩니다

DELIMITER $
CREATE EVENT testevent
ON SCHEDULE
-- 1분마다 이벤트 실행
EVERY 1 MINUTE
-- 이벤트 시작시간은 지금 시간으로 부터 1시간 후 부터
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
-- 종료 시간은 지금 시간 부터 2시간 후에 종료 한다.
ENDS CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO

BEGIN
	-- 수행할 구문
	INSERT INTO testtable VALUES (1,2,3,4);
END $
DELIMITER ;

이벤트 BEGIN 과 END 안에 사용하고 자 하는 SQL구문은 모두 가능합니다. 프로시져를 호출 할 수도 있고 , FUNCTION을 사용할 수 있습니다. ^^

좀더  SQL생활이 윤택해졌나요? ㅋㅋ