MENU


EVENT

The event scheduler thread must be first turned on, for events to work.

Setting @@global.event_scheduler to DISABLED prevents the state from changing during runtime.
SET @@global.event_scheduler=ON;
SHOW PROCESSLIST;
……
35, event_scheduler, localhost, , Daemon, 39,
Waiting on empty queue,


CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body; schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...] interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
DEFINER records the user who defines the event.
ON COMPLETION PRESERVE prevents the event from being dropped once it expires.
ON COMPLETION NOT PRESERVE is the default.
DISABLE prevents the event from running. ENABLE is the default. DISABLE ON SLAVE prevents the event from running on the replicated slave.
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
DROP EVENT [IF EXISTS] event_name
This runs the INSERT statement exactly once, one and a half hour from now.
CREATE TABLE tbl (a INT);
CREATE
    DEFINER = 'root'@'localhost'
    EVENT myEvent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR + INTERVAL 30 MINUTE
    DO
      INSERT INTO tbl VALUES (1);
This runs the INSERT statement once every hour, in the period specified.
DROP TABLE IF EXISTS tbl;
DROP EVENT IF EXISTS myEvent;
CREATE TABLE tbl (a INT);
CREATE EVENT myEvent
    ON SCHEDULE EVERY 1 HOUR
                STARTS '2014-05-02 11:00:00'
                ENDS '2014-05-02 19:00:00'
    DO
      INSERT INTO tbl VALUES (1);