定时删除过期数据(MySQL数据表)

上一篇 / 下一篇  2020-09-08 10:13:37 / 个人分类:数据库

开启事件调度
SET GLOBAL event_scheduler = 1;
或可以在配置my.cnf文件 中加上 event_scheduler = 1


查看是否开启事件调度
SHOW VARIABLES LIKE 'event_scheduler';

创建每天删除7天前数据
CREATE EVENT `del_tbl_expired_data1`   
ON SCHEDULE EVERY 1 DAY STARTS '2020-03-09 17:33:43'   
ON COMPLETION NOT PRESERVE ENABLE 
DO CALL del_data_count (7);


创建存储过程
use django;
delimiter ;;
CREATE PROCEDURE `del_data_count1`(IN date_inter int)?
begin?
insert into result_copy(id,title,taskId,projectId,testcaseId,browsers,beforeLogin,environments,status,parameter,steps,checkType,checkValue,createTime) select id,title,taskId,projectId,testcaseId,browsers,beforeLogin,environments,status,parameter,steps,checkType,checkValue,createTime from result
where (TO_DAYS(NOW()) - TO_DAYS(createTime))>=date_inter;?
delete from result ?where (TO_DAYS(NOW()) - TO_DAYS(createTime))>=date_inter;
end ;;
delimiter;

查看已有定时事件任务
SHOW EVENTS;

事件的开启与关闭
开启某事件:
ALTER EVENT del_tbl_expired_data ON COMPLETION PRESERVE ENABLE;
关闭某事件:
ALTER EVENT del_tbl_expired_data ON COMPLETION PRESERVE DISABLE;


TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar