MySQL中的event(JOB、定时任务)
MySQL5.1.6起增加了事件调度器(Event Scheduler),可用来做定时执行某些特定任务,用于取代原先只能由操作系统的计划任务来执行的工作。MySQL的事件调度器可以精确到每秒执行一个任务,而操作系统的计划任务只能精确到分钟级别。对于对数据实时性要求比较高的应用非常合适。
事件调度器也称为临时触发器(Temporal Triggers),因为事件调度器是基于特定时间周期触发来执行某些任务,而触发器(Triggers)是基于某个表所产生的事件触发的。
MySQL定时任务的实现方式有两种:
- 使用MySQL的
event
定时任务
使用MySQL的事件计划,首先需要在服务器开启event_scheduler
后才能处理。 - 使用Linux的定时任务
crontab
如何开启事件计划呢?
1 2 3 4 5 6 7 | $ SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set |
如果执行命令后返回值为OFF
则表示目前事件计划是处于关闭的状态。
开启的方式也分为两种,临时方式使用命令行或脚本操作,永久修改则需要修改MySQL主配置文件my.ini
在其中添加event_schduler=1
的配置后重启MySQL。
临时性修改只要不重启MySQL在当前运行状态下会直接生效,一旦重启后则失效。
1 2 3 4 | $ SET GLOBAL event_scheduler = ON; $ SET @@global.event_scheduler = ON; $ SET GLOBAL event_scheduler = 1; $ SET @@global.event_scheduler = 1; |
事件调度器
要保证能够执行事件,就必须保证事件计划是开启状态,事件计划默认为关闭状态。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | # 查看MySQL版本 $ SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.7.18-log | +------------+ 1 row in set # 事件计划是否开启 $ SHOW VARIABLES LIKE 'event%' $ SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set # 查看事件任务是否开启 $ SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set # 开启事件计划 $ SET GLOBAL event_scheduler=1 $ SET GLOBAL event_scheduler=ON duler=1; Query OK, 0 rows affected # 关闭事件计划 $ SET GLOBAL event_scheduler=0 |
在真实开发环境中会遇到MySQL服务重启或断电的情况,此时会出现事件调度器被关闭的情况。所有事件都不再起作用,解决的方式需要在MySQL的配置文件mysql.ini
中加入event_scheduler=ON
的配置。
事件任务
事件任务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # 查看事件任务 $ SHOW EVENTS; Empty set # 查看事件任务错误 - 权限不足 $ SELECT * FROM mysql.event 1142 - SELECT command denied to user 'username'@'127.0.0.1' for table 'event' # 开启事件任务 $ ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE # 关闭事件任务 $ ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE # 删除事件 $ DROP EVENT [IF EXISTS] event_name |
设置定时任务执行SQL语句
例如:从当日开始每天凌晨4点删除fight表超过一个月的数据
计划任务
1 2 3 4 5 6 7 | DROP EVENT IF EXISTS event_fight_delete; CREATE EVENT event_fight_delete ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(CURDATE() + 1), INTERVAL 4 HOUR) DO DELETE FROM center_fight WHERE 1=1 AND createdate < DATE_ADD(CURDATE(), INTERVAL -1 MONTH) ; |
设置定时任务调用存储过程
1 2 3 4 5 6 7 8 9 10 11 | # 若计划任务存在则删除 DROP EVENT IF EXISTS event_name # 创建计划任务 CREATE EVENT event_name ON SCHEDULE EVERY 10 second STARTS TIMESTAMP '2018-07-12 00:00:00' ON COMPLETION PRESERVE DO BEGIN CALL producer() END |
参数说明
ON SCHDULE schduler
定义执行的时间和时间间隔ON COMPLETION [NOT] PRESERVE
定义事件是一次性执行还是永久执行,默认为一次性执行,即NOT PRESERVE
。
在事件中ON SCHEDULE
计划任务中有2种设定的方式
- 用来完成单次计划任务。
1 2 3 4 5 6 7 | AT 时间戳 eg:5天后 AT CURRENT_TIMESTAMP + INTERVAL 5 DAY eg:某时间点 AT TIMESTAMP '2018-07-12 12:00:00' |
- 用来完成重复的计划任务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | EVERY 时间(单位)的数量 时间单位 [STARTS 时间戳] [ENDS时间戳] eg:每隔1秒 EVERY 1 SECOND eg:每隔10分钟。 EVERY 10 MINUTE eg:从2018-08-01 12:00:00开始每隔1天 EVERY 1 DAY STARTS '2018-08-01 12:00:00' EVERY 10 second STARTS TIMESTAMP '2018-08-01 12:00:00' eg:5天后开启每天定时处理 EVERY 1 DAY START CURRENT_TIMESTAMP + INTERVAL 5 DAY eg:每天定时处理5天后停止 EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY |
在两种计划任务中,时间戳可以是任意的TIMESTAMP
和DATETIME
数据类型,时间戳需要大于当前时间。
在重复的计划任务中,时间(单位)的数量可以是任意非空(NOT NULL)的整数形式,时间单位是关键词:YEAR
、MONTH
、DAY
、HOUR
、MINUTE
、SECOND
...
1 | [ON COMPLETION [NOT] PRESERVE] |
ON COMPLETION
参数表示“当这个事件不会再发生的时候”,即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS
阶段。而PRESERVE
的作用是使事件在执行完毕后不会被DROP
掉,建议使用该参数,以便于查看EVENT
具体信息。
1 2 3 4 5 6 7 8 | CREATE DEFINER=`root`@`localhost` EVENT `event_knapsacks_remember_expire` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-13 15:09:49' ON COMPLETION PRESERVE ENABLE COMMENT '每分钟检测背包中换牌卡到期并每日自动减少' DO BEGIN CALL produce_knapsacks_remember_expire(); END |
存储过程
1 2 3 4 5 6 7 | DELIMITER $$ DROP PROCEDURE IF EXISTS procedure_name CREATE PROCEDURE procedure_name() BEGIN INSERT INTO procedure_name(name, create_time) VALUES('name_value', now()) END $$ DELIMITER ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- 存储过程 produce_knapsacks_remember_expire -- 作用:判断背包中道具记牌卡,是否过期,且每日减一。 CREATE DEFINER=`root`@`localhost` PROCEDURE `produce_knapsacks_remember_expire`() BEGIN DECLARE pk INT DEFAULT 0; DECLARE sec INT DEFAULT 0; DECLARE days INT DEFAULT 0; DECLARE expire INT DEFAULT 0; DECLARE mc CURSOR FOR (SELECT id,TIMESTAMPDIFF(SECOND,effect_time,NOW()) AS diff,TIMESTAMPDIFF(SECOND,NOW(),expire_time) AS expire FROM knapsacks WHERE name='REMEBER' AND expired=0); OPEN mc; ml:LOOP FETCH mc INTO pk,sec,expire; IF(expire <= 0) THEN UPDATE `knapsacks` SET `expired`=1 WHERE `id`=pk; ELSE IF(sec>0 && sec<=86400) THEN SET days = 1; ELSEIF(sec>86400) THEN SET days=CEILING(sec/86400); END IF; UPDATE `knapsacks` SET `quantity`=`purchase`-days,`consume`=days WHERE `id`=pk; END IF; COMMIT; END LOOP ml; CLOSE mc; END |
错误处理
出现错误
1 | [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
解决方案
1 2 3 | $ SELECT VERSION(); $ @@sql_mode; $ SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY' , '')); |
出现提示
ERROR
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 在SQL中查询计划事件的状态 $ SHOW VARIABLES LIKE 'event_scheduler' # 在mysql程序的目录下找到my.ini文件添加 $ vim my.ini event_scheduler = 1 # 保存后重启mysql服务 # 用脚本来实现 # 开启event_scheduler sql指令: SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1; $ 关闭event_scheduler指令: SET GLOBAL event_scheduler = OFF; SET @@global.event_scheduler = OFF; SET GLOBAL event_scheduler = 0; SET @@global.event_scheduler = 0; |
创建事件示例
先来看一下他的语法:
1 2 3 4 5 6 | CREATE EVENT [IFNOT EXISTS] event_name ONSCHEDULE schedule [ONCOMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] DO sql_statement; |
1 2 3 4 5 6 7 8 | 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) 首先来看一个简单的例子来演示每秒插入一条记录到数据表
1 2 3 4 5 6 | USE td2; CREATE TABLE aaa(timeline TIMESTAMP); CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT aaa VALUE(CURRENT_TIMESTAMP); |
等待3秒之后,再执行查询看看
1 | SELECT * FROM aaa; |
2) 5天后清空test表:
1 2 3 | CREATE EVENT e_test ON SCHEDULE AT CURRENT_TIMESTAMP+INTERVAL 5 DAY DO TRUNCATE TABLE aaa; |
3) 2007年7月20日12点整清空test表:
1 2 3 | CREATE EVENT e_test1 ON SCHEDULE AT TIMESTAMP '2018-09-17 18:16:00' DO TRUNCATE TABLE aaa; |
4) 每天定时清空test表:
1 2 3 | CREATE EVENT e_test2 ON SCHEDULE EVERY 1 DAY DO TRUNCATE aaa; |
5) 5天后开启每天定时清空test表:
1 2 3 4 | CREATE EVENT e_test3 ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY DO TRUNCATE aaa; |
6) 每天定时清空test表,5天后停止执行:
1 2 3 4 | CREATE EVENT e_test4 ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP+INTERVAL 5 DAY DO TRUNCATE aaa; |
7) 5天后开启每天定时清空test表,一个月后停止执行:
1 2 3 4 5 | CREATE EVENT e_test5 ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP+INTERVAL 1 MONTH DO TRUNCATE aaa; |
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE。
8) 每天定时清空test表(只执行一次,任务完成后就终止该事件):
1 2 3 4 | CREATE EVENT e_test6 ON SCHEDULE EVERY 1 DAY ON COMPLETION NOT PRESERVE DO TRUNCATE aaa; |
[ENABLE | DISABLE]可以设置该事件创建后状态是否开启或关闭,默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
修改事件
先来看一下他的语法:
1 2 3 4 5 6 7 | ALTER EVENT event_name [ONSCHEDULE schedule] [RENAME TOnew_event_name] [ON COMPLETION [NOT] PRESERVE] [COMMENT 'comment'] [ENABLE | DISABLE] [DO sql_statement] |
1) 临时关闭事件
1 | ALTER EVENT e_test DISABLE; |
2) 开启事件
1 | ALTER EVENT e_test ENABLE; |
3) 将每天清空test表改为5天清空一次:
1 2 | ALTER EVENT e_test ON SCHEDULE EVERY 5 DAY; |
删除事件
先来看一下他的语法:
1 | DROP EVENT [IF EXISTS] event_name |
例如删除前面创建的e_test事件
1 | DROP EVENT e_test; |
当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown event错误,因此最好加上IF EXISTS
1 | DROP EVENT IF EXISTS e_test; |
注意:如果你将event执行了Alter event event_name disable.那么当你重新启动mysql服务
器后,该event将被删除(测试版本:5.1.30)
备注:在event事件中:ON SCHEDULE 计划任务,有两种设定计划任务的方式:
(1)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 scheduler 的特性,每秒钟调用一次存储过程,用于判断 SLAVE 是否正常运行,如果发现 SLAVE 关闭了,忽略 0 次错误,然后重新启动 SLAVE。
1)首先创建存储过程
1 2 3 4 5 6 7 8 9 10 | CREATE PROCEDURE Slave_Monitor() BEGIN SELECT VARIABLE_VALUE INTO @SLAVE_STATUS FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='SLAVE_RUNNING'; IF('ON'!=@SLAVE_STATUS) THEN SET GLOBAL SQL_SLAVE_SKIP_COUNTER=0; SLAVE START; END IF; END; |
由于存储过程中无法调用类似 SHOW SLAVE STATUS 这样的语句,因此无法得到确切的复制错误信息和错误代码,不能进一步的处理 SLAVE 停止的各种情况。
2)接着,创建任务
1 2 3 4 5 | CREATE EVENT IF NOT EXISTS Slave_Monitor ON SCHEDULE EVERY 5 SECOND ON COMPLETION PRESERVE DO CALL Slave_Monitor(); |
创建了一个任务,每 5秒钟执行一次,任务结束后依旧保留该任务,而不是删除。当然了,在本例中的任务不会结束,除非将它手动禁止了。
如果在运行中想要临时关闭一下某个任务,执行 ALTER EVENT 语句即可:
1 2 | ALTER EVENT Slave_Monitor ON COMPLETION PRESERVE DISABLE; ##关闭事件 ALTER EVENT Slave_Monitor ON COMPLETION PRESERVE ENABLE; ##开启事件 |