一、简介
自MySQL5.1.6起,增加了一个非常有特色的功能-事件调度器(Event Scheduler),可以用做定时执行某些特定任务(例如:删除记录、对数据进行汇总、数据备份等等),来取代原先只能由操作系统的计划任务来执行的工作。更值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux的cron或Windows下的任务计划)只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用(例如:股票、赔率、比分等)就非常适合。
1、开启事件计划
在使用这个功能之前必须确保event_scheduler已开启
#查看事件计划是否开启
show variables like '%event_sche%';
#将事件计划开启
SET GLOBAL event_scheduler = 1
#将事件计划关闭
SET GLOBAL event_scheduler = 0
#关闭事件任务:
ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;
#开启事件任务:
ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;
#查看事件任务:
SHOW EVENTS ;
mysql服务启动的时候开启时间,只需要在my.ini配置文件的[mysqld]部分加上event_scheduler=ON 即可
二、创建事件
先来看一下他的语法:
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [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}
1、循环定时任务
每秒插入一条记录到test_user表:
CREATE EVENT e_test_insert ON SCHEDULE
EVERY 1 SECOND DO
INSERT test_user VALUE ( NULL, CURRENT_TIMESTAMP );
每天定时清空test_user表:
CREATE EVENT e_test2 ON SCHEDULE
EVERY 1 DAY
DO TRUNCATE test_user;
5天后开启每天定时清空test_user表:
CREATE EVENT e_test3 ON SCHEDULE
EVERY 1 DAY
STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY
DO TRUNCATE test_user;
每天定时清空test_user表,5天后停止执行:
CREATE EVENT e_test4 ON SCHEDULE
EVERY 1 DAY
ENDS CURRENT_TIMESTAMP+INTERVAL 5 DAY
DO TRUNCATE test_user;
5天后开启每天定时清空test_user表,一个月后停止执行:
CREATE EVENT e_test5
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP+INTERVAL 1 MONTH
DO TRUNCATE test_user;
每天定时清空test表(每天执行一次五天后停止,任务完成后就终止该事件且不记录):
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件结束后是否保存,默认为NOT PRESERVE。
CREATE EVENT e_test6 ON SCHEDULE
EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE test_user;
[ENABLE | DISABLE]可以设置该事件创建后状态是否开启或关闭,默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
2、定时任务
5天后清空test_user表:
CREATE EVENT e_test ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 5 DAY DO
TRUNCATE TABLE test_user;
2021年9月29日18点整清空test_user表:
CREATE EVENT e_test1 ON SCHEDULE
AT TIMESTAMP '2021-09-29 18:00:00'
DO TRUNCATE TABLE test_user;
3、修改事件
先来看一下他的语法:
ALTER EVENT event_name
[ONSCHEDULE schedule]
[RENAME TOnew_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
临时关闭事件
ALTER EVENT e_test DISABLE;
开启事件
ALTER EVENT e_test ENABLE;
如果你将event执行了Alter event event_name disable.那么当你重新启动mysql服务器后,该event将被删除
4、删除事件
先来看一下他的语法:
DROP EVENT [IF EXISTS] event_name
例如删除前面创建的e_test事件
DROP EVENT e_test;
当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown event错误,因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;
将每天清空test表改为5天清空一次:
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
5、两种任务方式
- AT 时间戳,用来完成单次的计划任务2
- EVERY 时间(单位)的数量实践单位[STARTS 时间戳] [ENDS时间戳],用来完成重复的计划任务。
在两种计划任务中,时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型,时间戳需要大于当前时间。
在重复的计划任务中,时间(单位)的数量可以是任意非空(Not Null)的整数式,时间单位是关键词:YEAR,MONTH,DAY,HOUR,MINUTE 或者SECOND。
提示: 其他的时间单位也是合法的如:QUARTER,WEEK,YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND,不建议使用这些不标准的时间单位。
[ON COMPLETION [NOT] PRESERVE]:ON COMPLETION参数表示"当这个事件不会再发生的时候",即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉,建议使用该参数,以便于查看EVENT具体信息。
三、应用案例
假如在定义event的时候有指定ends:
#当event到期了,event会被disable,但是该event记录还是会存在(任务记录用于重启任务和记录)
on completion preserve
#当event到期的时候,该event会被自动删除掉
on completion not preserve
创建存储过程存入当前时间
DELIMITER $$
CREATE PROCEDURE test_user_task () BEGIN
INSERT test_user
VALUE
( NULL, CURRENT_TIMESTAMP );
END $$
DELIMITER;
接着,创建任务,每五秒执行一次
CREATE EVENT IF NOT EXISTS my_task ON SCHEDULE
-- 每一秒执行一次
EVERY 1 SECOND
-- 五秒后结束
ENDS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
-- 结束后不保存任务记录
ON COMPLETION NOT PRESERVE
DO
CALL test_user_task();
如果在运行中想要临时关闭一下某个任务,执行 ALTER EVENT 语句即可:
ALTER EVENT my_taskON COMPLETION PRESERVE DISABLE; ##关闭事件
ALTER EVENT my_taskON COMPLETION PRESERVE ENABLE; ##开启事件