最近在一个项目中用到了MySql的任务调度,为方便以后学习,在这简要的梳理复习一下。
MySQL提供了Event Scheduler,可以根据时间调度来运行任务,运行一次或多次。
下面是创建一个事件的完整代码:
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] …
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] …]
[ENDS timestamp [+ INTERVAL interval] …]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
具体理论讲解参考:
http://www.tuicool.com/articles/mUnE73V
http://www.jb51.net/article/74891.htm
http://blog.csdn.net/crazylaa/article/details/5368819
下面直接上实例,已测试通过。
创建测试表:
CREATE TABLE `event_test` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`time` time NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=532
ROW_FORMAT=DYNAMIC
;
创建一个每个1秒就向event_test表中插入一条数据的事件,代码如下:
CREATE EVENT IF NOT EXISTS e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO event_test(time) VALUES(CURRENT_TIME);
创建一个1分钟后清空event_test表的事件,代码如下:
CREATE EVENT IF NOT EXISTS e_test_clear
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO TRUNCATE TABLE event_test;
创建一个在某段时间内每隔3秒插入一条记录的事件,代码如下:
CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 3 SECOND
STARTS '2016-11-28 19:45:00'
ENDS '2016-11-28 19:45:00'+ INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO INSERT INTO event_test(time) VALUES(CURRENT_TIME);
开启事件,代码如下:
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;
关闭事件,代码如下:
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;
下面创建一个定时调用存储过程的事件
首先创建存储过程,代码如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS e_test $$
CREATE PROCEDURE e_test()
BEGIN
INSERT INTO event_test(time) VALUES(CURRENT_TIME);
END $$
DELIMITER ;
创建一个事件,每隔1秒自动调用e_test()存储过程
CREATE EVENT IF NOT EXISTS call_e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();
先总结到这,希望对大家对自己的学习有帮助。。。