mysql originator_MySQL任务调度工具EVENT-阿里云开发者社区

概述

MySQL的EVENT 是根据计划运行的任务，类似于Linux上的crontab 任务

主要具有以下属性

mysql中，定时事件的对象是全局唯一的

event根据时间表来执行操作，包含一个SQL操作，也可以使用begin,end 实现多个SQL的复合执行

event 的权限(创建修改和删除)进行控制赋予

可以使用SQL语句设置或修改事件的许多属性。这些属性包括事件的名称，时间，持久性(即是否在其计划到期后保留)，状态(启用或禁用)，要执行的操作以及为其分配的架构

如何开启

查看系统变量，ON是表示已启动，OFF表示已停止(默认值)，也可以为DISABLED，表示无法运行，不推荐使用这个参数值，状态没法在运行是进行修改

mysql> show variables like '%event_scheduler%';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | ON |

+-----------------+-------+

1 row in set (0.00 sec)

只要是开启了这个功能，就可以在看到此模块的线程，线程运行并执行所有定时任务

mysql> show processlist\G;

*************************** 1. row ***************************

Id: 454

User: event_scheduler

Host: localhost

db: NULL

Command: Daemon

Time: 45647

State: Waiting for next activation

Info: NULL

开启命令，如下，关闭也是同样

SET GLOBAL event_scheduler = ON;

SET @@GLOBAL.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@GLOBAL.event_scheduler = 1;

另外，因为event是全局变量，未指定全局来设置，会导致报错

mysql> set @@event_scheduler = OFF;

ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL

权限赋予

EVENT 控制事件创建、修改和删除，权限由grant赋予，基本格式如下

mysql> GRANT EVENT ON test.* TO test;

Query OK, 0 rows affected (0.04 sec)

由于event是全局架构级别的权限，所以不能针对某个表进行赋权

mysql> GRANT EVENT ON test.test01 TO test;

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

很显然的，包含在events里面的SQL语句，定时任务执行的用户一定需要有相关的权限，否则定时任务触发，但是任务执行也会失败，此时可以在mysql日志里可以看到信息

## 相关信息

创建event的基本语法

CREATE EVENT

CREATE EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement;

schedule:

AT timestamp [+ INTERVAL interval]

| EVERY interval [STARTS timestamp] [ENDS timestamp]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

系统表信息，会记录每个创建的定时任务

mysql> select * from information_schema.events\G;

*************************** 1. row ***************************

EVENT_CATALOG: def --永远是def

EVENT_SCHEMA: test --所在是的数据库名

EVENT_NAME: event_test_insert --event名

DEFINER: root@% --创建这个event的用户

TIME_ZONE: SYSTEM --调度时区，默认是SYSTEM

EVENT_BODY: SQL --主体语言

EVENT_DEFINITION: INSERT INTO test.event_test VALUES (CURRENT_TIMESTAMP) --需要执行的部分，就是DO 的部分内容

EVENT_TYPE: RECURRING --事件重复类型，一次(TRANSIENT)重复(REPEATING)

EXECUTE_AT: NULL --如果事件的计时由EVERY子句而不是AT子句确定(也就是说，事件类型为repeating)，则此列的值为NULL。

INTERVAL_VALUE: 1 --every的数字部分，对于一次性事件，此列为null

INTERVAL_FIELD: SECOND --every的单位部分，用于管理事件的时间，此处为秒

SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION --在创建或更改事件并在其下执行事件时有效的SQL模式

STARTS: 2020-03-19 13:51:52 --starts子句的事件

ENDS: NULL --事件定义时包含了ENDS子句的循环事件

STATUS: DISABLED --ENABLE, DISABLE或SLAVESIDE_DISABLED 三个值之一，表示event的启用状态

ON_COMPLETION: PRESERVE

CREATED: 2020-03-19 13:51:52 --事件创建时间

LAST_ALTERED: 2020-03-19 13:53:10 --最后修改时间

LAST_EXECUTED: 2020-03-19 13:53:10 --最后一次执行时间

EVENT_COMMENT: --备注

ORIGINATOR: 0 --服务器id，默认为0

CHARACTER_SET_CLIENT: utf8 --字符

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: latin1_swedish_ci

除了查看系统表，可以有以下几种方式来查看已经创建的events，输出信息基本一致

SHOW EVENTS;

SHOW CREATE EVENT xxxx;

SELECT * FROM MYSQL.EVENTS;

测试创建

创建一张测试表

mysql> CREATE TABLE kmtest(timeline varchar(20));

Query OK, 0 rows affected (0.02 sec)

定时循环任务

计划每一秒插入表中当前时间，如下

mysql> CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);

Query OK, 0 rows affected (0.00 sec)

mysql> select * from kmtest limit 2;

+---------------------+

| timeline |

+---------------------+

| 2020-03-20 14:54:07 |

| 2020-03-20 14:54:08 |

+---------------------+

2 rows in set (0.00 sec)

停止当前的定时任务

mysql> ALTER EVENT event_kmtest ON COMPLETION PRESERVE DISABLE;

Query OK, 0 rows affected (0.00 sec)

如果不再需要，可以删除任务

mysql> drop event event_kmtest;

Query OK, 0 rows affected (0.00 sec)

定某一时刻执行

创建在某一时刻执行一次任务

mysql> CREATE EVENT event_kmtest ON SCHEDULE AT '2020-03-20 15:20:20' DO set @time= CURRENT_TIMESTAMP;INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);

Query OK, 0 rows affected (0.00 sec)

过了这个时间点再来查看

mysql> select * from kmtest;

+---------------------+

| timeline |

+---------------------+

| 2020-03-20 15:20:20|

+---------------------+

1 row in set (0.00 sec)

在等待执行的这段时间可以看到event的执行信息，到期执行完了就自动去除了，可以看到type是one time

mysql> show events\G;

*************************** 1. row ***************************

Db: test

Name: event_kmtest

Definer: root@%

Time zone: SYSTEM

Type: ONE TIME

多个SQL

一个events包含多个SQL，在使用的过程中，可能有时需要执行的SQL不仅仅是一个，有可能是多个，此时就需要使用定界符和begin end 来进行创建。例子如下：

delimiter |

CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND

DO

begin

set @time= CURRENT_TIMESTAMP;

INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);

end |

delimiter;

PS：语法是没错的，但是我在mysql的客户端命令行去执行时，会默认为换行输出，我也不知道啥原因，利用一些工具来执行是没问题的