MySQL Events are tasks that execute according to a specified schedule. Therefore, sometimes MySQL events are referred to as scheduled events.
It contains one or more SQL statement. They are stored in the database and executed at one or more intervals.
MySQL Event Scheduler manages the schedule and execution of Events.
MySQL uses a special thread called event scheduler thread to execute all scheduled events.
To enable and start the event scheduler thread, set event_scheduler system variable to ON.
SET GLOBAL event_scheduler = ON;
To disable and stop the event scheduler thread, set event_scheduler system variable to OFF:
SET GLOBAL event_scheduler = OFF;
To view the status of the event scheduler thread, execute the below command.
SHOW PROCESSLIST;
Now let's create a MySQL Event.
The following example creates an on-time event that inserts a new row in the messages table.
First create a messages table.
CREATE TABLE messages (
id INT AUTO_INCREMENT,
userId INT,
message VARCHAR(255) NOT NULL,
PRIMARY KEY (id , userId)
);
Now create an event.
create event event_01
on schedule at current_timestamp
do
insert into messages(userId,message)
values('1','Test MYSQL Event');
Check messages table and 1 row is inserted which means that event was executed when it is created.
To see all events in the database, use the following command.
SHOW EVENTS FROM databasename;
The output shows no row because it is a one-time event and expired when its execution completed.
To keep the event after it is expired,use the ON COMPLETION PRESERVE clause.
create event event_02
on schedule at current_timestamp
ON COMPLETION PRESERVE
do
insert into messages(userId,message)
values('1','Test MYSQL Event 1');
The above statement creates one-time event that is executed when it is created and not dropped after execution.
Now check the messages table.
If you execute the SHOW EVENTS statement again, you will see that the event is there because of the effect of the ON
COMPLETION PRESERVE clause:
Now let's create a recurring event which will execute every minute and is expired in 5 minutes from its creation time:
create event event_03
on schedule every 1 minute
starts current_timestamp
ends current_timestamp + interval 5 minute
do
insert into messages(userId,message)
values('1','Test MYSQL Event 2');
Check messages table after 5 mins.
To drop an event, use the following command.
DROP EVENT [IF EXIST] event_name;
