官方文件
Using the Event Scheduler
https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html
CREATE EVENT Statement
https://dev.mysql.com/doc/refman/5.7/en/create-event.html
ALTER EVENT Statement
https://dev.mysql.com/doc/refman/5.7/en/alter-event.html
DROP EVENT Statement
https://dev.mysql.com/doc/refman/5.7/en/drop-event.html

Mysql的event就類似Oracle的DBMS_SCHEDULER

event_scheduler引數等於on或1，才能使用event
mysql> SHOW VARIABLES LIKE '%event_scheduler%';

建立一個事件每分鐘執行一次儲存過程sp_insert_heartbeat_table()
CREATE EVENT event_replication_insert ON SCHEDULE EVERY 60 SECOND  DO call sp_insert_table();

建立一個事件每分鐘執行一次delete語句
CREATE EVENT event_replication_delete ON SCHEDULE EVERY 60 SECOND  DO delete from table1 where id>2

檢視事件執行結果
mysql> select * from events_list;

預設建立事件儲存在當前庫中，也可顯示指定事件建立在哪個庫中
透過show events只能檢視當前庫中建立的事件

檢視當前所在庫的事件
mysql> show events;

檢視某個庫的事件
mysql> SHOW EVENTS FROM dbname;

檢視所有事件
mysql> select * from mysql.event;
mysql> select * from information_schema.events;

事件開啟與關閉：
開啟某事件：ALTER EVENT DBNAME.EVENT_NAME ENABLE;
關閉某事件：ALTER EVENT DBNAME.EVENT_NAME DISABLE;

修改事件每2分鐘執行一次
ALTER EVENT EVENT_NAME ON SCHEDULE EVERY 2 MINUTE;

重新命名事件EVENT_NAME1為EVENT_NAME2
ALTER EVENT EVENT_NAME1 RENAME TO EVENT_NAME2;

查詢某個event的狀態
select created,starts,last_executed,ends,status,on_completion from mysql.event where name='eventname';

刪除某個event
DROP EVENT event_name

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit. 
ALTER EVENT EVENT_NAME ON COMPLETION PRESERVE ENABLE; --此時就算事件過期了，也不會被自動刪除掉

Mysql 關於event的詳解

相關文章