MySQL regularly executes scripts of plans task command instances

Check to see if event is on

show variables like '%sche%'; 

Turn the event plan on

set global event_scheduler =1; 

Create the stored procedure test

CREATE PROCEDURE test () 
BEGIN 
update examinfo SET endtime = now() WHERE id = 14; 
END; 

Create event e_test

create event if not exists e_test 
on schedule every 30 second 
on completion preserve 
do call test(); 

The stored procedure test is executed every 30 seconds, updating the current time to the endtime field of the record id=14 in the examinfo table.

Close event task

alter event e_test ON 
COMPLETION PRESERVE DISABLE; 

Account opening event task

alter event e_test ON 
COMPLETION PRESERVE ENABLE; 

All the above tests were successful and the test environment was mysql 5.4.2-ES44en-ES45en mysql community server(GPL)

The above related content is the introduction of MySQL regular execution, I hope you can have the harvest.

The mysql scheduled task disappears after restarting

We just need to change the 1 configuration

event_scheduler is set to OFF in config of mysql. Go to mysql and change the configuration to ON and you're done.

You can read more about it below

MySQL5.1.x has introduced a new feature, EVENT, as the name suggests is the event, timing task mechanism, in a specified time unit to perform a specific task, so in the future some data timing operations no longer rely on external programs, but directly use the database provided by the function.

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 can also be added to the start command. For example:

mysqld ... --event_scheduler=1

my.ini or
[mysqld]
Add event_scheduler = ON

Create events (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 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 20th 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) Open test table after 5 days:

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

6) Clear test table regularly 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 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. The 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'] you 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;