user455318 user455318 - 6 months ago 43
SQL Question

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

Answer

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();