Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use event in mysql", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to use event in mysql" this article.

-- View the create information of event

SHOW CREATE event event_test

-- View the event status under 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)

| enables 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 system time into the test table

DELIMITER $$

DROP PROCEDURE IF EXISTS e_test $$

CREATE PROCEDURE e_test ()

BEGIN

INSERT INTO t VALUES (NOW ())

END $$

DELIMITER

-- create events

-- automatically call the e_test () stored procedure every ten 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 later is as follows:

-- enable the event

ALTER EVENT event_test ON

COMPLETION PRESERVE ENABLE

-- close event

ALTER EVENT event_test ON

COMPLETION PRESERVE DISABLE

Some examples:

It will be implemented regularly 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

Executed at 1: 00 a. M. on the first of every month

CREATE EVENT EVENT2 ON SCHEDULE EVERY 1 MONTH STARTS

DATE_ADD (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

Executed at 2: 00 a. M. on the first of every quarter

CREATE EVENT TOTAL_SEASON_EVENT

ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD (DATE_ADD (DATE (YEAR (CURDATE ()),'-', ELT (QUARTER (CURDATE ()), 1 -', 1)), INTERVAL 1 QUARTER), INTERVAL 2 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

CALL SEASON_STAT ()

END

Implemented at 4: 00 a. M. on January 1 every year

CREATE EVENT TOTAL_YEAR_EVENT

ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD (DATE (CONCAT (YEAR (CURDATE ()) + 1 momentum)), INTERVAL 4 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

CALL YEAR_STAT ()

END

The above is all the contents of the article "how to use event in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.