数据库教程:带你了解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]>showeventsG ***************************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.eventG ***************************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.eventsG ***************************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_testeventG ***************************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数据库学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)!