mysql计划事件即定时任务的实现

前言

在MySQL中，创建一个定时任务（即“计划事件”）通常涉及使用EVENT对象。有些时候使用mysql定时任务做一些批量处理是非常方便的，比如每天零晨记录头天的库存数据，发货超过7天后自动修改订单状态为收货状态并给相应的积分，每天定时批量修复一些数据靠等直接在数据库层就处理了，非常方便。

设置系统参数

要使用mysql定时任务，首先要开启定时任务，通过设置系统参数event_scheduler为ON:

1. 查看开启情况

SHOW VARIABLES LIKE 'event_scheduler';

2. 如何开启

    #临时开启（mysql服务重启后就失效了）
	set global event_scheduler=1;
	#设置永久生效,在mysql的配置文件中设置（my.ini）
	[mysqld]
	event_scheduler = ON

创建计划事件

1. 创建事件。使用以下SQL语法创建一个事件

	CREATE EVENT my_event
	ON SCHEDULE EVERY 1 DAY -- 或者使用你需要的时间间隔
	STARTS CURRENT_TIMESTAMP -- 或者指定开始时间
	DO BEGIN
	-- 这里写上你想要执行的SQL语句
	END;

例如，以下是一个每天定时清空一个名为my_table的表的计划事件示例

	CREATE EVENT flush_my_table
	ON SCHEDULE EVERY 1 DAY
	STARTS CURRENT_TIMESTAMP
	DO BEGIN
	    TRUNCATE TABLE my_table;
	END;  

2. 实例
   每隔30分钟更新一下retry_record表数据

# 每隔30分钟执行
CREATE EVENT IF NOT EXISTS event_update_eas_data
on schedule every 30 minute
DO BEGIN
#处理销售出库单
UPDATE eas_retry_record SET req_url = 'http://airportadmin/orderThird/easSaleIssueBill',retry_is_success = 2 WHERE req_url = 'http://airportfile/jindieeas/jySaleIssueBillImport' AND retry_is_success = 0;
DELETE FROM air_eas_record WHERE push_is_success = 0 AND business_type = 0;
#采购入库单
UPDATE eas_retry_record SET req_url = 'http://airportadmin/putorder/pushPutOrderToEas',retry_is_success = 2 WHERE req_url = 'http://airportfile/jindieeas/purInWarehsBillImport' AND retry_is_success = 0;
DELETE FROM air_eas_record WHERE push_is_success = 0 AND business_type = 8;
#其它入库单
UPDATE eas_retry_record SET req_url = 'http://airportadmin/putorder/pushOtherPutOrderToEas',retry_is_success = 2 WHERE req_url = 'http://airportfile/jindieeas/otherInWarehsBillImport' AND retry_is_success = 0;
DELETE FROM air_eas_record WHERE push_is_success = 0 AND business_type = 9;
#其它出库单
UPDATE eas_retry_record SET req_url = 'http://airportadmin/out/pushOtherOutOrderToEas',retry_is_success = 2 WHERE req_url = 'http://airportfile/jindieeas/otherIssueBillImport' AND retry_is_success = 0;
DELETE FROM air_eas_record WHERE push_is_success = 0 AND business_type = 10;
END;

查看计划事件：

SHOW EVENTS

删除计划事件

DROP EVENT IF EXISTS event_update_eas_data;

时间间隔举例

1. 每天凌晨一点执行定时任务

on schedule every  1 DAY STARTS DATE_ADD(DATE(CURRENT_DATE+1), interval 1 hour)
#或
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour) 

2. 特定的日期特定的时间点执行定时任务

ON SCHEDULE at '2019-10-10 19:14:10'

3. 每五分钟执行一次定时任务

ON SCHEDULE EVERY 5 MINUTE STARTS CURDATE()

4. 每小时执行一次定时任务

ON SCHEDULE EVERY 1 HOUR STARTS CURDATE()

at 特定时间执行，every 重复执行

补充：定时器常用案例
周期性

1. 从现在开始每隔x天执行一次

DROP EVENT IF EXISTS user_event ;    
CREATE EVENT user_event
ON SCHEDULE EVERY 9 DAY STARTS NOW()
DO  call user_procedure();

2. 每天凌晨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();  

3. 每个月的一号凌晨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(); 

4. 每个季度一号的凌晨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. 每年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();  

周期一次性

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();

2. 从现在开始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();

一次性

1. 在未来指定时间点执行一次

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();

2.在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; -- 关闭定时任务