MySQL事件简介

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL事件。
MySQL的事件功能同Oracle里面的JOB，作为定时任务使用。

一.语法相关

创建事件语法:

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 COMPLETION [NOT] PRESERVE]： 可选项，默认是ON，运行后不删除，如果是ON COMPLETION NOT PRESERVE 则会删除。
[COMMENT ‘comment’] ：可选项，事件的备注信息。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
DO event_body: 需要执行的sql，代码块，存储过程

调度时间相关:
YEAR – 年
QUARTER – 季度
MONTH – 月
DAY – 天
HOUR – 小时
MINUTE – 分钟
WEEK – 周
SECOND – 秒
YEAR_MONTH – 年月
DAY_HOUR – 天小时
DAY_MINUTE – 天分钟
DAY_SECOND – 天秒
HOUR_MINUTE – 小时分钟
HOUR_SECOND – 小时秒
MINUTE_SECOND – 分钟秒

二.开启和关闭事件调度

MySQL 8.0 事件调度默认是开启的
查看事件调度是否开启:

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

如果没有开启，可以通过如下命令开启:

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

如果需要关闭，可以通过如下命令进行关闭:

SET GLOBAL event_scheduler = OFF; 
SET @@global.event_scheduler = OFF; 
SET GLOBAL event_scheduler = 0; 
SET @@global.event_scheduler = 0; 

开启事件

alter event event_name(事件名称) ON COMPLETION PRESERVE ENABLE;

关闭事件

alter event event_name(事件名称) ON COMPLETION PRESERVE DISABLE;

删除事件

DROP EVENT [IF EXISTS] event_name(事件名称)

三.案例

1.创建一个指定时间执行的事件
也可以把at now()修改为执行时间
例如: at ‘2020-05-25 23:00:00’
事件代码:

create event evt_now_userinfo_bak
on schedule 
at now() 
do create table user_info_bak as select * from user_info;

执行记录:

mysql> create event evt_now_userinfo_bak
    -> on schedule
    -> at now()
    -> do create table user_info_bak as select * from user_info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from user_info_bak;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)

mysql>

2.创建基于调度的事件

基于scheduler的事件。

创建每分钟执行一次的事件

create table t(id int,credate datetime);

create event evt_test2
on schedule every 1 minute 
starts now()
do insert into t(id,credate)  select ifnull(max(id),0),now() from t;

执行记录:

mysql> create event evt_test2
    -> on schedule every 1 minute
    -> starts now()
    -> do insert into t(id,credate)  select ifnull(max(id),0),now() from t;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> select * from t;
+------+---------------------+
| id   | credate             |
+------+---------------------+
|    0 | 2020-05-25 23:08:07 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+---------------------+
| id   | credate             |
+------+---------------------+
|    0 | 2020-05-25 23:08:07 |
+------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from t;
+------+---------------------+
| id   | credate             |
+------+---------------------+
|    0 | 2020-05-25 23:08:07 |
|    0 | 2020-05-25 23:09:07 |
+------+---------------------+
2 rows in set (0.00 sec)

查看事件:

mysql> show events\G;
*************************** 1. row ***************************
                  Db: test
                Name: evt_test2
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2020-05-25 23:08:07
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)