Explain Mysql to create scheduled tasks with event scheduler event scheduler

Time:2021-10-17

The event scheduler is in MySQLv5.1.6A new function in, which is equivalent to a timer. It can execute an SQL statement or a statement block at a specified time point, or it can be used to execute repeatedly at fixed intervals. The event scheduler is equivalent to scheduled tasks in the operating system (such as cron in Linux and scheduled tasks in window), but the event scheduler in MySQL can be accurate to seconds, which is very useful for some data processing with high real-time requirements.

  1. Create / modify event
  2. Configuration of event scheduler

1. Create / modify event

In mysql, create a new scheduler to useCREATE 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;

In the above statement, include the following parameters:

  • event_name – The event name can be any combined MySQL identifier and cannot exceed 64 characters.

    When creating an event, you can specify a schema at the same time. The syntax structure is:schema_name.event_name

  • schedule – Scheduling rules specify the execution time and execution rules of events. Is a sub 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}

  • event_body – Event body, which can be single line SQL syntax orBEGIN……ENDStatement block

To view the created events, you can useSHOWLanguage name:

SHOW EVENTS;

Event modification

For an existing event scheduler, you can useALTERThe 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 opening and closing events is to useALTERStatement to modify the created event. For example, close an event:

ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;

Start an event:

ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;

Some event usage examples

The simplest example will bemyschema.mytableTabularmycolColumn, self increment per hour1

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

In this way, we create a file namedmyeventEvent, which is executed every hour after the event is created. The set execution rule is equivalent to:

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

If the transaction needs to be started at a certain interval, for example, 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 can be a statement block, such as:

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. Configuration of event scheduler

2.1 event scheduler status

To ensure the normal execution of the created events, you should first start the event scheduler. You can view the scheduler status in the following three ways:

SHOW VARIABLES LIKE ‘event_scheduler’;
SELECT @@event_scheduler;
SHOW PROCESSLIST;

To view the execution of an event:

SELECT * FROM information_schema.EVENTS;

The above will output all event information in the current schema, which can be passed firstDESC information_schema.EVENTS;View the output fields, and then view the required information. For example, I just want to see the event name and the last execution time:

SELECT EVENT_NAME, LAST_EXECUTED FROM information_schema.EVENTS;

2.2 turn on / off event scheduler

If the event scheduler is not enabled, it can be enabled 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;

1orONIndicates that it is set to on. Similarly, if you need to turn off, just set the value0orOFFJust.

\

Original text:https://itbilu.com/database/mysql/Nkk32pnB…

This work adoptsCC agreement, reprint must indicate the author and the link to this article