mysql删除过期食品记录_mysql定时任务event——清理过期数据 （转）

1.查询是否开启事件调度

SHOW VARIABLES LIKE 'event_scheduler';

2.开启事件调度

临时： SET GLOBAL event_scheduler = 1;

永久： vim /etc/my.cnf event_scheduler=ON

[mysqld]

event_scheduler=ON //这一行加入mysqld标签下

3.创建p_del_count存储过程。 接收传入参数30并执行删除数据操作，删除大于30天的数据。

drop procedure if existsp_del_count;

create procedure p_del_count(IN `date_inter` INT)

BEGIN

delete from t_req_log where (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(time),'%Y%m%d'))) >=date_inter;

END;

BEGIN

delete from t_warnmsg where (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(warntime),'%Y%m%d'))) >=date_inter;

delete from t_collection where (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(colltime),'%Y%m%d'))) >=date_inter;

END

4.创建定时任务 从2018-05-15 00:00:00起每一天执行一次p_del_count这个存储过程，并将参数传给 p_del_count存储过程

drop event if existse_del_t_req_log;

createevent e_del_t_req_log

on schedule every 1 day starts '2018-05-15 00:00:00'

on completion not preserve enable do call p_del_count (30);

on schedule every 1 day starts '2019-05-3 00:00:00'

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

6.事件的开启与关闭 开启某事件：ALTER EVENT e_del_t_req_log ON COMPLETION PRESERVE ENABLE;

关闭某事件：ALTER EVENT e_del_t_req_log ON COMPLETION PRESERVE DISABLE;