MySQL定时任务(EVENT事件)如何配置详解

梦里梦外; 2022-10-29 01:47 467阅读 0赞

一、事件（EVENT）是干什么的  
  自MySQL5.1.6起，增加了一个非常有特色的功能 - 事件调度器（Event Scheduler），可以用做定时执行某些特定任务（例如：删除记录、数据统计报告、数据备份等等），来取代原先只能由操作系统的计划任务来执行的工作。  
  值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux的cron）只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合。

事件有时也可以称为临时触发器（temporal triggers），因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器（Triggers）是基于某个表所产生的事件触发的，区别也就在这里。

二、开启“事件”功能  
使用“事件”功能之前必须确保event\_scheduler已开启

1、查询功能是否开启：  
1  
2  
3  
4  
\-- 方法一  
SELECT @@event\_scheduler;  
\-- 方法二  
SHOW VARIABLES LIKE 'event%';  
显示 “ON”说明功能已开启；如下图：

1  
2  
3  
4  
5  
6  
\-- 开启功能命令：  
SET GLOBAL event\_scheduler = 1;  
SET GLOBAL event\_scheduler = ON;  
\-- 关闭功能命令：  
SET GLOBAL event\_scheduler = 0;  
SET GLOBAL event\_scheduler = OFF;  
当然，通过命令开启当数据库重启后会自动关闭；

持久化开启方式：将event\_scheduler=1写到my.cnf配置文件中;如下图：

在这里插入图片描述

常用的事件操作命令：

关闭指定事件： ALTER EVENT 事件名称 ON COMPLETION PRESERVE DISABLE;  
开启指定事件：ALTER EVENT 事件名称 ON COMPLETION PRESERVE ENABLE;  
查看当前事件：SHOW EVENTS ;  
三、事件 - SQL语法创建  
下面是EVENT事件创建语句，乍一看挺复杂的，让我们拆开解读一下  
1  
2  
3  
4  
5  
6  
CREATE EVENT \[IFNOT EXISTS\] event\_name  
　　 ON SCHEDULE schedule(调度时间设置)  
　　 \[ON COMPLETION \[NOT\] PRESERVE\]  
　　 \[ENABLE | DISABLE | DISABLE ON SLAVE\]  
　　 \[COMMENT 'comment'\]  
　　 DO sql\_statement;  
SQL语法 说明  
DEFINER 可选项，给指定用户使用权限  
IF NOT EXISTS 可选项，用于判断要创建的事件是否存在  
EVENT event\_name 必选项，指定事件名称，event\_name的最大长度为64个字符，如果为指定event\_name，则默认为当前的MySQL用户名（不区分大小写）  
ON SCHEDULE schedule 必选项，这里的schedule用于定义执行的时间和时间间隔，在下面我们详细讲解  
ON COMPLETION \[NOT\] PRESERVE 可选项，配置事件执行完一次后的处理方式；  
当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在  
当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉.  
ENABLE、DISABLE、DISABLE ON SLAVE 可选项，用于指定事件的一种属性。  
ENABLE表示该事件是开启的，也就是调度器检查事件是否必选调用；  
DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；  
DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。  
COMMENT ‘comment' 可选项，用于定义事件的注释  
DO event\_body 必选项，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN…END复合结构  
schedule 调度时间配置语法：调度时间配置包括AT 和 EVERY两种

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
AT timestamp \[+ INTERVAL interval\] ...  
| EVERY interval  
\[STARTS timestamp \[+ INTERVAL interval\] ...\]  
\[ENDS timestamp \[+ INTERVAL interval\] ...\]  
  
  
\-- INTERVAL中包含的时间单位如下:  
\{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |  
WEEK | SECOND | YEAR\_MONTH | DAY\_HOUR | DAY\_MINUTE |  
DAY\_SECOND | HOUR\_MINUTE | HOUR\_SECOND | MINUTE\_SECOND\}  
1、创建 - 单次定时执行事件  
1  
AT TIMESTAMP 时间字符串 \[+ INTERVAL INTERVAL\]  
  AT TIMESTAMP表示该事件只执行一次，TIMESTAMP表示一个具体的时间点，后面可以加上一个时间间隔，表示在这个时间间隔后事件发生。\[+ INTERVAL INTERVAL\]表示延迟触发时间；

需要注意的是，TIMESTAMP和具体字符串连用，如果不是具体字符串（如CURRENT\_TIMESTAMP取当前时间等），则不加TIMESTAMP;  
示例 1：往demo\_1119表中插入一行数据，执行时间：2020-11-20 00:00:00

1  
2  
3  
CREATE EVENT demo\_event2  
ON SCHEDULE AT TIMESTAMP '2020-11-20 00:00:00'  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
结果查询：

