Usage of mysql scheduled task Event Scheduler

Event Scheduler is a new mysql event. It queries a Scheduler task class function. We can create an Event in mysql to regularly execute some SQL statements, in this case, we do not need to use the scheduled tasks of the system to execute the scheduled tasks as before.

1. Enable

The global variable event_scheduler is used to set whether the server runs the execution plan task. The variable has the following three values:
OFF: the scheduled task is stopped and the event scheduler thread is not running. It is the default value of event_scheduler (execute any command to close it );

The Code is as follows:	Copy code
Set global event_scheduler = OFF; SET @ global. event_scheduler = OFF; Set global event_scheduler = 0; SET @ global. event_scheduler = 0;

ON: the scheduled task is running. The event scheduler thread starts and executes all the scheduled tasks (execute any command to enable it );

 

The Code is as follows:	Copy code
Set global event_scheduler = ON; SET @ global. event_scheduler = ON; Set global event_scheduler = 1; SET @ global. event_scheduler = 1;

DISABLED: This value will make the scheduled task unavailable
Add parameters when running the MySql Service

The Code is as follows:	Copy code
-- Event-schedabled = DISABLED

Or set it in my. cnf.

View Source code printing help
1 event_schedabled = DISABLED
MySQL Event sched is a database object that performs database operations according to a pre-scheduled schedule. It can be seen as a "time trigger ". Event is actually executed by a special event scheduler thread. If it is running, you can see it through the show processlist command.

2. Create

The basic syntax is as follows:

The Code is as follows:	Copy code
1 create event (1) 2 [if not exists] (2) 3 event_name (3) 4 on schedule schedule (4) 5 [on completion [NOT] PRESERVE] (5) 6 [ENABLE | DISABLE] (6) 7 [COMMENT 'comment'] (7) 8 DO SQL _statement (8)

One by one:
(1) Create an Event. These two keywords cannot be missing.
(2) create a database object with the same name if it does not exist.
(3) Name of the scheduled task. As a database object, each database has a unique name for identification.
(4) Plan the task here. There are two keywords to set the task execution plan: AT and EVERY:
AT specifies a one-time plan followed by a timestamp. Subsequent SQL statements will be executed once AT the specified time;
EVERY specifies a periodic plan. In this clause, you can specify that the task is executed EVERY cycle from a certain time point to a certain time point.
(5) Whether the scheduled task object is retained in the database after the task is completed. Not retained by default
(6) valid or invalid. Valid by default
(7) statements to be executed

3. Example

1. Insert a timestamp to the myschema. mytable table every 10 seconds:

The Code is as follows:	Copy code
Create event e_store_ts on schedule every 10 second do insert into myschema. mytable VALUES (UNIX_TIMESTAMP ());

2. In the year starting from 23:59:00, the previous 10 records of the messages table were deleted every one hour:

The Code is as follows:	Copy code
Create event e_hourly ON SCHEDULE EVERY 1 hour starts '2017-02-10 23:59:00 'ENDS '2017-02-10 23:59:00 ′ DO Delete from messages limit 10;

4. view the EVENT

The Code is as follows:

Copy code

Mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS > WHERE EVENT_NAME = 'e _ store_ts' > AND EVENT_SCHEMA = 'myscheme' G * *************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: myschema EVENT_NAME: e_store_ts DEFINER: jon @ ghidora EVENT_BODY: SQL EVENT_DEFINITION: insert into myschema. mytable VALUES (UNIX_TIMESTAMP ()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 5 INTERVAL_FIELD: SECOND SQL _MODE: NULL STARTS: 0000-00-00 00:00:00 ENDS: 0000-00-00 00:00:00 STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2006-02-09 22:36:06 LAST_ALTERED: 2006-02-09 22:36:06 LAST_EXECUTED: NULL EVENT_COMMENT: 1 row in set (0.00 sec)

5. Delete the EVENT

The Code is as follows:	Copy code
Delete from mysql. event WHERE db = 'myschema' AND definer = 'Jon @ ghidora' AND name = 'e _ insert ';