MySQL批量更新多条记录不同值的最佳实践教学

简介：一份针对mysql批量更新场景的实用技术笔记，面向需要一次性更新多条记录、却对update语句与case表达式结合方式不熟悉的开发人员。资源从实际工作问题出发：已用insert导入name字段后，再想补充package字段时的处理思路，给出了一种基于case when与where in的动态sql写法，并附有php拼接语句示例，可帮助读者快速迁移到自己的批量更新需求中。包体为单份pdf文档，大小约40kb，内容精炼，适合快速查阅。目前已有5746人学习下载，说明该场景确实常见。通过这份笔记，能理解批量更新不同值的核心语法、动态拼接sql时的注意事项，以及mysql_*函数已废弃、应改用mysqli或pdo等实践提示，对提升日常数据库操作效率有直接帮助。

1. 一次更新多条记录，难点不在 update 语法，而在值的映射

mysql 的 update 默认一次只做一件事：把 where 命中的行，全部改成 set 里的同一个值。但真实业务里最常见的是另一类需求——订单 1 改成已支付，订单 2 改成已发货，订单 3 改成已取消。于是很多人下意识写三条 update，再套一层循环，结果 n 条记录就产生 n 次网络往返、n 次行锁申请。如果能把这一批主键和对应新值映射到一条 update 里，性能差距会非常直观，这也是「一次更新多条记录」在工程上的真正含义。下面会围绕三种落地写法展开：case when、join 临时表、insert on duplicate key update，分别说明构造方式、适用边界和锁/影响行数的坑，适合正在做批量更新接口或数据修复脚本的后端工程师参考。

2. 多条 update 的核心障碍：set 是统一赋值，数据是一对一映射

2.1 update 单表语句的语法边界

update orders
set status = 'paid'
where id = 1001;

这是最常见的单条更新：先根据 where 定位到 id=1001 的行，锁住该行，再把 status 改成 paid。注意这里的赋值表达式 'paid' 是固定的，所以所有满足 where 的行最终都会得到同一个值。当需求变成 id=1001 改成 paid、id=1002 改成 shipped、id=1003 改成 cancelled 时，单表 update 的 set 子句没有办法直接表达“每行赋不同值”。

这不是 mysql 的能力缺陷。set 后面可以跟 case 表达式，也可以把另一张表 join 进来直接引用对方列，还可以借用 insert 的 upsert 行为完成更新。真正的挑战不是 mysql 写不出来，而是你选择哪种映射方式：把对应关系写成表达式，还是把对应关系放进一张表。

2.2 批量更新的三类典型场景

这三类场景的共同点是：主键集合已知，新值集合与主键一一对应。sql 要解决的问题是如何把这条对应关系带进 update 语句。最直接的两种思路，一是把对应关系固化成表达式，也就是 case when；二是把对应关系物化成表，也就是临时表或派生表，再通过 join 让更新值从别的表里取。选型主要看更新行数和字段数量。

2.3 为什么 for 循环逐条 update 是下策

# 低效写法：每条记录都走一次网络往返
for item in items:
    cursor.execute(
        "update orders set status = %s where id = %s",
        (item["status"], item["id"]),
    )

这段代码在数据量只有几条时问题不大，但到几十条以上，应用层与 mysql 的交互次数会线性增长。每条 update 默认有独立的事务边界，行锁持有时间被拆散，线程在等待锁上的总耗时反而更高。更麻烦的是，如果循环执行到一半失败，已经执行的更新要么全部回滚（需要应用程序自己管理事务），要么在 autocommit 模式下残留一部分，一致性很难控制。

批量更新并不是为了把 sql 写得“好看”，而是把 n 次短事务压缩成一次较长事务，减少总锁时间。代价是单条 sql 变复杂，出错后的定位成本变高。所以后面的每种写法，都要配合 explain 和影响行数检查一起使用。

2.4 选型思路：先回答三个问题

