Search code examples
mysqldatabasestored-proceduresmysql-routines

How to schedule a stored procedure in MySQL

I have this stored procedure. How can I run this for example with intervals of 5 seconds? Like a routine for eliminate data with a time-stamp older than one day?

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
Solution

  • You can use mysql scheduler to run it each 5 seconds. You can find samples at http://dev.mysql.com/doc/refman/5.1/en/create-event.html

    Never used it but I hope this would work:

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