本文實例講述了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
好啦，本次記錄就到這里了。
更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數據庫計有所幫助。
您可能感興趣的文章:- MySQL 重命名表的操作方法及注意事項
- MySQL數據庫重命名的快速且安全方法(3種)
- 詳解MYSQL中重命名procedure的一種方法
- MySQL中使用SQL語句對字段進行重命名
- mysql數據庫重命名語句分享
- 教你怎么用Python操作MySql數據庫
- 用python開發一款操作MySQL的小工具
- Python基礎之操作MySQL數據庫
- Python聊天室帶界面實現的示例代碼(tkinter,Mysql,Treading,socket)
- Python操作MySQL數據庫的簡單步驟分享
- Python使用sql語句對mysql數據庫多條件模糊查詢的思路詳解
- Python中tkinter+MySQL實現增刪改查
- 運用Python快速的對MySQL數據庫進行重命名