1. 一次更新的行数在几十条以内，字段只有一两个，优先用 case when。
2. 字段多、行数大，或者新值本身就在另一张表里，优先用 join 临时表。
3. 需要“有则更新、无则插入”的幂等同步场景，优先用 insert on duplicate key update。

这个划分不是硬性的。超过 500 行时，case 语句会生成很长的一段 sql，可能超过 max_allowed_packet 限制；join 临时表虽然多了一次建表和一次 insert，但 sql 本身结构清晰。反过来，只有 5 条数据时建临时表反而多出两步操作，得不偿失。接下来按这个顺序逐步展开。

3. 用 case when 在一条 update 里映射不同值

3.1 最小可跑示例：按主键改三个价格

update products
set price = case id
    when 1 then 9.99
    when 2 then 12.50
    when 3 then 15.00
    else price
end
where id in (1, 2, 3);

case id when ... then ... 是 case when id = ... then ... 的简写。mysql 会拿 id 的值从上往下匹配，命中第一个 then 就返回，后续 when 不再比较。else price 必须写，否则当 where 命中了某个 id、但该 id 不在 when 列表里时，case 会返回 null，最终 price = null 会把价格清空，这是最容易踩的坑。

这里的 where id in (1,2,3) 不负责过滤 case 用不到的记录，而是限定扫描范围。id 是主键，in 列表会走主键索引的 range 访问，锁只落在这几行上。如果不加 where，mysql 会对全表每一行都计算一次 case，虽然 else 能保护不在列表内的行不变，但全表扫描带来的锁和 io 开销会被放大。

then 后面的值可以是常量，也可以是表达式，比如 price * 0.8 。要注意所有分支的返回类型尽量一致，price 是 decimal 时，then 写整数会被隐式转换，精度敏感的业务场景建议先单独测试。

3.2 多个字段同时更新与 else 兜底

update products
set
  price = case id
    when 1 then 9.99
    when 2 then 12.50
    else price
  end,
  stock = case id
    when 1 then 100
    when 2 then 50
    else stock
  end,
  updated_at = now()
where id in (1, 2);

set 里的多个字段各自独立计算。这里 id=1 和 id=2 都出现在两个 case 的 when 列表里，所以 else 不会触发。但如果某一行只出现在 where 中、没出现在某个 case 的 when 列表中，这个字段就会被 else 保护，保留原值，而其他字段可能被更新。这里有个容易忽略的差异：where 的集合和 case 的集合必须完全一致，否则就会出现“同一行部分字段变了，部分字段没变”的中间状态。

updated_at = now() 是固定赋值，对所有命中行生效。如果业务要求只有当数据真正变化时才更新时间，就不能这么写，得把 now() 也放进 case，或者用 if 判断新旧值是否相同。

3.3 动态生成 case 语句时的长度和注入边界

def build_case_update(rows):
    whens = " ".join(
        f"when {row['id']} then {row['price']}" for row in rows
    )
    ids = ",".join(str(row["id"]) for row in rows)
    return (
        f"update products set price = case id {whens} else price end "
        f"where id in ({ids})"
    )

这种拼接方式只能用于可信的纯数字数据。id 和 price 来自用户输入时，不能直接拼进 sql，因为参数绑定无法覆盖动态生成的 case 分支，必须在前置层做严格的类型校验。

生成后的 sql 长度大约等于 when 分支数量之和。1000 条数据时语句会变得很长，mysql 默认的 max_allowed_packet 是 64mb，通常不会撞到，但长 sql 在网络传输和解析阶段的开销会明显上升。常见做法是控制单批行数，比如每 200 行拆一批，既避免语句过长，也降低长事务持锁的风险。

3.4 case when 的适用边界与参数化替代

当更新字段超过 3 个，或者每条记录有自定义计算公式时，case when 的 sql 会膨胀到难以维护。此时常见的做法是把目标数据先装进临时表，用 join 来更新。但 case when 依然是理解批量更新最直观的一把钥匙：后续 join 写法的思想，本质上就是把这里的 then 值列表搬进一张表。

