MySQL同表内Shop1商品库存同步至Shop3的SQL实现

需求说明 #

现有stocks表存储3家店铺的库存数据，需完成Shop1到Shop3的库存同步逻辑：

• 若Shop3已存在对应商品（匹配id_product+id_product_attribute+id_shop_group，目标店铺id_shop固定为3）的库存记录，更新其quantity为Shop1对应值
• 若Shop3无该商品记录，插入全新库存记录（如示例中的2136-3-9、2138-3-2）

修正后的更新语句 #

你之前的更新语句存在两处问题：未限定同步源为Shop1，且仅通过id_product关联，忽略了id_product_attribute和id_shop_group（这两个字段是唯一约束的组成部分，必须匹配才能定位到正确的库存记录）。修正后的更新语句如下：

UPDATE stocks sa
JOIN stocks sb 
  ON sa.id_product = sb.id_product 
  AND sa.id_product_attribute = sb.id_product_attribute 
  AND sa.id_shop_group = sb.id_shop_group
SET sa.quantity = sb.quantity
WHERE sa.id_shop = 3 
  AND sb.id_shop = 1; -- 指定同步源为Shop1

完整同步方案（更新+插入） #

方式1：单语句实现全量同步（推荐，高效简洁） #

利用表的唯一约束product_sqlstock，结合INSERT ... ON DUPLICATE KEY UPDATE语法，可一次性完成"存在则更新、不存在则插入"的逻辑：

INSERT INTO stocks (
  id_product, 
  id_product_attribute, 
  id_shop, 
  id_shop_group, 
  quantity, 
  physical_quantity, 
  reserved_quantity, 
  depends_on_stock, 
  out_of_stock, 
  location
)
SELECT 
  sb.id_product, 
  sb.id_product_attribute, 
  3 AS id_shop, -- 目标店铺固定为Shop3
  sb.id_shop_group, 
  sb.quantity, 
  sb.physical_quantity, 
  sb.reserved_quantity, 
  sb.depends_on_stock, 
  sb.out_of_stock, 
  sb.location
FROM stocks sb
WHERE sb.id_shop = 1 -- 仅同步Shop1的库存记录
ON DUPLICATE KEY UPDATE 
  quantity = VALUES(quantity),
  -- 可选：若需要同步其他字段，可按需添加以下行
  physical_quantity = VALUES(physical_quantity),
  reserved_quantity = VALUES(reserved_quantity),
  depends_on_stock = VALUES(depends_on_stock),
  out_of_stock = VALUES(out_of_stock),
  location = VALUES(location);

方式2：拆分更新与插入操作 #

若需明确拆分两步执行，插入语句可筛选出Shop3缺失的Shop1记录：

-- 第一步：更新Shop3已有记录的库存值
UPDATE stocks sa
JOIN stocks sb 
  ON sa.id_product = sb.id_product 
  AND sa.id_product_attribute = sb.id_product_attribute 
  AND sa.id_shop_group = sb.id_shop_group
SET sa.quantity = sb.quantity
WHERE sa.id_shop = 3 
  AND sb.id_shop = 1;

-- 第二步：插入Shop3缺失的Shop1库存记录
INSERT INTO stocks (
  id_product, 
  id_product_attribute, 
  id_shop, 
  id_shop_group, 
  quantity, 
  physical_quantity, 
  reserved_quantity, 
  depends_on_stock, 
  out_of_stock, 
  location
)
SELECT 
  sb.id_product, 
  sb.id_product_attribute, 
  3 AS id_shop, 
  sb.id_shop_group, 
  sb.quantity, 
  sb.physical_quantity, 
  sb.reserved_quantity, 
  sb.depends_on_stock, 
  sb.out_of_stock, 
  sb.location
FROM stocks sb
WHERE sb.id_shop = 1
  AND NOT EXISTS (
    SELECT 1 FROM stocks sa
    WHERE sa.id_product = sb.id_product
      AND sa.id_product_attribute = sb.id_product_attribute
      AND sa.id_shop = 3
      AND sa.id_shop_group = sb.id_shop_group
  );

关键说明 #

• 表的唯一约束product_sqlstock（id_product,id_product_attribute,id_shop,id_shop_group）是实现"防重复+更新"的核心，确保不会生成重复库存记录
• 自增字段id_stock_available无需手动指定，MySQL会自动生成唯一值
• 若仅需同步quantity字段，在ON DUPLICATE KEY UPDATE中保留quantity = VALUES(quantity)即可，其他字段可删除

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