How to Schedule a Stored Procedure to Delete Stale Data in MySQL?

Scheduling a Stored Procedure in MySQL

MySQL allows you to schedule stored procedures to run at specific intervals, making it convenient for automating tasks. For instance, you could set up a procedure to periodically delete stale data.

Let's explore how to schedule the following stored procedure:

DROP PROCEDURE IF EXISTS `delete_rows_links` 
GO

CREATE PROCEDURE delete_rows_links
BEGIN 

    DELETE activation_link
    FROM activation_link_password_reset
    WHERE  TIMESTAMPDIFF(DAY, `time`, NOW()) < 1 ; 

END 

GO

Scheduling the Procedure

To schedule the procedure, use the CREATE EVENT statement. Here's an example that runs the procedure every 5 seconds:

CREATE EVENT myevent
    ON SCHEDULE EVERY 5 SECOND
    DO
      CALL delete_rows_links();

This event will trigger the procedure to run every 5 seconds, effectively eliminating outdated data.

Additional Information

For more reference on event scheduling in MySQL, consult the following resources:

• [MySQL Event Scheduling](https://dev.mysql.com/doc/refman/8.0/en/create-event.html)
• [Using MySQL Events for Scheduling Tasks](https://www.digitalocean.com/community/tutorials/how-to-use-mysql-events-for-scheduling-tasks)

By following these steps, you can easily schedule stored procedures in MySQL, ensuring timely execution of automated tasks and data management routines.

The above is the detailed content of How to Schedule a Stored Procedure to Delete Stale Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!