In MYSQL, tasks can be scheduled to run at particular date and time. These tasks are called events. The events are different from triggers as they do not occur for a particular event such as INSERT, UPDATE or DELETE, but, events rather occur at regular intervals at specified time.

Events can be one-time or recurrent.

Events are introduced from MYSQL 5.1.12.

Adding Events

Events can be created and scheduled using syntax given below:

Syntax

CREATE EVENT [IF NOT EXISTS] eventName

ON SCHEDULE schedule

DO

...eventBody

Where the eventName is identifier of the event.

A schedule defines when and how the event should occur. The timestamp can be DATETIME or TIMESTAMP type. The schedule can be of format:

AT timestamp [+ INTERVAL interval]...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

Where, Interval is:

YEAR/QUARTER/MONTH/DAY/

HOUR/MINUTE/WEEK/SECOND/

YEAR_MONTH/DAY_HOUR/DAY_MINUTE/DAY_SECOND/

HOUR_MINUTE/HOUR_SECOND/MINUTE_SECOND

The example below shows a vehicle and its timestamp crossing a check point in the database every 5 seconds. It updates the vehicles passed through station assuming 4 vehicles pass through check point every time.

Example

DELIMITER //

CREATE EVENT vehicleTime 

   ON SCHEDULE EVERY 5 SECOND 

   STARTS CURRENT_TIMESTAMP 

   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR 

 

DO 

   BEGIN 

      INSERT INTO event VALUES ('Auto', NOW ()); 

      INSERT INTO event VALUES('Car', NOW()); 

      INSERT INTO event VALUES('Cycle', NOW()); 

      INSERT INTO event VALUES('Bike', NOW()); 

END//

DELIMITER ;

Modifying Events

To modify an existing event the syntax given below can be used. If it is applied to a non-existing event the database throws an error. The modifications to the event can be done on schedule and eventBody.

Syntax

ALTER EVENT eventName;

ON SCHEDULE schedule

DO

...eventBody

The event can be renamed using syntax:

ALTER EVENT eventName1 RENAME TO eventName2;

To move events from one database to another:

ALTER EVENT dataBase1.eventName1 RENAME TO database2.otherEvent;

To disable an event:

ALTER EVENT eventName DISABLE;

Retroactively, an event may be enabled using:

ALTER EVENT eventName ENABLE;

The example from above, may be altered to lesser vehicles for schedule of 1 MINUTE, as shown below:

Example

DELIMITER //

ALTER EVENT vehicleTime 

ON SCHEDULE EVERY 1 MINUTE

STARTS CURRENT_TIMESTAMP

ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

 

DO 

   BEGIN

      INSERT INTO event VALUES('Auto', NOW());

END//

DELIMITER ;

Drop Events

To entirely delete an event we use command DROP:

Syntax

DROP EVENT IF EXIST eventName;

DROP EVENT IF EXIST eventAddTime;

Event Scheduler

MYSQL uses an event schedule thread to process the events. The status of the threads can be viewed by SHOW PROCESSLISTS command. By default the event_scheduler of MYSQL is 'OFF'. It can be turned on using command:

SET GLOBAL event_scheduler=ON;

To set the scheduler OFF, we use:

SET GLOBAL event_scheduler=OFF;