mysql数据库批量插入优化 mysql批量增加

首先推荐使用PreparedStatement的批量处理操作。 

Connection conn = null;
   PreparedStatement stmt = null;
   try{
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      String SQL = "INSERT INTO Employees(id,first,last,age) " +
                   "VALUES(?, ?, ?, ?)";

      stmt = conn.prepareStatement(SQL);

      conn.setAutoCommit(false);
　　　　//数据量多的 可以使用for循环批量
      stmt.setInt( 1, 400 );
      stmt.setString( 2, "Python" );
      stmt.setString( 3, "Zhang" );
      stmt.setInt( 4, 33 );
      stmt.addBatch();

      stmt.setInt( 1, 401 );
      stmt.setString( 2, "C++" );
      stmt.setString( 3, "Huang" );
      stmt.setInt( 4, 31 );
      stmt.addBatch();
      int[] count = stmt.executeBatch();
 }catch(Exception e){
      e.printStackTrace();
   }finally{
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }
或类似于

1. PreparedStatement ps = conn.prepareStatement(  
2.    "INSERT into employees values (?, ?, ?)");  
3.   
4. for (n = 0; n < 100; n++) {  
5.   
6.   ps.setString(name[n]);  
7.   ps.setLong(id[n]);  
8.   ps.setInt(salary[n]);  
9.   ps.addBatch();  
10. }

删除的，类似。

其次，使用下面的方式：

1.批量新增

int count = list.size() / 500; //自己定义一次批量处理条数（不要太高，我这边遇到批量超过20000条，速度明显变慢，批量15w等的急死，如果太大的话，还有可能超过mysql默认的最大长度1M的限制）
int remainder = list.size() % 500;
List<Map<String, String>> oneSubmit = null;
for(int i = 0; i<= count; i++) {
　　if(i == count) {
　　　　if(remainder > 0) {
　　　　oneSubmit = list.subList(500*i, 500*i+remainder);
　　　　ceShiDao.batchInsertZztContactTmp(oneSubmit);
　　　　}
　　}else {
　　　　oneSubmit = list.subList(500*i, 500*(i+1));
　　　　ceShiDao.batchInsert(oneSubmit);
　　}
}

@Insert({"<script>",
"insert into xxx (file_name, file_id, user_id, phone,create_user_id,update_user_id,status,params) values ",
"<foreach collection='list' item='item' separator=','>",
" (#{item.fileName},",
" #{item.fileId},",
" #{item.userId},",
" #{item.phone},",
" #{item.createUserId},",
" #{item.updateUserId},",
" #{item.status},",
" #{item.params})",
"</foreach>",
"</script>"})
int batchInsert(@Param("list") List<Map<String, String>> list);

 

2.批量删除

int count = contactIdList.size() / 500;
int remainder = contactIdList.size() % 500;
List<Integer> oneSubmit = null;
for(int i = 0; i<= count; i++) {
　　if(i == count) {
　　　　if(remainder > 0) {
　　　　　　oneSubmit = contactIdList.subList(500*i, 500*i+remainder);
　　　　　　ceShiDao.batchDelete(oneSubmit);
　　　　}
　　}else {
　　　　oneSubmit = contactIdList.subList(500*i, 500*(i+1));
　　　　ceShiDao.batchDelete(oneSubmit);
　　}
}

@Delete({"<script>",
"delete from xxx",
"where tmp_contact_id in ",
" <foreach collection='list' item='item' open='(' separator=',' close=')'>",
" #{item}",
" </foreach>",
"</script>"
})
int batchDelete(@Param("list") List<Integer>list);