MySQL事件调度器event的使用

共计 4410 个字符，预计需要花费 12 分钟才能阅读完成。

Q：假设，有一个需求，希望在某一个时刻系统调用一个 begin end 执行一下；十分钟以后执行一下 begin end。亦或有一个需求，每个多长时间周期性执行 begin end。那么这个时候该怎么办呢？

A：

在 Linux 里面可以使用 at、crontab 来实现上面的需求；MySQL 里面也有这样的方法，就是 event 对象。

也被称为 MySQL 事件调度器（Event Scheduler），可以在某一个时间点执行一个 SQL 语句或一个语句块（BEGIN … END）；或者每隔固定间隔重复执行。类似于 Linux 下的 at、crontab 或 Windows 下的 Task Scheduler。

那么如何使用 event，步骤如下：

1、开启数据库的 event 执行调度

> 查看是否开启定时器

mysql> show variables like ‘%event_scheduler%‘;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | OFF |
+—————–+——-+

> 开启

0：off

1：on

mysql> set global event_scheduler=1;
mysql> exit
Bye

[root@linuxidc ~]# mysql -uroot –p123

mysql> show variables like ‘%event_scheduler%‘;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| event_scheduler | ON |
+—————–+——-+

注意：

如果是设定事件计划为 0 或 OFF，即关闭事件计划进程的时候，不会有新的事件执行，但现有的正在运行的事件会执行到完毕。

对于线上环境来说，使用 even 时，注意在主库上开启定时器，从库上关闭定时器，event 触发所有操作均会记录 binlog 进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。

2、CREATE EVENT 创建

CREATE
[DEFINER = {user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment‘]
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}

详细解析：

①definer：指明该 event 的用户，服务器在执行该事件时，使用该用户来检查权限。

默认用户为当前用户，即 definer = current_user；

如果明确指明了 definer，则必须遵循如下规则：

1. 如果没有 super 权限，唯一允许的值就是自己当前用户，而不能设置为其他用户。

2. 如果具有 super 权限，则可以指定任意存在的用户；如果指定的用户不存在，则事件在执行时会报错。

②if not exists：如果事件已经存在，则不会创建，也不会报错。

③on schedule 子句：指定何时执行该事件，以及如何执行该事件

1）at timestamp 用于创建单次执行的事件，timestamp 执行事件执行的时间(如果指定的时间是过去的时间，则会产生一个 warning)，时间可以是具体的时间字符串或者是一个 datetime 类型的表达式(如 current_timestamp)：

如果要指定将来某个时间，直接使用 at timestamp，例：at ‘2017-08-08 08:08:08’；

如果要指定将来某个时间间隔，可利用 interval 关键字(interval 关键字可以进行组合，at timestamp + INTERVAL 2 HOUR、+ INTERVAL 30 MINUTE)

2）every 子句用于创建重复执行的事件，如果每分钟执行一次，则可以：EVERY 1 MINUTE。

当然，every 子句可以指定一个开始事件和结束时间，通过 STARTS 和 ENDS 关键字来表示，具体语法与前面类似

例如：EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK。

④通常情况下，如果一个事件过期已过期，则会被立即删除。但是，create event 定义中通过 on completion preserve 子句可以保留已过期的时间。

默认：ON COMPLETION NOT PRESERVE，也就是不保存

⑤默认情况下，enable on slave，事件一旦创建后就立即开始执行；可以通过 disable 关键字来禁用该事件。

⑥comment 子句用于给事件添加注释。

⑦do 子句用于指示事件需要执行的操作，可以是一条 SQL 语句，也可以是被 begin…end 包括的语句块，也可以在语句块中调用存储过程。

基本格式：

CREATE EVENT event_name

ON SCHEDULE <schedule>

DO <event_body>;

mysql> create event my_event  
    -> on schedule every 10 second 
    -> do update myschema.mytable set mycol = mycol + 1;

示例：建立一个计划任务，每分钟往表 t2 中添加数据（当前时间）

mysql> show events;
Empty set (0.02 sec)

mysql> create table t2(id int auto_increment primary key,t_time datetime);

mysql> delimiter $$
mysql> CREATE EVENT e_daily
-> ON SCHEDULE
-> EVERY 1 MINUTE
-> COMMENT ‘Saves total number of sessions then clears the table each day‘
-> DO
-> BEGIN
-> INSERT INTO t2 values (null,current_timestamp);
-> END $$
mysql> delimiter ;

……过一段时间……

mysql> select * from t2;
+—-+———————+
| id | t_time |
+—-+———————+
| 1 | 2017–04–04 18:02:38 |
| 2 | 2017–04–04 18:03:38 |
| 3 | 2017–04–04 18:04:38 |

…………

3、查看新建的计划任务

mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
+————+———————+
| EVENT_NAME | LAST_EXECUTED |
+————+———————+
| e_daily | 2017–04–04 18:02:38 |
+————+———————+

mysql> show events\G;
*************************** 1. row ***************************
Db: db1
Name: e_daily
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2017–04–04 18:02:38
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci

[root@linuxidc ~]# tail –1 /var/log/mysqld.log
2017–04-04T08:01:16.311514Z 12 [Note] Event Scheduler: scheduler thread started with id 12

通过查看 MySQL 日志，查看执行情况。

4、修改 alter event

ALTER
[DEFINER = {user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment‘]
[DO event_body]

alter event 语句可以修改事件的一个或多个属性，语法与 create event 语句完全相同，唯一不同的是可以对事件重命名，使用 RENAME TO 子句。

例如：

ALTER EVENT OLDDB.MYEVENT RENAME TO NEWDB.MYEVENT;

5、删 drop event