mysql 7天后自动删除,在MYSQL中自动删除超过7天的行的存储过程

Mysql有它的EVENTfunction，用于避免复杂的cron交互，当你正在调度的内容大部分是与sql相关的，并且文件相关性较低。 请参阅手册页面。 希望下面的内容能够快速浏览重要的步骤和需要考虑的事项，也可以进行可validation的testing。

show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+

ooops，事件调度程序未打开。 什么都不会触发。

SET GLOBAL event_scheduler = ON; -- turn her on and confirm below

show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+

架构进行testing

create table theMessages ( id int auto_increment primary key, userId int not null, message varchar(255) not null, updateDt datetime not null, key(updateDt) -- FK's not shown ); -- it is currently 2015-09-10 13:12:00 -- truncate table theMessages; insert theMessages(userId,message,updateDt) values (1,'I need to go now, no followup questions','2015-08-24 11:10:09'); insert theMessages(userId,message,updateDt) values (7,'You always say that ... just hiding','2015-08-29'); insert theMessages(userId,message,updateDt) values (1,'7 day test1','2015-09-03 12:00:00'); insert theMessages(userId,message,updateDt) values (1,'7 day test2','2015-09-03 14:00:00');

创build2个活动，每天第一次运行，每10分钟运行第二次

忽略他们实际在做什么(相互对抗)。 重点在于time difference方法和时间安排 。

DELIMITER $$ CREATE EVENT `delete7DayOldMessages` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN delete from theMessages where datediff(now(),updateDt)>6; -- not terribly exact, yesterday but <24hrs is still 1 day -- etc etc all your stuff in here END;$$ DELIMITER ;

…

DELIMITER $$ CREATE EVENT `Every_10_Minutes_Cleanup` ON SCHEDULE EVERY 10 MINUTE STARTS '2015-09-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN delete from theMessages where TIMESTAMPDIFF(HOUR, updateDt, now())>168; -- messages over 1 week old (168 hours) -- etc etc all your stuff in here END;$$ DELIMITER ;

显示事件状态(不同的方法)

show events from so_gibberish; -- list all events by schema name (db name) show events; -- prompt *************************** 1. row *************************** Db: so_gibberish Name: delete7DayOldMessages Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: DAY Starts: 2015-09-01 00:00:00 Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 2. row *************************** Db: so_gibberish Name: Every_10_Minutes_Cleanup Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 10 Interval field: MINUTE Starts: 2015-09-01 00:00:00 Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 2 rows in set (0.06 sec)

随机的东西来考虑

drop event someEventName; –

所以不能用别名在1行的where子句中使用

select id,DATEDIFF(now(),updateDt) from theMessages where datediff(now(),updateDt)>6;

得到更准确的，一个星期的168小时

select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as `difference` FROM theMessages; +----+------------+ | id | difference | +----+------------+ | 1 | 410 | | 2 | 301 | | 3 | 169 | | 4 | 167 | +----+------------+

手动页面的链接显示了相当多的灵活性和间隔select，如下所示：

间隔：

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

并发

embedded任何需要多个事件(或同一个事件的多个事件)的并发性措施不会导致数据运行失控。

设置和忘记

请记住，现在，因为你会忘记它，这些事件只是继续开火。 所以build立一个稳定的代码，即使在你忘记的时候也会继续运行。 你最有可能的。

您的特殊要求

您需要确定哪些行需要首先按表删除，以使其符合主键约束。 只需要通过CREATE EVENT语句在显而易见的区域内以适当的顺序包装它们，这可能是巨大的。