mysql 任务调度

59519751

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