1  
2  
3  
4  
5  
6  
7  
mysql> select \* from demo\_1119;  
\+-----+-----------+---------------------+  
| id | name | createTime |  
\+-----+-----------+---------------------+  
| 145 | 陈哈哈 | 2020-11-20 00:00:00 |  
\+-----+-----------+---------------------+  
9 rows in set (0.00 sec)  
示例 2：往demo\_1119表中插入一行数据，执行时间：当前时间往后5个小时；

1  
2  
3  
CREATE EVENT demo\_event2  
ON SCHEDULE AT CURRENT\_TIMESTAMP + INTERVAL 5 HOUR  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
2、创建 - 循环定时执行事件  
1  
EVERY INTERVAL \[STARTS TIMESTAMP\] \[ENDS TIMESTAMP\]  
EVERY表示循环执行该事件，其中STARTS子句用于指定开始时间；ENDS子句用于指定结束时间。

示例 1：从当前开始，每10秒往demo\_1119表中插入一行数据

1  
2  
3  
4  
CREATE EVENT demo\_event3  
ON SCHEDULE EVERY 10 SECOND  
ON COMPLETION PRESERVE  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
mysql> select \* from demo\_1119;  
\+-----+-----------+---------------------+  
| id | name | createTime |  
\+-----+-----------+---------------------+  
| 145 | 陈哈哈 | 2020-11-19 11:10:39 |  
| 146 | 陈哈哈 | 2020-11-19 11:10:49 |  
| 147 | 陈哈哈 | 2020-11-20 11:10:59 |  
| 148 | 陈哈哈 | 2020-11-20 11:11:09 |  
| 149 | 陈哈哈 | 2020-11-20 11:11:19 |  
| 150 | 陈哈哈 | 2020-11-20 11:11:29 |  
| 151 | 陈哈哈 | 2020-11-20 11:11:39 |  
\+-----+-----------+---------------------+  
9 rows in set (0.00 sec)  
示例 2：从2020-11-20 12:00:00开始，每10分钟往demo\_1119表中插入一行数据

1  
2  
3  
4  
CREATE EVENT demo\_event4  
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-11-20 12:00:00'  
ON COMPLETION PRESERVE  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
mysql> select \* from demo\_1119;  
\+-----+-----------+---------------------+  
| id | name | createTime |  
\+-----+-----------+---------------------+  
| 152 | 陈哈哈 | 2020-11-20 12:00:00 |  
| 153 | 陈哈哈 | 2020-11-20 12:10:00 |  
| 154 | 陈哈哈 | 2020-11-20 12:20:00 |  
| 155 | 陈哈哈 | 2020-11-20 12:30:00 |  
| 156 | 陈哈哈 | 2020-11-20 12:40:00 |  
\+-----+-----------+---------------------+  
5 rows in set (0.00 sec)  
示例 3：从当前时间一小时后开始，每10分钟往demo\_1119表中插入一行数据；已经测试过可用，我就不再贴冗余查询的数据咯~

1  
2  
3  
4  
CREATE EVENT demo\_event5  
ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT\_TIMESTAMP+INTERVAL 1 HOUR  
ON COMPLETION PRESERVE  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
示例4：从当前时间一天后开始，每1小时往demo\_1119表中插入一行数据，三天后结束

1  
2  
3  
4  
5  
6  
CREATE EVENT demo\_event5  
ON SCHEDULE EVERY 1 HOUR  
STARTS CURRENT\_TIMESTAMP+INTERVAL 1 DAY  
ENDS CURRENT\_TIMESTAMP+INTERVAL 3 DAY  
ON COMPLETION PRESERVE  
DO INSERT INTO \`demo\_1119\` (\`id\`, \`name\`, \`createTime\`) VALUES (null, '陈哈哈', NOW())  
示例5：每天零点定时清一下demo\_1119表数据

1  
2  
3  
4  
CREATE EVENT demo\_event5  
ON SCHEDULE EVERY 1 DAY STARTS '2020-11-20 00:00:00'  
ON COMPLETION PRESERVE  
DO TRUNCATE table \`demo\_1119\`  
3、修改事件  
修改事件语句跟创建语句如出一辙，语法如下：

1  
2  
3  
4  
5  
6  
7  
ALTER EVENT event\_name  
　　 \[ONSCHEDULE schedule\]  
　　 \[old\_NAME TO new\_NAME\]  
　　 \[ON COMPLETION \[NOT\] PRESERVE\]  
　　 \[COMMENT 'comment'\]  
　　 \[ENABLE | DISABLE\]  
　　 \[DO sql\_statement\]  
关闭事件任务： ALTER EVENT 事件名称 ON COMPLETION PRESERVE DISABLE;

开启事件任务：ALTER EVENT 事件名称 ON COMPLETION PRESERVE ENABLE;