MySQL定时任务(EVENT事件)如何配置详解

一、事件（event）是干什么的

　　自mysql5.1.6起，增加了一个非常有特色的功能 - 事件调度器（event scheduler），可以用做定时执行某些特定任务（例如：删除记录、数据统计报告、数据备份等等），来取代原先只能由操作系统的计划任务来执行的工作。

　　值得一提的是mysql的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：linux的cron）只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合。

　　事件有时也可以称为临时触发器（temporal triggers），因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器（triggers）是基于某个表所产生的事件触发的，区别也就在这里。

二、开启“事件”功能

　　使用“事件”功能之前必须确保event_scheduler已开启。

1、查询功能是否开启

-- 方法一
select @@event_scheduler;
-- 方法二
show variables like 'event%';

 

显示 “on”说明功能已开启；如下图：

2、开启、关闭功能命令

-- 开启功能命令：
set global event_scheduler = 1;
set global event_scheduler = on;
-- 关闭功能命令：
set global event_scheduler = 0;
set global event_scheduler = off;

 

当然，通过命令开启当数据库重启后会自动关闭

持久化开启方式：将event_scheduler=1写到my.cnf配置文件中;如下图：

 常用的事件操作命令：

• 关闭指定事件： alter event 事件名称 on completion preserve disable;
• 开启指定事件：alter event 事件名称 on completion preserve enable;
• 查看当前事件：show events/select * from information_schema.`EVENTS`;

三、事件 - sql语法创建

event事件创建语句

create event [ifnot exists] event_name
 　　 on schedule schedule(调度时间设置)
 　　 [on completion [not] preserve]
 　　 [enable | disable | disable on slave]
 　　 [comment 'comment']
 　　 do sql_statement;

sql语法	说明
definer	可选项，给指定用户使用权限
if not exists	可选项，用于判断要创建的事件是否存在
event event_name	必选项，指定事件名称，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的mysql用户名（不区分大小写）
on schedule schedule	必选项，这里的schedule用于定义执行的时间和时间间隔，在下面我们详细讲解
on completion [not] preserve	可选项，配置事件执行完一次后的处理方式； 当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在 当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉.
enable、disable、disable on slave	可选项，用于指定事件的一种属性。 enable表示该事件是开启的，也就是调度器检查事件是否必选调用； disable表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用； disable on slave表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。
comment ‘comment'	可选项，用于定义事件的注释
do event_body	必选项，用于指定事件启动时所要执行的代码。可以是任何有效的sql语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用begin…end复合结构

 

schedule 调度时间配置语法：调度时间配置包括at 和 every两种

 at timestamp [+ interval interval] ...
 | every interval
 [starts timestamp [+ interval interval] ...]
 [ends timestamp [+ interval interval] ...]
 
-- interval中包含的时间单位如下:
{year | quarter | month | day | hour | minute |
 week | second | year_month | day_hour | day_minute |
 day_second | hour_minute | hour_second | minute_second}

1、创建 - 单次定时执行事件

 at timestamp 时间字符串 [+ interval interval] 

at timestamp表示该事件只执行一次，timestamp表示一个具体的时间点，后面可以加上一个时间间隔，表示在这个时间间隔后事件发生。[+ interval interval]表示延迟触发时间；

需要注意的是，timestamp和具体字符串连用，如果不是具体字符串（如current_timestamp取当前时间等），则不加timestamp;

 

示例 1：往demo表中插入一行数据，执行时间：2022-08-22 15:29:47

create event demo_event1
on schedule at timestamp '2022-08-22 15:29:47'
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

结果查询：

 

示例 2：往demo表中插入一行数据，执行时间：当前时间往后5个小时；

create event demo_event2
on schedule at current_timestamp + interval 5 hour
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

2、创建 - 循环定时执行事件

 every interval [starts timestamp] [ends timestamp] 

every表示循环执行该事件，其中starts子句用于指定开始时间；ends子句用于指定结束时间。

 

