为了账号安全,请及时绑定邮箱和手机立即绑定

修改mysql事件(event)

标签:
MySQL

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.

Related Tutorials

原文链接:http://outofmemory.cn/mysql/trigger/mysql-triggers-modifying-mysql-events

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消