Detailed Mysql create a timed task with Event scheduler Events Scheduler _mysql

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:

Show EVENTS;

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;

DOThe 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.