# 修改事件 本教程将向您展示如何使用`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事件的各种属性。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-si-zhang/xiu-gai-shi-jian.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.