MyBatis 批量操作的五個坑，千萬不要踩了！
大家好，我是君哥。
在日常開發中，為了提高操作數據庫效率，我們往往會選擇批量操作，比如批量插入、批量更新，這樣可以減少程序和數據庫的交互，減少執行時間。但是批量操作往往隱藏著一些坑，使用不當，很可能會造成生產事故。
今天來分享使用 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=utf81.查詢條數
<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，批量操作最終都是要操作底層數據庫，批次性能怎么樣、會不會出問題，主要還得參考底層數據庫的能力。因此，想用好批量，首先要了解數據庫的特性。
