修改mysql事件（event）-原创手记-慕课网

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 EVENT statement 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 messages table.

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 messages table 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 ENABLE keyword 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 newdb database 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.