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: root@127.0.0.1
           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: root@127.0.0.1
           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: NULL

3、调度器的修改：
语法：

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: root@127.0.0.1
           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: root@127.0.0.1
           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中索引的使用，原理及注意事项，欢迎大家评论，转发，共同学习~