MySQL-定时器
介绍
在开发过程中经常会遇到这样一个问题：每天或者每月必须定时去执行一条sql语句或更新或删除或执行特定的sql语句。而遇到这样的问题我想很多人会说,我直接在程序代码里写一个定时器不就行了,对你说的没问题,但是有些时候只是数据层面的问题,那么我们在程序里写一个定时器就有点浪费了,
比如:
- 定时清理脏数据
- 在每月的月底统计这个月的业务,将统计后的值放入统计表中
- 分段定时导出批量数据,进行数据迁移
- 定时整理其他系统推送来的数据,然后将整理后的数据放入本系统业务表中
- …自行研究
可以看出来,mysql定时器作用其实挺大的,只是很多人都把它忽略了而已,但是缺陷也很明显就是只能处理mysql内已有的数据,这个就看你自己了具体怎么使用了.
注意: mysql定时器是从mysql5.1开始的，如果你的mysql版本低于5.1，那就不能使用mysql定时器
开启定时器
在使用定时器前必须检查一下,mysql的定时器是否开启的, 默认是关闭的.
查询定时器是否开启
OFF 关闭 , ON开启
show VARIABLES like 'event_scheduler'
开启mysql定时器
SET GLOBAL event_scheduler = 1;然后我们在查询下就是ON了,到这里我们mysql定时器开启成功了
注意: 如果mysql服务器重启这个还是会关闭的,
永久解决办法:
找到mysql根目录下my.ini文件，打开并在[mysqld]标记下方添加一句event_scheduler = ON即可。
注意: 不要添加到[mysql]标记下，而是[mysqld]标记下，不然会配置无效。
重启mysql 之后就会永久生效
使用sql语句创建定时器
为啥不使用Navicat创建定时器呢?, 反正我自我感觉的话,SQL比较踏实,使用Navicat创建定时器总是出现各种问题,我也不知道是不是软件的问题,还是我自己的问题,反正用sql从来就没有出现问题过.如果是简单的定时器创建的话那么使用Navicat 创建也行,没要求的…
先介绍下常用关键字的意思先别管怎么用的,后面参照案例代码就懂了:
常用的语法关键字:NOT PRESERVE 任务完成后清除定时器, (默认)PRESERVE 任务完成后不清除定时器EVERY 周期执行STARTS ENDS 在某个时间段执行AT 某个时间点执行ENDS 结束定时器时间INTERVAL 间隔(时间)
常用的单位关键字:HOUR : 小时SECOND 秒MINUTE : 分钟DAY : 天MONTH: 月
定时器语法结构:
DROP EVENT IF EXISTS 定时器名称 ;
CREATE EVENT user_event
ON SCHEDULE xx -- 定时器从什么开始执行的 (必须)
ENDS CURRENT_TIMESTAMP xx -- 定时器开始后在什么时候结束 (非必选)
ON COMPLETION xx -- 定时器清除策略 (默认就行)
DO sql语句 -- 定时器执行的内容,只允许一条sql语句以分号结束 (必须)大致就是这样,别纠结,后面会举几个例子你参照参照就行了.
这里有一个地方需要注意:DO 后面能执行的sql语句,只要mysql中有的都能执行,但是每一个定时器只能执行一个sql,
如果多条sql的话,建议使用存储过程,然后在使用定时器执行存储过程就行了
定时器常用案例
周期性
定时器,一直执行
从现在开始每隔x天执行一次
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE EVERY 9 DAY STARTS NOW()
DO call user_procedure();每天凌晨1点执行
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)
DO call user_procedure();每个月的一号凌晨1 点执行
DROP EVENT IF EXISTS 定时器名称 ;
CREATE EVENT user_event
ON schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
DO call user_procedure();每个季度一号的凌晨1点执行
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour)
DO call user_procedure();每年1月1号凌晨1点执行
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour)
DO call user_procedure();周期一次性
定时器执行很多次后在满足某个条件,定时器结束
从现在开始每天执行一次，5天后停止执行
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO call user_procedure();从现在开始5天后开始执行，一个月后停止执行
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO call user_procedure();一次性
满足指定条件时只会执行一次定时器,然后定时器被清除
在未来指定时间点执行一次
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE AT TIMESTAMP '2021-09-24 18:26:00'
ON COMPLETION NOT PRESERVE
DO call user_procedure();在2021-09-24 18:26:00时候执行user_procedure()存储过程一次之后,该定时器被系统清除
从现在开始1小时后执行一次
DROP EVENT IF EXISTS user_event ;
CREATE EVENT user_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ON COMPLETION NOT PRESERVE
DO call user_procedure();开启和关闭指定定时器
alter event user_event on completion preserve enable; -- 开启定时任务
alter event user_event on completion preserve disable; -- 关闭定时任务查看当前用户的定时任务
注意: 建议在创建定时器的时候使用,root用户,这样到时候统一管理也比较方便
能查看到定时器调用的sql
select * from information_schema.`EVENTS`;
能查看到定时器的状态
ENABLED(定时器使开启状态)
DISABLED (定时器使关闭状态)
select * from mysql.event;
