mysql 批量insert 和 拼语句的优化_习惯累积沉淀_新浪博客

开始是用jdbc 一条一条insert的 , 速度不能接受, 每秒几十条

用批量insert  每500插入一次,    很厉害  每秒上万条

 int insertAllCount = 0;
int insertAllMaxCount = 500; //每到500条插入一次
String insertAllStr = ""; 

insertAllCount ++;

insertAllStr += "('"+code+"','"+fieldgroup+"','"+fieldvalues+"','"+source_from+"','"+created_by+"','"+time_in_local+"','"+time_in_gmt+"'),";

if(insertAllCount == insertAllMaxCount){

insertAllStr = insertAllStr.substring(0,insertAllStr.length()-1);  //去掉最后一个逗号

int rtnCount = dao.insertAll(table_name, insertAllStr);

if (rtnCount != insertAllMaxCount){

System.out.println("批量插入错误");

}

insertAllCount = 0;

insertAllStr = "";

}

line = dr.readLine();

if(line == null){

if(insertAllStr.length() >0){

insertAllStr = insertAllStr.substring(0,insertAllStr.length()-1);

int rtnCount = dao.insertAll(table_name, insertAllStr);

if (rtnCount != insertAllCount){

System.out.println("批量插入错误");

}

insertAllCount = 0;

insertAllStr = "";

}

}

public int insertAll(String table_name, String values){

Statement stmt = null;

//插入

String insertStr = "insert into "+table_name+

" (CODE,FIELD_GROUP,FIELD_VALUES,SOURCE_FROM,CREATED_BY,TIME_IN_LOCAL,TIME_IN_GMT)VALUES ";

insertStr += values;

int rows = 0;

try {

stmt = mysqlCon.con.createStatement();

rows = stmt.executeUpdate(insertStr);

} catch (SQLException e) {

System.out.println(insertStr);

e.printStackTrace();

}finally{

if(stmt!=null){

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return rows;

}