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}  

简单使用如下

    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 ;  

标签： Mysql

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。