4. 用 join、临时表和 upsert 承载批量 update，绕开长 sql 和高字段数

4.1 update ... join 的多表更新语法

update products p
join new_prices t on p.id = t.id
set p.price = t.new_price;

这是 mysql 多表更新的标准写法。mysql 会先在 on 条件上做关联，再对关联结果集中匹配到的行执行 set。 p 和 t 是表别名，set 里的 t.new_price 直接取自另一张表的列，这正好解决了“不同主键对应不同新值”的映射问题。

这里的 join 默认是 inner join，意味着主表 p 中如果在 t 里找不到匹配，那一行不会被更新；反过来 t 里多出的记录也不会报错。如果希望 p 中所有行都更新，未匹配的按默认值处理，需要改成 left join，并在 set 里用 coalesce 提供兜底值。

4.2 用临时表装载目标数据后更新

create temporary table tmp_product_updates (
  product_id int primary key,
  new_price decimal(10,2) not null
) engine=innodb;

insert into tmp_product_updates (product_id, new_price) values
(1, 19.99), (2, 25.00), (3, 30.00);

update products p
join tmp_product_updates t on p.id = t.product_id
set p.price = t.new_price;

临时表只对当前会话可见，连接关闭后自动释放，不会影响其他连接。三步操作通常放在同一个连接和事务里执行。第一步建表，第二步把映射关系插入，第三步执行更新。

临时表需要建索引吗？如果临时表只有几十行，mysql 通常会把它作为驱动表，对 products 的每一行去临时表里按主键查找，效率已经很不错。当要更新的主表行数非常大时，建议在 products.id 和临时表的 product_id 上都建索引，并先跑 explain 确认访问类型不是 all。如果出现全表扫描，会把扫描到的每一行都加锁，批量更新退化成大范围锁表。

4.3 不用临时表：values 派生表与 union all 写法

mysql 8.0.19 起可以直接用行构造器生成派生表：

update products p
join (
  values row(1, 9.99), row(2, 12.50), row(3, 15.00)
) as t(product_id, new_price)
on p.id = t.product_id
set p.price = t.new_price;

values row(...) 生成一个虚拟表，列名在 as 子句中指定。它不需要 create temporary table，适合一次性更新。如果 mysql 版本低于 8.0.19，或者需要兼容 mariadb，可以用 union all 派生表：

update products p
join (
  select 1 as product_id, 9.99 as new_price
  union all select 2, 12.50
  union all select 3, 15.00
) t on p.id = t.product_id
set p.price = t.new_price;

union all 的每个分支定义一行，列名来自第一个 select。派生表在语句执行期间物化，语句结束自动释放，不产生额外 ddl，兼容 mysql 5.7。

这里有一个必须提前检查的问题：如果派生表里出现重复 product_id，join 会让 products 中同一行被匹配多次，set 赋值可能执行多次，影响行数会被放大。执行 update 前对派生表做一次 distinct 检查是最稳妥的。

4.4 更新子查询最容易踩的坑：不能直接查自己的表

-- 会报错：you can't specify target table 'orders' for update in from clause
update orders
set status = 'paid'
where id in (
  select order_id from orders where create_time < '2024-01-01'
);

mysql 不允许 update 的目标表同时出现在 from 子句中，防止执行顺序出现二义性。规避方法很固定：在子查询外面再包一层派生表，让 mysql 先物化子查询结果。

update orders
set status = 'paid'
where id in (
  select temp.order_id from (
    select order_id from orders
    where create_time < '2024-01-01'
  ) as temp
);

内层 select 先执行并物化成临时结果，外层再从结果里取 order_id。此时 update 的目标表 orders 没有直接出现在 from 子查询里，绕过了限制。注意派生表必须写别名，示例中的 temp 不能省。这种写法在数据量较大时可能产生磁盘临时表，但对绝大多数批量修复场景来说，比改造应用代码成本低。

