MySQL事务中使用IF-THEN-ELSE语句执行无响应，求排查问题

原代码的实现存在多处语法和逻辑问题，导致执行后无输出也无预期效果，具体问题如下：

• 未定义变量@purchaseqty：代码中使用了@purchaseqty但未赋值，MySQL会将其视为NULL，最终UPDATE执行后库存会变成@balance - NULL（即NULL），且无错误提示。
• IF...ELSE不能直接用于普通SQL脚本：MySQL的控制结构IF...ELSE仅支持在存储过程、函数、触发器等可编程对象中使用，直接写在事务块里会触发语法错误，部分客户端可能未显式抛出错误。
• 库存判断逻辑错误：判断@balance >= 0毫无意义，正常逻辑应该是验证库存是否大于等于购买数量@purchaseqty，否则哪怕库存为0也会执行扣减，导致库存负数。
• 事务流程错误：若进入ELSE分支执行ROLLBACK，后续的COMMIT会报错（事务已回滚结束），且原代码的DELIMITER设置未包裹完整逻辑块，语法不成立。
• 未处理商品不存在的情况：如果items表中没有PEN-001，@balance会被赋值为NULL，后续条件判断会出现非预期结果。

修正后的实现方案 #

方案1：用存储过程封装事务逻辑（推荐） #

适合重复调用场景，同时加入错误处理和边界判断：

DELIMITER //
CREATE PROCEDURE deduct_stock(IN p_item_code VARCHAR(20), IN p_purchase_qty INT)
BEGIN
    DECLARE v_balance INT;
    START TRANSACTION;
    
    -- 查询当前库存，捕获商品不存在的情况
    SELECT quantity_in_hand INTO v_balance FROM items WHERE item_code = p_item_code;
    
    IF v_balance IS NOT NULL AND v_balance >= p_purchase_qty THEN
        -- 扣减库存
        UPDATE items 
        SET quantity_in_hand = quantity_in_hand - p_purchase_qty 
        WHERE item_code = p_item_code;
        COMMIT;
        SELECT '库存扣减成功' AS result;
    ELSE
        ROLLBACK;
        IF v_balance IS NULL THEN
            SELECT '商品不存在' AS result;
        ELSE
            SELECT '库存不足' AS result;
        END IF;
    END IF;
END //
DELIMITER ;

调用方式：

CALL deduct_stock('PEN-001', 2); -- 第二个参数为购买数量

方案2：单脚本实现（无需存储过程） #

适合一次性执行，同时避免先查询再更新的竞态问题：

SET @item_code = 'PEN-001';
SET @purchaseqty = 2; -- 先定义购买数量

START TRANSACTION;
UPDATE items 
SET quantity_in_hand = quantity_in_hand - @purchaseqty 
WHERE item_code = @item_code AND quantity_in_hand >= @purchaseqty;

-- 通过受影响行数判断是否扣减成功
IF ROW_COUNT() > 0 THEN
    COMMIT;
    SELECT '库存扣减成功' AS result;
ELSE
    ROLLBACK;
    -- 区分商品不存在和库存不足的情况
    IF EXISTS(SELECT 1 FROM items WHERE item_code = @item_code) THEN
        SELECT '库存不足' AS result;
    ELSE
        SELECT '商品不存在' AS result;
    END IF;
END IF;

内容的提问来源于stack exchange，提问作者Honey Trivedi