今天有个业务需求,每天要重置流水号.想起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 ;
来源: http://www.phpxs.com/code/1005306/