MySQL5.1.6起增加了事件调度器(Event Scheduler),可用来做定时执行某些特定任务,用于取代原先只能由操作系统的计划任务来执行的工作。MySQL的事件调度器可以精确到每秒执行一个任务,而操作系统的计划任务只能精确到分钟级别。对于对数据实时性要求比较高的应用非常合适。
事件调度器也称为临时触发器(Temporal Triggers),因为事件调度器是基于特定时间周期触发来执行某些任务,而触发器(Triggers)是基于某个表所产生的事件触发的。
MySQL定时任务的实现方式有两种:
- 使用MySQL的
event
定时任务
使用MySQL的事件计划,首先需要在服务器开启event_scheduler
后才能处理。 - 使用Linux的定时任务
crontab
如何开启事件计划呢?
$ SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set
如果执行命令后返回值为OFF
则表示目前事件计划是处于关闭的状态。
开启的方式也分为两种,临时方式使用命令行或脚本操作,永久修改则需要修改MySQL主配置文件my.ini
在其中添加event_schduler=1
的配置后重启MySQL。
临时性修改只要不重启MySQL在当前运行状态下会直接生效,一旦重启后则失效。
$ SET GLOBAL event_scheduler = ON;
$ SET @@global.event_scheduler = ON;
$ SET GLOBAL event_scheduler = 1;
$ SET @@global.event_scheduler = 1;
事件调度器
要保证能够执行事件,就必须保证事件计划是开启状态,事件计划默认为关闭状态。
# 查看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
的配置。
事件任务
事件任务
# 查看事件任务
$ 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表超过一个月的数据
![4933701-f2ddde0269279ab5.png](https://i-blog.csdnimg.cn/blog_migrate/32b78e377fff70586c3490999eb6b783.png)
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
BEGIN
DELETE FROM center_fight WHERE 1=1 AND createdate < DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
END
设置定时任务调用存储过程
# 若计划任务存在则删除
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种设定的方式
- 用来完成单次计划任务。
AT 时间戳
eg:5天后
AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
eg:某时间点
AT TIMESTAMP '2018-07-12 12:00:00'
- 用来完成重复的计划任务
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
...
[ON COMPLETION [NOT] PRESERVE]
ON COMPLETION
参数表示“当这个事件不会再发生的时候”,即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS
阶段。而PRESERVE
的作用是使事件在执行完毕后不会被DROP
掉,建议使用该参数,以便于查看EVENT
具体信息。
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
存储过程
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 ;
-- 存储过程 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
错误处理
出现错误
[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
解决方案
$ SELECT VERSION();
$ @@sql_mode;
$ SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY' , ''));
出现提示
![4933701-0878f463db853808.png](https://i-blog.csdnimg.cn/blog_migrate/72fdb1f1bf1f2c3c9e9b624d1853b142.png)
# 在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;