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

我想知道是否可以创build一个自动存储过程,每天在00:00,删除每个表的每一行超过7天。

我已经看到了一些解决scheme,但不知道它是我所寻找的,如果有人有任何好的例子,会很好。 我知道这可以用python和php中的简单脚本完成,但是我希望MySQL更自动化

任何帮助将非常感激。

谢谢!

Solutions Collecting From Web of "在MYSQL中自动删除超过7天的行的存储过程"

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

 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 | +-----------------+-------+ 

架构进行测试

 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个活动,每天第一次运行,每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; -- <--------- 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; – <—–知道一件好事

所以不能用别名在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 | +----+------------+ 

手动页面的链接显示了相当多的灵活性和间隔选择,如下所示:

间隔:

 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语句在显而易见的区域内以适当的顺序包装它们,这可能是巨大的。

您可以使用下面的存储过程,并通过crontab或通过事件进行安排。

注意:只要在数据库中更改mydb,在测试环境中首先要删除和测试哪个数据库表数据。

 DELIMITER $$ USE `mydb`$$ DROP PROCEDURE IF EXISTS `sp_delete`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete`() BEGIN DECLARE done INT(1) DEFAULT 0; DECLARE _tbl VARCHAR(100) DEFAULT ''; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=done; DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='mydb' AND table_type='base table'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=done; OPEN cur1; REPEAT FETCH cur1 INTO _tbl; IF _db = '' THEN SET done = 1; END IF; IF (done<>1) THEN SET @str=CONCAT("delete from ",_tbl," where updateon < SUBDATE(CURDATE(),INTERVAL 7 DAY)"); PREPARE stmt FROM @str; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; UNTIL done END REPEAT; CLOSE cur1; SELECT 'done'; END$$ DELIMITER ;