THe Mysql has great flexibility in database.It has triggers,stored procedure and much more.When you want run a SQL based on change of table data(like event occurred on data), then you need to create Trigger.
But MySQL also has magic EVENT Scheduler functionality, its like cron job which will RUN on specific time.EX:
You need to delete all data of table on specific time,In script level you can create cron job which will RUN on schedule time.
BUT in database level you can create EVENT on database which will RUN on schedule time.
How to check EVEN Scheduler is enabled
Run below command on mysql for check setting of event scheduler:
If you will get below row that’s mean your Mysql has enable:
The Syntax to Create Event Schedular in MySql
CREATE EVENT `event_name`
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
-- event body
This statement creates and schedules a new event. The event will not run unless the Event Scheduler is enabled.
you can get more details from MySQL CREATE EVENT Syntax documentation
The schedule can be assigned various settings, e.g.
Run Event Scheduler once on a specific date/time
AT ‘YYYY-MM-DD HH:MM.SS’
<strong>e.g.</strong> AT ’2013-30-01 05:00.00′
Run Event Scheduler once after a specific period has elapsed
AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
<strong>e.g.</strong> AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
Run Mysql Event Scheduler at specific intervals forever
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
<strong>e.g.</strong> EVERY 1 DAY
Run Event Scheduler at specific intervals during a specific period
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date
e.g. EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS <code>’2012-01-01 00:00.00′
You can also dropped once its schedule has expired with help of (ON COMPLETION NOT PRESERVE).
IF you will set (ON COMPLETION PRESERVE) then your event will preserve after schedule.
How to Create Scheduled Events in MySQL
ALter DEFINER=`root`@`localhost` EVENT `scg_insights_app1`.`failedservices_truncate` ON
SCHEDULE EVERY 1 MINUTE
Delete From failedservices