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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。