MySQL定时器常用案例

admin Mysql

2023-12-01 0 572

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点执行

周期一次性

定时器执行很多次后在满足某个条件,定时器结束

从现在开始每天执行一次，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`;