如何通过单条SQL UPDATE JOIN同时更新两种商品库存？

场景说明：存在一组与市场及商品生产相关的表，以Ice（冰）生产Water（水）为基础示例。

现有一条性能优异（耗时约5ms）的SQL查询，可计算生产指定数量Water所需消耗的Ice库存，但仅能更新一种商品的库存——当前仅将Water的库存更新为原有库存加新增产量，Ice库存未变更。

需要更优的SQL结构，实现一次执行同时更新两种商品的库存。目前调研到的替代方案仅为：

• 多条UPDATE语句：需考虑执行期间其他进程修改库存导致计算失效的问题
• 获取SELECT结果后在应用代码中处理：执行时间会大幅增加，希望避免

现有SQL语句 #

UPDATE market_locationgoods AS A
    INNER JOIN (SELECT locationgood.location_id AS locationid,
                       locationgood.good_id AS goodid,
                       reqgood.id AS reqgoodid,
                       reqstock.stock,
                       (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                           floor(reqstock.stock / goodsreq.mininput)) + locationgood.stock) AS newstock,
                       reqstock.stock - (IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick,
                                            floor(reqstock.stock / goodsreq.mininput)) * goodsreq.mininput) AS usedstock
                FROM market_locationgoods AS locationgood
                         LEFT JOIN market_goods AS goods ON goods.id = locationgood.good_id
                         LEFT JOIN market_goodsrequirement AS goodsreq ON goods.id = goodsreq.good_id
                         LEFT JOIN market_goods AS requiredgoods ON requiredgoods.id = goodsreq.requires_id
                         LEFT JOIN market_locationgoods AS reqstock on requiredgoods.id = reqstock.good_id AND
                                                                                     reqstock.location_id =
                                                                                     locationgood.location_id
                WHERE goods.type != 'Resource'
                  AND goods.name = 'Ice'
                  AND reqstock.stock is not null) AS B
    ON B.locationid = A.location_id and B.goodid = A.good_id
SET A.stock = B.newstock
WHERE A.location_id = B.locationid
  AND A.good_id = B.goodid;

内层SELECT示例输出 #

newstock为成品（goodid对应）更新后的库存值，usedstock为原料（reqgoodid对应）更新后的库存值，当前查询未更新usedstock对应的库存：

locationid|goodid|reqgoodid|stock|newstock|usedstock
622994|1282|1283|482676.48|800|477676.48
623078|1282|1283|58383.36|800|53383.36
623610|1282|1283|149852.16|800|144852.16

解决方案：单条SQL原子更新两种库存 #

可以通过在UPDATE语句中同时关联成品和原料的market_locationgoods记录，利用CASE分支分别设置两者的库存值。该操作是原子性的，不会出现中间状态被其他进程修改的问题，性能也能保持与原查询接近。

修改后的SQL示例：

UPDATE market_locationgoods AS target
INNER JOIN (
    SELECT 
        locationgood.location_id AS locationid,
        locationgood.good_id AS product_goodid,
        requiredgoods.id AS material_goodid,
        -- 计算本次可生产的数量
        IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick, floor(reqstock.stock / goodsreq.mininput)) AS produce_qty,
        -- 成品新库存：原有库存 + 产量
        locationgood.stock + IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick, floor(reqstock.stock / goodsreq.mininput)) AS product_newstock,
        -- 原料新库存：原有库存 - 消耗数量
        reqstock.stock - IF(reqstock.stock / goodsreq.mininput > goods.maxpertick, goods.maxpertick, floor(reqstock.stock / goodsreq.mininput)) * goodsreq.mininput AS material_newstock
    FROM market_locationgoods AS locationgood
    LEFT JOIN market_goods AS goods ON goods.id = locationgood.good_id
    LEFT JOIN market_goodsrequirement AS goodsreq ON goods.id = goodsreq.good_id
    LEFT JOIN market_goods AS requiredgoods ON requiredgoods.id = goodsreq.requires_id
    LEFT JOIN market_locationgoods AS reqstock ON requiredgoods.id = reqstock.good_id AND reqstock.location_id = locationgood.location_id
    WHERE goods.type != 'Resource'
      AND goods.name = 'Ice'
      AND reqstock.stock IS NOT NULL
) AS calc ON target.location_id = calc.locationid 
    AND target.good_id IN (calc.product_goodid, calc.material_goodid)
SET target.stock = CASE
    WHEN target.good_id = calc.product_goodid THEN calc.product_newstock
    WHEN target.good_id = calc.material_goodid THEN calc.material_newstock
END;

逻辑说明

1. 内层查询先计算每个位置下，成品的新库存、原料的新库存，以及两者对应的good_id
2. 外层UPDATE关联market_locationgoods，匹配该位置下的成品和原料记录
3. 通过CASE语句，根据good_id的不同，分别设置对应的新库存值
4. 整个操作是原子执行的，避免了多语句更新时的并发问题，同时保持了原查询的性能优势

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