How Can I Automatically Delete Old MySQL Records Using Events?

Deleting MySQL Records After a Specified Time with an Event

To automatically delete messages older than 7 days from a MySQL database, consider this alternative approach using an event:

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;

This event will execute every day, at the specified time, and delete all messages whose date column is older than 7 days.

Here's an explanation of the code:

• CREATE EVENT: Creates a new event with the specified name (delete_event).
• ON SCHEDULE AT CURRENT_TIMESTAMP INTERVAL 1 DAY: Schedules the event to run once a day, starting from the current timestamp.
• ON COMPLETION PRESERVE: Ensures that the event continues to execute even if it encounters an error.
• DO BEGIN: Start the event's execution.
• DELETE messages ...: Deletes rows from the messages table based on the specified condition. In this case, it deletes messages with a date column that's older than 7 days.
• WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY): Uses the DATE_SUB() function to subtract 7 days from the current date (NOW()) and compares it to the date column value.
• END;: Ends the event's execution.

Alternatively, you could use a cron script to perform this task. However, an advantage of using an event is that it can be configured to run independently of the system's cron scheduler.

The above is the detailed content of How Can I Automatically Delete Old MySQL Records Using Events?. For more information, please follow other related articles on the PHP Chinese website!