Event Automation in mysql

A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows.

You can use MySQL events in many cases such as optimizing database tables, cleaning up logs, archiving data, or generate complex reports during off-peak time.

MySQL uses a special thread called event schedule thread to execute all scheduled events. We can make use of this by running the following command :

SET GLOBAL event_scheduler = ON;

To disable and stop the event the event scheduler thread, we can run the following command :

SET GLOBAL event_scheduler = OFF;

-> A stored procedure is only executed when it is invoked directly; a trigger is executed when an event associated with a table such as an insert, update, or delete  event occurs while an event can be executed at once or more regular intervals.

To create and schedule a new event, you use the  CREATE EVENT statement as follows:

CREATE EVENT [IF NOT EXIST]  event_name

ON SCHEDULE schedule

DO

event_body

The event name is contained in the  CREATE EVENT clause. The event name must be unique within a database schema. The  ON SCHEDULE clause : If the event is a one-time event, you use the syntax : AT timestamp [+ INTERVAL] . If the event is a recurring event, you use the EVERY clause : EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL] . Place the SQL statements after the DO keyword. It is important to notice that you can call a stored procedure inside the body of the event. In case you have compound SQL statements, you can wrap them in a  BEGIN END block.

Example :

CREATE EVENT myEvent

ON SCHEDULE EVERY 1 MINUTE

STARTS CURRENT_TIMESTAMP

ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

DO

   INSERT INTO myTable(name,age,joining_date)

   VALUES('a',25,NOW());

For deleting an event, the syntax is :


DROP EVENT [IF EXIST] event_name;

Happy Reading
Gunjan Singh