Detailed Description: MySQL uses Event Scheduler to create a scheduled task, mysqlscheduler

Detailed Description: MySQL uses Event Scheduler to create a scheduled task, mysqlscheduler

Preface

The event scheduler is equivalent to scheduled tasks in the operating system (for example, scheduled tasks in cron and Window in Linux), but the MySql event scheduler can be precise to seconds, it is very useful for data processing with high real-time requirements.

1. Create/modify an EVENT)

In MySql, CREATE a new scheduler to use the create event. The 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;

The preceding statement contains the following parameters:

1,event_name-Event name, which can be any MySql identifier and cannot exceed 64 characters.
When creating an event, you can specifySchema, Syntax structure:schema_name.event_name

2,schedule -Scheduling rules: Specify the event execution time and execution rules. Is a substatement 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-The event body can be a single-line SQL syntax or a BEGIN ...... END statement Block

You can useSHOWLanguage Name:

SHOW EVENTS;

Event Modification

For an existing event scheduler, you can useALTERStatement. The syntax structure is 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 essence of enabling and disabling an event is to useALTERStatement to modify the created event. For example,Close an event:

ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;

Enable an event:

ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;

Some event examples

In the simplest examplemyschema.mytableTablemycolColumn, auto-increment by 1 per hour:

CREATE EVENT myevent  ON SCHEDULE   AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR  DO   UPDATE myschema.mytable SET mycol = mycol + 1;

In this way, we createmyeventIs executed every hour after the event is created. The execution rules are equivalent:

CREATE EVENT myevent  ON SCHEDULE   EVERY 1 HOUR  STARTS CURRENT_TIMESTAMP  DO   UPDATE myschema.mytable SET mycol = mycol + 1;

If you need to enable the transaction at a certain interval, for example, enable the transaction after 1 day:

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 statement can be a statement block, such:

DELIMITER // CREATE EVENT e ON SCHEDULE   EVERY 5 SECOND DO BEGIN   DECLARE v INTEGER;   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;   SET v = 0;   WHILE v < 5 DO     INSERT INTO t1 VALUES (0);       UPDATE t2 SET s1 = s1 + 1;     SET v = v + 1;   END WHILE; END // DELIMITER ; 

2. Configure the event Scheduler

2.1 event scheduler status

To ensure that the created event can be executed properly, enable the event scheduler. You can view the scheduler status in the following three ways:

SHOW VARIABLES LIKE 'event_scheduler';SELECT @@event_scheduler;SHOW PROCESSLIST;

View the execution status of an event:

SELECT * FROM information_schema.EVENTS;

The above will output when offSchemaAll event information, you can firstDESC information_schema.EVENTS;View the output field, and then view the required information. For example, I only want to view the event name and the last execution time:

SELECT EVENT_NAME, LAST_EXECUTED FROM information_schema.EVENTS;

2.2 enable/disable the event Scheduler

If the event scheduler is not enabled, you can enable it in the following four 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 it is enabled. Similarly, if you want to disable it, you only need to set the value to 0 or OFF.

Summary

The above is all about using Event Scheduler in MySQL to create a scheduled task. I hope this article will help you learn how to use MySQL.