～ MySQL 事件

MySQL Event 事件是什么

在数据库管理中，经常要周期性的执行某一命令或 SQL 语句，于是 MySQL 5.1 版本以后就提供了事件，它可以很方便的实现 MySQL 数据库的计划任务，定期运行指定命令，使用起来非常简单方便。

事件（Event）也可称为事件调度器（Event Scheduler），是用来执行定时任务的一组 SQL 集合，可以通俗理解成 MySQL 中的定时器。一个事件可调用一次，也可周期性的启动。

事件可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL 的事件可以实现每秒钟执行一个任务，非常适合对实时性要求较高的环境，而操作系统的计划任务只能精确到每分钟一次。

事件和触发器类似，都是在某些事情发生时启动。当数据库启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

[1] 查看事件是否开启

在 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 的信息，则说明事件已经开启。

[2] 开启事件

开启事件主要通过以下两种方式实现。

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 中，可以通过 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 语句由多个子句组成，各子句的详细说明如下表所示。

在 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

一般情况下，不建议使用不标准（以上未加粗关键字）的时间单位。

代码示例：在 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 秒插入一条数据，这说明事件创建执行成功了。

查看事件

创建好事件后，用户可以通过以下 3 种方式来查看事件的状态信息：

1. 查看 mysql.event
2. 查看 information_schema.events
3. 切换到相应的数据库后执行 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)

以上参数说明如下表所示：

修改事件

在 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 语句中的子句与“创建事件”中讲解的基本相同，这里不再赘述。另外，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;

代码示例：删除事件 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)

事件，又可以称为事件调度器（Event Scheduler），是用来执行定时任务的一组 SQL 集合。简单来说，它可以很方便的实现 MySQL 数据库的计划任务，定期运行指定命令，一个事件可调用一次，也可周期性的启动。

事件和触发器类似，都是在某些事情发生时启动。当数据库启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以，事件也称为临时性触发器。

[ 查看事件是否开启 ] MySQL 中，查看事件是否开启：

在 MySQL 中，调度器 event_scheduler 负责调用事件一般情况下默认值为 OFF。

mysql> SHOW VARIABLES LIKE 'event_scheduler';    // 查看事件是否开启
// 或者 SELECT @@event_scheduler;

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

mysql> SHOW PROCESSLIST;  // 查看事件信息列表

+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  2 | root | localhost:56279 | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+----------+------------------+

如果参数 event_scheduler 的值为 ON，或在 PROCESSLIST 中显示了 event_scheduler 的信息，则说明事件已经开启。

[ 开启事件 ] MySQL 中，开启事件主要通过以下两种方式来实现：

（1）通过设置全局参数修改

可以使用 SET GLOBAL 命令设定全局变量 event_scheduler 的值，开启或关闭事件。但是，通过 SET GLOBAL 命令开启或关闭事件，MySQL 重启服务后事件又会回到原来的状态，如果想要始终开启或关闭事件，可以修改 MySQL 配置文件。

# 将 event_scheduler 参数的值设置为 ON，表示开启事件；设置为 OFF，则关闭事件。

SET GLOBAL event_scheduler = ON ;  // 设置，开启

SHOW VARIABLES LIKE 'event_scheduler';  // 查看

---

（2）更改配置文件

在 MySQL 配置文件中找到 [mysqld] 选项，然后在下面添加以下代码开启事件。

event_scheduler = ON

// 在配置文件中添加代码并保存文件后，重启 MySQL 服务才能生效。
// 通过该方法开启或关闭事件，重启 MySQL 服务后，不会回到原来的状态。

SHOW VARIABLES LIKE 'event_scheduler';

创建事件

在 MySQL 中，可以通过 CREATE EVENT 语句来创建事件，其语法格式如下：

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE 触发点    // 必选，用于定义事件执行的事件和事件间隔
    [ON COMPLETION [NOT] PRESERVE]    // 可选，是否循环执行
    [ENABLE | DISABLE | DISABLE ON SLAVE]    // 可选，默认为 enable，用于指定事件的一种属性
    [COMMENT 'comment']    // 可选，用于定义注释
    DO event_body;    // 必选，用于指定时间启动时执行的代码；如果是多条语句，可以使用 begin-end 复合结构

# 代码示例：创建一个名称为 e_test 的事件，用于每隔 5 秒向表 tb_eventtest 中插入一条数据

# 准备工作：创建 tb_eventtest 表

create table tb_eventtest(
	e_id int(11) primary key auto_increment,
	e_user varchar(20),
	create_time datetime
)

# 创建 e_test 事件

create event if not exists e_test on schedule every 5 second
on completion preserve
do insert into tb_eventtest(e_user,create_time) values ('MySQL',now());

创建事件后，可以查看 tb_eventtest 表中的数据；结果显示，系统每隔 5 秒插入一条数据，说明事件创建成功了。

select * from tb_eventtest

查看事件

事件创建好之后，用户可以通过以下 3 种方式来查看事件的状态信息：

• 查看 mysql.event
• 查看 information_schema.events（推荐）
• 切换到相应的数据库后执行 SHOW EVENTS

# 代码示例：查看 information_schema.events 表中的事件状态信息

SELECT * FROM information_schema.events limit 1

修改事件

在 MySQL 中，事件创建之后，可以使用 ALTER EVENT 语句修改其定义和相关属性。

ALTER EVENT event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

# 代码示例 1：修改 e_test 事件，让其每隔 30 秒向表 tb_eventtest 中插入一条数据

mysql> ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND
       ON COMPLETION PRESERVE
       DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW());

# 修改事件后，表 tb_eventtest 中的数据由原来的每 5 秒插入一条，变为每 30 秒插入一条

---

# 代码示例 2：使用 ALTER EVENT 语句还可以临时关闭一个已经创建的事件

# 临时关闭事件后，系统就不再继续向表 tb_eventtest 中插入数据了

ALTER EVENT e_test DISABLE;

# 为了确定事件已关闭，可以查询两次（每次间隔 1 分钟）tb_eventtest 表的数据

SELECT * FROM tb_eventtest;

删除事件

在 MySQL 中，可以使用 DROP EVENT 语句删除已经创建的事件。语法格式如下：

# 基本语法
DROP EVENT [IF EXISTS] event_name;

# 代码示例
drop event e_test