mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作详解
本文实例讲述了mysql事件之修改事件（ALTEREVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作。分享给大家供大家参考，具体如下：
我们要知道，MySQL允许我们更改现有事件的各种属性。如果我们要更改现有事件，可以使用ALTEREVENT语句，如下所示：
ALTEREVENTevent_name ONSCHEDULEschedule ONCOMPLETION[NOT]PRESERVE RENAMETOnew_event_name ENABLE|DISABLE DO event_body
ALTEREVENT语句仅适用于存在的事件，如果我们尝试修改不存在的事件，MySQL将会发出一条错误消息，因此在更改事件之前，应先使用SHOWEVENTS语句检查事件的存在：
mysql>SHOWEVENTSFROMtestdb; +--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+ |Db|Name|Definer|Timezone|Type|Executeat|Intervalvalue|Intervalfield|Starts|Ends|Status|Originator|character_set_client|collation_connection|DatabaseCollation| +--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+ |testdb|test_event_02|root@localhost|SYSTEM|ONETIME|2017-08-0304:24:48|NULL|NULL|NULL|NULL|DISABLED|0|utf8|utf8_general_ci|utf8_general_ci| +--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+ 1rowinset
创建一个每分钟将一条新记录插入到messages表中的示例事件来演示如何使用ALTEREVENT语句的各种功能：
USEtestdb;
CREATEEVENTtest_event_04
ONSCHEDULEEVERY1MINUTE
DO
INSERTINTOmessages(message,created_at)
VALUES('TestALTEREVENTstatement',NOW());
我们来把事件修改为为每2分钟运行一次：
ALTEREVENTtest_event_04 ONSCHEDULEEVERY2MINUTE;
我们还可以通过指定新的逻辑来更改事件的主体代码：
ALTEREVENTtest_event_04
DO
INSERTINTOmessages(message,created_at)
VALUES('Messagefromevent',NOW());
--清空表中的数据
truncatemessages;
修改完成后，可以等待2分钟，再次查看messages表：
mysql>SELECT*FROMmessages; +----+--------------------+---------------------+ |id|message|created_at| +----+--------------------+---------------------+ |1|Messagefromevent|2017-08-0304:46:47| |2|Messagefromevent|2017-08-0304:48:47| +----+--------------------+---------------------+ 2rowsinset
我们可以在ALTEREVENT语句之后使用DISABLE关键字来禁用某个事件：
ALTEREVENTtest_event_04 DISABLE;
我们也可以通过使用SHOWEVENTS语句来查看事件的状态：
mysql>SHOWEVENTSFROMtestdb; +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |Db|Name|Definer|Timezone|Type|Executeat|Intervalvalue|Intervalfield|Starts|Ends|Status|Originator|character_set_client|collation_connection|DatabaseCollation| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |testdb|test_event_02|root@localhost|SYSTEM|ONETIME|2017-08-0304: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-0304:44:47|NULL|DISABLED|0|utf8|utf8_general_ci|utf8_general_ci| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ 2rowsinset
我们可以在ALTEREVENT语句之后使用ENABLE关键字来启用事件：
ALTEREVENTtest_event_04 ENABLE;
查看下事件状态：
mysql>SHOWEVENTSFROMtestdb; +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |Db|Name|Definer|Timezone|Type|Executeat|Intervalvalue|Intervalfield|Starts|Ends|Status|Originator|character_set_client|collation_connection|DatabaseCollation| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |testdb|test_event_02|root@localhost|SYSTEM|ONETIME|2017-08-0304: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-0304:44:47|NULL|ENABLED|0|utf8|utf8_general_ci|utf8_general_ci| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ 2rowsinset
我们再来尝试使用ALTEREVENT重命名现有事件：
ALTEREVENTtest_event_04 RENAMETOtest_event_05;
来查看下事件状态：
mysql>SHOWEVENTSFROMtestdb; +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |Db|Name|Definer|Timezone|Type|Executeat|Intervalvalue|Intervalfield|Starts|Ends|Status|Originator|character_set_client|collation_connection|DatabaseCollation| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ |testdb|test_event_02|root@localhost|SYSTEM|ONETIME|2017-08-0304: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-0304:44:47|NULL|ENABLED|0|utf8|utf8_general_ci|utf8_general_ci| +--------+---------------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ 2rowsinset
完事再来通过使用RENAMETO子句将事件从一个数据库移动到另一个数据库中：
ALTEREVENTtestdb.test_event_05 RENAMETOnewdb.test_event_05;
再来查看事件状态：
mysql>SHOWEVENTSFROMnewdb; +-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ |Db|Name|Definer|Timezone|Type|Executeat|Intervalvalue|Intervalfield|Starts|Ends|Status|Originator|character_set_client|collation_connection|DatabaseCollation| +-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ |newdb|test_event_05|root@localhost|SYSTEM|RECURRING|NULL|2|MINUTE|2017-08-0304:44:47|NULL|ENABLED|0|utf8|utf8_general_ci|utf8_general_ci| +-------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1rowinset
好啦，本次记录就到这里了。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。