MySQL像数据库批量插入100w条数据

时间:2022-08-16 23:24:04
package com.test.mysql;

import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class MysqlBatchUtil {
private String sql="INSERT INTO tb_user (user_name,password,age) VALUES (?,?,?)"; 
private String connectStr="jdbc:mysql://localhost:3306/test";
private String username="root"; 
private String password="root"; 
private void doStore() throws ClassNotFoundException, SQLException, IOException { 
  Class.forName("com.mysql.jdbc.Driver"); 
  connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";//此处是测试高效批次插入,去掉之后执行时普通批次插入
  Connection conn = (Connection) DriverManager.getConnection(connectStr, username,password); 
  conn.setAutoCommit(false); // 设置手动提交 
  int count = 0;
  PreparedStatement psts = conn.prepareStatement(sql);
  long start = System.currentTimeMillis();
  for(int i=0;i<=1000000;i++){
   psts.setString(1, i+"username1");
   psts.setString(2, i+"password2");
   psts.setInt(3, i);
   psts.addBatch();   // 加入批量处理 
   count++;  
  } 
  psts.executeBatch(); // 执行批量处理 
  conn.commit(); // 提交 
  System.out.println("数量="+count); 
  System.out.println("运行时间="+ (System.currentTimeMillis() - start)+"毫秒");
  conn.close(); 

public static void main(String[] args) {
  try {
   new MysqlBatchUtil().doStore();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
}
}