Remove old Mysql records automatically

Question

I have a table that stores logs from a system. I want some method to automatically delete logs older than 60 days. It has how to do this?

Answer 1

You can create an EVENT with your intervalor CURRENT_TIMESTAMP + INTERVAL 1 DAY

Below are some examples of creating "Events" in Mysql:

CREATE EVENT `Dropar tabela t`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO DROP TABLE t;

This event will make Mysql Drope table "t" 5 days from the time of event creation.

CREATE EVENT O_evento_principal
ON SCHEDULE AT TIMESTAMP '2009-03-10 12:00:00'
DO DROP TABLE t;

This event will cause Mysql to Drope the "t" table on March 10, 2009 at 12:00.

ATTENTION: It is important that when an event is created with the instruction "EVERY" before the period, the task will be repeated ALWAYS during of a new cycle.

An instruction "EVERY YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND" results in a continuous run within a cycle ANUAL/MENSAL/SEMANAL/DIÁRIO/HORÀRIO/MINUTOS/SEGUNDOS.

For example:

CREATE EVENT e
ON SCHEDULE EVERY 1 YEAR
DO DROP TABLE t;

This event causes Mysql to perform a "drop" in the "t" table each year, considering as "zero" time the moment of creation of the event.

In your case you could do

CREATE EVENT `Delete tabela t`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
Delete FROM tabela
WHERE TIMESTAMPDIFF(DAY, SuaData + INTERVAL TIMESTAMPDIFF(MONTH,  SuaData , current_date) MONTH , current_date) >= 60;