MySQL 事件

恋喵大鲤鱼

发布于 2023-10-12 16:17:31

2870

发布于 2023-10-12 16:17:31

文章被收录于专栏：C/C++基础

1.简介

MySQL 事件（Event）事件是根据时间表运行的任务，类似于 Unix crontab 和 Windows 定时任务。

一个事件可调用一次，也可周期性地启动。它由一个特定的线程来管理，也就是所谓的事件调度器（Event Scheduler）。MySQL 的事件调度器可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下非常实用。

事件和触发器类似，都是在某些事情发生的时候启动。MySQL 事件有时候也称为“时间触发器”，因为它们是基于特定时间点触发的程序。

MySQL 事件可以用于许多场景，例如优化数据库表、归档数据、生成复杂查询报告、清理日志文件等。

MySQL 存储程序包括存储例程、触发器和事件，存储对象包括存储程序和视图。

2.事件调度器

事件调度器负责管理和执行事件，它本质上是一个特殊的线程。我们可以通过 SHOW PROCESSLIST 命令查看事件调度器线程的信息和状态：

SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 21
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 8
   User: root
   Host: localhost:59956
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

SHOW PROCESSLIST 命令返回一个结果集，其中包括以下信息：

Id： 客户端连接的唯一标识符。
User： 连接的数据库用户。
Host： 连接的主机或IP地址。
db： 正在访问的数据库（如果有）。
Command： 客户端正在执行的SQL命令，如SELECT、UPDATE、INSERT等。
Time： 查询已经运行的时间（以秒为单位）。
State： 查询的当前状态，例如正在执行、锁定等。
Info： 查询的详细信息，包括SQL查询文本。

如果 User 字段为 event_scheduler，代表事件调度器线程，如果没有显示该记录表示没有启动事件调度器。

我们也可以查看表 information_schema.processlist 查看事件调度器线程的信息和状态。

SELECT * FROM information_schema.processlist;

MySQL 通过全局系统变量 event_scheduler 控制是否允许和启动事件调度器，它有三种可能的取值：

ON，默认设置，表示启用事件调度器线程，负责事件的调度和执行。
OFF，关闭事件调度器线程，SHOW PROCESSLIST 命令不再显示相关信息，计划事件不再执行。
DISABLED，禁用事件调度器线程，不但停止了调度器线程，而且无法通过 ON 或者 OFF 设置它的状态。

使用 SHOW 命令可以查看当前的 event_scheduler 设置：

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set, 1 warning (0.06 sec)

只要状态不是 DISABLED，就可以通过 SET 语句启动或者关闭事件调度器。

-- 启动事件调度器
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = 1;

-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;
SET @@GLOBAL.event_scheduler = 0;

只有在启动服务的时候才能够将事件调度器设置为 DISABLED，运行时无法从 ON 或者 OFF 设置为 DISABLED；同样也无法在运行时从DISABLED 修改为其他状态。在启动服务时指定以下命令行参数可以禁用事件调度器：

--event-scheduler=DISABLED

或者在 MySQL 配置文件中的 [mysqld] 部分增加以下配置项：

event_scheduler=DISABLED

默认情况下，我们不需要进行任何配置就可以使用 MySQL 计划事件功能。

3.创建事件

MySQL 提供了 CREATE EVENT 语句，用于创建计划事件：

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

其中，event_name 是计划事件的名称；ON SCHEDULE 用于指定事件的执行计划，也就是执行的时间和频率；COMMENT 用于为事件增加注释信息；event_body 包含了事件执行的 SQL 语句，可以是简单语句或者由 BEGIN … END 组成的复合语句，甚至存储过程调用。

对于执行计划 schedule，AT timestamp 用于创建一次性执行的事件，指定了该事件发生的具体时间。

CREATE TABLE t_event(id int auto_increment primary key, ts timestamp);

CREATE EVENT event1
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
DO
INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

事件 event1 将会在创建的 10 秒之后插入一条记录到表 t_event 中，随后查询该表可以看到相应的记录：

SELECT * FROM t_event;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2023-09-25 11:31:29 |
+------+---------------------+
1 row in set (0.00 sec)

默认情况下，一次性事件在执行完成后自动删除。如果想要保留事件定义，可以使用 ON COMPLETION PRESERVE 选项：

CREATE EVENT event1
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
ON COMPLETION PRESERVE
DO
INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

如果没有显式指定，创建事件时默认使用 ON COMPLETION NOT PRESERVE 选项。

EVERY interval 子句可以用于创建一个重复执行的事件，它指定了事件的执行频率和有效期限。

CREATE EVENT event2
ON SCHEDULE EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS '2023-12-31 23:59:59'
DO
INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

事件 event2 从创建时开始每 5 分钟执行一次。并通过 STARTS 和 ENDS 子句用于定义事件的有效期限，省略时默认从事件创建时开始，并且无限期执行。

默认情况下，事件创建之后处于激活状态。我们也可以使用 DISABLE 选项创建一个被禁用的事件：

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

使用 DISABLE 选项创建的事件不会被执行，除非将状态修改为 ENABLE。DISABLE ON SLAVE是指在主备复制的数据库服务器中，在备机上也创建该定时器，但是不执行。

4.查看事件

使用 SHOW EVENTS 语句可以查看当前数据库中的计划事件：

SHOW EVENTS
    [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

例如查询前文创建的两个事件：

SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: hrdb
                Name: event1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2023-09-25 21:32:45
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: DISABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
                  Db: hrdb
                Name: event2
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: MINUTE
              Starts: 2023-10-07 21:35:07
                Ends: 2023-12-31 23:59:59
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.03 sec)

另外，MySQL 系统表 INFORMATION_SCHEMA.EVENTS 中存储了更加详细的事件信息。

也可以使用 SHOW CREATE EVENT 语句查看指定事件的定义。

SHOW CREATE EVENT event_name

例如：

SHOW CREATE EVENT event1\G
*************************** 1. row ***************************
               Event: event1
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE AT '2020-10-07 21:32:45' ON COMPLETION PRESERVE DISABLE DO INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

5.修改事件

如果想要修改计划事件的属性和定义，可以使用 ALTER EVENT 语句：

ALTER
    [DEFINER = user]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

ALTER EVENT 语句支持的选项和 CREATE EVENT 语句相同，另外它可以通过 RENAME TO 子句修改事件的名称。例如：

ALTER EVENT event2
RENAME TO repeat_event
COMMENT 'This is a repeat event.';

6.删除事件

如果想要删除一个存在的计划事件，可以使用 DROP EVENT 语句：

DROP EVENT [IF EXISTS] event_name

默认情况下，已经过期的事件会自动删除，除非设置了 ON COMPLETION PRESERVE 选项。

参考文献

25.4 Using the Event Scheduler 13.1.13 CREATE EVENT Statement 13.7.7.18 SHOW EVENTS Statement 13.7.7.7 SHOW CREATE EVENT Statement 13.1.3 ALTER EVENT Statement 13.1.25 DROP EVENT Statement 《MySQL 入门教程》第 34 篇计划任务

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-09-25，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

MySQL 事件

MySQL 事件

1.简介

2.事件调度器

3.创建事件

4.查看事件

5.修改事件

6.删除事件

参考文献

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