Mysql的批量导入
时间：2021-07-01 10:21:17
帮助过：23人阅读
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;
public class InsertDemo {
private static String user = "root"
;
private static String pass = "123456"
;
private static String URL = "jdbc:mysql://127.0.0.1:3306/test"
;
@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);
}
}
View Code
2. SQL实现
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
Mysql的批量导入
标签：sed ima stat length -418 dba substr 批量 batch