示例 1：从当前开始，每10秒往demo表中插入一行数据

create event demo_event3
on schedule every 10 second
on completion preserve
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

结果查询：

 

示例 2：从2022-08-22 12:00:00开始，每10分钟往demo表中插入一行数据

create event demo_event4
on schedule every 10 minute starts '2022-08-22 12:00:00'
on completion preserve
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

结果查询：

 

 

示例 3：从当前时间一小时后开始，每10分钟往demo表中插入一行数据

create event demo_event5
on schedule every 10 minute starts current_timestamp+interval 1 hour
on completion preserve
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

 

示例4：从当前时间一天后开始，每1小时往demo表中插入一行数据，三天后结束

create event demo_event6
on schedule every 1 hour
starts current_timestamp+interval 1 day
ends current_timestamp+interval 3 day
on completion preserve
do insert into `demo` (`name`, `createtime`) values ('陈哈哈', now())

 

示例5：每天零点定时清一下demo表数据

create event demo_event7
on schedule every 1 day starts '2022-08-22 00:00:00'
on completion preserve
do truncate table `demo`

 

示例6：每天执行一次，开始执行时间为明天凌晨1点整

drop event if exists demo_event8;
create event demo_event8
on schedule every 1 day starts DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
on completion preserve
enable
do truncate table `demo`

3、修改事件

修改事件语句跟创建语句如出一辙，语法如下：

alter event event_name
 　　 [onschedule schedule]
 　　 [old_name to new_name]
 　　 [on completion [not] preserve]
 　　 [comment 'comment']
 　　 [enable | disable]
 　　 [do sql_statement]

关闭事件任务： alter event 事件名称 on completion preserve disable;

开启事件任务：alter event 事件名称 on completion preserve enable;

4、删除事件

 drop event [if exists] event_name 

四、事件 - 用navicat创建（推荐）

　　很多小伙伴喜欢在命令行敲sql语句，会有种专业感，但也可能是被领导道德绑架了~~

　　就像有些领导们觉得自己员工用google就很cool，很带劲；用百度查 csdn 就很 low。 但作为菜狗的我还是喜欢用我的navicat小工具和度娘。好了废话不多说，我们来看看navicat是如何创建event事件的，go！

 

如下图，右键点击创建新的事件

 

 创建事件中的定义一栏是写执行sql的，可以包括一条或多条sql语句、存储过程等，计划一栏是定义事件触发时间的。如下图，我在执行过程中定义了一条插入语句。

 

 当然也可以同时写多条sql，中间带分号。以begin开头，end结尾即可。

 

 打开计划栏

参数说明：

at：表示该事件只执行一次，可以设置一个具体的时间，也可以如图中current_timestamp代表当前时间，后面可以加上一个时间间隔interval，表示在这个时间多久以后后事件发生，表示延迟触发时间；

• every：循环执行该事件，其中starts子句用于指定开始时间； 
• ends：子句用于指定结束时间。
• interval：表示从现在开始时间延迟多久以后的一个时间点，其值由一个数值和单位构成。例如，使用“4 week”表示4周后；使用“‘1:10' hour_minute”表示1小时10分钟后。间隔的距离用date_add()函数来支配。

interval中包含的时间单位如下：

year | quarter | month | day | hour | minute | week | second |
year_month | day_hour | day_minute |
day_second | hour_minute | hour_second | minute_second

 

示例 1：往demo表中插入一行数据，执行时间：2022-08-22 16:18:00

 

示例2：往demo表中插入一行数据，执行时间：当前时间往后5个小时；

 

 示例 3：从当前开始，每10秒往demo表中插入一行数据

 

示例 4：从2022-08-22 16:18:00开始，每10分钟往demo表中插入一行数据

 

示例 5：从当前时间一小时后开始，每10分钟往demo表中插入一行数据

 

示例 6：从当前时间一天后开始，每1小时往demo表中插入一行数据，三天后结束

 

示例 7：每天零点定时清一下demo表数据