使用mysql创建定时任务

工作笔记

项目中有些工作是需要定时任务来执行的，而单纯的操作数据库，并不需要再搭建一个后台service，只需借助数据库即可完成。

场景，每天检查当前用户的消费记录，根据一年内的消费总额，动态更新用户的会员等级，仅供参考

mysql做法

1、检查当前mysql是否开启了定时策略

show VARIABLES like '%event_sche%';

显示的event_scheduler为OFF时用以下命令临时开启

set global event_scheduler = 1;  

若想要数据库每次重启时都生效，需要修改my.ini，再重启就会生效

[mysqld]
event_scheduler=ON //这一行加入mysqld标签下

2、创建一个存储过程作为定时执行的执行任务

使用sql创建：

use mydb;
-- 临时修改mysql的结束符，避免下面的分号回车时，直接执行
delimiter //
CREATE PROCEDURE `checkVipLevel`()
BEGIN
-- 删除可能存在的临时表
DROP TABLE IF EXISTS z_tmp_vip; 

-- 创建临时表
CREATE TEMPORARY TABLE z_tmp_vip (
`user_id` VARCHAR ( 32 ) NOT NULL,
`total_fee` VARCHAR ( 20 ) NOT NULL,
`vip_level` TINYINT ( 3) NOT NULL,
`check_date` datetime ( 0 ) NOT NULL,
PRIMARY KEY ( `user_id` ) 
) ENGINE = MYISAM DEFAULT charset = utf8;

-- 查询一年内的总消费记录并计算会员等级，插入临时表中
INSERT INTO z_tmp_vip (user_id,total_fee,vip_level,check_date)
SELECT user_id,SUM(total_fee) AS total_fee,CASE 
		WHEN SUM(total_fee) > 0 && SUM(total_fee) < 200000  THEN 2
		WHEN SUM(total_fee) >= 200000 && SUM(total_fee) < 1000000  THEN 3
		WHEN SUM(total_fee) >= 1000000 && SUM(total_fee) < 2000000  THEN 4
		WHEN SUM(total_fee) >= 2000000 && SUM(total_fee) < 5000000  THEN 5
		WHEN SUM(total_fee) >= 5000000 && SUM(total_fee) < 10000000  THEN 6
		WHEN SUM(total_fee) >= 10000000  THEN 7
		ELSE 1 
	END vip_level,NOW() check_date
FROM b_order_payment WHERE TIMESTAMPDIFF(YEAR,pay_time,NOW()) = 0
GROUP BY user_id;

-- 根据临时表更新用户的会员等级
UPDATE sys_user INNER JOIN (SELECT user_id,vip_level FROM z_tmp_vip) v 
ON v.user_id = sys_user.id 
SET sys_user.vip_level = v.vip_level;

END//

-- 最终要将mysql的结束符修改回分号
delimiter ;

3、创建定时任务，并设定执行周期

create event vip_check_event
on schedule every 1 day
on completion preserve disable
do call checkVipLevel();

代码说明：

• 上面为整体代码，敲完再写分号
• 第一行create event vip_check_event是创建名为vip_check_event的事件,注意此处没有括号
• 第二行是创建周期定时的规则，本处的意思是每天执行一次
• 第三行on completion preserve disable是表示创建后并不开始生效。
• 第四行do call test_proce()是该event(事件)的操作内容，表示调用我们刚刚创建的checkVipLevel()存储过程。

查看定时任务列表，检查是否创建成功

SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

检查创建成功后，开启定时任务

alter event vip_check_event on completion preserve enable;//开启定时任务vip_check_event
alter event vip_check_event on completion preserve disable;//关闭定时任务vip_check_event

附： 定时任务的执行周期的几种参数

• 周期执行–关键字 EVERY

 单位有：second,minute,hour,day,week(周),quarter(季度),month,year
 举例：
 	on schedule every 1 second //每秒执行1次
	on schedule every 2 minute //每两分钟执行1次
	on schedule every 3 day //每3天执行1次

• 在具体某个时间执行–关键字 AT

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2019-06-20 21:50:00' //在2019年6月20日，晚上9点50执行

• 在某个时间段执行–关键字STARTS ENDS

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天