mysql的计划任务与事件调度实例分析

本文实例讲述了mysql的计划任务与事件调度。分享给大家供大家参考，具体如下：

mysql事件是基于预定义的时间表运行的任务，因此有时它被称为预定事件。mysql事件也被称为“时间触发”，因为它是由时间触发的，而不是像触发器一样更新表来触发的。mysql事件类似于unix中的cron作业或windows中的任务调度程序。我们可以在优化数据库表，清理日志，归档数据或在非高峰时间生成复杂的报告的时候使用mysql事件。

mysql使用一个名为事件调度线程的特殊线程来执行所有调度的事件。我们可以通过执行以下命令来查看事件调度程序线程的状态：

show processlist;

执行上面查询语句，得到以下结果：

mysql> show processlist;
+----+------+-----------------+----------+---------+------+----------+------------------+
| id | user | host      | db    | command | time | state  | info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | null   | sleep  | 1966 |     | null       |
| 3 | root | localhost:50406 | yiibaidb | sleep  | 1964 |     | null       |
| 4 | root | localhost:50407 | yiibaidb | query  |  0 | starting | show processlist |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

默认情况下，事件调度程序线程未启用。 要启用和启动事件调度程序线程，需要执行以下命令：

set global event_scheduler = on;

现在看到事件调度器线程的状态，再次执行show processlist命令，结果如下所示：

mysql> show processlist;
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| id | user      | host      | db    | command | time | state         | info       |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| 2 | root      | localhost:50405 | null   | sleep  | 1986 |            | null       |
| 3 | root      | localhost:50406 | luyaran | sleep  | 1984 |            | null       |
| 4 | root      | localhost:50407 | luyaran | query  |  0 | starting        | show processlist |
| 5 | event_scheduler | localhost    | null   | daemon |  6 | waiting on empty queue | null       |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
4 rows in set

要禁用并停止事件调度程序线程，可通过执行set global命令将event_scheduler其值设置为off ：

set global event_scheduler = off;

我们知道，事件是一个包含sql语句的命名对象，创建事件与创建其他数据库对象(如存储过程或触发器)类似，然而，存储过程仅在直接调用时执行; 触发器则与一个表相关联的事件(例如插入，更新或删除)事件发生时，可以在一次或更多的规则间隔执行事件时执行触发。那么，事件呢？我们接下来就尝试使用create event语句，来创建事件，下面先来看下语法结构：

create event [if not exist] event_name
on schedule schedule
do
event_body

我们来详细看下上述sql中的参数的含义：

首先，在create event子句之后指定事件名称。事件名称在数据库模式中必须是唯一的。

其次，在on schedule子句后面加上一个表。如果事件是一次性事件，则使用语法：at timestamp [+ interval]，如果事件是循环事件，则使用every子句：every interval starts timestamp [+interval] ends timestamp [+interval]

第三，将do语句放在do关键字之后。请注意，可以在事件主体内调用存储过程。 如果您有复合sql语句，可以将它们放在begin end块中。

我们先来创建一个messages表来做演示：

create table if not exists messages (
  id int primary key auto_increment,
  message varchar(255) not null,
  created_at datetime not null
);

完事我们来使用create event语句创建一个事件：

create event if not exists test_event_01
on schedule at current_timestamp
do
 insert into messages(message,created_at)
 values('test mysql event 1',now());

检查messages表; 会看到有1条记录，这意味着事件在创建时被执行：

mysql> select * from messages;
+----+--------------------+---------------------+
| id | message      | created_at     |
+----+--------------------+---------------------+
| 1 | test mysql event 1 | 2017-08-03 04:23:11 |
+----+--------------------+---------------------+
1 row in set

要显示数据库(testdb)的所有事件，请使用以下语句：

show events from testdb;

执行上面查询看不到任何行返回，因为事件在到期时自动删除。 在我们的示例中，它是一次性的事件，在执行完成时就过期了。要更改此行为，可以使用on completion preserve子句。以下语句创建另一个一次性事件，在其创建时间1分钟后执行，执行后不会被删除：

create event test_event_02
on schedule at current_timestamp + interval 1 minute
on completion preserve
do
  insert into messages(message,created_at)
  values('test mysql event 2',now());

等待1分钟后，查看messages表，添加了另一条记录：

mysql> select * from messages;
+----+--------------------+---------------------+
| id | message      | created_at     |
+----+--------------------+---------------------+
| 1 | test mysql event 1 | 2017-08-03 04:23:11 |
| 2 | test mysql event 2 | 2017-08-03 04:24:48 |
+----+--------------------+---------------------+
2 rows in set

如果再次执行show events语句，看到事件是由于on completion preserve子句的影响：

mysql> show events from testdb;
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| db   | name     | definer    | time zone | type   | execute at     | interval value | interval field | starts | ends | status  | originator | character_set_client | collation_connection | database collation |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| testdb | test_event_02 | root@localhost | system  | one time | 2017-08-03 04:24:48 | null      | null      | null  | null | disabled |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set

我们再来创建一个循环的事件，每分钟执行一次，并在其创建时间的1小时内过期：

create event test_event_03
on schedule every 1 minute
starts current_timestamp
ends current_timestamp + interval 1 hour
do
  insert into messages(message,created_at)
  values('test mysql recurring event',now());

我们要注意，我们使用starts和ends子句定义事件的有效期。等待个3，5分钟后再查看messages表数据，以测试验证此循环事件的执行：

mysql> select * from messages;
+----+----------------------------+---------------------+
| id | message          | created_at     |
+----+----------------------------+---------------------+
| 1 | test mysql event 1     | 2017-08-03 04:23:11 |
| 2 | test mysql event 2     | 2017-08-03 04:24:48 |
| 3 | test mysql recurring event | 2017-08-03 04:25:20 |
| 4 | test mysql recurring event | 2017-08-03 04:26:20 |
| 5 | test mysql recurring event | 2017-08-03 04:27:20 |
+----+----------------------------+---------------------+
5 rows in set

完事我们可以使用drop event语句来删除事件，看下语法结构：

drop event [if exists] event_name;

要删除test_event_03的事件，我们可以使用如下sql：

drop event if exists test_event_03;

好啦，本次记录就到这里了。