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
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
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757