MySQL自动删除指定时间以前的记录

总体思路：

• 创建存储过程
• 创建事件
• 开启事件

创建存储过程

CREATE PROCEDURE `prc_del_user`(IN daycount int) COMMENT '自动删除过期记录'
BEGIN
delete from user where (TO_DAYS(NOW()) - TO_DAYS(createtime))>=daycount;  
END;

创建事件

CREATE  EVENT `auto_delete_user` ON SCHEDULE EVERY 1 DAY STARTS '2019-05-15 00:00:00' 
ON COMPLETION PRESERVE ENABLE COMMENT '自动删除30天以前的日志数据' DO call prc_del_user(30);

// 30表示删除30天前的数据    createtime表示 user 表的createtime时间字段

开启事件的四种方法

//1.
SET GLOBAL event_scheduler = 1; 
//2. 
SET @@global.event_scheduler = 1;  
//3.
SET GLOBAL event_scheduler = ON;  
//4.
SET @@global.event_scheduler = ON; 

查看当前事件是否已开启

//1.
SHOW VARIABLES LIKE 'event_scheduler';
//2.
SELECT @@event_scheduler;
//3.
SHOW PROCESSLIST;​

事件开启与关闭

ALTER EVENT auto_delete_user ON COMPLETION PRESERVE ENABLE; //开启某事件
ALTER EVENT auto_delete_user ON COMPLETION PRESERVE DISABLE; //关闭某事件