您的当前位置:首页mysql任务调度实现

mysql任务调度实现

2020-11-09 来源:世旅网

今天有个业务需求,每天要重置流水号.想起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 ;
显示全文