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

This article mainly shows you "sample analysis of mysql event modification event, disable event, enable event, event rename and database event migration operation". The content is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "sample analysis of mysql event modification event, disable event, enable event, event rename and database event migration operation".

The details are as follows:

We need to know that MySQL allows us to change various properties of existing events. If we want to change an existing event, we can use the ALTER EVENT statement, as follows:

ALTER EVENT event_nameON SCHEDULE scheduleON COMPLETION [NOT] PRESERVERENAME TO new_event_nameENABLE | DISABLEDO event_body

The ALTER EVENT statement applies only to existing events, and if we try to modify an event that does not exist, MySQL will issue an error message, so you should use the SHOW EVENTS statement to check for the existence of the event before changing the event:

Mysql > SHOW EVENTS FROM testdb +- -+-+ | Db | Name | Definer | Time Zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-- -- +-+ | testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci | +- -+- -+-+ 1 row in set

Create a sample event that inserts a new record into the messages table every minute to demonstrate how to use the various features of the ALTER EVENT statement:

USE testdb;CREATE EVENT test_event_04ON SCHEDULE EVERY 1 MINUTEDO INSERT INTO messages (message,created_at) VALUES ('Test ALTER EVENT statement',NOW ())

Let's modify the event to run every 2 minutes:

ALTER EVENT test_event_04ON SCHEDULE EVERY 2 MINUTE

We can also change the body code of the event by specifying new logic:

ALTER EVENT test_event_04DO INSERT INTO messages (message,created_at) VALUES ('Message from event',NOW ());-- clear the data truncate messages in the table

After the modification is complete, you can wait 2 minutes to view the messages table again:

Mysql > SELECT * FROM messages +-+ | id | message | created_at | +-+ | 1 | Message from event | 2017-08-03 04:46:47 | | 2 | Message from event | 2017-08-03 04:48:47 | +-+ 2 rows in set

We can use the DISABLE keyword after the ALTER EVENT statement to disable an event:

ALTER EVENT test_event_04DISABLE

We can also view the status of the event by using the SHOW EVENTS statement:

Mysql > SHOW EVENTS FROM testdb +- -+- -- + | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-+ -+- -+ | testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_ General_ci | | testdb | test_event_04 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci | +- -+- -+-+ 2 rows in set

We can use the ENABLE keyword after the ALTER EVENT statement to enable events:

ALTER EVENT test_event_04ENABLE

Check the status of the event:

Mysql > SHOW EVENTS FROM testdb +- -+- -- + | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-+ -+- -+ | testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_ General_ci | | testdb | test_event_04 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci | +- -+- -+-+ 2 rows in set

Let's try again to rename an existing event using ALTER EVENT:

ALTER EVENT test_event_04RENAME TO test_event_05

To check the status of the event:

Mysql > SHOW EVENTS FROM testdb +- -+- -- + | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-+ -+- -+ | testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_ General_ci | | testdb | test_event_05 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci | +- -+- -+-+ 2 rows in set

When you're done, move events from one database to another by using the RENAME TO clause:

ALTER EVENT testdb.test_event_05RENAME TO newdb.test_event_05

Let's check the status of the event:

Mysql > SHOW EVENTS FROM newdb +- -+-+ | Db | Name | Definer | | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +-- | -+- -+-+ | newdb | test_event_05 | root@localhost | SYSTEM | RECURRING | NULL | 2 | MINUTE | 2017-08-03 04:44:47 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci | +-- +-- -+ 1 row in set or above is the modification event of the mysql event, Sample analysis of disabling events, enabling events, event renaming, and database event migration operations "all of the content of this article 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.