Хранимая процедура, которая автоматически удаляет строки старше 7 дней в MYSQL

Я хотел бы знать, возможно ли создать хранимую процедуру, которая автоматически, каждый день в 00:00, удаляет каждую строку каждой таблицы, которая превышает 7 дней.

Я видел несколько решений, но не уверен, что это то, что я ищу, и было бы неплохо, если у кого-то есть хороший пример. Я знаю, что это можно сделать с помощью простых скриптов на python и php, но я бы хотел, чтобы MySQL сделал что-то более автоматизированное.

Любая помощь будет очень признательна.

Спасибо!

Источник

2 ответа

Решение

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 события, 1-й пробег ежедневно, 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; - <----- хорошо знать о

не может использовать псевдоним datediff и использовать оператор where в 1 строке, поэтому

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, который может быть огромным.

Источник

Вы можете использовать хранимую процедуру ниже и либо планировать ее по 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 ;
Источник
Другие вопросы по тегам