The basic learning course of event scheduling in Mysql _mysql

It is often necessary to have some timed tasks performed on MySQL tables, such as statistics, migrations, deleting unwanted data, and so on. The previous practice was to use Linux cron to run the script regularly, but found that such additional dependencies are sometimes inconvenient, such as stand-alone multiple instance deployment, the need to manually configure different cron tasks separately, the need for additional configuration of the appropriate users and permissions, the new environment is easy to leave out cron tasks.

MySQL provides an event Scheduler, similar to the crontab under Linux, that can run tasks, run one or more times according to time schedule.

The complete event Schduler creation statement is 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;

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}

First, the Dispatch scheduler
the schedule in MySQL can be run only once, or you can specify the time interval to run repeatedly. The definition is in the ON schedule clause defined by the event. The clause is formatted as follows:

On SCHEDULE at
timestamp [+ INTERVAL INTERVAL] ...
| EVERY interval
  [starts timestamp [+ interval interval] ...]
  [ENDS timestamp [+ INTERVAL INTERVAL] ...]

The timestamp must include "month and Hour", which participate in the expression calculation, the result is datetime or timestamp type.

And the time interval interval can be as follows:

< digital > {year | Quarter | MONTH | Day | HOUR | MINUTE |
      WEEK | SECOND | Year_month | Day_hour | Day_minute |
      Day_second | Hour_minute | Hour_second | Minute_second}

The meaning is clear, such as year, Quarter quarter, Year_month year + month, Minute_second minute + second.

Add:

Year | Quarter | MONTH | Year_month the background are converted to MONTH, other time intervals are converted into second
The time in the on schedule is used when creating the time zone information in this session time_zone, which defaults to the global time_zone of the server, and may be subsequently manually updated. These times are converted to UTC time and stored in the Mysql.event table.
1. One run
at directly specify a time, or use a time expression to calculate a fixed point in time.

Example:

At ' 2006-02-10 23:59:00′ specify the exact elapsed time, the local time zone.
At Current_timestamp + INTERVAL ' 1:15′minute_second specifies 1 minutes 15 seconds to run.
2. Run multiple times
Every sets the time interval for the run, where [+ INTERVAL INTERVAL] can no longer be specified.

Specifies that starts and ends are optional.

Starts is the first time that you specify a recurring operation. When not specified, it runs the first time when the event is created, which is equivalent to starts current_timestamp!
Ends tells MySQL to end the recurring time point. In the case of unspecified, MySQL will continue to run forever.
Example:

EVERY 5 WEEK Run once every 5 weeks and run for the first time when created.
EVERY 3 day starts ' 2013-12-4 09:10:00′ starts running from ' 2013-12-4 09:10:00′ ' for the first time, runs every 3 days.
EVERY 2 MONTH starts Current_timestamp + INTERVAL MINUTE ' 2014-12-31 ENDS minutes after the start of 2014, run every two months.
Second, event
1. Enable the Event Scheduler feature
the event is executed by a specific event scheduler thread, which can view its current state information through show full processlist, such as:

7384313 event_scheduler localhost [null] Daemon 3 waiting on empty queue [null]

The default event dispatch Event Scheduler feature is not enabled and requires the configuration of global parameter Event_scheduler, which can be dynamically set and take effect immediately.

Event_scheduler has the following three kinds of values:

off/0 off, default value. Event scheduling is not possible without running the event scheduler thread. Set to on to enable immediately.
ON/1 enabled.
DISABLED disabled. The event scheduler thread is also not running. Setup is only useful if the MySQL service is started. When Event_scheduler is on or off, you cannot set Event_scheduler to disabled at run time. If event-scheduler=disabled is configured at startup, the runtime cannot be set to On/off. In other words, you can set the MySQL service to disabled at startup, and then completely disable the Event_scheduler and not dynamically adjust it.
So, to enable Event_scheduler, run-time execution:

Set Global Event_scheduler=on

To be enabled with the MySQL service, add the/ETC/MY.CNF in the

[Mysqld]
Event-scheduler=on

2. Create the syntax of the event

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}

Parameters Detailed Description:
Definer: Defines the user who checks permissions when an event is executed.
On SCHEDULE SCHEDULE: Defines the time and time interval for execution.
On completion [NOT] PRESERVE: Defines whether an event is executed once or permanently, by default to a single execution, that is, not PRESERVE.
ENABLE | DISABLE | DISABLE on SLAVE: Defines whether event creation is turned on or off, and closed from. Disable on SLAVE is automatically added to the statement that the master's creation event is automatically synchronized from the server.
COMMENT ' COMMENT ': A comment that defines an event.

3. Change the syntax of the event

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]

4. Delete the syntax of the event

