Create MySQL Events / Schedulers

MySQL Event is, performing or executing some operation based on the specified or scheduled time. MySQL Events have been added from version 5.1.6 MySQL event scheduler is a process that runs in background and looks for events to execute. Before we create or schedule an event in MySQL, we need to first verify whether its enabled or not Issue the following command to turn on the scheduler

1
SET GLOBAL event_scheduler = ON;

To disable the scheduler run

1
SET GLOBAL event_scheduler = OFF;

To check whether scheduler is enabled or not, use the following command

1
select @@GLOBAL.event_scheduler;

Use the following command to list all the events that are created

1
SHOW EVENTS;

By using events, we will insert records into some table lets call it as test_table, for each day at a specific time. Our test_table will look something like below

1
2
3
4
5
CREATE TABLE test.test_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  created_date DATETIME DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

The following will be used for creating an event which will insert a record into test_table for every 1 minute

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$

CREATE EVENT test.first_event
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION NOT PRESERVE ENABLE
DO
BEGIN
    INSERT INTO test.test_table(created_date) VALUES (CURRENT_TIMESTAMP);
END $$

DELIMITER ;

Once we have created our first_event, it will insert a new record for each minute into test_table. If we want to alter the existing event to run for each 1 hour then

1
2
3
4
5
6
DELIMITER $$

ALTER EVENT test.first_event
ON SCHEDULE EVERY 1 HOUR $$

DELIMITER ;

The following can be used drop the events from the database

1
DROP EVENT IF EXISTS test.first_event;

Reference:
https://dev.mysql.com/doc/refman/5.1/en/events-overview.html