Simple use of MySQL event

Simple use of MySQL event

-- View the create information of the event

Show create event event_test;

-- View the event status in a schema

SELECT event_schema, event_name, status from information_schema.events WHERE event_schema = 'xxxx'

 

-- Check whether the event function is enabled

Mysql> show variables like '% event % ';

+ ----------------- + ------- +

| Variable_name | Value |

+ ----------------- + ------- +

| Event_scheduler | OFF |

+ ----------------- + ------- +

1 row in set (0.00 sec)

 

Enable the event function, which can be set to ON | 1

Mysql> set global event_scheduler = on;

Query OK, 0 rows affected (0.00 sec)

 

Mysql> show variables like '% event % ';

+ ----------------- + ------- +

| Variable_name | Value |

+ ----------------- + ------- +

| Event_scheduler | ON |

+ ----------------- + ------- +

1 row in set (0.00 sec)

 

-- Create a test table

Create table t (x datetime );

-- Create a test proc to insert the system time to the test table

DELIMITER $

Drop procedure if exists e_test $

Create procedure e_test ()

BEGIN

Insert into t VALUES (NOW ());

END $

DELIMITER;

 

-- Create an event

-- Automatically calls the e_test () stored procedure every 10 seconds

Create event if not exists event_test

On schedule every 10 SECOND

ON COMPLETION PRESERVE

Do call e_test ();

 

The data in the select test table is as follows:
 

-- Enable event

Alter event event_test ON

Completion preserve enable;

 

-- Close the event

Alter event event_test ON

Completion preserve disable;

 

 

Some examples:

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

This article permanently updates the link address: