自动删除MYSQL中7天以上的行的存储过程

Mysql具有其EVENT功能，可避免您计划的大部分与sql有关，而与文件无关的情况下避免复杂的cron交互。请参见此处的手册页面。希望以下内容可以作为重要步骤和要考虑事项以及可验证测试的简要概述。

show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 糟糕，事件调度程序未打开。什么都不会触发。

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

show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 测试架构 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'); 创建2个事件，每天运行1次，每10分钟运行2次 忽略他们实际在做什么（互相对抗）。重点是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; -- <--------- from workbench / sqlyog show events\G;` -- <--------- I like this one from mysql> 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; -<-----要了解的一件好事

无法别名datediff并在1行的where子句中使用，因此

select id,DATEDIFF(now(),updateDt) from theMessages where datediff(now(),updateDt)>6; 获得更精确的信息，1周龄为168小时

select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as difference FROM theMessages; +----+------------+ | id | difference | +----+------------+ | 1 | 410 | | 2 | 301 | | 3 | 169 | | 4 | 167 | +----+------------+ 手册页的链接显示了很多灵活的间隔选择，如下所示：

间隔：

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} 并发 嵌入所有必要的并发措施，以确保多个事件（或同一事件的多次触发）不会导致数据无法正常运行。

设置并忘记 请记住，现在，因为您将忘记它，所以这些事件一直在触发。因此，即使您忘了，也要构建可靠的代码以保持运行。您最有可能会。

您的特殊要求 您需要确定哪些表首先需要由表删除，以使其符合主键约束。只需通过CREATE EVENT语句将它们全部按适当顺序放在明显区域内即可，这可能会很大。来源：stack overflow