使用MyBatis的foreach标签拼接批量插入SQL各种问题解决方法

使用MyBatis的foreach标签拼接批量插入SQL各种问题解决方法

在业务开发中，批量数据入库是高频场景，如批量导入用户数据、批量保存订单明细、批量记录日志数据等。大部分开发者为了简化代码，直接使用MyBatis的foreach标签拼接批量插入SQL，将List集合数据一次性入库。该写法在本地开发、小数据量测试时完全正常，效率极高，但部署至生产环境，当批量数据量过大时，会频繁出现SQL语句过长、数据库数据包超限、批量插入失败的线上异常，且本地无法复现，排查难度极大。

QQ20260814-185003.png
　　本文基于真实生产故障，详细分析MyBatis批量插入本地与线上表现不一致的核心原因，提供List分批批量插入的通用工具类、优化写法，解决SQL超长报错问题，同时兼顾批量入库性能与线上稳定性。

一、故障现象与场景还原

　　业务场景：用户批量导入功能，支持单次导入最多1000条用户数据，后端接收前端导入数据后，通过MyBatis批量插入SQL一次性入库。

　　本地测试：导入100-1000条数据，批量插入正常，接口响应迅速，数据入库完整，无任何报错。

　　生产环境：单次导入超过500条数据时，接口直接报错，异常信息：Packet for query is too large (xxxx >
max_allowed_packet)、SQL statement is too long，数据入库失败；导入少量数据（100条以内）正常无异常。

　　核心问题：本地数据库max_allowed_packet参数默认值较大，可支持超长SQL，而线上数据库为了安全规范，默认配置参数较小，大数量批量插入时，拼接后的SQL语句超出数据库数据包限制，直接触发异常。

二、错误代码还原

　　后端业务层批量插入代码，直接将完整List集合传入Mapper，未做分批处理：
@Service
public class UserServiceImpl implements UserService {

@Autowired
private UserMapper userMapper;

@Override
public void batchSaveUser(List<User> userList) {
    // 直接全量批量插入，无分批逻辑
    if (!CollectionUtils.isEmpty(userList)) {
        userMapper.batchInsert(userList);
    }
}

}
　　MyBatis Mapper接口代码：
@Mapper
public interface UserMapper {
/**
* 批量插入用户数据
*/
int batchInsert(@Param("list") List userList);
}
　　MyBatis XML批量插入语句：

INSERT INTO sys_user (id, username, phone, email, create_time)
VALUES

(#{item.id}, #{item.username}, #{item.phone}, #{item.email}, #{item.createTime})

三、问题核心根因

　　1. 数据库配置环境差异
　　MySQL数据库通过max_allowed_packet参数限制单次SQL请求的最大数据包大小，本地开发环境该参数默认值通常为64M甚至更大，可容纳超大批量拼接的SQL；而线上生产数据库为防止恶意超长SQL攻击、优化数据库性能，默认配置仅为4M或8M，大数量批量插入时，拼接后的SQL体积远超限制，直接报错。

　　2. 代码无分批容错逻辑
　　原生代码未对批量数据做分片处理，无论数据量多少均一次性拼接SQL，小数据量正常、大数据量直接失效，不具备线上高并发、大数据量场景的兼容性。

　　3. MyBatis foreach拼接机制缺陷
　　foreach标签会将所有数据拼接为单条SQL语句，数据量越大，SQL字符串越长，数据包体积越大，极易触发数据库参数限制，同时单条超长SQL会锁表时间过长，引发数据库性能卡顿、超时等衍生问题。

四、落地解决方案

　　最优解决方案：自定义通用List分批工具类，将大批量数据拆分固定大小的小批次数据，循环批量插入，既规避SQL超长报错问题，又保证批量入库性能，无需修改数据库线上配置，适配所有生产环境。

  1. 通用分批工具类（全局可复用）
    import org.springframework.util.CollectionUtils;
    import java.util.ArrayList;
    import java.util.List;

/**

  • 集合分批工具类

  • 解决MyBatis批量插入SQL超长、数据库数据包超限问题
    */
    public class BatchSplitUtil {

    /**

    • 默认分批大小
    • 经实测：500条为最优值，兼顾性能与SQL长度限制
      */
      private static final int DEFAULT_BATCH_SIZE = 500;

    /**

    • 集合分批拆分
    • @param list 原始集合
    • @param batchSize 单批数量
    • @return 拆分后的分批集合
      */
      public static List<List> splitList(List list, int batchSize) {
      List<List> resultList = new ArrayList<>();
      if (CollectionUtils.isEmpty(list)) {
      return resultList;
      }
      // 计算分批数量
      int size = list.size();
      int startIndex = 0;
      while (startIndex < size) {
      // 计算结束下标，防止越界
      int endIndex = Math.min(startIndex + batchSize, size);
      List subList = list.subList(startIndex, endIndex);
      resultList.add(subList);
      startIndex = endIndex;
      }
      return resultList;
      }

    /**

    • 默认分批拆分（500条/批）
      */
      public static List<List> splitList(List list) {
      return splitList(list, DEFAULT_BATCH_SIZE);
      }
      }

  1. 优化后的业务层代码
    @Service
    public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public void batchSaveUser(List userList) {
    if (CollectionUtils.isEmpty(userList)) {
    return;
    }
    // 拆分批次，循环批量插入
    List<List> batchList = BatchSplitUtil.splitList(userList);
    for (List subList : batchList) {
    userMapper.batchInsert(subList);
    }
    }
    }

  2. 进阶优化：事务兜底+批量性能提升
    　　为保证批量插入数据一致性，避免部分批次插入成功、部分失败，新增事务控制，同时开启MyBatis批量优化参数，提升入库效率：
    @Service
    @Transactional(rollbackFor = Exception.class)
    public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public void batchSaveUser(List userList) {
    if (CollectionUtils.isEmpty(userList)) {
    return;
    }
    // 拆分批次批量入库
    List<List> batchList = BatchSplitUtil.splitList(userList);
    batchList.forEach(subList -> userMapper.batchInsert(subList));
    }
    }

五、线上最佳实践与避坑要点

　　1. 固定分批阈值：建议统一设置500条/批，该数值经过大量项目实测，既不会触发SQL超长报错，也不会因分批过多导致性能下降；
　　2. 禁止随意修改线上数据库max_allowed_packet参数：盲目调大参数会增加数据库安全风险、内存占用，属于治标不治本；
　　3. 批量操作必须加事务：防止大数据量分批插入出现数据不一致、部分入库问题；
　　4. 超大数据量异步处理：单次导入超5000条数据时，建议开启异步线程批量入库，避免接口超时。

六、友情链接

凡尘博客
凡尘博客文章|凡尘博客文摘
凡尘影院
凡尘乡音|凡尘街坊
凡尘博客|雨落凡尘博客|羽落凡尘博客
凡尘博客|雨落凡尘博客|羽落凡尘博客

七、版权声明

本文为凡尘博客原创技术文章，采用 CC BY-NC-ND 4.0 协议，禁止未经授权商业转载、二次修改，非商业转载请注明作者及原文链接。作者：凡尘（雨落凡尘、羽落凡尘）

标签: none

添加新评论

  • 上一篇:
  • 下一篇: