Update every 5 minutes a value

I am using a MySql DataBase and I want to know if there are any methods to modify a value of a column every x minutes/hours/days.

For example, I want to execute the following query every 5 minutes, UPDATE table SET x=0;.

Could I set an event or something like this from the PHPMyAdmin interface?

Answer

I’m not aware about how to use PHPMyAdmin interface to create events, however, this can be done in “pure” SQL :

CREATE EVENT IF NOT EXISTS your_event_name
ON SCHEDULE EVERY 5 MINUTE
DO UPDATE table SET x=0;

About the part ON SCHEDULE EVERY 5 MINUTE, this will execute the event now, and then every 5 minutes, forever.

If you want to delay the execution, you can add STARTS after the EVERY statement :

-- This will delay the first execution in 1 hour
ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

You can define when the EVENT should stop working using ENDS :

-- This will end the event in 1 week
ON SCHEDULE EVERY 5 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 1 WEEK

Of course, you can combine both STARTS and ENDS

-- This will end the event in 1 week
ON SCHEDULE
    EVERY 5 MINUTE -- Play every 5 minutes
    STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR -- Start in 1 hour
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 WEEK -- End in 1 week

If you have more than 1 query to perform in the event, you need to wrap the instructions inside BEGIN / END :

DELIMITER $$

CREATE EVENT IF NOT EXISTS your_event_name
ON SCHEDULE EVERY 5 MINUTE
DO
    BEGIN
        UPDATE table1 SET x=0;
        UPDATE table2 SET foo='bar';
    END $$

DELIMITER ;

For more informations, check the documentation