My-SQL에 event_scheduler 라는 기능이 있다.
윈도우의 작업 스케줄러와 유사한 기능을 한다.

5.1 이상 가능
> select version();


show variables like '%event%';

Variable_name        Value 
event_scheduler      OFF 
 
>>>
set global event_scheduler = on;

----------------------------------------------------------------------------------------------------------

DELIMITER $$


DROP event 이벤트명$$

CREATE EVENT `이벤트명` ON 
SCHEDULE EVERY 1 DAY 
STARTS '2009-11-12 11:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO BEGIN

 -- 스케쥴러 내부에서 실행할 SQL 작성

END$$

DELIMITER ;



//스케줄러 1 : 임차인 퇴거상태

DELIMITER $$

CREATE EVENT `lessee_contract_update` ON 

SCHEDULE EVERY 1 DAY 

STARTS '2013-12-12 03:00:00' 

ON COMPLETION NOT PRESERVE ENABLE DO BEGIN 

update plusm.lessee_tb set is_contract = 'N' where expire_date < curdate() and is_contract = 'Y';  

END$$

DELIMITER ;


//스케줄러 2 : 임차인 계약기간종료에 따른 권한 해제

DELIMITER $$

CREATE EVENT `user_type_LE_update` ON 

SCHEDULE EVERY 1 DAY 

STARTS '2013-12-12 03:10:00' 

ON COMPLETION NOT PRESERVE ENABLE DO BEGIN 

update plusm.user_tb set type = replace(type, ',LE', '') where uid =

(select * from 

(select uid from plusm.user_tb a where type like '%LE%' and 0 >=  

(select count(c.user_id) as cnt from plusm.user_lessee_rtb b, plusm.lessee_tb c where a.uid = b.user_uid and b.lessee_uid = c.uid and c.is_contract = 'Y')

) AS ysm);

END$$

DELIMITER ;



DELIMITER $$

CREATE EVENT `user_type_LE_update` ON 

SCHEDULE EVERY 1 DAY 

STARTS '2013-12-16 03:10:00' 

ON COMPLETION NOT PRESERVE ENABLE DO BEGIN 

update plusm.user_tb set type = replace(type, ',LE', '') where uid IN 

(select * from 

(select uid from plusm.user_tb user where type like '%LE%' and 0 >= 

(select count(c.uid) as cnt from plusm.user_tb a, plusm.user_lessee_rtb b, plusm.lessee_tb c where a.uid = b.user_uid and b.lessee_uid = c.uid and a.uid = user.uid and c.is_contract = 'Y')

) AS ysm); 

END$$

DELIMITER ;




=> SCHEDULE EVERY 1 DAY  
 실행 주기 설정 ( 1 DAY, 1 SECOND, 1 MINUTE, 1 HOUR, 1 WEEK ,1 MONTH, 1 YEAR 등등)
=> STARTS '2009-11-12 11:00:00' 
 스케쥴러가 최초  실행할 시간 (시간 설정될 경우 해당 설정된 시간 이후로 부터 실행 주기에 의해 실행됨)


----------------------------------------------------------------------------------------------------------


* 현재 구동중인 EVENT 확인

   MYSQL> SHOW EVENTS;

 

 

* 생성

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body; 


ex)

CREATE EVENT (이벤트명)

 ON SCHEDULE EVERY 30 SENCOND    -> 주기 (30초 주기)

DO

SELECT/UPDATE/INSERT 등  쿼리문 ;

 

* 수정 

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]
?
 

 

* 삭제 

DROP EVENT [IF EXISTS] event_name;