| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
m8gy
6年前发布

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 ;