How to Schedule a Stored Procedure to Run Regularly in MySQL?

How to Schedule a Routine in MySQL

MySQL allows you to schedule stored procedures to run at specified intervals. This can be useful for automating tasks such as data cleanup or regular reports. To schedule a stored procedure, you can use the CREATE EVENT statement.

Scheduling a Stored Procedure to Delete Old Data

Consider the following stored procedure (delete_rows_links) which deletes activation links that are older than one day:

<code class="sql">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</code>

To schedule this stored procedure to run every 5 seconds, you can use the following CREATE EVENT statement:

<code class="sql">CREATE EVENT myevent
    ON SCHEDULE EVERY 5 SECOND
    DO
      CALL delete_rows_links();</code>

This event will cause the delete_rows_links stored procedure to be executed every 5 seconds. You can find more examples of event scheduling in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/create-event.html

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