MySQL的定时任务利器——事件调度器event

原创 Tonyhacks 2024-09-26

365

MySQL 8.0 的事件调度器（Event Scheduler）是一个强大的工具，它允许用户在指定的时间点或时间间隔内自动执行SQL语句、事务或存储过程。这对于需要定期执行维护任务、数据清理、报表生成等操作的数据库来说，具有非常重要的意义。本文将深入探讨MySQL 8.0 事件调度器的特性、使用方法以及注意事项。

启用事件调度器

在 MySQL 8.0 中，事件调度器默认是禁用的。如果需要使用事件调度器，首先要确保它被启用。用户需要super权限才能启动事件调度器可以通过以下命令查看当前事件调度器的状态：

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

如果返回的值为 OFF，则可以通过以下命令启用：

SET GLOBAL event_scheduler = ON;

注意，这种方式只在 MySQL 服务重启之前有效。如果希望事件调度器永久启用，可以在 MySQL 的配置文件 my.cnf 或 my.ini 中添加以下配置：

event_scheduler=ON

创建事件调度器

用户需要event权限才能创建事件调度器

GRANT EVENT ON database.* TO 'username'@'localhost';

创建事件调度器格式

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}

示例

CREATE DEFINER = 'admin'@'localhost' EVENT IF NOT EXISTS clear_logs
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'This event clears old logs every month'
DO
DELETE FROM log_table WHERE log_date < NOW() - INTERVAL 1 YEAR;

schedule: 调度的时间表达式：

AT NOW() 立即执行

AT TIMESTAMP(‘2024-09-27 10:00:00’) 指定时间执行

EVERY 1 MINUTE 每分钟

EVERY 1 DAY 每天

EVERY 1 MONTH 每个月

从当前时间起，1小时后开始，每5分钟执行一次：

ON SCHEDULE EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

从下个月的1号开始每个月1号早上5点执行

STARTS TIMESTAMP(‘2024-10-01 05:00:00’) – 假设这是下个月的第一天
EVERY 1 MONTH

ON COMPLETION: 指定事件执行完成后如何处理，preserve 表示保留事件以便下次执行，not preserve 表示执行完后删除事件。

sql_statement: 要执行的SQL语句、事务或存储过程调用。

实践一：创建一个简单的事件调度器

创建并查看事件调度器

mysql> CREATE EVENT IF NOT EXISTS truncate_log_sbtest1
    -> ON SCHEDULE EVERY 10 MINUTE  
    -> STARTS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
    -> DO  
    ->   TRUNCATE TABLE sbtest.sbtest1;
Query OK, 0 rows affected (0.85 sec)

mysql> show events\G
*************************** 1. row ***************************
                  Db: sbtest
                Name: truncate_log_sbtest1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: MINUTE
              Starts: 2024-09-25 14:46:59
                Ends: 2024-10-02 14:41:59
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb3_bin
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (1 min 19.77 sec)

查看事件调度器执行情况

mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
+----------------------+---------------------+
| EVENT_NAME           | LAST_EXECUTED       |
+----------------------+---------------------+
| truncate_log_sbtest1 | 2024-09-25 14:46:59 |
+----------------------+---------------------+
1 row in set (0.94 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

删除事件调度器

mysql> drop event truncate_log_sbtest1;
Query OK, 0 rows affected (0.00 sec)

实践二：执行完事件删除事件调度器的调度器

创建执行完事件删除事件调度器的调度器，注意：ON COMPLETION NOT PRESERVE

mysql> select * from sbtest.sbtest1;
Empty set (0.00 sec)

mysql> CREATE EVENT IF NOT EXISTS insert_sbtest1
    -> ON SCHEDULE AT TIMESTAMP('2024-09-26 10:23:00') 
    -> ON COMPLETION  NOT PRESERVE
    -> DO  
    ->   insert into sbtest.sbtest1 (k,c,pad) values (4,'Tonyhacks','Tonyhacks');
Query OK, 0 rows affected (0.03 sec)

mysql> show events\G
*************************** 1. row ***************************
                  Db: sbtest
                Name: insert_sbtest1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2024-09-26 10:23:00
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb3_bin
1 row in set (0.00 sec)

mysql> select * from  information_schema.EVENTS\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: sbtest
          EVENT_NAME: insert_sbtest1
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: insert into sbtest.sbtest1 (k,c,pad) values (4,'Tonyhacks','Tonyhacks')
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2024-09-26 10:23:00
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2024-09-26 10:20:58
        LAST_ALTERED: 2024-09-26 10:20:58
       LAST_EXECUTED: NULL
       EVENT_COMMENT: 
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb3_bin
1 row in set (0.00 sec)

这个调度器是2024-09-26 10:23:00向sbtest.sbtest1插入一条数据，执行完就立即删除调度器。

到了10:23查看执行情况

mysql> select * from sbtest.sbtest1;
+----+---+-----------+-----------+
| id | k | c         | pad       |
+----+---+-----------+-----------+
|  1 | 4 | Tonyhacks | Tonyhacks |
+----+---+-----------+-----------+
1 row in set (0.01 sec)

mysql> show events\G
Empty set (0.00 sec)

mysql> select * from  information_schema.EVENTS\G
Empty set (0.01 sec)

备份包含事件调度的数据库

mysqldump -u username -p --events database_name > backup_file.sql

事件调度器的注意事项

事件调度器状态： 默认情况下，事件调度器是关闭的。需要使用 SET GLOBAL event_scheduler = ON; 来开启。
时区设置： 确保MySQL服务器的时区设置正确，否则事件执行的时间可能不准确。
并发问题： 如果多个事件同时访问同一个表，可能会产生并发问题。可以考虑使用锁机制或者调整事件的执行顺序。
性能影响： 定时任务的执行可能会影响数据库的性能，尤其是当任务比较耗时或者并发量比较大时。可以考虑将耗时的任务拆分成多个小任务，或者使用异步执行的方式。

最后修改时间：2024-09-26 10:49:28

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者