MySQL事件(Event)与事件调度器(Event Scheduler)介绍

前言

事件调度器是在 MySQL 5.1 中新增的另一个特色功能,可以作为定时任务调度器(例如:删除记录、对数据进行汇总等等),取代部分原先只能用操作系统任务调度器才能完成的定时功能。而且 MySQL 的事件调度器可以实现每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用(例如:股票、赔率、比分等)就非常适合。

事件调度器是定时触发执行的,在这个角度上也可以称作是”临时的触发器”。触发器只是针对某个表产生的事件执行一些语句,而事件调度器则是在某一个(间隔)时间执行一些语句。事件是由一个特定的线程来管理的,也就是所谓的”事件调度器”。启用事件调度器后,拥有 SUPER 权限的账户执行 SHOW PROCESSLIST 就可以看到这个线程了。通过设定全局变量event_scheduler 的值即可动态的控制事件调度器是否启用。

开启event_scheduler

1、临时开启(以下方式都可以)

1
2
3
4
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler =ON;
SET GLOBAL event_scheduler = 1; ##0代表关闭
SET @@global.event_scheduler = 1;

2、永久开启

1)在my.cnf中的[mysqld]部分添加如下内容,然后重启mysql
1
event_scheduler=ON
2)编译的时候开启
1
mysqld … –event_scheduler=ON

创建定时任务的语法

语法:

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

参数说明:

1
2
3
4
5
6
7
- DEFINER:指定可执行该定时器的MySQL账号,user的格式是’user_name’@’host_name’,CURRENT_USER或CURRENT_USER(),注意,单引号是需要在语句中输入的。如果不指定,默认是DEFINER = CURRENT_USER。
- event_name:事件名称,最大64个字符,不区分大小写,MyEvent和myevent是一样的,命名规则和其他MySQL对象是一样的。
- ON SCHEDULE schedule:定义执行的时间和时间间隔。
- [ON COMPLETION [NOT] PRESERVE]:可选,preserve是保持的意思,这里是说这个定时器第一次执行完成以后是否还需要保持,如果是NOT PRESERVE,该定时器只执行一次,完成后自动删除事件;没有NOT,该定时器会多次执行,可以理解为这个定时器是持久性的。默认是NOT PRESERVE。
- [ENABLE | DISABLE | DISABLE ON SLAVE]:可选,是否启用该事件,ENABLE-启用,DISABLE-禁用,可使用alter event语句修改该状态。DISABLE ON SLAVE是指在主备复制的数据库服务器中,在备机上也创建该定时器,但是不执行。
- COMMENT: 注释,必须用单引号括住。
- DO event_body:事件要执行的SQL语句,可以是一个SQL,也可以是使用BEGIN和END的复合语句,和存储过程相同。

创建定时任务

示例一

1小时后执行该事件:
1
2
3
4
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;

示例二

每小时执行一次指定操作:
1
2
3
4
5
6
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'Clears out sessions table each hour.'
DO
DELETE FROM site_activity.sessions;

示例三

以下是个稍微复杂点的例子,用到了复合语句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter $$

CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END $$

delimiter ;

存储过程中用到的变量、错误处理和流程控制(while,if……)语句在eventbody中都可以使用,有一点区别是,事件不能接收参数。如果需要,可以在事件中调用存储过程,通过存储过程传参数。

修改定时任务

语法:

1
2
3
4
5
6
7
8
9
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 'string'] ##更改注释
[DO event_body] ##更改计划执行体

示例:

原始的Event定义如下:
1
2
3
4
5
6
CREATE EVENT myevent
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
下面的语句,将myevent从当前开始每6个小时执行,改为4个小时候每隔12个小时执行:
1
2
3
4
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
在一个Alter Event语句中,可以修改Event的多个属性,下面的语句将myevent修改成1天后删除mytable表中的所有数据,只执行一次:
1
2
3
4
5
ALTER EVENT myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
禁用事件:
1
ALTER EVENT myevent DISABLE;
修改事件名称:
1
ALTER EVENT myevent RENAME TO yourevent;
更进一步,可以将事件从一个数据库移动到另外一个数据库:
1
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

查看定时任务

MySQL的information_schema提供了访问数据库元数据的方式。 元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。其中Events表中可以查看所有已定义的事件的具体信息,包括事件所属库名称、事件名称、事件类型、事件的完整定义、开始时间、INTERVAL定义、上一次执行时间等等。

示例:

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
mysql> SELECT * FROM EVENTS\G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: ^^^^^^ --数据库名称
EVENT_NAME: ev_xxzh_tranhistory
DEFINER: ^^^^^^^^^^^
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: BEGIN
call sp_xxzh_transhistory();
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE: NO_ENGINE_SUBSTITUTION
STARTS: 2017-10-19 05:30:00
ENDS: NULL
STATUS: SLAVESIDE_DISABLED
ON_COMPLETION: PRESERVE
CREATED: 2017-10-19 18:00:26
LAST_ALTERED: 2017-10-19 18:00:26
LAST_EXECUTED: NULL
EVENT_COMMENT:
ORIGINATOR: 110
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci

删除定时任务

1
drop event myevent;

查看定时任务状态

1
show variables like "event_scheduler";
查询结果:
1
2
3
4
5
6
7
8
mysql> show variables like "event_scheduler";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.04 sec)
---------------------

查看EVENT命令有如下几种

1
2
3
4
5
(1)查询mysql.event表;
(2)通过SHOW EVENTS命令;
(3)通过SHOW FULL EVENTS命令;
(4)通过查询information_schema.events表
(5)SHOW CREATE EVENT。
博主QQ:1012405802
技术交流QQ群:830339411
版权声明:网站内容有原创和转载,如有侵权,请联系删除,谢谢!!
感谢打赏,93bok因你们而精彩!!(支付宝支持花呗)
0%