MyBatis 批量操作的五个坑，千万不要踩了！-51CTO.COM

大家好，我是君哥。

在日常开发中，为了提高操作数据库效率，我们往往会选择批量操作，比如批量插入、批量更新，这样可以减少程序和数据库的交互，减少执行时间。但是批量操作往往隐藏着一些坑，使用不当，很可能会造成生产事故。

今天来分享使用 MyBatis 批量操作可能会遇到的一些坑。下面我们以一张员工信息表为例进行讲解，建表 SQL 如下(MySQL)：

CREATE TABLE `staff` (
  `staff_id` tinyint(3) NOT NULL COMMENT '员工编号',
  `name` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(1) DEFAULT '0' COMMENT '性别，0:男 1:女',
  `address` varchar(300) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(200) DEFAULT NULL COMMENT '邮件地址',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.查询条数

<select id="getStaffList" parameterType="int" resultType="Admin">
 select * from staff limit #{offset},50000
</select>

对应 Java 代码如下：

public List<Staff> processStaffList(){
    int offset = 0;
    List<Staff> staffList = staffDao.getStaffList(offset);
    while(true){
        //...处理逻辑
        if(staffList.size() < 10000){
            break;
        }
        offset += 10000;
        staffList = staffDao.getStaffList(offset);
    }
}

上面的查询想一次想查回 50000 条数据，很有可能数据库不能返回 50000 条。一般数据库都有查询结果集限制，比如 MySQL 会受两个参数的限制：

max_allowed_packet，返回结果集大小，默认 4M，超过这个大小结果集就会被截断；
max_execution_time，一次查询执行时间，默认值是 0 表示没有限制，如果超过这个时间，MySQL 会终止查询，返回结果。

所以，如果结果集太大不能全部返回，而我们在代码中每次传入的 offset 都是基于上次的 offset 加 50000，那必定会漏掉部分数据。

2.分页问题

<select id="getStaffList" resultType="Staff">
 select * from staff limit #{offset},1000
</select>

如果单表数据量非常大，offset 会很大造成深度分页问题，查询效率低下。我们可以通过传入一个起始的 staffId 来解决深度分页问题。

我们修改一下 xml 中的代码：

<select id="getStaffList" resultType="Staff">
 select * from staff 
  <if test="staffId != null">
        WHERE staff_id > #{staffId}
  </if>
 order by staff_id limit 1000
</select>

对应 Java 代码如下：

public List<Staff> processStaffList(){
    List<Staff> staffList = staffDao.getStaffList(null);
    while(true){
        //...处理逻辑
        if(staffList.size() < 1000){
            break;
        }
        Staff lastStaffInPage = staffList.get(staffList.size() - 1);
        staffList = staffDao.getStaffList(lastStaffInPage.getStaffId());
 }
}

3.参数数量

下面看一下这一条插入 SQL：

<insert id="batchInsertStaff">
INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
 <foreach collection="staffList" index="" item="item" separator=",">
  (#{item.staffId,}, #{item.name},#{item.age},#{item.sex},#{item.address},#{item.email})
 </foreach>
</insert>

上面的代码如果 staffList 数量太大，会导致整条语句参数过多。如果使用 Oracle 数据库，参数数量超过 65535，会报 ORA-7445([opiaba]when using more than 65535 bind variables) 的错误，导致数据库奔溃。一定要对参数数量进行限制。参数太多，也可能会抛出下面异常。

4.参数类型

上一节的代码中，插入语句并没有指定参数类型。这样会有一个问题，虽然一个字段我们定义成可以为空，但是通过参数传进来的这个字段值是空，就会抛出下面异常导致插入失败。

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: 
ParameterMapping{property='_frch_item_50.name', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', 
expressinotallow='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . 
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111

要保证程序健壮性，就要给插入语句中参数指定类型，上面代码优化后如下：

<insert id="batchInsertStaff">
    INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
 <foreach collection="staffList" index="" item="item" separator=",">
  (#{item.staffId,jdbcType=TINYINT}, #{item.name,jdbcType=VARCHAR},#{item.age,jdbcType=TINYINT},#{item.sex,jdbcType=TINYINT},#{item.address,jdbcType=VARCHAR},#{item.email,jdbcType=VARCHAR})
 </foreach>
</insert>

5.批量条数

批量操作是为了减少应用和数据库的交互，提高操作效率。但是如果对插入、更新这些批量操作不做条数限制，很可能会导致操作效率低下甚至数据库 hang 住。我们可以通过分页操作对批量条数做一些限制，看下面示例代码：

public List<Staff> processStaffList(){
    List<Staff> staffList = ...;
    int pageSize = 500;
    int pageCount = staffList / pageSize;
    for(int i = 0; i < pageCount + 1; i++){
    List<Staff> subList = (i == pageCount)? staffList.subList(i * pageSize, staffList.size()) :
          staffList.subList(i * pageSize, (i + 1) * pageSize);
    staffDao.batchInsertStaff(subList);
    }
}

总结

作为一个 orm 框架，无论我们选择 JDBC、MyBatis 还是 MyBatis-Plus，批量操作最终都是要操作底层数据库，批次性能怎么样、会不会出问题，主要还得参考底层数据库的能力。因此，想用好批量，首先要了解数据库的特性。