开启事件计划
mysql> show variables like '%event%'
mysql> set GLOBAL event_scheduler = 1;
在MySQL命令行里进行设置开始,当重启MySQL后,该设置就会失效。如果想重启后该设置依然有效,那么就在MySQL配置文件my.cnf里设置my.cnf event_scheduler=ON。
查询事件
select * from mysql.event
创建事件
# 创建一个简单的存储过程,用来测试,比如查询当前时间
CREATE DEFINER=`root`@`%` PROCEDURE `SELECT_TIME`()
BEGIN
select now();
END
# 定时调存储过程
CREATE DEFINER=`root`@`%` EVENT if not exists `SELECT_TIME_EVENT`
ON SCHEDULE EVERY 1 DAY STARTS '2018-11-25 01:30:00'
ON COMPLETION PRESERVE
DO CALL topdb_dev.SELECT_TIME();
解释:
- 创建SELECT_TIME_EVENT事件
- 2018-11-25 01:30:00开启事件,以后每天01:30:00执行
- ON COMPLETION PRESERVE 事件完成后,该event仍然保留
ON COMPLETION NOT PRESERVE 事件完成后,该event会被删除 - CALL topdb_dev.SELECT_TIME() 调用SELECT_TIME存储过程
修改事件
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ‘comment’]
[ENABLE | DISABLE]
DO sql_statement]
ALTER EVENT SELECT_TIME_EVENT DISABLE; # 禁用
ALTER EVENT SELECT_TIME_EVENT ENABLE; # 启用
ALTER EVENT SELECT_TIME_EVENT ON SCHEDULE EVERY 5 DAY;
ALTER EVENT SELECT_TIME_EVENT ON SCHEDULE EVERY 100 DAY STARTS '2018-11-25 15:45:00';
删除事件
DROP EVENT [IF EXISTS] event_name
DROP EVENT IF EXISTS topdb_dev.SELECT_TIME_EVENT
参考网址
https://blog.csdn.net/qq_26562641/article/details/53301407
https://bbs.csdn.net/topics/320033700