MySQL触发器内使用本地变量执行算术运算结果不符合预期问题

• NULL值运算异常 #

  这是你遇到问题的核心诱因：当你执行select price into oldSneakerPrice from sneakers where id = old.sneakerId时，如果old.sneakerId对应的鞋款记录已被删除、或者old.sneakerId是非法值，查询结果会返回NULL。MySQL中任何数值和NULL做算术运算，最终结果都会为NULL，自然不符合预期。去掉减法部分时没有用到可能为NULL的oldSneakerPrice变量，所以运行正常。

• 关联字段逻辑错误（高概率） #

  从命名规则判断，orders_sneakers是订单明细表，它关联orders主表的外键应该是order_id这类字段，你代码中写的where orders.id = new.id里的new.id是orders_sneakers自身的主键ID，和订单ID完全是两个字段，你取到的currentTotalMoney本身就是错误值。

• 无兜底判断逻辑 #

  所有select into语句都没有加异常兜底，只要任意一步查询不到结果，变量就会被赋值为NULL，直接导致后续计算失效。

你可以按照下面的逻辑修改触发器：

CREATE DEFINER=`root`@`localhost` TRIGGER `orders_sneakers_AFTER_UPDATE` AFTER UPDATE ON `orders_sneakers` FOR EACH ROW BEGIN
    -- 声明变量时默认赋值为0，避免NULL
    DECLARE currentTotalMoney DECIMAL(13,2) DEFAULT 0;
    DECLARE newSneakerPrice DECIMAL(13,2) DEFAULT 0;
    DECLARE oldSneakerPrice DECIMAL(13,2) DEFAULT 0;
    DECLARE updatedTotalMoney DECIMAL(13,2) DEFAULT 0;

    -- 注意：这里的关联条件请替换成你订单明细表实际关联订单主表的外键字段，比如new.order_id
    SELECT IFNULL(totalMoney, 0)
    INTO currentTotalMoney
    FROM orders 
    WHERE orders.id = new.order_id;

    -- 用IFNULL兜底，查不到价格默认返回0
    SELECT IFNULL(price, 0)
    INTO newSneakerPrice
    FROM sneakers 
    WHERE id = new.sneakerId;

    SELECT IFNULL(price, 0)
    INTO oldSneakerPrice
    FROM sneakers 
    WHERE id = old.sneakerId;

    SET updatedTotalMoney := currentTotalMoney + (newSneakerPrice * new.sneakerQty) - (oldSneakerPrice * old.sneakerQty);

    -- 同样替换成正确的订单外键
    UPDATE orders SET totalMoney = updatedTotalMoney WHERE id = new.order_id;
END

如果你的orders_sneakers表确实是用id字段关联orders表的id，把上面的new.order_id改回new.id即可。

你可以增加字段变更判断，只有当sneakerId或者sneakerQty被修改时才执行计算逻辑，减少不必要的数据库开销：

-- 放在变量声明之后，查询逻辑之前
IF NOT (NEW.sneakerId <=> OLD.sneakerId OR NEW.sneakerQty <=> OLD.sneakerQty) THEN
    LEAVE;
END IF;

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