Apr 24, 2014

Event Scheduler in Mysql

Posted by Nevil patel
Mysql Event scheduler is used to schedule events which can be triggered periodically or only 1 time. It is supported in version MySQL 5.1.6 and above. It is similar to Cron jobs in UNIX which run periodically to perform operations which need to be done daily,monthly etc.

By default, Event scheduler is off. You have to start it explicitly. To do so, you can run this query.

---------------------------------------------------------------------------------------------
SET GLOBAL event_scheduler = ON;
---------------------------------------------------------------------------------------------



To turn off, event scheduler in mysql,

---------------------------------------------------------------------------------------------
SET GLOBAL event_scheduler = OFF;
---------------------------------------------------------------------------------------------

This event scheduling can be used reporting functionality. You can generate reports on hourly,monthly,daily basis.

Create Event To Trigger Once

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

So, above event triggers after 1 minute from system time and store the data of user table into excel file.

Now, different time stamps you can specify,

(1) CURRENT_TIMESTAMP
(2) CURRENT_TIMESTAMP + n [HOUR|MONTH|WEEK|DAY|MINUTE]

Create Event To Trigger Periodically

This event triggers at every hour everyday.

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username5`
ON SCHEDULE EVERY 1 HOUR
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

but suppose if you want to run the trigger every hour but only for few days then, below will help,

This will run every hour but for only 5 days.

---------------------------------------------------------------------------------------------
DELIMITER $$
CREATE
EVENT `change_username`
ON SCHEDULE EVERY 1 HOUR
Starts CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\nev.xls";
END;
$$
---------------------------------------------------------------------------------------------

Check All Events

To check all the events, created you can fire below query,

---------------------------------------------------------------------------------------------
show events;
---------------------------------------------------------------------------------------------




ALTER EVENTS

You can also alter the name of event or its timings.

(1) Alter Event name

---------------------------------------------------------------------------------------------
ALTER EVENT oldeventname RENAME TO neweventname;
---------------------------------------------------------------------------------------------

(2) Alter triggering timings

---------------------------------------------------------------------------------------------
DELIMITER $$
ALTER
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO BEGIN
select * from user INTO OUTFILE "C:\\NewF\\new.xls";
END;
$$
---------------------------------------------------------------------------------------------

(3) Alter queries inside events
    
---------------------------------------------------------------------------------------------
DELIMITER $$
ALTER
EVENT `change_username5`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO BEGIN
update user set username='nev' where user_id = 100;
END;
$$
---------------------------------------------------------------------------------------------

DELETE EVENTS

---------------------------------------------------------------------------------------------
Drop event  change_username5;
---------------------------------------------------------------------------------------------

0 comments:

Post a Comment