mysql event

Mysql 5.1.17 에서부터는 Event 란 기능을 어느정도 이용할수 있다고 한다.
Event 란 특정 시간에 어떠한 쿼리 프로시저 함수등을 실행 시키는것을 말한다.
우선 root 권한으로 작업을 한다는 가정하에 적는다.

Event 를 사용하기위해서는
1. 서버가 동작중이라면 아래와 같은 SQL문을 통해 실행할수 있다.
    SET GLOBAL event_scheduler = ON;
    SET @@global.event_scheduler = ON;
    SET GLOBAL event_scheduler = 1;
    SET @@global.event_scheduler = 1;
2. my.cnf or my.ini 에 옵션(event_scheduler=On)을 추가하고 mysql 을 시작한다.

반대로 Event 를 사용하지 않으려면
1. 서버가 동작중이라면 아래와 같은 SQL문을 통해 종료할수 있다.

SET GLOBAL event_scheduler = OFF;    SET @@global.event_scheduler = OFF;    SET GLOBAL event_scheduler = 0;    SET @@global.event_scheduler = 0;

2. my.cnf or my.ini 에 옵션(event_scheduler=Off)을 추가하고 mysql 을 시작한다.

그럼 실제 event 를 만들어 보자.
우선 아래와 같이 test DB에 테이블을 하나 만들어보자

CREATE TABLE `e` (   `num` int(10) unsigned NOT NULL AUTO_INCREMENT,   `regdate` datetime NOT NULL,   PRIMARY KEY (`num`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

그리고 이벤트를 하나 만들어 보자
아래의 SQL문은 test.e 테이블에 현재시간으로 부터 30초마다 insert 를 실행하는
event 를 생성하는 것이다.

create event IF NOT EXISTS e1     ON SCHEDULE         EVERY 30 SECOND         STARTS CURRENT_TIMESTAMP     DO insert into test.e set regdate=now();

몇분지나다 보면 test.e 테이블에 데이터가 입력되는것을 확인할수 있을것이다.

EVERY 30 SECOND 의 의미가 30초씩 루프를 돈다는 의미이며 STARTS CURRENT_TIMESTAMP 가
현재 시간부터 시작한다는 의미이다. SECOND 외에도 아래와 같은 옵션으로 올수 있다.
YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
EVERY 2 MINUTE => 2분마다 DO 구문을 실행
EVERY 1 HOUR => 매일 실행
...

이렇게 입력한 Event 는 mysql.event 테이블에 등록이 된다.
또는 show events; 를 통해 확인할수도 있다. 그외로 information_schema.EVENTS 에서도 확인할수 있다.
mysql.event 를 확인하다보면 created modified 시간이 정확하지만
last_excuted starts ends 는 뭔가 좀 다르다는걸 알수 있다. 이는 그리니치 표준시각을 의미한다.

이런 Event 를 삭제하기 위해서는 아래와 같은 SQL 문을 실행하면 된다.
drop event 이벤트명;
즉 위의 e1 이라는 event 를 삭제하기 위해서는 drop event e1; 인 SQL문을 실행하면 된다.
event는 mysql.event 테이블에 등록된다고 했는데 mysql.event 에서 바로 삭제하는경우
메모리의 영향때문인지 삭제후 똑같은 event 명으로 등록을 한경우 앞전에 사용한 event 가 제대로 지워지지
않고 동작하는 경우를 볼수 있다. 그러니 삭제시는 꼭 drop event 이벤트명; 을 이용하길 바란다.

여하튼 위에서 생성한 e1 쿼리를 좀더 발전시켜 아래와 같이 작성해보자

create event IF NOT EXISTS e2    ON SCHEDULE        EVERY 1 MINUTE        STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE        ENDS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE    DO insert into test.e set regdate=now();

위의 SQL문과 다른점은 ENDS 가 지정되고 + INTERVAL 1 MINUTE 같은 문구가 지정된것이다.
이는 현재시간으로 부터 1 분뒤 부터 Event 가 실행되고 1분마다 do 의 sql 문이 실행되며
현재시간으로부터 5분뒤에 멈춘다는 것이다. 그러니 총 5번의 실행후 멈추게 된다.
예를 들면 13:00:00 이 현재 시간이라고 한다면 13:01:00 부터 이벤트가 실행되며 13:05:00 까지 insert 가
일어나고 동시에 13:05:00 에 이벤트가 종료된다.
이런경우 즉 Ends 가 있는 경우는 event 가 종료되고 mysql.event 테이블에서 해당 event 가 자동삭제된다.

그럼 딱 한번 실행을 위해서는 어떻게 해야 할까?

create event IF NOT EXISTS e2    ON SCHEDULE        EVERY 1 MINUTE        STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE        ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE    DO insert into test.e set regdate=now();

이렇게 해도 될것이다.
하지만 이는 너무 비효율적이다.

그래서 AT 이라는 지시어가 별도로 있다.
즉 EVERY는 순환을 위해 AT 은 한번 사용을 위해 사용된다.
Unux 계열의 at 과 cron 의 의미와 관련지어 생각하면 될듯 하다.
위의 SQL문은 아래와 같이 수정할수 있다.

create event IF NOT EXISTS e2    ON SCHEDULE        AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE     DO insert into test.e set regdate=now();

이 경우에도 Ends 가 붙은것처럼 한번실행하고 나면 mysql.event 에서 삭제된다.
즉 종료 조건이 있는 경우 자동삭제된다고 보면 된다.

물론 아래와 같이 ON COMPLETION PRESERVE 구문을 추가하면 이부분 역시 남겨둘수 있다.

create event IF NOT EXISTS e2    ON SCHEDULE        AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE         ON COMPLETION PRESERVE    DO insert into test.e set regdate=now();

Event 는 위의 경우처럼 do 에서 insert 외에도 다양한 구문을 지원한다.
아래는 mysql 에서 제공하는 예제이다.
mysql 프로시저 등을 공부해보신 분이라면 금방이해가 갈듯...

DELIMITER | CREATE EVENT e     ON SCHEDULE       EVERY 5 SECOND     DO       BEGIN         DECLARE v INTEGER;         DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;         SET v = 0;         WHILE v < 5 DO           INSERT INTO t1 VALUES (0);           UPDATE t2 SET s1 = s1 + 1;           SET v = v + 1;         END WHILE;     END | DELIMITER ; CREATE EVENT e_call_myproc     ON SCHEDULE       AT CURRENT_TIMESTAMP + INTERVAL 1 DAY     DO CALL myproc(5, 27);

Mysql Event 관련 자료를 찾아보기가 어려워서 만들어봤습니다. 잘못된 부분이 있으면 지적해주시면 감사 ^__^

 

이 글은 스프링노트에서 작성되었습니다.

.