MySQL数据库DDL操作之事件调度器
MySQL中的事件调度器是在5.1版本之后新增的，可以在数据库中定时触发某种操作，类似于Spring中的Quartz定时任务或者Linux中的crontab任务调度器，下面将介绍MySQL中事件调度器的用法。
1、调度器的创建：
（1）语法：
CREATE EVENT event_name ON SCHEDULE <time_frequency> DO <event_statement>;
参数解释：
event_name：表示自定义的事件调度器的名称，放在CREATE EVENT关键字之后； time_frequency：表示该事件调度器什么时间执行以及执行周期为多少； event_statement：表示该事件调度器中要执行的具体操作或者事件，可以为一个语句，也可以为一个语句块，即：由BEGIN...END包含的语句块，中间可以添加一些执行逻辑，需要使用\d指定定界符。除此之外，还可以在event_statement中调用其他的存储过程和函数；
（2）示例：
示例1：创建一张测试表t_test，每隔10秒钟向该表中插入一条记录，操作如下：
创建表结构：
mysql> CREATE TABLE t_test(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL DEFAULT '',
create_time DATETIME
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;创建事件调度器：
mysql> CREATE EVENT insert_event
ON SCHEDULE EVERY 10 SECOND
DO
INSERT INTO t_test(name,create_time) VALUES('test_name',NOW());10秒之后查询t_test表，结果如下：
mysql> SELECT * FROM t_test; Empty set (0.00 sec)
发现并没有起作用，10秒之后并未插入数据，这是由于事件调度器未打开导致，通过如下命令查看事件调度器的状态，发现结果为OFF，表示未打开：
mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.01 sec)
使用如下命令打开事件调度器：
mysql> SET GLOBAL event_scheduler = ON; 或者使用：mysql> SET @@global.event_scheduler = ON; Query OK, 0 rows affected (0.11 sec)
再次查看，已经打开事件调度器：
mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec)
打开事件调度器的开关之后，在经过10秒，查看t_test表，发现事件调度器已经正常执行了，如下：
mysql> SELECT * FROM t_test; +----+-----------+---------------------+ | id | name | create_time | +----+-----------+---------------------+ | 1 | test_name | 2018-05-16 16:58:45 | +----+-----------+---------------------+
注意：上述使用"SET GLOBAL"命令只能全局修改服务器参数，如果数据库重启，该参数会失效。如果要永久修改，需要修改MySQL的配置文件，编辑/etc/my.cnf，在[mysqld]中添加如下内容：
[mysqld] event_scheduler = ON #添加该项 ...
示例2：上述示例是每10秒给t_test表中插入一条记录，表中的记录会快速增多，现在通过另外一个事件调度器，完成每1分钟清空一次t_test表中的记录，如下：
创建事件调度器：
mysql> CREATE EVENT clear_event
ON SCHEDULE EVERY 1 MINUTE
DO
TRUNCATE TABLE t_test;创建完成之后，立刻查看，会发现t_test表中的数据已经被清空，再10秒后差生的数据，在1分钟之后又会被再次清空：
mysql> SELECT * FROM t_test; Empty set (0.00 sec)
示例3：定期清理t_log日志表，并将清除时间及清除的记录数量写入t_delete_log中，通过事件调度器和存储过程实现：
创建t_delete_log表：
CREATE TABLE t_delete_log(
id INT PRIMARY KEY AUTO_INCREMENT,
delete_time DATETIME,
delete_count INT DEFAULT 0
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;创建存储过程：
mysql> \d $
mysql> CREATE PROCEDURE p_clear()
BEGIN
DECLARE d_count INT DEFAULT 0;
SET AUTOCOMMIT = 0;
SET d_count = (SELECT COUNT(*) FROM t_log);
# 判断是否有待清除的数据
IF d_count > 0 THEN
TRUNCATE t_log;
INSERT INTO t_delete_log(delete_time,delete_count) VALUES(NOW(),d_count);
END IF;
COMMIT;
END $
mysql> \d ;创建事件调度器：
mysql> CREATE EVENT student_clear_event
ON SCHEDULE EVERY 1 MINUTE
DO
CALL p_clear();上述事件调度器会每隔1分钟调用一次清除表数据的存储过程，完成一次历史数据清理及记录归档，综合使用到了事件调度器和存储过程。
2、调度器信息的查看：
语法：
SHOW EVENTS <LIKE statement>; #查看已有调度器的信息，可以添加LIKE对调度器的名称进行筛选 SHOW CREATE EVENT event_name; #查看指定名称的调度器的创建信息 SHOW PROCESSLIST; #如果用户具有PROCESS权限，可以使用该命令查看调度器的线程状态 SELECT <field> FROM information_schema.event; #也可以从系统库中查看
输出结果中重要参数说明：
Db:表示调度器所在的数据库 Name:表示调度器的名称，可以使用Like条件过滤查看 Definer:表示调度器的定义者 Time zone:表示调度器使用的时区，SYSTEM表示使用系统默认的时区 Interval value:表示调度器时间周期的单位，包括：YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND Starts:表示调度器的开始执时间 Status：表示调度器的可用状态，包括：ENABLE,DISABLE,ENABLE ON SLAVE
示例1：查看在test库中创建的事件调度器，可以发现刚才创建的调度器器insert_event和clear_event，如下：
mysql> USE test;
mysql> SHOW EVENTS \G
*************************** 1. row ***************************
Db: test
Name: clear_event
Definer: [email protected]
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2018-05-16 17:08:43
Ends: NULL
Status: ENABLED
Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: test
Name: insert_event
Definer: [email protected]
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 10
Interval field: SECOND
Starts: 2018-05-16 16:53:55
Ends: NULL
Status: ENABLED
Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)示例2：查看触发器clear_event的创建信息：
mysql> USE test;
mysql> SHOW CREATE EVENT clear_event;
*************************** 1. row ***************************
Event: clear_event
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`127.0.0.1` EVENT `clear_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-05-16 17:08:43' ON COMPLETION NOT PRESERVE ENABLE DO TRUNCATE TABLE t_test
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)示例3：查看调度器的线程状态：
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 1
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 55
State: Waiting for next activation
Info: NULL3、调度器的修改：
语法：
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name] #修改名称，支持RENAME语法
[ENABLE | DISABLE | DISABLE ON SLAVE] #将其修改为可用或者不可用
[COMMENT 'string'] #添加注释信息
[DO event_body]示例：
示例1：将test库中的insert_event事件调度器改名为save_event，操作如下：
mysql> USE test;
mysql> ALTER EVENT insert_event RENAME TO save_event;
mysql> SHOW EVENTS LIKE 'save_event'\G
*************************** 1. row ***************************
Db: test
Name: save_event
Definer: [email protected]
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 10
Interval field: SECOND
Starts: 2018-05-16 16:53:55
Ends: NULL
Status: ENABLED
Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
#查看，发现已经修改。示例2：将save_event调度器改为不可用：
mysql> USE test;
mysql> ALTER EVENT save_event DISABLE;
#查看save_event，发现Status已经变为DISABLE，表示该调度器已经不可用了
mysql> SHOW EVENTS LIKE 'save_event'\G
*************************** 1. row ***************************
Db: test
Name: save_event
Definer: [email protected]
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 10
Interval field: SECOND
Starts: 2018-05-16 16:53:55
Ends: NULL
Status: DISABLED
Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
#修改完成之后，再次查看save_event，已经发现没有新数据插入了，表示修改生效。4、调度器的禁用和删除：
语法：
ALTER EVENT event_name DISABLE; #禁用某个调度器 DROP EVENT [IF EXISTS] event_name; #删除某个调度器
示例：禁用和删除save_event事件调度器并查看：
mysql> USE test; #如果某个调度器不用了，可以先把其禁用 mysql> ALTER EVENT save_event DISABLE; #确定删除之后，可以使用DROP完成调度器删除操作 mysql> DROP EVENT save_event; Query OK, 0 rows affected (0.00 sec) mysql> SHOW EVENTS LIKE 'save_event'; Empty set (0.00 sec)
5、调度器的优缺点及适用场景：
（1）优点：
a.可以实现类似于操作系统层面的定时任务调度；
b.可以再数据库层面解决事件的调度，由DBA统一维护，不依赖于操作系统层面的事件调度，有效防止了系统维护人员误操作调度器而导致的错误；
（2）缺点：
a.在服务器繁忙的情况下，或者功能对于性能要求很高的情况下，使用调度器会对性能产生一定影响，因为调度器也是在后台开启线程一直在运行及判断；
b.需要具有SUPER权限的用户才可以创建调度器，而SUPER用户权限一般开发人员是不可能具有的，需要DBA专门创建；
（3）适用场景：
a.历史数据定期统计
b.过期数据清除
至此，MySQL事件调度器的内容介绍完毕，下篇文章将详细介绍MySQL中索引的使用，原理及注意事项，欢迎大家评论，转发，共同学习~
相关推荐
CoderToy 2020-11-16
bianruifeng 2020-11-16
云中舞步 2020-11-12
敏敏张 2020-11-11
暗夜之城 2020-11-11
好记忆也需烂 2020-11-11
Coder技术文摘 2020-09-29
huacuilaifa 2020-10-29
Gexrior 2020-10-22
lpfvip00 2020-10-07
云中舞步 2020-09-11
康慧欣 2020-09-10
好记忆也需烂 2020-07-28
silencehgt 2020-09-07
幸福ITman汪文威 2020-09-05
sofia 2020-09-03
nan00zzu 2020-08-19
CHINA华军 2020-08-18