本文实例讲述了mysql事件之修改事件(ALTER EVENT)、禁用事件(DISABLE)、启用事件(ENABLE)、事件重命名及数据库事件迁移操作。分享给大家供大家参考,具体如下:

我们要知道,MySQL允许我们更改现有事件的各种属性。如果我们要更改现有事件,可以使用ALTER EVENT语句,如下所示:

ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
 event_body

ALTER EVENT语句仅适用于存在的事件,如果我们尝试修改不存在的事件,MySQL将会发出一条错误消息,因此在更改事件之前,应先使用SHOW EVENTS语句检查事件的存在:

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      | NULL      | NULL  | NULL | DISABLED |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set

创建一个每分钟将一条新记录插入到messages表中的示例事件来演示如何使用ALTER EVENT语句的各种功能:

USE testdb;
CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test ALTER EVENT statement',NOW());

我们来把事件修改为为每2分钟运行一次:

ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;

我们还可以通过指定新的逻辑来更改事件的主体代码:

ALTER EVENT test_event_04
DO
  INSERT INTO messages(message,created_at)
  VALUES('Message from event',NOW());
-- 清空表中的数据
truncate messages;

修改完成后,可以等待2分钟,再次查看messages表:

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

我们可以在ALTER EVENT语句之后使用DISABLE关键字来禁用某个事件:

ALTER EVENT test_event_04
DISABLE;

我们也可以通过使用SHOW EVENTS语句来查看事件的状态:

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      | NULL      | NULL        | 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

我们可以在ALTER EVENT语句之后使用ENABLE关键字来启用事件:

ALTER EVENT test_event_04
ENABLE;

查看下事件状态:

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      | NULL      | NULL        | 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

我们再来尝试使用ALTER EVENT重命名现有事件:

ALTER EVENT test_event_04
RENAME TO test_event_05;

来查看下事件状态:

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      | NULL      | NULL        | 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

完事再来通过使用RENAME TO子句将事件从一个数据库移动到另一个数据库中:

ALTER EVENT testdb.test_event_05
RENAME TO newdb.test_event_05;

再来查看事件状态:

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

好啦,本次记录就到这里了。

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

华山资源网 Design By www.eoogi.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
华山资源网 Design By www.eoogi.com

稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!

昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?