com.sb.test;
import java.sql.*
;
import java.util.Random;
/**
* 批量操作+事务提交
**/
public class JdbcInsertDataLess {
private static String url = "jdbc:mysql://localhost:3306/TEST?characterEncoding=utf8&serverTimezone=GMT%2B8"
;
private static String user = "root"
;
private static String password = "root"
;
public static void main(String[] args) {
Connection conn =
null;
PreparedStatement pstm =
null;
ResultSet rt =
null;
try {
Class.forName("com.mysql.jdbc.Driver"
);
conn =
DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO person(pid,name) VALUES(?,CONCAT(‘姓名‘,?))"
;
pstm =
conn.prepareStatement(sql);
conn.setAutoCommit(false);
Long startTime =
System.currentTimeMillis();
Random rand =
new Random();
int a, b;
//插入100000条数据
for (
int i = 1; i <= 100000; i++
) {
pstm.setInt(1
, i);
pstm.setInt(2
, i);
//随机数
a = rand.nextInt(10
);
b = rand.nextInt(10
);
pstm.setString(1, "188" + a + "88" +
b);
pstm.addBatch();
}
pstm.executeBatch();
conn.commit();
Long endTime =
System.currentTimeMillis();
System.out.println("************************正在插入中，请稍等******************************"
);
System.out.println("批量插入用时：" + (endTime -
startTime));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
if (pstm !=
null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn !=
null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
}
二.存储过程批量插入
DROP PROCEDURE IF EXISTS proc_initData;-- 如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=100000 DO
INSERT INTO person VALUES(i,CONCAT(‘姓名‘,i));
SET i = i+1;
END WHILE;
END $
CALL proc_initData();
三.使用UNION ALL来进行插入操作
INSERT INTO person(pid,NAME)
SELECT 4,‘000‘
UNION ALL
SELECT 5,‘001‘
UNION ALL
SELECT 6,‘002‘
1、INSERT INTO person(pid, name)
VALUES(11, val12),
(21, val22),
(31,val32) ;
2、INSERT INTO person(pid, name) SELECT 333, val12 UNION ALL SELECT 33333, val22 union all select 444, val32 ;
这样的写法是属于复合SQL语句，表示先把两个SELECT的结果集进行无删减的联合，再把联合结果插入到TABLE中。
四.脚本插入数据
insert.sql
INSERT INTO person(pid, name)
SELECT 777,‘val12‘
UNION ALL
SELECT 88888888,‘val22‘
union all
select 88888, ‘val32‘ ;
insertbatch.bat
#!/bin/bash
p="/tmp" //目录下全是sql文件
dbUser=‘root‘
dbPassword=‘root‘
dbName=‘hengda‘
cd $p;
for f in `ls $p/*.sql`
do
echo $f;
mysql -u $dbUser -p$dbPassword -f $dbName -e "source $f";
mv $f $f.done;
done
echo ‘finished!‘
reradme.md
/tmp" ://目录下全是sql文件
批量插入数据到 MySQL的几种方式
标签：ring tar exti commit cte ace img ndt union
