1.查看是否开启定时策略(root管理员用户执行):
SHOW VARIABLES LIKE 'event_scheduler';
或 SELECT @@event_scheduler;
或 SHOW PROCESSLIST;
如果你的是OFF,那就需要开启(root管理员用户执行):
set global event_scheduler=1;
或 SET GLOBAL event_scheduler = ON;
2.创建存储函数,存储定时执行的事件
CREATE PROCEDURE `NewProc`()
BEGIN
DECLARE total INT;
-- 创建接收游标数据的变量
DECLARE sd datetime;
DECLARE sname VARCHAR(10);
-- 创建总数变量
insert into category_log(name,last_update,END_time)
SELECT t1.`name` sname ,t2.END_time tm,NOW() ti from proc_name t1 inner join category_log t2 on t1.name = t2.name;
commit;
select max(last_update) into sd from category_log;
delete from category_log where END_time<=sd;
commit;
select count(*) into total from category_log;
insert into log(msg) SELECT concat(NOW(),'插入',total,'条数据')
commit;
select concat(NOW(),'插入',total,'条数据');
END;
3.创建定时任务(事件)
-- 每天一分钟执行
CREATE EVENT IF NOT EXISTS MY_MONITOR
ON SCHEDULE EVERY 1 MINUTE STARTS CURDATE()
ON COMPLETION PRESERVE DO CALL NewProc();
# 每天的凌晨1点执行定时任务
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(CURRENT_DATE+1), INTERVAL 1 HOUR)
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(ADDDATE(CURDATE(),1)),INTERVAL 1 HOUR)
# 特定的日期特定的时间点执行定时任务
ON SCHEDULE at '2019-10-10 19:14:10'
# 每五分钟执行一次定时任务
ON SCHEDULE EVERY 5 MINUTE STARTS CURDATE()
# 每小时执行一次定时任务
ON SCHEDULE EVERY 1 HOUR STARTS CURDATE()
补充:ON SCHEDULE 后面可以 自由发挥,at 特定时间执行,every 重复执行
4.查看创建的定时任务
show events;
或者 select * from mysql.event;
5.删除定时任务
drop event my_monitor
6.开启或关闭定时任务
-- 关闭
alter event my_monitor on COMPLETION PRESERVE DISABLE;
-- 开启
alter event my_monitor on COMPLETION PRESERVE ENABLE;
7.定时任务每隔4分钟清除表
create event table_delete on schedule every 240 second do delete from log ;