How to Automatically Delete MySQL Records After Seven Days?

Deleting MySQL Records After a Duration

In this post, we'll address the concern of deleting records from a MySQL database after a specified time interval.

Problem:

Messages in a MySQL table with "id," "message," and "date" columns need to be deleted after seven days. Dates are specified in the "YYYY-MM-DD HH:MM:SS" format. It's proposed to use a MySQL event rather than a cron job for this deletion process.

Query:

The query below includes a revised condition to delete messages older than 7 days:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO BEGIN
    DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

Clarification:

Instead of using the condition suggested in the question ("DELETE messages WHERE date >= (the current date - 7 days)"), we use "WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)". This ensures that messages that are exactly 7 days old are deleted, whereas the previous condition would not delete them.

Recommendation:

While MySQL events can be used for this purpose, using a simple cron script is also a valid option. It offers easier maintenance, avoids complex SQL workarounds, and seamlessly integrates with the system.

The above is the detailed content of How to Automatically Delete MySQL Records After Seven Days?. For more information, please follow other related articles on the PHP Chinese website!