How to use MySQL Events? Reviewed by Momizat on . 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, ag 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, ag Rating: 0

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;

Leave a Comment

© 2014 Powered By