"批量插入 5000 条"在 MyBatis 里有三种完全不同的实现，代价差一个数量级：一条 SQL 拼 5000 组 values、5000 条 SQL 攒成 JDBC batch、以及 5000 条 SQL 逐条发。

选错的症状各不相同：拼太多直接 PacketTooBigException，攒批忘了 flush 数据没落库，逐条发就是几千次网络往返。这篇按"机制 → 限制 → 失败语义"拆开。

---

1. 三种写法的机制差异

写法	发往 MySQL 的语句	网络往返	主要风险
<foreach> 多值 values	1 条长 SQL	1 次	SQL 文本超包、解析开销大
ExecutorType.BATCH	驱动攒批，flush 时按批发送	批次数	忘记 flushStatements
逐条 insert（SIMPLE）	N 条	N 次	往返与事务开销
BATCH + rewriteBatchedStatements	驱动合并成多值 SQL	批次数，但语句数变少	只对特定 SQL 形状生效

关键区别在于谁来做合并：<foreach> 是你在 XML 里合并，rewriteBatchedStatements 是 JDBC 驱动在协议层合并，BATCH 只是把多条语句攒起来延后发送。三者可以叠加，但叠加后限制也叠加。

---

2. <foreach> 批量插入：先算三个上限

<insert id="batchInsert">
  insert into t_user (name, status, created_at) values
  <foreach collection="list" item="u" separator=",">
    (#{u.name}, #{u.status}, #{u.createdAt})
  </foreach>
</insert>

上限	典型值	超了会怎样
max_allowed_packet	MySQL 8.0 默认 64MB，按版本核对	PacketTooBigException / 连接被断开
单语句占位符数	65535（16 位计数）	语句无法 prepare，报语法/参数错误
单语句行数	无硬限制，受上面两条约束	解析与 undo 开销线性增长
in 列表长度	无硬限制	执行计划退化，优化器放弃索引

按三列算，65535 / 3 ≈ 21845 行就是占位符上限；实际还要留余量给包大小。把批量大小定在 500–1000 行是常见工程选择：再大，SQL 解析、锁持有时间、undo 增长都会变成新问题。

in 列表同理：where id in (1000 个值) 不是语法错误，但优化器可能放弃索引改走全表。用 500–1000 分批，既保住计划也保住包大小。

---

3. ExecutorType.BATCH 的用法

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    UserMapper mapper = session.getMapper(UserMapper.class);
    for (int i = 0; i < list.size(); i++) {
        mapper.insert(list.get(i));
        if (i % 500 == 499) {
            session.flushStatements();   // 必须显式 flush，否则数据不落库
        }
    }
    session.flushStatements();
    session.commit();
} finally {
    session.close();
}

三个必须知道的点：

1. 不 flush 就不落库。BATCH 模式下 insert 只是把语句加到 BatchResult 里，flushStatements() 才真正执行。Spring 里用 SqlSessionTemplate 时，flushStatements 在事务提交前由 MyBatis 自动触发，所以"直接注入 mapper 用 BATCH"要注意 executor 的注入方式；手写 SqlSession 就不会自动。
2. useGeneratedKeys 在 BATCH 下的行为依驱动而变。MySQL Connector/J 在 rewriteBatchedStatements=true 时，回填的自增 id 可能不完整。需要主键就改用 <foreach> 多值插入——MySQL 对多值 insert 会返回全部生成键（Jdbc3KeyGenerator 能逐行处理）。
3. BATCH 与 SIMPLE 的取舍看事务。一次事务里要写几千行，BATCH 优；只写两三条，BATCH 的攒批与 flush 开销反而多余。

Spring Boot 里的配置方式：

mybatis:
  executor-type: batch        # 全局默认，慎用：会影响所有写入

全局设 batch 是常见误操作：所有 insert 都变成延后执行，某个方法里查了一次刚插入的数据，结果查不到（还没 flush）。正确做法是按方法粒度用 SqlSessionTemplate 的 batch 版本，或在批量任务里单独开 SqlSession。

---

4. rewriteBatchedStatements=true 的前提

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/demo?rewriteBatchedStatements=true&useServerPrepStmts=false

它的作用是把 N 条同形状的 insert into t (a,b) values (?,?) 在驱动层合并成一条多值语句，从而把 N 次往返压成 1 次。生效的前提很硬：

前提	说明
SQL 形状完全一致	同一 MappedStatement、同列数、同顺序
必须是 insert into ... values (?,?)	insert ... select、replace、带函数的写法不合并
参数类型一致	混用 setNull/setString 会退化为逐条发送
驱动版本支持	Connector/J 5.1.13+，具体行为按版本核对
useServerPrepStmts=false（常见组合）	服务端预编译与批量重写同时开启时合并可能失效

最容易踩的是第一条：一个 if 里给不同字段赋值，导致某些语句列数不同，驱动就放弃合并，退回逐条——而你以为已经开了重写。

---

5. 批量更新：case when 与 on duplicate key update

<update id="batchUpdateStatus">
  update t_user
  set status = case id
    <foreach collection="list" item="u">when #{u.id} then #{u.status}</foreach>
  end
  where id in <foreach collection="list" item="u" open="(" separator="," close=")">#{u.id}</foreach>
</update>

