Mysql Event Scheduler

佳境Shmily 2019-11-15 21:25:04
技术

Mysql事件调度器

工作的时候遇到一张表需要每天Truncate,就想到了Mysql的Event Scheduler,但是又忘了它的语法了,所以这里来复习一下。

什么是Event Scheduler

事件调度器,可以作为定时调度器,类似于Crontab,可以取代部分操作系统任务调度器的定时任务工作。Mysql在5.1版本后新增了事件调度器,它可以支持秒级调度,很实用方便。
时间调度器也可以看作是一个触发器,是针对某个表进行操作的,时间调度器执行采用了单独一个线程,可通过SHOW PROCESSLIST命令查看

Event Scheduler语法

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}

语法说明:

ON SCHEDULE时间类型

两种时间类型AT timestampEvery interval

AT timestamp

用于只执行一次的事件。执行的时间由timestamp指定,timestamp必须包含完整的日期和时间,即年月日时分秒都要有。可以使用DATETIME或TIMESTAMP类型,或者可以转换成时间的值,例如“2018-01-21 00:00:00”。如果指定是时间是过去的时间,该事件不会执行,并生成警告。

mysql> create table test(id int,name varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-11-16 11:30:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> create event insert_test ON SCHEDULE AT '2019-11-16 11:30:59' DO show tables;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1588
Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> create event insert_test ON SCHEDULE AT '2019-11-16 11:34:59' DO show tables; 
Query OK, 0 rows affected (0.01 sec)

mysql> create event insert_test ON SCHEDULE AT '2019-11-16 11:37:59' DO insert into test(id,name) values (1,'qjj'); 
Query OK, 0 rows affected (0.00 sec)

时间过后我发现我的test表里仍然没数据

mysql> show variables like "event_scheduler";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF    |
+-----------------+-------+
1 row in set (0.00 sec)

原因是我没开启event_scheduler
vim /etc/my.cnf 在[mysqld]这一栏下添加event_scheduler = ON来永久启用event_scheduler
重启mysql服务systemctl restart mysqld.service

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-11-16 11:40:37 |
+---------------------+
1 row in set (0.00 sec)

mysql> create event insert_test ON SCHEDULE AT '2019-11-16 11:41:37' DO insert into test(id,name) values (1,'qjj');       
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;                                                               +------+------+
| id   | name |
+------+------+
|    1 | qjj  |
+------+------+
1 row in set (0.00 sec)

mysql> show events;
Empty set (0.00 sec)

# 一小时后执行 命令示例 
mysql> CREATE EVENT update_test ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE test SET id = 2;   
Query OK, 0 rows affected (0.00 sec)

上述结果说明:必须先开启event_scheduler之后event才会生效,AT timestamp的方式只会在指定时间点执行一次,然后这个event就会被销毁,如果指定的时间是过去的是时间点,则这个event会有警告,且不执行也不保留event。

Every interval

让事件定期执行,每多久执行一次
ON SCHEDULE后面时间写法的几个栗子:
EVERY 6 WEEK 每六周
EVERY 20 second 每20秒
EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK 一周以后开始,每隔三个月
EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE 6小时15分钟以后开始,每隔两周执行
EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 2 WEEK 5分钟以后开始,每隔一天执行,两周后结束

举个栗子

mysql> create event daily_truncate_test
    -> ON SCHEDULE
    -> EVERY 1 DAY
    -> COMMENT '每天执行一次清空test表数据'
    -> DO
    -> truncate test;
Query OK, 0 rows affected (0.00 sec)

mysql> mysql> show events;
+------+---------------------+-------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db   | Name                | Definer     | Time zone | Type      | Execute at          | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------+---------------------+-------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| test | daily_truncate_test | root@CDH066 | SYSTEM    | RECURRING | NULL                | 1              | DAY            | 2019-11-16 12:10:36 | NULL | ENABLED |          1 | utf8                 | utf8_unicode_ci      | utf8_unicode_ci    |
| test | update_test         | root@CDH066 | SYSTEM    | ONE TIME  | 2019-11-16 12:58:52 | NULL           | NULL           | NULL                | NULL | ENABLED |          1 | utf8                 | utf8_unicode_ci      | utf8_unicode_ci    |
+------+---------------------+-------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST;
+----+-----------------+--------------+------+---------+------+-----------------------------+------------------+
| Id | User            | Host         | db   | Command | Time | State                       | Info             |
+----+-----------------+--------------+------+---------+------+-----------------------------+------------------+
|  1 | event_scheduler | localhost    | NULL | Daemon  |  721 | Waiting for next activation | NULL             |
|  7 | root            | CDH066:34902 | test | Query   |    0 | starting                    | SHOW PROCESSLIST |
+----+-----------------+--------------+------+---------+------+-----------------------------+------------------+
2 rows in set (0.00 sec)


# 示例2 指定每天具体时间点的event事件
CREATE EVENT truncate_with_time
ON SCHEDULE EVERY 1 day STARTS date_add(concat(current_date(), ' 00:00:00'), interval 0 second)
ON COMPLETION PRESERVE ENABLE
COMMENT
DO
TRUNCATE test;

操作和查看事件

show events;  # 查看事件及其状态
ALTER EVENT daily_truncate_test DISABLE;  # 禁用指定事件
ALTER EVENT daily_truncate_test ENABLE;   # 启用指定事件
ALTER EVENT daily_truncate_test RENAME TO daily_truncate;   # 重命名事件
ALTER EVENT test.daily_truncate_test RENAME TO qjj_test.daily_truncate_test;    # 事件是数据库层面的,可以把事件从一个数据库移动到另一个数据库(另一个数据库要有对应的表)  
DROP EVENT daily_truncate;   # 删除事件

总结

Mysql作为最热门的关系型数据库之一,有很多东西值得我们去探索,好记性不如烂笔头,写了博客,对事件调度器的理解更加深刻了。