修改 mysql event_修改mysql事件（event）

最新推荐文章于 2021-02-02 16:58:15 发布

明佳岑

最新推荐文章于 2021-02-02 16:58:15 发布

阅读量508

点赞数

文章标签：修改 mysql event

本文链接：https://blog.csdn.net/weixin_32014283/article/details/113191233

版权

This tutorial shows you how modify existing MySQL events by using the ALTER EVENT statement. After the tutorial, you will know how to modify schedule of an event, how to enable or disable an event, and how to rename an event.

MySQL allows you to change various attributes of an existing event. To change existing events, you use the ALTER EVENT statement as follows:ALTER EVENT event_name ON SCHEDULE schedule ON COMPLETION [NOT] PRESERVE RENAME TO new_event_name ENABLE | DISABLE DO event_body

Notice that the ALTER EVENTstatement is only applied to an existing event. If you try to modify a nonexistent event, MySQL will issue an error message therefore you should always use the SHOW EVENTS statement to check the event for its existence before changing it.SHOW EVENTS FROM classicmodels;

ALTER EVENT examples

Let’s create a sample event to demonstrate various features of the ALTER EVENT statement.

The following statement creates an event that inserts a new record every minute into the messagestable.CREATE EVENT test_event_04 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO messages(message,created_at) VALUES('Test ALTER EVENT statement',NOW());

Changing schedules

To make the event run every 2 minutes, you use the following statement:ALTER EVENT test_event_04 ON SCHEDULE EVERY 2 MINUTE;

Changing event body

You can also change the event’s body by specifying the new logic as follows:ALTER EVENT test_event_04 DO INSERT INTO messages(message,created_at) VALUES('Message from event',NOW());

You can wait for 2 minutes and check the messagestable again:SELECT * FROM messages;

Disable events

To disable the event, you use the following statement:ALTER EVENT test_event_04 DISABLE;

You can check the status of the event by using the SHOW EVENTS statement as follows:SHOW EVENTS FROM classicmodels;

Enable events

To enable a disabled event, you use the ENABLEkeyword after the ALTER EVENT statement as follows:ALTER EVENT test_event_04 ENABLE;

Rename events

MySQL does not provide you with the RENAME EVENT statement. Fortunately, you can use the ALTER EVENT to rename an existing event as follows:ALTER EVENT test_event_04 RENAME TO test_event_05;

Move events to another database

You can move an event from a database to another database by using the RENAME TO clause as follows:ALTER EVENT classicmodels.test_event_05 RENAME TO newdb.test_event_05

It is assumed that the newdbdatabase is available in your MySQL database server.

In this tutorial, we have shown you how to change various attributes of a MySQL event by using the ALTER EVENTstatement.