带你了解MySQL中的事件调度器EVENT
MySQL中的事件调度器，EVENT，也叫定时任务，类似于Unixcrontab或Windows任务调度程序。
EVENT由其名称和所在的schema唯一标识。
EVENT根据计划执行特定操作。操作由SQL语句组成，语句可以是BEGIN…END语句块。EVENT可以是一次性的，也可以是重复性的。一次性EVENT只执行一次，周期性EVENT以固定的间隔重复其操作，并且可以为周期性EVENT指定开始日期和时间、结束日期和时间。（默认情况下，定期EVENT在创建后立即开始，并无限期地继续，直到它被禁用或删除。）
EVENT由一个特殊的事件调度器线程执行，用SHOWPROCESSLIST可以查看。
root@database-one13:44:[gftest]>showvariableslike'%scheduler%'; +-----------------+-------+ |Variable_name|Value| +-----------------+-------+ |event_scheduler|OFF| +-----------------+-------+ 1rowinset(0.01sec) root@database-one13:46:[gftest]>showprocesslist; +--------+------+----------------------+-----------+---------+------+----------+------------------+ |Id|User|Host|db|Command|Time|State|Info| +--------+------+----------------------+-----------+---------+------+----------+------------------+ ...... +--------+------+----------------------+-----------+---------+------+----------+------------------+ 245rowsinset(0.00sec) root@database-one13:46:[gftest]>setglobalevent_scheduler=1; QueryOK,0rowsaffected(0.00sec) root@database-one13:47:[gftest]>showvariableslike'%scheduler%'; +-----------------+-------+ |Variable_name|Value| +-----------------+-------+ |event_scheduler|ON| +-----------------+-------+ 1rowinset(0.01sec) root@database-one13:47:[gftest]>showprocesslist; +--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ |Id|User|Host|db|Command|Time|State|Info| +--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ ...... |121430|event_scheduler|localhost|NULL|Daemon|33|Waitingonemptyqueue|NULL| ...... +--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ 246rowsinset(0.01sec)
可以看到，默认情况下，MySQL的EVENT没有打开，通过设置event_scheduler参数来打开或者关闭EVENT。打开后就会多一个event_scheduler，这个就是事件调度器线程。
除了打开和关闭，还可以禁用，要禁用EVENT，请使用以下两种方法之一：
- 启动MySQL时用命令行参数
--event-scheduler=DISABLED
- 在MySQL配置文件中配置参数
event_scheduler=DISABLED
MySQL5.7中创建EVENT的完整语法如下：
CREATE
[DEFINER=user]
EVENT
[IFNOTEXISTS]
event_name
ONSCHEDULEschedule
[ONCOMPLETION[NOT]PRESERVE]
[ENABLE|DISABLE|DISABLEONSLAVE]
[COMMENT'string']
DOevent_body;
schedule:
ATtimestamp[+INTERVALinterval]...
|EVERYinterval
[STARTStimestamp[+INTERVALinterval]...]
[ENDStimestamp[+INTERVALinterval]...]
interval:
quantity{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|
WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|
DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}
详细说明可以参考官网https://dev.mysql.com/doc/refman/5.7/en/create-event.html
我们通过一个实例来验证下。
1）创建一张表。
root@database-one13:47:[gftest]>createtabletestevent(idintauto_incrementprimarykey,create_timedatetime); QueryOK,0rowsaffected(0.01sec) root@database-one13:50:[gftest]>select*fromtestevent; Emptyset(0.00sec)
2）创建一个EVENT，每3秒往表中插一条记录。
root@database-one13:50:[gftest]>createeventinsert_date_testeventonscheduleevery3seconddo ->insertintotestevent(create_time)values(now()); QueryOK,0rowsaffected(0.01sec) root@database-one13:53:[gftest]>showevents\G ***************************1.row*************************** Db:gftest Name:insert_date_testevent Definer:root@% Timezone:+08:00 Type:RECURRING Executeat:NULL Intervalvalue:3 Intervalfield:SECOND Starts:2020-03-2613:53:10 Ends:NULL Status:ENABLED Originator:1303306 character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:utf8_general_ci 1rowinset(0.00sec)
3）过一会，去表中查询数据。
root@database-one13:53:[gftest]>select*fromtestevent; +----+---------------------+ |id|create_time| +----+---------------------+ |1|2020-03-2613:53:10| |2|2020-03-2613:53:13| |3|2020-03-2613:53:16| |4|2020-03-2613:53:19| |5|2020-03-2613:53:22| |6|2020-03-2613:53:25| |7|2020-03-2613:53:28| |8|2020-03-2613:53:31| |9|2020-03-2613:53:34| |10|2020-03-2613:53:37| |11|2020-03-2613:53:40| |12|2020-03-2613:53:43| |13|2020-03-2613:53:46| |14|2020-03-2613:53:49| |15|2020-03-2613:53:52| |16|2020-03-2613:53:55| +----+---------------------+ 16rowsinset(0.00sec)
从表里数据可以看到，创建的插数定时任务已经在正常运行了。
EVENT的详细信息除了用showevent命令，还可以从mysql.event或information_schema.events中查询，也可以用showcreateevent命令查看。
root@database-one00:09:[gftest]>select*frommysql.event\G ***************************1.row*************************** db:gftest name:insert_date_testevent body:insertintotestevent(create_time)values(now()) definer:root@% execute_at:NULL interval_value:3 interval_field:SECOND created:2020-03-2613:53:10 modified:2020-03-2613:53:10 last_executed:2020-03-2616:09:37 starts:2020-03-2605:53:10 ends:NULL status:ENABLED on_completion:DROP sql_mode:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION comment: originator:1303306 time_zone:+08:00 character_set_client:utf8 collation_connection:utf8_general_ci db_collation:utf8_general_ci body_utf8:insertintotestevent(create_time)values(now()) 1rowinset(0.00sec) root@database-one00:09:[gftest]>select*frominformation_schema.events\G ***************************1.row*************************** EVENT_CATALOG:def EVENT_SCHEMA:gftest EVENT_NAME:insert_date_testevent DEFINER:root@% TIME_ZONE:+08:00 EVENT_BODY:SQL EVENT_DEFINITION:insertintotestevent(create_time)values(now()) EVENT_TYPE:RECURRING EXECUTE_AT:NULL INTERVAL_VALUE:3 INTERVAL_FIELD:SECOND SQL_MODE:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STARTS:2020-03-2613:53:10 ENDS:NULL STATUS:ENABLED ON_COMPLETION:NOTPRESERVE CREATED:2020-03-2613:53:10 LAST_ALTERED:2020-03-2613:53:10 LAST_EXECUTED:2020-03-2700:10:22 EVENT_COMMENT: ORIGINATOR:1303306 CHARACTER_SET_CLIENT:utf8 COLLATION_CONNECTION:utf8_general_ci DATABASE_COLLATION:utf8_general_ci 1rowinset(0.02sec) root@database-one00:10:[gftest]>showcreateeventinsert_date_testevent\G ***************************1.row*************************** Event:insert_date_testevent sql_mode:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION time_zone:+08:00 CreateEvent:CREATEDEFINER=`root`@`%`EVENT`insert_date_testevent`ONSCHEDULEEVERY3SECONDSTARTS'2020-03-2613:53:10'ONCOMPLETIONNOTPRESERVEENABLEDOinsertintotestevent(create_time)values(now()) character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:utf8_general_ci 1rowinset(0.00sec)
以上就是带你了解MySQL中的事件调度器EVENT的详细内容，更多关于MySQL事件调度器EVENT的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。