自动删除MYSQL中7天以上的行的存储过程 [英] Stored procedure that Automatically delete rows older than 7 days in MYSQL
问题描述
我想知道是否有可能创建一个存储过程，该存储过程自动在每天的00:00删除超过7天的每个表的每一行.
I would like to know if is possible to create a stored procedure that automatically, every day at 00:00, deletes every row of every table that is over 7 days.
我看到的解决方案很少，但不确定是否要寻找它，如果有人有很好的榜样，那会很好.我知道可以使用python和php中的简单脚本来完成此操作，但我希望MySQL可以更自动化.
I have seen few solutions but not sure if its what I am looking for, and would be nice if someone has any good example. I know this could be done with simple scripts in python and php, but I would like something more automated by MySQL.
任何帮助将不胜感激.
谢谢！
推荐答案
Mysql具有其EVENT功能，可以避免在您计划的大部分与sql有关，而与文件无关的情况下进行复杂的cron交互.请参见手册页此处.希望下面的内容可以作为对重要步骤和要考虑的事项以及可验证的测试的快速概述.
Mysql has its EVENT functionality for avoiding complicated cron interactions when much of what you are scheduling is sql related, and less file related. See the Manual page here. Hopefully the below reads as a quick overview of the important steps and things to consider, and verifiable testing too.
show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
糟糕，事件调度程序未打开.什么都不会触发.
ooops, the event scheduler is not turned on. Nothing will trigger.
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;-< -----要了解的一件好事
Random stuff to consider
drop event someEventName; -- <----- a good thing to know about
不能别名datediff并在1行的where子句中使用，所以
can't alias datediff and use in where clause in 1 line, so
select id,DATEDIFF(now(),updateDt) from theMessages where datediff(now(),updateDt)>6;
获得更准确的信息，即1周龄168小时
get more exact, 168 hours for 1 week old
select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as `difference` FROM theMessages;
+----+------------+
| id | difference |
+----+------------+
| 1 | 410 |
| 2 | 301 |
| 3 | 169 |
| 4 | 167 |
+----+------------+
手册页"的链接显示了很多灵活的间隔选择，如下所示:
The link to the Manual Page shows quite a bit of flexibilty with interval choices, shown below:
间隔:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
并发性
嵌入所有必要的并发措施，以防止多个事件(或同一事件的多次触发)不会使数据运行不正常.
Concurrency
Embed any concurrency measures necessary that multiple events (or multiple firings of the same event) don't cause data to run amok.
现在请记住，因为您将忘记它，所以这些事件一直在触发.因此，即使您忘了，也要编写可靠的代码以保持运行.您最有可能会这么做.
Remember, for now, because you are going to forget it, that these events just keep firing. So build in solid code that will just keep running, even when you forget. Which you most likely will.
您需要确定首先要由表删除哪些行，以便采用主键约束.只需通过CREATE EVENT语句将它们全部按适当顺序放在明显区域内即可.
You need to determine which rows need to be deleted first by table, such that it honors Primary Key constraints. Just lump them all in proper order inside of the obvious area via the CREATE EVENT statement, which can be massive.
这篇关于自动删除MYSQL中7天以上的行的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
