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

SHOW EVENTS;

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;