MySQL数据库DDL操作之事件调度器

头像
夏日寒冰
    阅读 8 分钟
    2
    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中索引的使用，原理及注意事项，欢迎大家评论，转发，共同学习~

    后续更多文章将更新在个人小站上，欢迎查看。

    另外提供一些优秀的IT视频资料，可免费下载！如需要请查看https://www.592xuexi.com

    mysql
    夏日寒冰
    318 声望85 粉丝

    忠实的技术爱好者，追求极致，喜欢总结一些自己用过的技术点，与他人交流分享。

    « 上一篇
    MySQL数据库DDL操作之存储过程和函数
    下一篇 »
    MySQL数据库之索引的应用

    引用和评论

    推荐阅读
    头像
    面试常问的Spring源码，出maven版本了

    夏日寒冰阅读 559

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 332

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 646

    头像
    Mysql梳理1

    cashew赞 1阅读 2.1k

    头像
    mysql梳理2

    cashew赞 1阅读 1.5k

    0 条评论
    得票最新