Analysis of the Method of Setting Timing Tasks in mysql

In this paper, an example is given to describe the method of setting timing task in mysql. Share it for your reference, as follows:

Today, I met a task that needs to be executed regularly every day. This function is provided in mysql database, which is just sorted out and shared.

1. First check whether the timer task is turned on

Check whether event is turned on:

SHOW VARIABLES LIKE '%event_sche%';

Open the event plan:

SET GLOBAL event_scheduler = 1;

Close the event plan:

SET GLOBAL event_scheduler = 0;

Close the event task:

ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;

Open 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 the timed task to call this stored procedure (every 10 seconds from 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 schedule task, there are two ways to set the schedule task:

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

2. The number of EVERY time units [STARTS timestamps] [ENDS timestamps] used to complete repetitive scheduled tasks.

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

In repeated scheduled tasks, the number of times (units) can be any non-empty integer (Not Null), and the time units are keywords: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

Note: Other time units are also legal, such as QUARTER, WEEK, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND and 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 happen again", that is, when a single scheduled task is completed or when a repetitive scheduled task is executed to the ENDS stage. The function of PRESERVE is to make the event will not be dropped by Drop after execution. It is recommended to use this parameter to view the specific information of EVENT.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.