Objective
The event Scheduler is equivalent to timed tasks in the operating system (such as Cron in Linux, scheduled Tasks in Windows), but the MySQL event Scheduler can be accurate to seconds and is useful for some real-time data processing requirements.
1. Create/Modify events (event)
In MySQL, create a new scheduler to use the Create EVENT, whose syntax rules are as follows:
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;
In the preceding statement, the following parameters are included:
1, event_name
-event name, can be any combination of MySQL identifier, not exceeding 64 characters.
When you create an event, you can specify Schema
that the syntax structure is:schema_name.event_name
2, schedule
-Scheduling rules, specify the execution time of the event and enforcement rules. is a child statement that can contain the following values:
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}
3, event_body
-event body, can be a single line of SQL syntax, or begin ... End Statement block
To view the events that have been created, you can use the SHOW
language name:
Changes to Events
For an existing event scheduler, you can use the ALTER
statement to modify the syntax as follows:
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]
The nature of the event's opening and closing is ALTER
to modify the created event with the statement. For example, close an event :
ALTER EVENT e_test on completion PRESERVE ENABLE;
To open an event:
ALTER EVENT e_test on completion PRESERVE DISABLE;
Some examples of event usage
One of the simplest examples is to myschema.mytable
increase the table mycol
columns by 1 per hour:
CREATE EVENT myevent on
SCHEDULE in
current_timestamp + INTERVAL 1 HOUR
do
UPDATE myschema.mytable SET MyCol = MyCol + 1;
In this way, we create a named myevent
event that executes once every hour after the event is created. The execution rules set are equivalent to:
CREATE EVENT myevent on
SCHEDULE
EVERY 1 HOUR
starts does
UPDATE current_timestamp SET MyCol = MyCol + 1;
If you need to open a transaction for a certain amount of time, for example, 1 days later:
CREATE EVENT myevent on
SCHEDULE
EVERY 1 HOUR
starts Current_timestamp + INTERVAL 1 day
do
UPDATE myschema.mytable SET MyCol = MyCol + 1;
DO
The executed SQL can be a block of statements, such as:
DELIMITER//
CREATE EVENT E on
SCHEDULE
EVERY 5 SECOND
do
BEGIN
DECLARE v INTEGER;
DECLARE CONTINUE HANDLER for SQLEXCEPTION the BEGIN end;
SET v = 0;
While v < 5 does
INSERT into T1 VALUES (0);
UPDATE t2 SET S1 = s1 + 1;
SET v = v + 1;
End While;
End//
2. Configuration of the event scheduler
2.1 Event Scheduler Status
To ensure that the events you create work correctly, you should first turn on the event scheduler, which allows you to view the scheduler status in 3 ways:
Show VARIABLES like ' Event_scheduler ';
SELECT @ @event_scheduler;
Show Processlist;
To view the execution of an event:
SELECT * from INFORMATION_SCHEMA. EVENTS;
This will output Schema
all the event information in the shutdown, and you can DESC information_schema.EVENTS;
view the required information by looking at the output field first. For example, I just want to see the event name and last execution time:
SELECT Event_Name, last_executed from INFORMATION_SCHEMA. EVENTS;
2.2 Toggle Event Scheduler on/Off
If the event scheduler is not turned on, you can enable it in the following 4 ways:
SET GLOBAL event_scheduler = 1;
SET @ @global. Event_scheduler = 1;
SET GLOBAL event_scheduler = on;
SET @ @global. Event_scheduler = on;
1 or on indicates that the is set to open. Similarly, if you need to close, you can have a value of 0 or off.
Summarize
The above is in MySQL with the event Scheduler Events Scheduler to create the full contents of the scheduled task, I hope this article on the content of everyone learning to use MySQL can help.