mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作详解

本文实例讲述了mysql事件之修改事件（alter event）、禁用事件（disable）、启用事件（enable）、事件重命名及数据库事件迁移操作。分享给大家供大家参考，具体如下：

我们要知道，mysql允许我们更改现有事件的各种属性。如果我们要更改现有事件，可以使用alter event语句，如下所示：

alter event event_name
on schedule schedule
on completion [not] preserve
rename to new_event_name
enable | disable
do
 event_body

alter event语句仅适用于存在的事件，如果我们尝试修改不存在的事件，mysql将会发出一条错误消息，因此在更改事件之前，应先使用show events语句检查事件的存在：

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

创建一个每分钟将一条新记录插入到messages表中的示例事件来演示如何使用alter event语句的各种功能：

use testdb;
create event test_event_04
on schedule every 1 minute
do
  insert into messages(message,created_at)
  values('test alter event statement',now());

我们来把事件修改为为每2分钟运行一次：

alter event test_event_04
on schedule every 2 minute;

我们还可以通过指定新的逻辑来更改事件的主体代码：

alter event test_event_04
do
  insert into messages(message,created_at)
  values('message from event',now());
-- 清空表中的数据
truncate messages;

修改完成后，可以等待2分钟，再次查看messages表：

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

我们可以在alter event语句之后使用disable关键字来禁用某个事件：

alter event test_event_04
disable;

我们也可以通过使用show events语句来查看事件的状态：

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  |
| testdb | test_event_04 | root@localhost | system  | recurring | null        | 2       | minute     | 2017-08-03 04:44:47 | null | disabled |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
2 rows in set

我们可以在alter event语句之后使用enable关键字来启用事件：

alter event test_event_04
enable;

查看下事件状态：

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  |
| testdb | test_event_04 | root@localhost | system  | recurring | null        | 2       | minute     | 2017-08-03 04:44:47 | null | enabled |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
2 rows in set

我们再来尝试使用alter event重命名现有事件：

alter event test_event_04
rename to test_event_05;

来查看下事件状态：

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  |
| testdb | test_event_05 | root@localhost | system  | recurring | null        | 2       | minute     | 2017-08-03 04:44:47 | null | enabled |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
2 rows in set

完事再来通过使用rename to子句将事件从一个数据库移动到另一个数据库中：

alter event testdb.test_event_05
rename to newdb.test_event_05;

再来查看事件状态：

mysql> show events from newdb;
+-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| db  | name     | definer    | time zone | type   | execute at | interval value | interval field | starts       | ends | status | originator | character_set_client | collation_connection | database collation |
+-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| newdb | test_event_05 | root@localhost | system  | recurring | null    | 2       | minute     | 2017-08-03 04:44:47 | null | enabled |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set

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