How Can I Automate Daily Row Deletion in MySQL Based on Age?

Automating Row Deletion in MySQL with Stored Procedures

You aim to create a MySQL stored procedure that removes rows older than seven days from all tables at midnight daily. While scripting solutions exist, this article focuses on a more automated approach using MySQL's EVENT functionality.

Creating the Procedure

First, ensure the event scheduler is enabled:

SET GLOBAL event_scheduler = ON;

Then, create an event that triggers daily at 00:00:

CREATE EVENT `delete7DayOldRows`
ON SCHEDULE EVERY 1 DAY STARTS '2023-03-01 00:00:00'
ON COMPLETION PRESERVE
DO
  -- Your delete logic here
END;

Defining the Deletion Logic

Inside the DO block of the event, specify the deletion logic. Here's an example:

BEGIN
  DECLARE tableName VARCHAR(255);
  DECLARE cursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database_name';
  OPEN cursor;
  FETCH cursor INTO tableName;
  WHILE tableName IS NOT NULL DO
    DELETE FROM `your_database_name`.`tableName` WHERE CURDATE() - INTERVAL 7 DAY > updateDt;
    FETCH cursor INTO tableName;
  END WHILE;
  CLOSE cursor;
END;

This logic iterates through all tables in the specified database and deletes rows where the updateDt column is more than seven days old.

Considerations

• Concurrency: Implement mechanisms to prevent data integrity issues if multiple events execute concurrently.
• Testing: Thoroughly test the procedure to ensure it operates as intended.
• Error Handling: Consider adding error handling within the procedure to catch and address any errors.
• Identification: Keep track of the scheduled events to easily identify and manage them.

The above is the detailed content of How Can I Automate Daily Row Deletion in MySQL Based on Age?. For more information, please follow other related articles on the PHP Chinese website!