MySQL 5.7存储过程实现：获取各合作方激活优惠券并更新状态

嘿，我来帮你搞定这个MySQL 5.7的需求！首先得提个小细节：你给的建表语句里字段是partner，但插入语句里用的是partner_name，而且INSERT的表名是table_name，和建表的Coupons不一致，这会导致插入失败，我先把这些地方修正了，再给解决方案。

第一步：修正表结构与测试数据 #

先把表结构和插入语句统一，避免字段不匹配的错误：

-- 修正后的建表语句（将partner改为partner_name，和插入字段一致）
CREATE TABLE Coupons ( 
    cid INT(11) PRIMARY KEY, 
    coupon_name VARCHAR(255), 
    partner_name VARCHAR(255), 
    is_active INT(1) 
);

-- 修正后的插入语句（表名改为Coupons）
INSERT INTO Coupons(cid,coupon_name,partner_name,is_active) VALUES (0,'SWIGGYONE','SWIGGY',1);
INSERT INTO Coupons(cid,coupon_name,partner_name,is_active) VALUES (1,'ZOMATOONE','ZOMATO',1);
INSERT INTO Coupons(cid,coupon_name,partner_name,is_active) VALUES (2,'SWIGGYONE','SWIGGY',1);
INSERT INTO Coupons(cid,coupon_name,partner_name,is_active) VALUES (3,'ZOMATOTWO','ZOMATO',1);

第二步：用存储过程实现需求（推荐） #

因为你需要原子性操作（要么全更新成功，要么全失败），同时返回被更新的记录，用存储过程是最清晰可靠的方式，MySQL 5.7完全支持这个方案：

DELIMITER //

CREATE PROCEDURE UpdateAndReturnActiveCoupons()
BEGIN
    -- 开启事务，保证操作的原子性
    START TRANSACTION;

    -- 创建临时表，用来暂存要更新的优惠券记录
    CREATE TEMPORARY TABLE IF NOT EXISTS TempUpdatedCoupons (
        cid INT(11),
        coupon_name VARCHAR(255),
        partner_name VARCHAR(255),
        is_active INT(1)
    );

    -- 从每个合作方中选1条激活的优惠券（这里选cid最小的，你可以按需调整逻辑）
    INSERT INTO TempUpdatedCoupons
    SELECT cid, coupon_name, partner_name, is_active
    FROM Coupons
    WHERE is_active = 1
    GROUP BY partner_name
    HAVING cid = MIN(cid);

    -- 更新原表中对应记录的is_active为0
    UPDATE Coupons c
    JOIN TempUpdatedCoupons tc ON c.cid = tc.cid
    SET c.is_active = 0;

    -- 返回被更新的记录
    SELECT * FROM TempUpdatedCoupons;

    -- 提交事务，确认所有操作生效
    COMMIT;

    -- 销毁临时表（可选，会话结束后临时表会自动删除）
    DROP TEMPORARY TABLE IF EXISTS TempUpdatedCoupons;
END //

DELIMITER ;

怎么用？ #

直接调用存储过程就行：

CALL UpdateAndReturnActiveCoupons();

调整选择逻辑 #

如果你不想选cid最小的，而是想随机选1条，可以把插入临时表的语句改成下面这样：

INSERT INTO TempUpdatedCoupons
SELECT c.cid, c.coupon_name, c.partner_name, c.is_active
FROM Coupons c
WHERE c.is_active = 1
AND c.cid = (
    SELECT cid FROM Coupons 
    WHERE partner_name = c.partner_name AND is_active = 1
    ORDER BY RAND()
    LIMIT 1
);

替代方案：不用存储过程的单步操作 #

如果你不想创建存储过程，也可以用临时表+手动事务来实现：

-- 开启事务
START TRANSACTION;

-- 创建临时表保存要更新的记录
CREATE TEMPORARY TABLE TempUpdatedCoupons AS
SELECT cid, coupon_name, partner_name, is_active
FROM Coupons
WHERE is_active = 1
GROUP BY partner_name
HAVING cid = MIN(cid);

-- 更新原表
UPDATE Coupons c
JOIN TempUpdatedCoupons tc ON c.cid = tc.cid
SET c.is_active = 0;

-- 查询返回结果
SELECT * FROM TempUpdatedCoupons;

-- 提交事务
COMMIT;

-- 清理临时表
DROP TEMPORARY TABLE TempUpdatedCoupons;

关键注意事项 #

• 事务的重要性：一定要用START TRANSACTION和COMMIT，避免出现部分更新成功、部分失败的情况（比如更新时数据库出错，事务会自动回滚，保证数据一致）。
• 临时表特性：临时表只在当前数据库会话中存在，会话关闭后自动删除，不用担心数据残留。
• 选择逻辑的灵活性：你可以根据业务需求调整选记录的方式（比如按创建时间、优惠券类型等），只要保证每个合作方只选1条即可。

内容的提问来源于stack exchange，提问作者Rajat Jain