在 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;-- 开启她并在下方确认

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 分钟第二次运行

忽略他们实际在做什么(相互对抗).重点在于时差方法和调度.

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屋！