MySQL · 事件

发表于 | 更新于 | 分类于 | 阅读次数：

简介

MySQL 使用 EVENTS 来处理计划任务，类似于 Linux 上的 cron，默认情况下，事件未启用。

权限：EVENT

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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}

参数说明

Property Value
Command-Line Format --event-scheduler[=value]
System Variable event_scheduler
Scope Global
Dynamic Yes
Type Enumeration
Default Value OFF
Valid Values ON、OFF、DISABLED 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 参数 event_scheduler
mysql> set global event_scheduler = on;
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

# 创建事件
CREATE EVENT Event_Archive_Daily
ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00'
ON COMPLETION PRESERVE
ENABLE
DO call sp_archive();

# 查询事件
mysql> show create event Event_Archive_Daily\G
*************************** 1. row ***************************
               Event: Event_Archive_Daily
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `Event_Archive_Daily` ON SCHEDULE EVERY 1 DAY STARTS '2019-11-01 02:00:00' ON COMPLETION PRESERVE ENABLE DO call sp_archive()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)