IT虾米网

mysql 任务调度实现详解

itxm 2018年06月24日 数据库 545 0

今天有个业务需求,每天要重置流水号.想起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 [email protected]   
         * @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 ;  

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

mysql 游标使用模版详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。