Some new users are not very familiar with MySQL timer enabling and calling. I will sort out some tests and share them with you, hoping to help you.
Some new users are not very familiar with MySQL timer enabling and calling. I will sort out some tests and share them with you, hoping to help you.
Create test table
The Code is as follows:
Create table t
(
V VARCHAR (100) NOT NULL
) Engine innodb default charset = utf8;
Create a stored procedure for timer calls
DELIMITER $
Drop procedure if exists e_test $
Create procedure e_test ()
BEGIN
Insert into t VALUES ('1 ');
END $
DELIMITER;
The constant GLOBAL event_scheduler of MySQL must be on or 1.
-- Check whether the timer is enabled
Show variables like '% sche % ';
-- Enable the timer 0: off 1: on
Set global event_scheduler = 1;
-- Create an event
-- Automatically calls the e_test () stored procedure every second
Create event if not exists event_test
On schedule every 1 SECOND
ON COMPLETION PRESERVE
Do call e_test ();
-- Enable event
Alter event event_test ON
Completion preserve enable;
-- Close the event
Alter event event_test ON
Completion preserve disable;
Scheduled execution every nine days from now on
Create event EVENT1
On schedule every 9 day starts now ()
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
Call total ();
END
Execute the task at on the first day of each month.
Create event EVENT2
On schedule every 1 month starts DATE_ADD (DATE_SUB (CURDATE (), interval day (CURDATE ()-1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
Call stat ();
END
Run at on Monday 1.
Create event TOTAL_SEASON_EVENT
On schedule every 1 quarter starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE (), '-', ELT (QUARTER (CURDATE (), 1, 4, 7, 10 ), '-', 1), INTERVAL 1 QUARTER), INTERVAL 2 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL SEASON_STAT ();
END
Execute at on January 1, January 1 every year
Create event TOTAL_YEAR_EVENT
On schedule every 1 year starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE () + 1, '-', 1, '-', 1), INTERVAL 4 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL YEAR_STAT ();
END
When MySQL event automatically calls the stored procedure on a monthly, quarterly, or yearly basis, you can change the system to the last day of the year for testing, for example, 23:59:55;
