背景
最近看到一段使用原生 JDBC 解析 CSV 文件,然后批量插入数据库的开源代码,它实现批量插入基本流程是这样的。
第一步,拼接一条 SQL 插入语句: insert into tableName(c1,c2...) values('v1','v2'...),(...),(...)...
,一个 SQL 语句后门跟着 batchDataCount
条数据的 values
的语句,values
的值都是统一拼接为字符串的。
第二步,调用 Statement
的 addBatch
,加入批量 SQL 中,达到最大批次 SQL 条数 batchSqlCount
后,统一执行 executeBatch
提交数据。
这样的话,每一次提交插入的总条数 = batchDataCount
* batchSqlCount
。
想到一个问题: insert 语句后跟 N 条 values 数据,每条 SQL addBatch ,和把 N 条语句用预编译占位符,addBatch 批处理,有什么区别呢?
批量插入方式一
批量插入 SQL 的 values
直接拼接数据,每次拼接 batchDataCount
条数据,再将该 SQL 加入 addBatch
中批处理提交,大概代码如下:
java
代码解读
复制代码
public static void batchInsert() throws SQLException { long start = System.currentTimeMillis(); Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); // 遍历每一行 CSV 数据 String sql = "insert into xxx values (c1,c2,c3,c4) values("; int batchDataCount = 0; for (int i=0;i < totalCount;i++){ batchDataCount ++; // 伪代码:拼接当前数据到 sql 后面 sql + = 'c1,c2,c3),' // 添加到批处理中 st.addBatch(sql); // 每batchSqlCount 记录插入一次 if (i % batchSqlCount == 0){ ps.executeBatch(); conn.commit(); ps.clearBatch(); } if (batchDataCount 达到最大数量){ sql = "insert into xxx values (c1,c2,c3,c4) values("; } } // 剩余数量不足batchSqlCount ps.executeBatch(); conn.commit(); ps.clearBatch(); }
这里用的是普通 SQL,缺点时 value 后面的数值固定为字符串了。
批量插入方式二
使用预编译 SQL 的方式 addBatch
,基础代码如下:
java
代码解读
复制代码
public static void batchInsert() throws SQLException { long start = System.currentTimeMillis(); Connection conn = getConnection(); conn.setAutoCommit(false); PreparedStatement ps = null; String sql = "insert into xxx values (?,?,?,?)"; ps = conn.prepareStatement(sql); // 遍历每一行 CSV 数据 for (int i=0;i < totalCount;i++){ // 遍历没一列数据的值,设置占位符值 ps.setString(1, i+""); ... // 添加到批处理中 ps.addBatch(); // 每batchSqlCount 记录插入一次 if (i % batchSqlCount == 0){ ps.executeBatch(); conn.commit(); ps.clearBatch(); } } // 剩余数量不足batchSqlCount ps.executeBatch(); conn.commit(); ps.clearBatch(); }
整理了一份核心面试笔记包括了:Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafka 面试专题
需要全套面试笔记的【点击此处即可】即可免费获取
个人思考
两种方式,好像没有多大区别,瓶颈都在于一次提交的请求的数据量。但是用预编译的 SQL 的方式进行批量插入的方式会更好一点。同时对于存在大文本的字段,当插入字符串超过4000 时,只能用预编译方式执行,否则会报 string literal too long 异常。
方案一在代码中直接拼接 SQL 语句中的 value 部分的值的时候,都是字符串类型,且左右都加了单引号,如果列类型不一致的话,这种直接拼值的方式就行不通了。而方案二可以自由指定占位符处数据的类型。
延伸想到了 MyBatis 的批量插入方法 insertBatch
的知识,它需要额外的配置数据库 url " rewriteBatchedStatements
"属性设置为true,方法是在 jdbc 的连接url处, 设置:
- &rewriteBatchedStatements=true
- &allowMultiQueries=true