# 修改事件

本教程将向您展示如何使用`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事件的各种属性。
