Mysql 关于event的详解

官方文档
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; --此时就算事件过期了，也不会被自动删除掉