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

问题描述：

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

我见过一些解决方案，但不确定这是否是我想要的，如果有人能提供一些好的例子就太好了。我知道这可以用 python 和 php 中的简单脚本来完成，但我希望 MySQL 能更自动化一些。

任何帮助都将非常感激。

谢谢！

解决方案 1：

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 个活动，第一个活动每天运行一次，第二个活动每 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 eventsG;` -- <--------- 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 用作别名并在 where 子句中使用，因此

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

更精确地说，168 小时代表 1 周大

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 语句将它们全部按正确的顺序放在明显的区域内即可，这可能非常庞大。

解决方案 2：

您可以使用下面的存储过程并通过 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 ;