您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“mysql事件之修改事件、禁用事件、啟用事件、事件重命名及數(shù)據(jù)庫(kù)事件遷移操作的示例分析”,內(nèi)容簡(jiǎn)而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“mysql事件之修改事件、禁用事件、啟用事件、事件重命名及數(shù)據(jù)庫(kù)事件遷移操作的示例分析”這篇文章吧。
具體如下:
我們要知道,MySQL允許我們更改現(xiàn)有事件的各種屬性。如果我們要更改現(xiàn)有事件,可以使用ALTER EVENT語(yǔ)句,如下所示:
ALTER EVENT event_name ON SCHEDULE schedule ON COMPLETION [NOT] PRESERVE RENAME TO new_event_name ENABLE | DISABLE DO event_body
ALTER EVENT語(yǔ)句僅適用于存在的事件,如果我們嘗試修改不存在的事件,MySQL將會(huì)發(fā)出一條錯(cuò)誤消息,因此在更改事件之前,應(yīng)先使用SHOW EVENTS語(yǔ)句檢查事件的存在:
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
創(chuàng)建一個(gè)每分鐘將一條新記錄插入到messages表中的示例事件來(lái)演示如何使用ALTER EVENT語(yǔ)句的各種功能:
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());
我們來(lái)把事件修改為為每2分鐘運(yùn)行一次:
ALTER EVENT test_event_04 ON SCHEDULE EVERY 2 MINUTE;
我們還可以通過(guò)指定新的邏輯來(lái)更改事件的主體代碼:
ALTER EVENT test_event_04 DO INSERT INTO messages(message,created_at) VALUES('Message from event',NOW()); -- 清空表中的數(shù)據(jù) 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語(yǔ)句之后使用DISABLE關(guān)鍵字來(lái)禁用某個(gè)事件:
ALTER EVENT test_event_04 DISABLE;
我們也可以通過(guò)使用SHOW EVENTS語(yǔ)句來(lái)查看事件的狀態(tài):
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語(yǔ)句之后使用ENABLE關(guān)鍵字來(lái)啟用事件:
ALTER EVENT test_event_04 ENABLE;
查看下事件狀態(tài):
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
我們?cè)賮?lái)嘗試使用ALTER EVENT重命名現(xiàn)有事件:
ALTER EVENT test_event_04 RENAME TO test_event_05;
來(lái)查看下事件狀態(tài):
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
完事再來(lái)通過(guò)使用RENAME TO子句將事件從一個(gè)數(shù)據(jù)庫(kù)移動(dòng)到另一個(gè)數(shù)據(jù)庫(kù)中:
ALTER EVENT testdb.test_event_05 RENAME TO newdb.test_event_05;
再來(lái)查看事件狀態(tài):
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事件之修改事件、禁用事件、啟用事件、事件重命名及數(shù)據(jù)庫(kù)事件遷移操作的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。