mysql批量插入java_java 下执行mysql 批量插入的几种方法及用时

方法1：

java code

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();

}

myisam：246.6秒、innodb：360.2秒

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

java code

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秒

方法3： executebatch

java code

conn = drivermanager.getconnection(jdbc_url

+ "?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=true

方法4：先load再commit

java code

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();

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！