mysql批量插入

2019独角兽企业重金招聘Python工程师标准>>>

使用JDBC连接MySQL数据库进行数据插入的时候，特别是大批量数据连续插入（10W+），如何提高效率呢？
在JDBC编程接口中Statement 有两个方法特别值得注意：
void addBatch() throws SQLException
Adds a set of parameters to this PreparedStatement object's batch of commands.
int[] executeBatch() throws SQLException
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch. 

通过使用addBatch（）和executeBatch（）这一对方法可以实现批量处理数据。

不过值得注意的是，首先需要在数据库链接中设置手动提交，connection.setAutoCommit(false)，然后在执行Statement之后执行connection.commit()。

插入1000条数据的几种方式对比

方法一：

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS); 
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)"); 
for (int i = 1; i <= COUNT; i++) {     
	pstmt.clearParameters();     
	pstmt.setInt(1, i);     
	pstmt.setString(2, DATA);     
	pstmt.execute(); 
	} 

InnoDB：360.2秒

方法二：使用事务，不自动commit

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS); 
conn.setAutoCommit(false); 
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)"); 
for (int i = 1; i <= COUNT; i++) {     
	pstmt.clearParameters();     
	pstmt.setInt(1, i);     
	pstmt.setString(2, DATA);     
	pstmt.execute();     
	if (i % COMMIT_SIZE == 0) {         
		conn.commit();     
		} 
		} 
conn.commit(); 

InnoDB：31.5秒

方法三：executeBatch

conn = DriverManager.getConnection(JDBC_URL + "??useServerPrepStmts=false&rewriteBatchedStatements=true",JDBC_USER, JDBC_PASS); 
conn.setAutoCommit(false); 
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)"); 
for (int i = 1; i <= COUNT; i += BATCH_SIZE) {     
	pstmt.clearBatch();     
	for (int j = 0; j < BATCH_SIZE; j++) {         
		pstmt.setInt(1, i + j);         
		pstmt.setString(2, DATA);         
		pstmt.addBatch();     
		}     
	pstmt.executeBatch();     
	if ((i + BATCH_SIZE - 1) % COMMIT_SIZE == 0) {         
		conn.commit();     
		} 
		} 
conn.commit(); 

InnoDB：5.2秒

上面的使用时必须
1）rewriteBatchedStatements=true
2）useServerPrepStmts=false

rewriteBatchedStatements=true，mysql默认关闭了batch处理，通过此参数进行打开，这个参数可以重写向数据库提交的SQL语句，具体参见：http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html
useServerPrepStmts=false，如果不开启(useServerPrepStmts=false)，使用com.mysql.jdbc.PreparedStatement进行本地SQL拼装，最后送到db上就是已经替换了?后的最终SQL.

方法四：先load再commit

 conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS); 
 conn.setAutoCommit(false); 
 pstmt = conn.prepareStatement("load data local infile '' " + "into table loadtest fields terminated by ','"); 
 StringBuilder sb = new StringBuilder(); 
 for (int i = 1; i <= COUNT; i++) {     
	sb.append(i + "," + DATA + "\n");     
	if (i % COMMIT_SIZE == 0) {         
		InputStream is = new ByteArrayInputStream(
			sb.toString().getBytes());         
			((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);         
			pstmt.execute();         
			conn.commit();         
			sb.setLength(0);     
			} 
			} 
InputStream is = new ByteArrayInputStream(sb.toString().getBytes()); 
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is); 
pstmt.execute(); 
conn.commit(); 

InnoDB：4.6秒

转载于:https://my.oschina.net/u/2000675/blog/868180