MySQL多表关联更新异常问题排查与解决

背景 #

涉及三张InnoDB表：

• orders：字段包含orderId、userId、status，用于记录订单基础信息
• orderedProducts：字段包含orderId、productId、quantity，用于关联订单与对应购买的商品及数量
• products：字段包含productId、quantity（库存）、purchased（已购数量），用于记录商品库存数据

初始数据状态：orders表存在2条userId=4、status='bending'的订单；orderedProducts表中同一商品对应多个订单的关联记录；预期更新完成后，products表中productId=22的quantity字段值为3。

尝试的错误更新语句 #

先后使用两种多表关联更新写法，均未得到预期结果：

写法一：

UPDATE 
    orders JOIN 
    orderedProducts ON orders.orderId=orderedProducts.orderId JOIN 
    products ON products.productId=orderedProducts.productId
SET
    products.quantity=products.quantity+orderedProducts.quantity,
    products.purchased=products.purchased-orderedProducts.quantity,
    orders.status="canceled"
WHERE
    orders.userId=4;

写法二：

UPDATE 
    orders, orderedProducts, products 
SET
    products.quantity=products.quantity+orderedProducts.quantity,
    products.purchased=products.purchased-orderedProducts.quantity,
    orders.status="canceled"
WHERE
    orders.userId=4 AND products.productId=orderedProducts.productId;

问题表现 #

执行上述语句后，products表中productId=22的quantity实际值为1，与预期的3不符。

问题根源 #

由于orderedProducts表中同一商品对应多条关联记录（关联了多个订单），直接进行多表关联更新时，products表的同一行会被匹配多次并执行多次更新操作。MySQL在处理这类多匹配的更新时，不会自动累加所有匹配行的数值，最终仅保留最后一次更新的结果，导致库存计算错误。

正确解决方案 #

先对orderedProducts表按productId分组，聚合出每个商品的总关联数量，再关联其他表执行更新，确保每个商品仅被更新一次：

UPDATE 
    orders o JOIN
    (
        SELECT 
            orderId , productId , SUM(quantity) as requiredQuantity 
            FROM 
                orderedProducts 
            GROUP BY 
                productId
    ) as op ON op.orderId=o.orderId JOIN
    products as p ON op.productId=p.productId
SET
    p.quantity=p.quantity+op.requiredQuantity,
    p.purchased=p.purchased-op.requiredQuantity,
    o.status="canceled"
WHERE
    o.userId=4;

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