虚拟宠物网站库存表数量为0时自动删除的SQL实现问询

MySQL不允许在同一张表的AFTER UPDATE触发器中执行DELETE操作，因为这会触发表锁冲突和循环触发检测。辅助表方案也会因同事务内的关联操作被拦截，更稳妥的方式是用存储过程封装完整的库存更新逻辑，替代直接更新inventory表的操作。

具体实现步骤 #

1. 创建存储过程 #

这个存储过程接收用户ID、物品ID和数量变化值（比如-1代表使用1个物品，+5代表新增5个物品），完成以下操作：

• 计算更新后的库存数量
• 同步更新用户的库存空间
• 若更新后数量≤0，自动删除该库存记录

DELIMITER //
CREATE PROCEDURE update_inventory(
    IN p_user_id INT,
    IN p_item_id INT,
    IN p_quantity_change INT
)
BEGIN
    DECLARE current_quantity INT;
    DECLARE new_quantity INT;
    START TRANSACTION;

    -- 获取当前库存数量，无记录则初始化为0
    SELECT COALESCE(quantity, 0) INTO current_quantity
    FROM inventory
    WHERE user_id = p_user_id AND item_id = p_item_id;

    SET new_quantity = current_quantity + p_quantity_change;

    IF new_quantity > 0 THEN
        -- 数量仍有效，更新或插入库存记录
        INSERT INTO inventory(user_id, item_id, quantity)
        VALUES(p_user_id, p_item_id, new_quantity)
        ON DUPLICATE KEY UPDATE quantity = new_quantity;

        -- 同步用户库存空间：空间变化 = 原数量 - 新数量
        UPDATE user
        SET inventory_space = inventory_space + (current_quantity - new_quantity)
        WHERE user_id = p_user_id;
    ELSE
        -- 数量≤0，删除库存记录
        DELETE FROM inventory
        WHERE user_id = p_user_id AND item_id = p_item_id;

        -- 释放原数量占用的库存空间
        UPDATE user
        SET inventory_space = inventory_space + current_quantity
        WHERE user_id = p_user_id;
    END IF;

    COMMIT;
END //
DELIMITER ;

2. 业务层调用存储过程 #

后续业务逻辑不再直接执行UPDATE inventory，而是调用这个存储过程完成操作：

CALL update_inventory(123, 45, -1); -- 用户123使用1个物品45

替代方案：定时清理（适合非实时场景） #

如果业务允许延迟清理，可使用MySQL事件调度器定时删除quantity≤0的记录：

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

DELIMITER //
CREATE EVENT clean_zero_quantity_inventory
ON SCHEDULE EVERY 1 MINUTE -- 可根据需求调整执行频率
DO
BEGIN
    DELETE FROM inventory
    WHERE quantity <= 0;
END //
DELIMITER ;

该方案实现简单，但无法实时清理库存，适合对实时性要求不高的场景。

原方案失败原因 #

• MySQL的AFTER UPDATE触发器执行时，原表处于事务锁定状态，不允许对同一张表执行写操作（DELETE/UPDATE），否则会触发死锁检测或循环触发限制。
• 辅助表方案中，触发器插入辅助表后，辅助表的触发器去删除原表记录属于同一事务内的关联操作，MySQL仍会判定为对原表的非法操作，导致执行失败。

内容的提问来源于stack exchange，提问作者Jerad Beauregard