> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-si-zhang/xiu-gai-shi-jian.md). # 修改事件 本教程将向您展示如何使用`ALTER EVENT`语句修改现有的[MySQL事件](http://www.yiibai.com/mysql/triggers/mysql/working-mysql-scheduled-event.html)。 在学习完本教程之后，您将了解如何修改事件和计划，如何启用或禁用事件以及如何重命名事件。 MySQL允许您更改现有事件的各种属性。要更改现有事件，请使用`ALTER EVENT`语句，如下所示： ```sql 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`语句检查事件的存在。 ```sql SHOW EVENTS FROM testdb; ``` 执行上面查询，得到以下结果 - ```sql 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 ``` ## ALTER EVENT示例 我们创建一个示例事件来演示如何使用`ALTER EVENT`语句的各种功能。 以下语句创建一个事件，每分钟将一条新记录插入到`messages`表中。 ```sql 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`分钟运行一次，请使用以下语句： ```sql ALTER EVENT test_event_04 ON SCHEDULE EVERY 2 MINUTE; ``` **改变事件的主体代码逻辑** 您还可以通过指定新的逻辑来更改事件的主体代码，如下所示： ```sql ALTER EVENT test_event_04 DO INSERT INTO messages(message,created_at) VALUES('Message from event',NOW()); -- 清空表中的数据 truncate messages; ``` 上面修改完成后，可以等待`2`分钟，再次查看`messages`表： ```sql SELECT * FROM messages; ``` 执行上面查询，得到以下结果 - ```sql 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`关键字，请使用以下语句： ```sql ALTER EVENT test_event_04 DISABLE; ``` 也可以通过使用`SHOW EVENTS`语句来查看事件的状态，如下所示： ```sql SHOW EVENTS FROM testdb; ``` 执行上面查询，得到以下结果 - ```sql 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`关键字，如下所示： ```sql ALTER EVENT test_event_04 ENABLE; ``` 查询上面语句执行结果，得到以下结果 - ```sql 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 ``` **重命名事件** MySQL不提供类似`RENAME EVENT`语句。幸运的是，我们可以使用`ALTER EVENT`重命名现有事件，如下所示： ```sql ALTER EVENT test_event_04 RENAME TO test_event_05; ``` 查询上面语句执行结果，得到以下结果 - ```sql 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`子句将事件从一个数据库移动到另一个数据库中，如下所示： ```sql ALTER EVENT testdb.test_event_05 RENAME TO newdb.test_event_05; ``` 查询上面语句执行结果，得到以下结果 - ```sql 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 ``` 假设`newdb`数据库在MySQL数据库服务器中可用。 在本教程中，我们向您展示了如何使用`ALTER EVENT`语句更改MySQL事件的各种属性。