How to use MySQL Events?
MySQL events were added in MySQL 5.1.6 and offer an alternative to scheduled tasks and cron jobs.
Events can be used to create backups,
delete stale records,
aggregate data for reports,
and so on.
Unlike standard triggers which execute given a certain condition,
an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger.
You can schedule events to run either once or at a recurring interval when you know your server traffic will be low.
#which is done by issuing the following command: SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = OFF; SHOW PROCESSLIST ## Add/ Create Event ## DELIMITER | CREATE EVENT abc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO BEGIN TRUNCATE login_attempts; END | DELIMITER; After an event has expired it will be automatically deleted unless you explicitly stated otherwise with an ON COMPLETION clause, for example: ## Add/ Create Event with COMPLETION PRESERVE ## DELIMITER | CREATE EVENT abc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR ON COMPLETION PRESERVE DO BEGIN UPDATE mytable SET likes = likes + 1; END | DELIMITER; ## Delete/Remove Event ## DROP EVENT myevent; To specify a recurring event, you would use the EVERY clause: DELIMITER | CREATE EVENT abc ON SCHEDULE EVERY 1 HOUR DO BEGIN UPDATE mytable SET likes = likes + 1; END | DELIMITER; Having an event that just runs once or forever, you can also schedule a re occurring event that is valid only within a specific time period, using START and END clauses: DELIMITER | CREATE EVENT abc ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR DO BEGIN UPDATE mytable SET likes = likes + 1; END | DELIMITER; ## Updating Events ## ALTER EVENT myevent ON SCHEDULE EVERY 1 MONTH STARTS '2011-12-01 01:00:00'| To update the event with a different set of queries, you would use: ALTER EVENT abc DO BEGIN INSERT INTO mystats (total) SELECT COUNT(*) FROM sessions; TRUNCATE sessions; END | To rename an event, you would specify a RENAME clause: ALTER EVENT myevent RENAME TO yourevent;