How to Automatically Delete MySQL Records Older Than 7 Days?

Deleting MySQL Records Based on Time

To automatically delete messages from your MySQL database after 7 days, you can utilize MySQL events. Here's how to do it:

1. Define the Event:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO
BEGIN
    /* Insert the adjusted delete statement here. */
END;

2. Adjust the Delete Statement:

The provided delete statement uses the >= operator, which includes the current date. To ensure that messages from 7 days ago are deleted, use < instead:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)

3. Complete the Script:

Putting it all together, your revised script should look like this:

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;

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