很多时候需要对数据库进行定时任务操作,下面以订单未支付超时修改为取消状态为例进行记录
第一步:检查当前mysql是否开启定时策略
show VARIABLES like '%event_sche%';
当显示的event_scheduler为OFF时用以下命令临时开启
set global event_scheduler = 1;
若想要数据库每次重启时都生效,需要修改my.ini,再重启就会生效
[mysqld]
event_scheduler=ON //这一行加入mysqld标签下
第二步:创建一个存储过程作为定时执行任务
BEGIN
#Routine body goes here...
-- 若支付订单超时则修改订单状态
UPDATE b_order_payment SET flag = '2'
WHERE flag = '0' AND (UNIX_TIMESTAMP(create_time)+expires_in)<UNIX_TIMESTAMP(NOW());
UPDATE B_ORDER SET `status` = '1'
WHERE id = (SELECT order_id FROM b_order_payment WHERE flag = '0' AND (UNIX_TIMESTAMP(create_time)+expires_in)<UNIX_TIMESTAMP(NOW()));
UPDATE b_order_team SET `status` = '1'
WHERE id = (SELECT order_id FROM b_order_payment WHERE flag = '0' AND (UNIX_TIMESTAMP(create_time)+expires_in)<UNIX_TIMESTAMP(NOW()));
END
说明:expires_in 为有效时长(秒) UNIX_TIMESTAMP()函数用于将日期类型转换为时间戳(秒)
第三步:创建定时任务,设定执行周期
create event pay_check_timeout
on schedule every 1 minute
on completion preserve disable
do call checkTimeout();
说明:
create event pay_check_timeout 是创建名为vip_check_event的事件,注意此处没有括号
on schedule every 1 minute 创建周期定时的规则,每一分钟执行一次
on completion preserve disable 是表示创建后并不开始生效
do call checkTimeout()是该event(事件)的操作内容,表示调用我们刚刚创建的checkTimeout()存储过程
查看定时任务列表,检查是否创建成功
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
检查创建成功后,开启定时任务
alter event pay_check_timeout on completion preserve enable;//开启定时任务pay_check_timeout
alter event pay_check_timeout on completion preserve disable;//关闭定时任务pay_check_timeout