欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql 任务调度实现

程序员文章站 2022-05-10 12:58:54
...
今天有个业务需求,每天要重置流水号.想起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 $$  
  /**  
     * 重置流水号  
     *   
     */  
-- 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 ;