MySQL 定时计划任务 事件的使用
目录
查看事件是否开启
开启事件
1）通过设置全局参数修改
2）更改配置文件
MySQL如何创建并执行事件？
例 1
MySQL查看事件状态信息
MySQL修改和删除事件
例 1
例 2
删除事件
例 3
在数据库管理中，经常要周期性的执行某一命令或 SQL 语句，于是 MySQL 5.1 版本以后就提供了事件，它可以很方便的实现 MySQL 数据库的计划任务，定期运行指定命令，使用起来非常简单方便。
事件（Event）也可称为事件调度器（Event Scheduler），是用来执行定时任务的一组 SQL 集合，可以通俗理解成 MySQL 中的定时器。一个事件可调用一次，也可周期性的启动。
事件可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL 的事件可以实现每秒钟执行一个任务，非常适合对实时性要求较高的环境，而操作系统的计划任务只能精确到每分钟一次。
事件和触发器类似，都是在某些事情发生时启动。当数据库启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。
查看事件是否开启
在 MySQL 中，调度器 event_scheduler 负责调用事件。我们可以通过以下几种命令查看事件是否开启，一般情况下默认值为 OFF。SQL 命令和运行结果如下：
mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set, 1 warning (0.02 sec) mysql> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.00 sec) mysql> SHOW PROCESSLIST; +----+------+-----------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+------------------+ | 2 | root | localhost:56279 | NULL | Query | 0 | starting | SHOW PROCESSLIST | +----+------+-----------------+------+---------+------+----------+------------------+ 1 row in set (0.01 sec)
从结果可以看出，事件没有开启。因为参数 event_scheduler 的值为 OFF，并且在 PROCESSLIST 中查看不到 event_scheduler 的信息。如果参数 event_scheduler 的值为 ON，或者在 PROCESSLIST 中显示了 event_scheduler 的信息，则说明事件已经开启。
开启事件
开启事件主要通过以下两种方式实现。
1）通过设置全局参数修改
可以使用 SET GLOBAL 命令设定全局变量 event_scheduler 的值，开启或关闭事件。将 event_scheduler 参数的值设置为 ON，表示开启事件；设置为 OFF，则关闭事件。
例如，要开启事件可以在命令行窗口中输入以下命令。
mysql> SET GLOBAL event_scheduler = ON ; Query OK, 0 rows affected (0.06 sec) mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set, 1 warning (0.01 sec)
结果显示，event_scheduler 的值为 ON，表示事件已经开启。
通过 SET GLOBAL 命令开启或关闭事件，MySQL 重启服务后事件又会回到原来的状态，如果想要始终开启或关闭事件，可以修改 MySQL 配置文件。
2）更改配置文件
在 MySQL 配置文件中找到 [mysqld] 选项，然后在下面添加以下代码开启事件。
event_scheduler = ON
在配置文件中添加代码并保存文件后，重启 MySQL 服务才能生效。
通过该方法开启或关闭事件，重启 MySQL 服务后，不会回到原来的状态。例如，此时重启 MySQL 服务器，然后查看事件是否开启。
mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set, 1 warning (0.01 sec)
结果显示，参数 event_scheduler 的值为 ON，表示已经开启。
MySQL如何创建并执行事件？
在 MySQL 中，可以通过 CREATE EVENT 语句来创建事件，其语法格式如下：
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
从上面的语法可以看出，CRATE EVENT 语句由多个子句组成，各子句的详细说明如下表所示。
| 子句 | 说明 |
|---|---|
| DEFINER | 可选 用于定义事件执行时检查权限的用户 |
| IF NOT EXISTS | 可选 用于判断要创建的事件是否存在 |
| EVENT event_name | 必选 用于指定事件名称，event_name 的最大长度为 64 个字符 如果未指定 event_name，则默认为当前的 MySQL 用户名（不区分大小写） |
| ON SCHEDULE schedule | 必选 用于定义执行的时间和时间间隔 schedule 表示触发点 |
| ON COMPLETION [NOT] PRESERVE | 可选 用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE |
| ENABLE | DISABLE | DISABLE ON SLAVE | 可选，用于指定事件的一种属性。 其中，关键字 ENABLE 表示该事件是活动的，即调度器检查事件是否必须调用； 关键字 DISABLE 表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该调用； 关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。 如果不指定以上 3 个选项中的任何一个，默认为 ENABLE |
| COMMENT 'comment' | 可选，用于定义事件的注释 |
| DO event_body | 必选 用于指定事件启动时所要执行的代码，可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。 如果包含多条语句，则可以使用 BEGIN..END 复合结构 |
在 ON SCHEDULE 子句中，参数 schedule 的值为一个 AT 子句，用于指定事件在某个时刻发生，其语法格式如下：
AT timestamp [+ INTERVAL interval]...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp[+ INTERVAL interval]...]
参数说明如下：
- timestamp：一般用于只执行一次，表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
- EVERY 子句：用于事件在指定时间区间内每隔多长时间发生一次，其中 STARTS 子句用于指定开始时间；ENDS 子句用于指定结束时间。
- interval：一般用于周期性执行，表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示 4 周，使用“'1:10'HOUR_MINUTE”表示 1 小时 10 分钟。间隔的长短用 DATE_ADD() 函数支配。
interval 参数可以是以下值：
YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
一般情况下，不建议使用不标准（以上未加粗关键字）的时间单位。
例 1
在 test 数据库中创建一个名称为 e_test 的事件，用于每隔 5 秒向表 tb_eventtest 中插入一条数据。
创建 tb_eventtest 表，SQL 语句和运行结果如下：
mysql> CREATE TABLE tb_eventtest(
-> id INT(11) PRIMARY KEY AUTO_INCREMENT,
-> user VARCHAR(20),
-> createtime DATETIME);
Query OK, 0 rows affected (0.07 sec)
创建 e_test 事件，SQL 语句和运行结果如下：
mysql> CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 5 SECOND
-> ON COMPLETION PRESERVE
-> DO INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW());
Query OK, 0 rows affected (0.04 sec)
创建事件后，查询 tb_eventtest 中的数据，SQL 语句和运行结果如下：
mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user | createtime | +----+-------+---------------------+ | 1 | MySQL | 2020-05-21 10:41:39 | | 2 | MySQL | 2020-05-21 10:41:44 | | 3 | MySQL | 2020-05-21 10:41:49 | | 4 | MySQL | 2020-05-21 10:41:54 | +----+-------+---------------------+ 4 rows in set (0.01 sec)
从结果可以看出，系统每隔 5 秒插入一条数据，这说明事件创建执行成功了。
MySQL查看事件状态信息
创建好事件后，用户可以通过以下 3 种方式来查看事件的状态信息：
- 查看 mysql.event
- 查看 information_schema.events
- 切换到相应的数据库后执行 SHOW EVENTS;
以上方式的运行结果基本一致，这里就不一一演示了。下面查看 information_schema.events 表中的事件状态信息。SQL 语句和运行结果如下：
mysql> SELECT * FROM information_schema.events limit 1\G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: test
EVENT_NAME: e_test
DEFINER: root@localhost
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STARTS: 2020-05-21 10:41:39
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: PRESERVE
CREATED: 2020-05-21 10:41:39
LAST_ALTERED: 2020-05-21 10:41:39
LAST_EXECUTED: 2020-05-21 12:38:54
EVENT_COMMENT:
ORIGINATOR: 1
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
DATABASE_COLLATION: utf8_unicode_ci
1 row in set (0.08 sec)
以上参数说明如下表所示：
| 参数名 | 说明 |
|---|---|
| EVENT_CATALOG | 事件存放目录，一般情况下，值为 def，不建议修改 |
| EVENT_SCHEMA | 事件所在的数据库 |
| EVENT_NAME | 事件名称 |
| DEFINER | 事件的定义者 |
| TIME_ZONE | 事件使用的时区，默认是 SYSTEM，不建议修改 |
| EVENT_BODY | 一般情况下，值为 SQL，不建议修改 |
| EVENT_DEFINITION | 该事件的内容，可以是具体的 INSERT 等 SQL，也可以是一个调用的存储过程 |
| EVENT_TYPE | 事件类型，这个参数比较重要，在定义时指定 有两个值：RECURRING 和 ONE TIME RECURRING 表示只要符合条件就会重复执行，RECURRING 类型的事件一般为 NULL，表示该事件的预计执行时间 ONE TIME 只会调用 EXECUTE_AT，针对 one-time 类型的事件有效 |
| INTERVAL_VALUE | 针对 RECURRING 类型的事件有效，表示执行间隔长度 |
| INTERVAL_FIELD | 针对 RECURRING 类型的事件有效，表示执行间隔的单位，一般是 SECOND，DAY 等值，可参考创建语法 |
| SQL_MODE | 当前事件采用的 SQL_MODE |
| STARTS | 针对 RECURRING 类型的事件有效，表示一个事件从哪个时间点开始执行，和 one-time 的 EXECUTE_AT 功能类似。 为 NULL 时表示一符合条件就开始执行 |
| ENDS | 针对 RECURRING 类型的事件有效，表示一个事件到了哪个时间点后不再执行，如果为 NULL 就是永不停止 |
| STATUS | 一般有三个值，ENABLED、DISABLED 和 SLAVESIDE_DISABLED |
| ON_COMPLETION | 只有两个值，PRESERVE 和 NOT PRESERVE |
| CREATED | 事件的创建时间 |
| LAST_ALTERED | 事件最近一次被修改的时间 |
| LAST_EXECUTED | 事件最近一次执行的时间，如果为 NULL 表示从未执行过 |
| EVENT_COMMENT | 事件的注释信息 |
| ORIGINATOR | 当前事件创建时的 server-id，用于主从上的处理，比如 SLAVESIDE_DISABLED |
| CHARACTER_SET_CLIENT | 事件创建时的客户端字符集 |
| COLLATION_CONNECTION | 事件创建时的连接字符校验规则 |
| DATABASE_COLLATION | 事件创建时的数据库字符集校验规则 |
MySQL修改和删除事件
在 MySQL 中，事件创建之后，可以使用 ALTER EVENT 语句修改其定义和相关属性。
修改事件的语法格式如下：
ALTER EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
ALTER EVENT 语句中的子句与MYSQL创建事件，一节中讲解的基本相同，这里不再赘述。另外，ALTER EVENT 语句还有一个用法就是让一个事件关闭或再次让其活动。
例 1
修改 e_test 事件，让其每隔 30 秒向表 tb_eventtest 中插入一条数据，SQL 语句和运行结果如下所示：
mysql> ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND
-> ON COMPLETION PRESERVE
-> DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW());
Query OK, 0 rows affected (0.04 sec)
mysql> TRUNCATE TABLE tb_eventtest;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM tb_eventtest;
+----+-------+---------------------+
| id | user | createtime |
+----+-------+---------------------+
| 1 | MySQL | 2020-05-21 13:23:49 |
| 2 | MySQL | 2020-05-21 13:24:19 |
+----+-------+---------------------+
2 rows in set (0.00 sec)
由结果可以看出，修改事件后，表 tb_eventtest 中的数据由原来的每 5 秒插入一条，变为每 30 秒插入一条。
使用 ALTER EVENT 语句还可以临时关闭一个已经创建的事件。
例 2
临时关闭事件 e_test 的具体代码如下所示：
mysql> ALTER EVENT e_test DISABLE;
Query OK, 0 rows affected (0.00 sec)
查询 tb_eventtest 表中的数据，SQL 语句如下：
SELECT * FROM tb_eventtest;
为了确定事件已关闭，可以查询两次（每次间隔 1 分钟）tb_eventtest 表的数据，SQL 语句和运行结果如下所示：
mysql> TRUNCATE TABLE tb_eventtest;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM tb_eventtest;
Empty set (0.00 sec)
mysql> SELECT * FROM tb_eventtest;
Empty set (0.00 sec)
由结果可以看出，临时关闭事件后，系统就不再继续向表 tb_eventtest 中插入数据了。
删除事件
在 MySQL 中，可以使用 DROP EVENT 语句删除已经创建的事件。语法格式如下：
DROP EVENT [IF EXISTS] event_name;
例 3
删除事件 e_test，SQL 语句和运行结果如下：
mysql> DROP EVENT IF EXISTS e_test; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM information_schema.events \G Empty set (0.00 sec)