package cn.enjoy;
import org.junit.Test;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
/**
* Created by
*/
public class InsertDemo {
private static String user = "root";
private static String pass = "root1234%";
private static String URL = "jdbc:mysql://127.0.0.1:3306/demo";
@Test
public void test1() throws Exception{
BufferedReader br = new BufferedReader(new FileReader("D:\\product_info.sql"));
Connection conn = DriverManager.getConnection(URL , user, pass);
LocalDateTime now = LocalDateTime.now();
System.out.println(now);
br.lines().forEach(sql->{
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.executeUpdate();
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
});
br.close();
conn.close();
LocalDateTime now2 = LocalDateTime.now();
System.out.println(now2);
}
int i=0;
@Test
public void test2() throws Exception{
BufferedReader br = new BufferedReader(new FileReader("D:\\product_info.sql"));
Connection conn = DriverManager.getConnection(URL , user, pass);
LocalDateTime now = LocalDateTime.now();
System.out.println(now);
conn.setAutoCommit(false);
br.lines().forEach(sql->{
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.addBatch();
if((i%2000)!=0 && i<=2097152) {
i++;
}else {
ps.executeBatch();
conn.commit();
i=0;
}
} catch (Exception e) {
e.printStackTrace();
}
});
br.close();
conn.close();
LocalDateTime now2 = LocalDateTime.now();
System.out.println(now2);
}
String str = "INSERT INTO `product_info` VALUES ";
@Test
public void test3() throws Exception{
BufferedReader br = new BufferedReader(new FileReader("D:\\product_info.sql"));
Connection conn = DriverManager.getConnection(URL , user, pass);
LocalDateTime now = LocalDateTime.now();
System.out.println(now);
conn.setAutoCommit(false);
br.lines().forEach(sql->{
try {
str =str + sql.split("VALUES")[1].replace(";",",");
if((i%2000)!=0 && i<=2097152) {
i++;
}else {
i++;
str= str.substring(0,str.length()-1);
// System.out.println(str);
PreparedStatement ps = conn.prepareStatement(str);
ps.executeUpdate();
str = "INSERT INTO `product_info` VALUES ";
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
}
});
br.close();
conn.close();
LocalDateTime now2 = LocalDateTime.now();
System.out.println(now2);
// 2018-08-29T22:46:50.861
// 2018-08-29T22:48:30.347
// 2018-08-30T09:54:47.041
// 2018-08-30T09:56:21.848
}
}
提交前关闭自动提交
尽量使用批量insert语句
可以使用MyISAM存储引擎
LOAD DATA INFLIE
LOAD DATA INFLIE；
使用LOAD DATA INFLIE ,比一般的insert语句快20倍
select * into OUTFILE 'D:\\product.txt' from product_info
load data INFILE 'D:\\product.txt' into table product_info fields terminated by ',' enclosed by '"';
| 
转播
