Procedura memorizzata che cancella automaticamente le righe più vecchie di 7 giorni in MYSQL

Vorrei sapere se è ansible creare una procedura memorizzata che automaticamente, ogni giorno alle 00:00, cancella ogni riga di ogni tabella che supera i 7 giorni.

Ho visto poche soluzioni ma non sono sicuro se è quello che sto cercando, e sarebbe bello se qualcuno avesse un buon esempio. So che questo potrebbe essere fatto con semplici script in python e php, ma vorrei qualcosa di più automatizzato da MySQL.

Qualsiasi aiuto sarebbe davvero apprezzato.

Grazie!

Mysql ha la sua funzionalità EVENT per evitare complicate interazioni cron quando gran parte di ciò che si sta pianificando è sql related e meno file correlati. Vedi la pagina del manuale qui . Si spera che il testo sottostante sia una rapida panoramica dei passaggi importanti e delle cose da considerare e dei test verificabili.

 show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 

ooops, l’event scheduler non è acceso. Nulla si innescherà.

SET GLOBAL event_scheduler = ON; -- turn her on and confirm below

 show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 

Schema per i test

 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'); 

Crea 2 eventi, 1a corsa al giorno, 2a corsa ogni 10 minuti

Ignora ciò che stanno effettivamente facendo (giocando l’uno contro l’altro). Il punto è sugli approcci e la programmazione della 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 ; 

Mostra stati degli eventi (approcci diversi)

 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: [email protected] 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: [email protected] 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) 

Roba a caso da considerare

drop event someEventName; - <----- una buona cosa da sapere

non posso alias datediff e usare in where clausola in 1 riga, quindi

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

ottenere più esatti, 168 ore per 1 settimana

 select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as `difference` FROM theMessages; +----+------------+ | id | difference | +----+------------+ | 1 | 410 | | 2 | 301 | | 3 | 169 | | 4 | 167 | +----+------------+ 

Il collegamento alla pagina di manuale mostra un po 'di flessibilità con le opzioni di intervallo, mostrate di seguito:

intervallo:

 quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} 

Concorrenza

Incorpora tutte le misure di concorrenza necessarie affinché più eventi (o più licenziamenti dello stesso evento) non facciano impazzire i dati.

Imposta e dimentica

Ricorda, per ora, perché lo dimenticherai, che questi eventi continuano a sparare. Quindi costruisci un codice solido che continuerà a funzionare, anche quando lo dimentichi. Che probabilmente lo farai.

Le tue esigenze particolari

È necessario determinare quali righe devono essere eliminate prima dalla tabella, in modo tale da rispettare i vincoli della chiave primaria. Basta raggrupparli tutti nell'ordine giusto all'interno dell'area ovvia tramite l'istruzione CREATE EVENT, che può essere enorme.

È ansible utilizzare la procedura memorizzata sotto e programmarla da crontab o tramite eventi.

Nota: basta cambiare mydb con il database, quali dati delle tabelle del database si desidera eliminare e testare prima nell’ambiente di test.

 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 ;