4.5 用 insert ... on duplicate key update 做 upsert 式批量更新

insert into products (id, price, updated_at) values
(1, 9.99, now()),
(2, 12.50, now()),
(3, 15.00, now())
on duplicate key update
price = values(price),
updated_at = values(updated_at);

这种写法把更新借道成插入。mysql 检测到主键冲突后，不报错，而是执行 update 分支。优势是语句非常紧凑，天然支持批量，还能把“不存在则插入”和“存在则更新”合并成一个操作。劣势是如果传入的数据里混有不存在的主键，它会悄悄插入新行，这可能不是更新操作想要的行为。只想更新、严格禁止插入时，需要先用 select 校验主键存在性。

values(price) 返回右侧 values 列表里当前行的 price 值。从 mysql 8.0.20 开始，这个函数被标记为弃用，可以改成别名写法：

insert into products (id, price) values
(1, 9.99), (2, 12.50) as new
on duplicate key update price = new.price;

别名写法下 new.price 直接引用待插入行的列，语义更清晰。如果 id 是自增主键，即使最终走了更新分支，on duplicate key update 在尝试插入时也可能消耗自增 id，导致自增值跳号，对依赖 id 连续性的业务有副作用。

5. 批量 update 的事务边界、影响行数与验证技巧

5.1 用 row_count() 确认真正修改的行数

批量 join 更新后，mysql 返回的 affected rows 可能和预期不一致。一种常见原因是派生表中存在重复主键，导致同一行被匹配多次；另一种原因是更新的行本来就是目标值，mysql 默认不把它计入修改行数。执行完更新后可以用 row_count() 拿到立即结果：

update products p
join (values row(1, 9.99), row(1, 12.50)) t(product_id, new_price)
  on p.id = t.product_id
set p.price = t.new_price;
select row_count();

如果看到影响行数明显大于预期，第一步不是改代码，而是去查映射表里有没有重复 id。重复匹配会让同一条记录被 set 多次，最终值取决于赋值顺序，结果不一定符合预期。

5.2 事务与锁：不在高并发路径上放长事务

start transaction;
update orders set status = 'shipped' where id in (1001, 1002, 1003);
-- 业务校验，发现问题就 rollback
commit;

批量更新通常要包在事务里，保证“要么全成功，要么全回滚”。但 innodb 对 update 会加行锁，更新的行越多，锁总量越大，长事务持有锁的时间也越长，其他写入请求只能等待。常见控制手段是把单批行数限制在几百以内，并错峰执行；where 条件必须走索引，否则 innodb 会对扫描到的每一行加锁，相当于锁住了整张表。

5.3 用锁等待视图快速定位阻塞

select * from sys.innodb_lock_waits;

mysql 5.7 之后，sys 库里提供了现成的锁等待视图，能看到哪个事务阻塞了哪个事务。如果批量 update 报锁等待超时，先执行这条语句拿到阻塞线程，再去 processlist 里看它在执行什么 sql。排查死锁时，show engine innodb status 的 latest detected deadlock 段会列出两个事务的 sql，通常可以直接看出加锁顺序的问题。

5.4 用更新前后快照验证数据正确性

create temporary table before_snapshot as
select id, price from products where id in (1, 2, 3);

-- 执行批量 update
update products set price = case id
  when 1 then 9.99
  when 2 then 12.50
  else price
end where id in (1, 2, 3);

select b.id, b.price as old_price, p.price as new_price
from before_snapshot b
join products p using (id)
where b.price <> p.price;

把更新前的目标列快照到临时表，更新后做一次差集查询，能直观看到哪些行真正发生了变化。如果查询结果比预期多，说明 join 或 case 的匹配条件有问题；如果比预期少，说明某些 when 分支没有覆盖到目标数据。临时表在会话结束自动释放，不需要额外清理。

以上就是mysql批量更新多条记录不同值的最佳实践教学的详细内容，更多关于mysql批量更新的资料请关注代码网其它相关文章！