The Event Schedule is one of the lesser known tools built into mysql. My latest project involves a creating a modbus simulator and the bulk of the project is data driven. It involves taking data from several MySQL tables and converting each field into a 2 byte word and storing in another table which represents a set of modbus registers. I know, not exactly your typical web developer task but who ever said i was a typical web developer.
So this process would occur several times an hour, possibly every five minutes. My first thought was “CRON JOBS”. However the thought of breaking out a server side language just to call some queries or maybe even a stored procedure, was not appealing. Enter MySQL Scheduler. There really is no magic to the scheduler. It operates much like cron jobs but for the database. It runs an operation at a particular time or at a specified interval.
Here is a simple example how to use it. This information is pretty basic and can be extracted from the awesome MySQL docs.
Step 1. Create table to store scheduled events. Add auto incrementing id, value field and auto updating timestamp.
CREATE TABLE zTestSchedule( id INT, value VARCHAR(10), t timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
Step 2. Create recurring event
delimiter | CREATE EVENT e_TEST_MIN ON SCHEDULE EVERY 1 MINUTE COMMENT 'ADDS A NEW COMMENT EACH MINUTE' DO BEGIN INSERT INTO zTestSchedule (`value`) VALUES ("NEW test value"); END | delimiter ;
Step 3. Check if the event was created
Step 4. Wait a few minutes and check you table for updates
SELECT * FROM `zTestSchedule`;
Step 5. Delete event
DROP EVENT IF EXISTS e_TEST_MIN;