MySQL更新时仅修改单条匹配行而非全部匹配行问题求助

兼容所有MySQL版本的实现 #

直接通过分组取每个序列号对应最小Slave ID的记录，仅更新该部分匹配条目：

UPDATE item_storage ist
INNER JOIN (
  -- 取每个序列号对应的最小Slave ID记录
  SELECT serial, MIN(slave_id) AS min_slave_id
  FROM item_storage
  GROUP BY serial
) ist_filter ON ist.serial = ist_filter.serial AND ist.slave_id = ist_filter.min_slave_id
INNER JOIN operation op ON ist.serial = op.serial
SET 
  ist.current_operation = op.current_operation,
  ist.id = op.id;

MySQL 8.0+ 更灵活的实现（支持随机选记录） #

用窗口函数实现，修改排序规则即可切换选最小Slave ID还是随机选一条：

WITH ranked_storage AS (
  SELECT 
    *,
    -- 按序列号分组，按Slave ID升序排序，rn=1即为最小Slave ID的记录
    -- 要随机选的话把ORDER BY slave_id ASC改成ORDER BY RAND()即可
    ROW_NUMBER() OVER (PARTITION BY serial ORDER BY slave_id ASC) AS rn
  FROM item_storage
)
UPDATE item_storage ist
INNER JOIN ranked_storage rs 
  ON ist.serial = rs.serial 
  AND ist.slave_id = rs.slave_id 
  AND rs.rn = 1
INNER JOIN operation op ON ist.serial = op.serial
SET 
  ist.current_operation = op.current_operation,
  ist.id = op.id;

注意事项 #

• 正式执行前可以把UPDATE语句改成SELECT查询，确认匹配的条目符合预期，避免误更新数据
• 建议给两个表的serial字段添加索引，可大幅提升大表场景下的执行效率

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