Rotina de limpeza - MySQL

Através do script abaixo, é possível realizar a limpeza da tabela de entrada e saída.

DROP PROCEDURE IF EXISTS limpezaOutput;

DELIMITER //

CREATE PROCEDURE limpezaOutput()

BEGIN

DECLARE registros INT;

SELECT COUNT(*) into registros FROM (SELECT * from nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

WHILE (registros > 0) DO

SELECT ("Deletando no maximo 1000 registros...");

DELETE FROM nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000;

SELECT COUNT(*) into registros FROM (SELECT * from nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

END WHILE;

END //

DELIMITER ;

DROP PROCEDURE IF EXISTS limpezaInput;

DELIMITER //

CREATE PROCEDURE limpezaInput()

BEGIN

DECLARE registros INT;

SELECT COUNT(*) into registros FROM (SELECT * from nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

WHILE (registros > 0) DO

SELECT ("Deletando no maximo 1000 registros...");

DELETE FROM nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000;

SELECT COUNT(*) into registros FROM (SELECT * from nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

END WHILE;

END //

DELIMITER ;

DROP EVENT IF EXISTS EvtLimpezaTabelas;

DELIMITER //

CREATE EVENT EvtLimpezaTabelas

ON SCHEDULE EVERY 10 MINUTE

COMMENT 'Remover registros com mais de 30 dias'

BEGIN

CALL limpezaInput();

CALL limpezaOutput();

END //

DELIMITER ;

Para ativação do agendamento de tarefa, deverá ser adicionado a linha abaixo na sessão [mysqld]:

event_scheduler = ON

Voltar