MySQL Events or scheduled events are tasks that execute according to a specified schedule. They are stored in the database and executed at one or more intervals.
For example, you can create an event that optimizes a table by backfilling data from another table the database that runs at 1:00 AM every day.
MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows. MySQL Event Scheduler manages the schedule and execution of Events. MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.
Run SHOW PROCESSLIST to make sure that event_scheduler User shows up as an indication that you can create an event.
Show event details
You can use show events query to show details of events. You can also use the INFORMATION_SCHEMA.EVENTS table to do the same.
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA = 'database_name'; SELECT EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION, EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA = 'database_name'; SHOW EVENTS FROM database_name;
Turn on and off the event scheduler
-- turn on event_scheduler SET GLOBAL event_scheduler = ON; -- turn off event_scheduler SET GLOBAL event_scheduler = OFF;
CREATE EVENT requires the
EVENT privilege for the schema in which the event is to be created. If the
DEFINER clause is present, the privileges required depend on the
user value. An event is associated with a schema. If no schema is indicated as part of
event_name, the default (current) schema is assumed.
// This event executes once—one hour following its creation CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.sometable SET somecol = somecol + 1;
Recurring event with end time
With ENDS the recurring event will stop its execution at that time.
CREATE EVENT IF NOT EXISTS E_MAX_FIRE ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO activity(event_name, event_fired_at) VALUES('max-event',NOW());
Recurring event with stored procedure
Note that with no ENDS clause the recurring event will continue to execute at EVERY 1 MINUTE forever.
delimiter | CREATE EVENT E_MIN_FIRE ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO activity(event_name, event_fired_at) VALUES('min-event',NOW()); END | delimiter ;
DROP EVENT [IF EXISTS] event_name; // delete E_MIN_FIRE event DROP EVENT IF EXISTS E_MIN_FIRE;