mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作详解

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

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

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

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

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

ALTER EVENT test_event_04ON SCHEDULE EVERY 2 MINUTE;

ALTER EVENT test_event_04DO  INSERT INTO messages(message,created_at)  VALUES('Message from event',NOW());-- 清空表中的数据truncate 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 test_event_04DISABLE;

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 test_event_04ENABLE;

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

ALTER EVENT testdb.test_event_05RENAME 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数据库计有所帮助。

更多相关文章

随机推荐