mysql 任務調度實現

今天有個業務需求,每天要重置流水號.想起oracle有job 于是聯想到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}  </pre> 


 簡單使用如下
    DELIMITER $$

      /**


     * 重置流水號  
     *   
     * @author xuyw  
     * @email xyw10000@163.com  
     * @date 2014-05-06  
     */  
-- 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 ;  </pre>