DROP EVENT [IF EXISTS] Event_Name

5.Do clause
the concrete logic of implementing events in a Do clause, almost all MySQL statements that can be run in a stored program can be used in an event.

1 Simple SQL Example:

CREATE EVENT e_hourly on
  SCHEDULE
   EVERY 1 HOUR
  COMMENT ' Clears out sessions the table each HOUR. '
  Do
   DELETE from Site_activity.sessions;

2 Complex SQL Example:

Delimiter |
CREATE EVENT E on
  SCHEDULE
   EVERY 5 SECOND
  does
   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 |
delimiter;

3 restrictions on SQL in DO clause

Basically, you can use any SQL statement that is allowed in the stored program (Stored routine), and there are some limitations in the stored program, and there are some additional limitations to the event.

Stored routine The following statement does not allow:

• LOCK Tables/unlock TABLES
• Load data and load TABLE

Support for dynamic SQL (PREPARE, EXECUTE, deaalocate PREPARE)! However, some statements in the prepare itself do not allow execution.

INSERT delayed will not take effect
Limit of event:

If the DO clause contains the ALTER EVENT clause, it can be created, but the run-time error occurs.
Do not use Select or show in a do clause so it is only a statement of the query because its output cannot be obtained externally. You can use the Select ... Into such a form to save the query results.

5. View Event
You can view event information in the following ways:

Mysql.event
information_schema.events Show
Events-
Create Event

Iii. Event Schedule other points of attention
MySQL saves the sql_mode as its run-time Sql_mode when the event is created;
If the task is not processed within a scheduling interval, the new schedule will still be generated, which can result in simultaneous and multiple tasks running. If you want to avoid multiple tasks being present at the same time, you can use the Get_lock () function or row and table locks.

Four, the MySQL incident actual combat
Test environment
Create a test table for testing:

CREATE TABLE ' test ' (
 ' id ' int (one) not null auto_increment,
 ' t1 ' datetime DEFAULT null,
 ' id2 ' int (one) not NUL L default ' 0 ',
 PRIMARY KEY (' id ')
engine=innodb auto_increment=106 default Charset=utf8

Combat 1
Ø Create an event that inserts a piece of data into the test table every 3 seconds, as follows:

CREATE EVENT IF not EXISTS test on SCHEDULE EVERY 3 SECOND on
completion PRESERVE does
INSERT into Test (ID,T1) VAL UES (', now ());

Ø Create an event that clears the test table data after 10 minutes

CREATE EVENT IF not EXISTS test on
SCHEDULE in
current_timestamp + INTERVAL 1 MINUTE do
TRUNCATE TABLE test.aa A

Ø to create an event that clears the test table data at 2012-08-23 00:00:00, the code is as follows:

CREATE EVENT IF not EXISTS test on
SCHEDULE in
TIMESTAMP ' 2012-08-23 00:00:00 ' do
TRUNCATE TABLE test;

Ø to create an event that starts from August 22, 2012 21:45 to 10 minutes and runs every 3 seconds to insert a piece of data into the test table with the following code:

CREATE EVENT IF not EXISTS test on SCHEDULE EVERY 3 SECOND
starts ' 2012-08-22 21:49:00 ' ENDS ' 
2012-08-22 21:49:0 0 ' + INTERVAL MINUTE
on completion PRESERVE does
INSERT into Test (ID,T1) VALUES (", now ());

Combat 2
The usual scenario is to call the stored procedure periodically through events, and here's a simple example:
Create a stored procedure that lets the Id2 field of the test table add Cardinal 2 to each row, stored procedure Code as follows:

DROP PROCEDURE IF EXISTS test_add;
DELIMITER//
CREATE PROCEDURE test_add ()
BEGIN
DECLARE 1_id INT DEFAULT 1;
DECLARE 1_id2 INT DEFAULT 0;
DECLARE error_status INT DEFAULT 0;
DECLARE datas CURSOR for SELECT ID from test;
DECLARE CONTINUE HANDLER for not FOUND SET Error_status=1;
OPEN datas;
FETCH datas into 1_id;
REPEAT
SET 1_id2=1_id2+2;
UPDATE test SET id2=1_id2 WHERE id=1_id;
FETCH datas into 1_id;
UNTIL Error_status end
REPEAT;
Close datas;
End
//

Event setting 2012-08-22 00:00:00 starts at a time, calls a stored procedure every 1 times, ends 40 days, and the code reads as follows:

CREATE EVENT test on SCHEDULE EVERY 1 day
starts ' 2012-08-22 00:00:00 ' ENDS ' 2012-08-22 00:00:00 ' +interval-
DA Y on
completion PRESERVE does call
Test_add ();