1.首先先查看mysql是否开启定时任务开关
SHOW VARIABLES LIKE 'event_scheduler';
如果是OFF,就先打开:
SET GLOBAL event_scheduler = ON;
2.创建要执行的任务
create PROCEDURE sched_clean()
#可以在方法里加参数 in paramname int,在需要传参使用时
BEGIN
#在这里面写要执行的sql语句即可
UPDATE user SET workTime=0;
END
3.创建定时函数,定时调用要执行的任务(这里是每天0点执行)
CREATE EVENT `quanxian`.`day_of_clean_event`
on schedule every 1 day starts date_add(date_add(curdate(), interval 1 day), interval 0 hour)
on completion preserve
do call sched_clean();
当为on completion preserve的时候, 是使事件在执行完毕后不会被drop掉;
当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉。
定时例子
/*每天凌晨一点执行*/
drop event if exists day_of_clean_event
create event if not exists day_of_clean_event
on schedule every 1 day starts date_add(date_add(curdate(), interval 1 day), interval 1 hour)
on completion preserve
do call sched_clean();
/*每周一凌晨一点执行*/
drop event if exists day_of_clean_event
create event if not exists day_of_clean_event
on schedule every 1 week starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 week ),interval 1 hour)
on completion preserve
do call sched_clean();
/*每月一号凌晨一点执行*/
drop event if exists day_of_clean_event
create event if not exists day_of_clean_event
on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
on completion preserve
do call sched_clean();
删除定时或者事件
#删除方法
DROP PROCEDURE sched_clean;
#查看事件
SHOW EVENTS;
#删除事件
DROP event day_of_clean_event;