Analysis of the method of setting up scheduled tasks in mysql

This article uses an example to describe how to set up scheduled tasks in MySQL. Share with you for your reference, the details are as follows:

Today I encountered a task that needs to be executed regularly every day. Such a function is provided in the MySQL database, so I just organized it and shared it.

1. First check whether the scheduled task is turned on

Check whether the event is enabled:

SHOW VARIABLES LIKE '%event_sche%';

To open the event plan:

SET GLOBAL event_scheduler = 1;

To close the event schedule:

SET GLOBAL event_scheduler = 0;

Close event task:

ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;

To start the event task:

ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;

View event tasks:

SHOW EVENTS;

2. Create a stored procedure

DELIMITER //
DROP PROCEDURE IF EXISTS p_test //
CREATE PROCEDURE p_test()
BEGIN
INSERT INTO test(name, create_time) values('testName', now());
END//

3. Set up a scheduled task to call this stored procedure (executed every ten seconds from 1:00 on August 8, 2015)

DROP EVENT IF EXISTS e_test//
CREATE EVENT e_test
ON SCHEDULE EVERY 10 second STARTS TIMESTAMP '2015-08-08 01:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
CALL p_test();
END//

Note: In the event event: ON SCHEDULE scheduled task, there are two ways to set the scheduled task:

1. AT timestamp, used to complete a single scheduled task.

2. EVERY time (unit) quantity time unit [STARTS timestamp] [ENDS timestamp], used to complete repeated scheduled tasks.

In both scheduled tasks, the timestamp can be any TIMESTAMP and DATETIME data type, and the timestamp needs to be greater than the current time.

In a recurring scheduled task, the time (unit) quantity can be any non-null integer, and the time unit is a keyword: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

Tip: Other time units are also legal, such as: QUARTER, WEEK, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND . It is not recommended to use these non-standard time units.

[ON COMPLETION [NOT] PRESERVE]

The ON COMPLETION parameter means "when this event will not occur again", that is, when a single scheduled task is completed or when a repetitive scheduled task reaches the ENDS stage. The function of PRESERVE is to prevent the event from being dropped after execution. It is recommended to use this parameter to view the specific information of EVENT.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.