MySQL修改事件

2019-10-16 22:56:02

本教學將向您展示如何使用ALTER EVENT語句修改現有的MySQL事件。 在學習完本教學之後,您將了解如何修改事件和計劃,如何啟用或禁用事件以及如何重新命名事件。

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語句檢查事件的存在。

SHOW EVENTS FROM testdb;

執行上面查詢,得到以下結果 -

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表中。

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表:

SELECT * FROM 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語句來檢視事件的狀態,如下所示:

SHOW EVENTS FROM testdb;

執行上面查詢,得到以下結果 -

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

重新命名事件

MySQL不提供類似RENAME EVENT語句。幸運的是,我們可以使用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

假設newdb資料庫在MySQL資料庫伺服器中可用。

在本教學中,我們向您展示了如何使用ALTER EVENT語句更改MySQL事件的各種屬性。