mysql 작업 스케줄링 구현

오늘 업무상 요구사항이 있어서 매일 시리얼넘버를 재설정해야 하는데 오라클이 하는 일이 생각나서 Mysql에도 비슷한 것이 있어야 한다고 생각했어요

EVENT를 통해 구현됩니다

CREATE EVENT [IF NOT EXISTS] event_name  
   
    ON SCHEDULE schedule  
   
    [ON COMPLETION [NOT] PRESERVE]  
   
    [ENABLE | DISABLE]  
   
    [COMMENT 'comment']  
   
    DO sql_statement;  
   
schedule:  
   
    AT TIMESTAMP [+ INTERVAL INTERVAL]  
   
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]  
   
INTERVAL:  
   
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |  
   
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |  
   
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

간단한 사용법은 다음과 같습니다

DELIMITER $$  
  /**  
     * 重置流水号  
     *   
     */  
-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create      
   
CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `xxx`.`reset_serialNumber`  
   
ON SCHEDULE EVERY 1 DAY STARTS '2014-05-06 23:59:59' 
     /* uncomment the example below you want to use */  
   
    -- scheduleexample 1: run once  
   
       --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }  
   
    -- scheduleexample 2: run at intervals forever after creation  
   
       -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]  
   
    -- scheduleexample 3: specified start time, end time and interval for execution  
       /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]  
   
       STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }  
   
       ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */  
   
/*[ON COMPLETION [NOT] PRESERVE]  
[ENABLE | DISABLE]  
[COMMENT 'comment']*/  
   
DO  
    BEGIN 
        UPDATE xxx_sequence  
                   SET current_value = 0  
                   WHERE id = 1;  
    END$$  
   
DELIMITER ;