MySQL插入表数据时如何实现关联表联动更新及库存校验

问题1：销售表插入更新时同步库存+超卖回滚的可行性 #

结论是可以通过触发器实现，建议拆分为前置校验触发器和后置同步触发器两部分，逻辑更清晰也更可靠。

具体实现逻辑： #

• 第一步：创建BEFORE INSERT/UPDATE前置触发器做超卖校验
  触发时机为每次向Merchandise_Sales插入/修改销售记录前，先计算对应商品的累计销量：原有总销量 + 本次新增/变更的销量，再和Merchandise表中该商品的总库存比对，若总销量超过库存直接抛出异常终止事务，自动触发回滚。
  示例代码（MySQL语法）：

  DELIMITER //
  CREATE TRIGGER trg_check_over_sell BEFORE INSERT ON Merchandise_Sales
  FOR EACH ROW
  BEGIN
    DECLARE total_sold INT;
    DECLARE total_stock INT;
    -- 计算该商品当前已售总量+本次新增销量
    SELECT IFNULL(SUM(QUANTITY_SOLD), 0) + NEW.QUANTITY_SOLD INTO total_sold
    FROM Merchandise_Sales WHERE ID_MERCH = NEW.ID_MERCH;
    -- 获取商品总库存
    SELECT STOCK INTO total_stock FROM Merchandise WHERE ID_MERCH = NEW.ID_MERCH;
    -- 超卖则抛错
    IF total_sold > total_stock THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '超卖错误：商品销量超过总库存，操作终止';
    END IF;
  END //
  DELIMITER ;
  

  注：如果需要支持销售记录删除的场景，还可以新增BEFORE DELETE触发器做对应逻辑校验。

• 第二步：创建AFTER INSERT/UPDATE/DELETE后置触发器同步Merch_Stock表
  校验通过、销售记录写入成功后，自动更新库存统计表的数据，不存在对应商品记录则新增，存在则更新：

  DELIMITER //
  CREATE TRIGGER trg_sync_merch_stock AFTER INSERT ON Merchandise_Sales
  FOR EACH ROW
  BEGIN
    DECLARE curr_total_sold DECIMAL(32,0);
    DECLARE curr_stock INT;
    SELECT SUM(QUANTITY_SOLD) INTO curr_total_sold FROM Merchandise_Sales WHERE ID_MERCH = NEW.ID_MERCH;
    SELECT STOCK INTO curr_stock FROM Merchandise WHERE ID_MERCH = NEW.ID_MERCH;
    -- UPSERT逻辑：存在则更新，不存在则插入
    INSERT INTO Merch_Stock (ID_MERCH, STOCK, TOTAL_SOLD, CURRENT_STOCK)
    VALUES (NEW.ID_MERCH, curr_stock, curr_total_sold, curr_stock - curr_total_sold)
    ON DUPLICATE KEY UPDATE 
      STOCK = curr_stock,
      TOTAL_SOLD = curr_total_sold,
      CURRENT_STOCK = curr_stock - VALUES(TOTAL_SOLD);
  END //
  DELIMITER ;
  

问题2：Merchandise表STOCK字段联动Merch_Stock的存储过程实现 #

可以编写两种场景的存储过程，分别满足单商品增量更新和全量批量更新的需求：

1. 单商品增量同步存储过程 #

适合单个商品总库存调整时调用，传入商品ID即可同步对应库存统计数据：

DELIMITER //
CREATE PROCEDURE sync_merch_stock_by_id(IN p_id_merch CHAR(20))
BEGIN
  DECLARE curr_total_sold DECIMAL(32,0);
  DECLARE curr_stock INT;
  -- 先判断商品是否存在
  SELECT STOCK INTO curr_stock FROM Merchandise WHERE ID_MERCH = p_id_merch;
  IF curr_stock IS NOT NULL THEN
    SELECT IFNULL(SUM(QUANTITY_SOLD), 0) INTO curr_total_sold FROM Merchandise_Sales WHERE ID_MERCH = p_id_merch;
    -- 同步更新库存统计表
    INSERT INTO Merch_Stock (ID_MERCH, STOCK, TOTAL_SOLD, CURRENT_STOCK)
    VALUES (p_id_merch, curr_stock, curr_total_sold, curr_stock - curr_total_sold)
    ON DUPLICATE KEY UPDATE
      STOCK = curr_stock,
      CURRENT_STOCK = curr_stock - TOTAL_SOLD;
  END IF;
END //
DELIMITER ;

调用示例：CALL sync_merch_stock_by_id('你的商品ID');

2. 全量同步存储过程 #

适合批量调整Merchandise表库存后，一次性刷新所有Merch_Stock数据：

DELIMITER //
CREATE PROCEDURE sync_all_merch_stock()
BEGIN
  -- 先清空原表（也可以用TRUNCATE，根据业务需求选择）
  DELETE FROM Merch_Stock;
  -- 插入全量最新统计数据，沿用你原有的生成逻辑
  INSERT INTO Merch_Stock (ID_MERCH, STOCK, TOTAL_SOLD, CURRENT_STOCK)
  SELECT 
    MS.ID_MERCH, 
    M.STOCK, 
    SUM(QUANTITY_SOLD) AS TOTAL_SOLD, 
    M.STOCK - SUM(QUANTITY_SOLD) AS CURRENT_STOCK
  FROM Merchandise_Sales MS 
  INNER JOIN Merchandise M ON M.ID_MERCH = MS.ID_MERCH 
  GROUP BY MS.ID_MERCH, M.STOCK;
END //
DELIMITER ;

调用示例：CALL sync_all_merch_stock();

额外优化建议：如果需要Merchandise表STOCK修改后自动同步，还可以额外给Merchandise表加AFTER UPDATE触发器，当STOCK字段变更时自动调用上面的增量同步存储过程即可。

内容的提问来源于stack exchange，提问作者Alline Gabriela M. Andrade