Home > Database > Mysql Tutorial > body text

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

Barbara Streisand
Release: 2024-11-04 02:19:01
Original
968 people have browsed it

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
Copy after login

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();
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!