How to implement event with mysql
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!
