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

Editor to share with you how mysql to achieve event, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Enable mysql event (event)

Mysql > show variables like'% event_sch%'

+-+ +

| | Variable_name | Value |

+-+ +

| | event_scheduler | OFF |

+-+ +

1 row in set (0.00 sec)

Turn on event (event)

Vi / etc/my.cnf

[mysql]

Event_scheduler=on

Restart the database

Service mysql restart

Mysql > show variables like'% event_sch%'

+-+ +

| | Variable_name | Value |

+-+ +

| | event_scheduler | ON |

+-+ +

1 row in set (0.00 sec)

2. Create event

DELIMITER $$

CREATE event event_p2

ON SCHEDULE

EVERY 5 MINUTE

STARTS NOW ()

DO

BEGIN

CALL p2 (160105)

END$$

DELIMITER

3. View event

Mysql > show events\ G

* * 1. Row *

Db: report

Name: event_p2

Definer: root@localhost

Time zone: SYSTEM

Type: RECURRING

Execute at: NULL

Interval value: 1

Interval field: MINUTE

Starts: 2016-05-10 20:09:50

Ends: NULL

Status: ENABLED

Originator: 1

Character_set_client: utf8

Collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

CREATE EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement

Schedule:

AT timestamp [+ INTERVAL interval]

| | EVERY interval [STARTS timestamp] [ENDS timestamp] |

Interval:

Quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Among them

Event_name: timer name, with a maximum length of 64 characters. If not specified, it defaults to the current MySQL user name (case-insensitive)

Schedule: limit execution time

ON COMPLETION [NOT] PRESERVE: indicates whether the Event needs to be reused in a loop

Sql_statement: the SQL statement to be executed (stored procedures can also be used instead of traditional SQL statements)

Comment: a comment on the time scheduler with a maximum length of 64 characters

[shutdown event]

ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE

[open event]

ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE

[delete event]

DROP EVENT [IF EXISTS] event_name

[note]: to use a timer, the constant GLOBAL event_scheduler of MySQL must be on or 1.

[example]

1. Data updates are performed every day at 1: 00 a.m.:

CREATE EVENT [IF NOT EXISTS] E_testEvent_1

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD (DATE_ADD (CURDATE (), INTERVAL 1 DAY), INTERVAL 1 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

Call p2 ()

End

two。 Data updates are performed at 1: 00 a.m. on the first day of each month (using stored procedures):

CREATE EVENT E_testEvent_2

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 p2 ()

END

3. Data updates are performed at 1: 00 a.m. on the first day of each quarter:

CREATE EVENT E_testEvent_3

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

UPDATE _ T_test SET col= 2

The above is all the content of the article "how to achieve 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.