How to Automatically Delete Rows Older Than 7 Days in MySQL?

Automatically Deleting Rows Older than 7 Days in MySQL

Introduction

Databases often accumulate obsolete data over time, leading to bloated tables and performance issues. To efficiently manage such data, MySQL provides the ability to create stored procedures that automate cleanup tasks.

Creating a Stored Procedure to Delete Old Data

The following stored procedure will automatically delete rows older than 7 days from all tables in a database:

DELIMITER $$

CREATE PROCEDURE delete_old_data()
BEGIN
  DECLARE tb_name VARCHAR(255);
  DECLARE max_date DATE;
  
  SET max_date = DATE_SUB(NOW(), INTERVAL 7 DAY);
  
  SET tb_name = (
    SELECT GROUP_CONCAT(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
  );
  
  CALL execute_delete_query(tb_name, max_date);
END $$

DELIMITER ;

This stored procedure initializes variables to store the database name, maximum deletion date, and a comma-separated list of table names. It then calls a helper function (execute_delete_query) to perform the actual deletion.

Helper Function for Deleting

CREATE FUNCTION execute_delete_query(tb_names VARCHAR(255), max_date DATE)
RETURNS INTEGER
BEGIN
  DECLARE tb_array [] VARCHAR(255);
  SET tb_array = SPLIT_STR(tb_names, ',');
  
  DECLARE i INT DEFAULT 1;
  DECLARE total_deleted INT DEFAULT 0;
  
  WHILE i <= SIZE(tb_array) DO
    SET @sql = CONCAT('DELETE FROM ', tb_array[i], ' WHERE update_timestamp < ''', max_date, ''');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET total_deleted = total_deleted + ROW_COUNT();
    DEALLOCATE PREPARE stmt;
    
    SET i = i + 1;
  END WHILE;
  
  RETURN total_deleted;
END

The helper function execute_delete_query takes two parameters: a list of table names and a maximum deletion date. It iterates through the table names, constructs a deletion query for each, and executes it using a prepared statement. The total number of deleted rows is returned.

Scheduling the Stored Procedure

To schedule the stored procedure to run automatically every day at 00:00, follow these steps:

1. Create an event using the following SQL statement:

   CREATE EVENT delete_old_data_event
   ON SCHEDULE AT '00:00' EVERY 1 DAY
   DO
   CALL delete_old_data();

2. Enable the event scheduler by setting the event_scheduler system variable to ON:

   SET GLOBAL event_scheduler = ON;

3. Verify that the event is scheduled by running SHOW EVENTS statement.

Conclusion

This method uses MySQL's stored procedures, event scheduling, and helper functions to create a comprehensive and automated solution for deleting obsolete data from a database. By regularly removing old records, you can maintain the efficiency and performance of your database while ensuring data compliance.

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