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 <p><strong>Clarification:</strong></p><p>Instead of using the condition suggested in the question ("DELETE messages WHERE date >= (the current date - 7 days)"), we use "WHERE date </p><p><strong>Recommendation:</strong></p><p>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.</p>

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!