MySQL planning task of event scheduler Event Scheduler introduction

To see if the event scheduler is currently on, perform SQL as follows:

SHOW VARIABLES LIKE 'event_scheduler';
or

SELECT @@event_scheduler;
or

SHOW PROCESSLIST;
If the display:

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
The executable

SET GLOBAL event_scheduler = 1;
or

SET GLOBAL event_scheduler = ON;
event_scheduler=1, for example:

mysqld ... --event_scheduler=1

my.ini or. my
[mysqld]
Add event_scheduler = ON

Create event (CREATE EVENT)
Let's take a look at its syntax:

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;

schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

1) Let's start with a simple example to demonstrate inserting 1 record per second into a data table

USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
Wait 3 seconds before executing the query successfully.

2) Clear the test table after 5 days:

CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

3) Clear test table at 12:00 on 20 July 2007:

CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;

4) Clear test table regularly every day:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;

5) After 5 days, start to clear test table regularly every day:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

6) Regularly empty test table every day and stop execution after 5 days:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

7) Start test table and clear it regularly after 5 days, and stop it after 1 month:

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE] can set whether this event is executed once or persisted. Default is NOT PRESERVE.

8) Regularly empty the test table every day (only execute once, and terminate the event when the task is completed) :

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE] sets whether the event is turned on or off after it is created. The default is ENABLE.
[COMMENT 'comment'] can annotate this event.

Modification event (ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
1) Temporary shutdown event

ALTER EVENT e_test DISABLE;

2) Open the event

ALTER EVENT e_test ENABLE;
3) Change test table to empty once every 5 days:

ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;

Delete event (DROP EVENT)
The syntax is simple, as follows:

DROP EVENT [IF EXISTS] event_name
For example, delete the e_test event you created earlier

DROP EVENT e_test;
Of course, if this event exists, it will generate ERROR 1513 (HY000): Unknown event error, so it is better to add IF EXISTS

DROP EVENT IF EXISTS e_test;