一、功能说明
本文档提供一套 MySQL 自动化脚本，实现优惠券过期后自动更新状态 + 库存回滚功能，核心逻辑：
- 开启事件调度器，每 15 秒执行一次任务；
- 检测过期未处理（state='0'）的优惠券；
- 按商品 ID 统计过期优惠券数量，回滚对应商品库存；
- 将过期优惠券状态更新为1（已过期）。
二、前置配置：开启事件调度器
1. 查看事件调度器状态
show variables like '%sche%';
- 若event_scheduler值为OFF，需手动开启。
2. 开启事件调度器（全局生效）
set global event_scheduler = 1;
- 说明：该配置重启 MySQL 后失效，若需永久生效，需在my.cnf/my.ini中添加event_scheduler=ON，重启服务。
三、核心 SQL 语句（手动执行验证）
1. 优惠券发放初始化（示例）
更新指定优惠券为发放状态，有效期 30 天：
UPDATE coupon
SET hairTime=NOW(), status='1', expiryTime=(SELECT date_add(now(), interval 30 day))
WHERE id="1115";
2. 过期优惠券查询
-- 统计过期未处理的优惠券总数
SELECT COUNT(id) FROM coupon WHERE NOW()>expiryTime AND state='0';
-- 按商品ID分组统计过期未处理的优惠券数量
SELECT count(*) num, productID
FROM coupon
WHERE NOW()>expiryTime AND state='0'
GROUP BY productID;
3. 手动执行过期处理（测试用）
-- 1. 回滚商品库存（过期优惠券数量从库存中扣除回滚）
UPDATE stock s
INNER JOIN (
SELECT count(*) num, productID
FROM coupon
WHERE NOW()>expiryTime AND state='0'
GROUP BY productID
) a ON s.productID = a.productID
SET s.roll_num = s.roll_num - (a.num);
-- 2. 更新优惠券状态为已过期（state='1'）
UPDATE coupon
SET state='1'
WHERE id IN (
SELECT a.id FROM (
SELECT id FROM coupon WHERE NOW()>expiryTime AND state='0'
) a
);
- 说明：子查询SELECT a.id FROM (...) a用于避免 MySQL「不能直接更新查询的表」的语法限制。
四、存储过程：封装过期处理逻辑
1. 创建存储过程
delimiter //
-- 若存储过程已存在则删除
drop procedure if exists task_proce//
-- 创建存储过程
create procedure task_proce()
begin
-- 步骤1：库存回滚
UPDATE stock s
INNER JOIN (
SELECT count(*) num, productID
FROM coupon
WHERE NOW()>expiryTime AND state='0'
GROUP BY productID
) a ON s.productID = a.productID
SET s.roll_num = s.roll_num - (a.num);
-- 步骤2：更新优惠券状态
UPDATE coupon
SET state='1'
WHERE id IN (
SELECT a.id FROM (
SELECT id FROM coupon WHERE NOW()>expiryTime AND state='0'
) a
);
end//
delimiter ;
- 关键字说明：
-
- delimiter //：临时修改语句结束符（默认;会与存储过程内的;冲突）；
-
- drop procedure if exists：避免重复创建报错；
-
- 存储过程内包含完整的过期处理逻辑，可直接调用执行。
2. 手动调用存储过程（测试）
call task_proce();
五、定时任务：自动执行存储过程
1. 创建定时事件（每 15 秒执行一次）
-- 若事件已存在则删除
drop event if exists task_event;
-- 创建事件
create event task_event
on schedule every 15 second -- 执行频率：每15秒
on completion preserve disable -- 创建时默认禁用
do call task_proce(); -- 执行存储过程
2. 事件状态管理
-- 启用事件（开始自动执行）
alter event task_event on completion preserve enable;
-- 禁用事件（暂停自动执行）
alter event task_event on completion preserve disable;
-- 查看事件调度器状态（验证是否开启）
show variables like 'event_scheduler';
六、关键说明与注意事项
- 表结构依赖：脚本默认存在以下表及字段，需确保表结构一致：
-
- coupon：id（优惠券 ID）、productID（商品 ID）、expiryTime（过期时间）、state（状态：0 = 未过期，1 = 已过期）、hairTime（发放时间）；
-
- stock：productID（商品 ID）、roll_num（库存数量）。
- 数据一致性：
-
- 若优惠券数量较大，建议将执行频率调整为 1 分钟（every 1 minute），避免高频次锁表；
-
- 生产环境建议先在测试库验证，再迁移到正式环境。
- 权限要求：执行以下操作需具备对应权限：
-
- 开启事件调度器：SUPER权限；
-
- 创建存储过程 / 事件：CREATE ROUTINE、ALTER ROUTINE、EVENT权限。
- 异常处理：
-
- 若库存roll_num可能出现负数，可在UPDATE stock语句中添加条件SET s.roll_num = GREATEST(s.roll_num - a.num, 0)（确保库存不小于 0）；
-
- 建议添加日志表，记录每次任务执行结果（如处理优惠券数量、库存变更情况），便于问题排查。
