MySQL更新approval_status时留存剩余数量后续累加执行的实现问题

表结构说明 #

你已创建的part_ids表建议设置以下核心字段，主键设为part_id以支持upsert操作：

• part_id：零件ID，主键
• remaining_qty：上次留存的剩余校验数量
  假设你当前存储零件库存的业务表为part_stock，核心字段包含id、part_id、quantity（单行李存储值）、approval_status（审批状态，0待校验，1已通过）。

完整执行逻辑 #

• 先读取part_ids表中对应part_id的留存剩余数量，无记录则默认值为0，与本次输入的校验数量相加，得到本次总可用校验额度total_available
• 按固定排序规则（如按入库顺序、ID升序等）筛选approval_status=0的待校验行，逐行累加quantity值，直到累加值超过total_available
• 将累加值未超过total_available的所有待校验行的approval_status更新为1
• 计算本次校验后的剩余额度：剩余值 = 总可用校验额度 - 已标记为1的行的quantity总和
• 若剩余值大于0，将剩余值upsert到part_ids表；若剩余值等于0，删除对应part_id的part_ids记录避免冗余

核心代码示例（MySQL 8.0） #

以下逻辑可封装为存储过程，调用时传入in_part_id（目标零件ID）和in_input_qty（本次输入的校验数量）即可：

-- 开启事务保证数据一致性
START TRANSACTION;

-- 1. 计算本次总校验额度
SET @total_available = in_input_qty + IFNULL((SELECT remaining_qty FROM part_ids WHERE part_id = in_part_id FOR UPDATE), 0);

-- 2. 筛选可标记的待校验行
SET @cumulative_qty = 0;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_marked_rows (
    row_id INT,
    is_marked TINYINT
) ENGINE=MEMORY;

INSERT INTO temp_marked_rows
SELECT 
    id,
    CASE WHEN (@cumulative_qty := @cumulative_qty + quantity) <= @total_available THEN 1 ELSE 0 END
FROM part_stock
WHERE part_id = in_part_id AND approval_status = 0
ORDER BY id ASC FOR UPDATE;

-- 3. 批量更新审批状态
UPDATE part_stock s
INNER JOIN temp_marked_rows t ON s.id = t.row_id
SET s.approval_status = 1
WHERE t.is_marked = 1;

-- 4. 更新留存剩余数量
SET @used_qty = IFNULL((SELECT SUM(s.quantity) FROM part_stock s INNER JOIN temp_marked_rows t ON s.id = t.row_id WHERE t.is_marked = 1), 0);
SET @remaining = @total_available - @used_qty;

IF @remaining > 0 THEN
    INSERT INTO part_ids (part_id, remaining_qty)
    VALUES (in_part_id, @remaining)
    ON DUPLICATE KEY UPDATE remaining_qty = @remaining;
ELSE
    DELETE FROM part_ids WHERE part_id = in_part_id;
END IF;

-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_marked_rows;

COMMIT;

可选优化点 #

• 若校验逻辑使用频率高，将上述逻辑封装为存储过程可减少重复代码，降低维护成本
• 代码中已添加FOR UPDATE行锁，避免并发操作同一part_id时出现脏数据，并发量低的场景可酌情移除
• 可添加日志表记录每次校验的输入值、已使用数量、剩余数量，方便后续问题排查

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