<insert id="upsertBatch">
  insert into t_user (id, name, status) values
  <foreach collection="list" item="u" separator=",">(#{u.id}, #{u.name}, #{u.status})</foreach>
  on duplicate key update
    name = values(name), status = values(status)
</insert>

维度	case when	on duplicate key update
语义	按 id 更新指定列	不存在则插入，存在则更新
风险	where 漏写就是全表更新	撞唯一键即静默改成 update，可能覆盖不该覆盖的行
影响行数	实际匹配行数	1=插入、2=更新、0=无变化，统计时容易误读
自增 id	无影响	冲突也会消耗自增值，表现为 id 跳号
binlog	普通 UPDATE	ROW 格式下安全；STATEMENT 格式有主从不一致风险

on duplicate key update 的隐蔽代价是它认所有唯一键，不只主键。表上还有一个 uk_phone 时，一条"主键不同但手机号相同"的记录会被当成冲突并更新那一行——数据被静默改到另一条 id 上。用之前先数清楚表上有几个唯一约束。

values(col) 在 MySQL 8.0.20 起被标记为废弃，新写法是行别名：

insert into t_user (id, name) values (1,'a')
as new on duplicate key update name = new.name;

---

6. 分批提交：批大小与事务粒度

int batchSize = 500;
for (int from = 0; from < list.size(); from += batchSize) {
    int to = Math.min(from + batchSize, list.size());
    List<User> part = list.subList(from, to);
    transactionTemplate.executeWithoutResult(s -> mapper.batchInsert(part));
}

每批一个独立事务，而不是"整个 10 万条一个事务"。理由有三条：

• undo 与锁：长事务持有 undo 直到提交，10 万行的单事务会让 undo 表空间膨胀、锁范围扩大；
• 主从延迟：一个巨型事务在从库上是一个大 binlog 事件，应用期间从库完全阻塞；拆成小事务，从库能持续追上；
• 失败恢复：批级事务失败只回滚当前批，前面的批已提交，重跑从断点继续。

代价是失去整体原子性。如果业务要求"全成功或全失败"，就不能分批提交，只能接受单事务的代价——这是取舍，不是优化。

---

7. 失败语义：怎么定位那一行

写法	失败范围	定位手段
<foreach> 多值插入	整条语句回滚	报错信息只有语句片段，需二分法缩小
BATCH + flush	该批中已执行的语句回滚	BatchUpdateException.getUpdateCounts() 与索引对齐
BATCH 未 flush	至多在 commit 时暴露	极易漏
分批提交	仅当前批	批内再二分，或按业务唯一键重试

BATCH 的定位能力最好：

try {
    session.flushStatements();
} catch (PersistenceException e) {
    BatchUpdateException bue = (BatchUpdateException) e.getCause();
    int[] counts = bue.getUpdateCounts();   // 与 batch 内语句顺序一一对应
}

<foreach> 拼 1000 行时没有这种能力：MySQL 只会告诉你"第 N 行附近的语法/约束错误"，业务上需要按唯一键反查，或者干脆二分插入。所以批量写失败率不为零的场景，优先 BATCH。

---

8. 排错

现象	原因	处理
PacketTooBigException	单条 SQL 超过 max_allowed_packet	降到 500–1000 行/批，或调大服务端与客户端包上限
Prepared statement contains too many placeholders	占位符超 65535	减少行数或列数
BATCH 执行完数据没落库	忘记 flushStatements()，事务也没提交	显式 flush 后再 commit
开了 rewriteBatchedStatements 却没加速	SQL 形状不一致 / 服务端预编译同时开启	统一语句形状，关 useServerPreparedStmts
批量插入后拿不到自增 id	BATCH + 重写时驱动回填不完整	改多值 insert，或不依赖自增 id
批量导入后从库延迟飙升	单事务过大	拆批，每批独立事务
on duplicate key update 改错行	命中了非主键的唯一键	先核对唯一约束，或改用 case when
一条数据非法导致整批失败	多值插入是单语句，原子回滚	预校验数据，或改 BATCH 定位后重试

---

9. 边界

场景	更合适的选择
几千行以内、要拿自增 id	<foreach> 多值插入
十万行导入	BATCH + 分批事务 + 关闭自动提交
批量更新少量列	case when 或 on duplicate key update
数据可能重复需要去重	insert ... on duplicate key update 或先建临时表
需要整体原子性	单事务，不分批，接受 undo 代价
跨库批量写	按库分别批处理，不要跨库单事务

一句话：<foreach> 是"一条长 SQL"，BATCH 是"多条短语句攒着发"，重写参数是"让驱动替你拼长 SQL"。选哪个取决于你要的是原子性、自增 id，还是吞吐。

---

10. 自测

1. 三列字段、单语句占位符上限 65535 时，多值插入的理论行数上限是多少？还要同时受什么约束？
2. BATCH 模式下不调用 flushStatements() 但调用了 commit()，数据会落库吗？
3. rewriteBatchedStatements=true 在什么情况下会静默失效？
4. on duplicate key update 为什么会改到"另一条 id"的记录上？怎么避免？
5. 一次 10 万行的单事务为什么会拖慢从库？拆批之后失去了什么？
6. 为什么说批量写失败率不为零时 BATCH 的定位能力强于 <foreach>？