如今mysql普遍的插入方式有如下两种：
1、循环单条插入
1. <insert id="insert" parameterType="com.chargeProject.consumer.entity.Test">
2. insert into test (id, nums, name)
3. values (#{id,jdbcType=INTEGER}, #{nums,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
4. </insert>2、拼装批量插入
2. insert into test (id, nums, name)
3. values
4. <foreach collection="list" item="item" separator=",">
5. (#{item.id,jdbcType=INTEGER}, #{item.nums,jdbcType=INTEGER}, #{item.name,jdbcType=VARCHAR})
6. </foreach>
7. </insert>一般都是通过mybatis框架进行辅助实现的，当然也可以自动拼装。今天介绍的是mysql自带的一种批量插入方式且效率更高，通过LOAD DATA LOCAL INFILE实现大批量插入。
MySQL使用LOAD DATA LOCAL INFILE从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。
但是这个方法有个缺点，就是导入数据之前，必须要有文件，也就是说从文件中导入。这样就需要去写文件，以及文件删除等维护。某些情况下，比如数据源并发的话，还会出现写文件并发问题，很难处理。
那么有没有什么办法，可以达到同样的效率，直接从内存（IO流中）中导入数据，而不需要写文件呢？
MySQL社区提供这样一个方法：setLocalInfileInputStream（），此方法位于com.mysql.jdbc.PreparedStatement 类中。通过使用 MySQL JDBC 的setLocalInfileInputStream 方法实现从Java InputStream中load data local infile 到MySQL数据库中。
代码如下：
public class LoadDataInFileUtil {
4. private Logger logger = LoggerFactory.getLogger(LoadDataInFileUtil.class);
5. private Connection conn = null;
6. @Resource
7. private JdbcTemplate jdbcTemplate;
8.
9. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
10.
11. /**
12. * 将数据从输入流加载到MySQL。
13. *
14. @param loadDataSql SQL语句。
15. @param dataStream 输入流。
16. @param jdbcTemplate JDBC。
17. @return int 成功插入的行数。
18. */
19. private int bulkLoadFromInputStream(String loadDataSql,
20. InputStream dataStream,
21. JdbcTemplate jdbcTemplate) throws SQLException {
22. if (null == dataStream) {
23. "输入流为NULL，没有数据导入。");
24. return 0;
25. }
26. conn = jdbcTemplate.getDataSource().getConnection();
27. PreparedStatement statement = conn.prepareStatement(loadDataSql);
28. int result = 0;
29. if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
30. class);
31. mysqlStatement.setLocalInfileInputStream(dataStream);
32. result = mysqlStatement.executeUpdate();
33. }
34. return result;
35. }
36.
37. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
38.
39. /**
40. * 组装 SQL 语句。
41. *
42. @param dataBaseName 数据库名。
43. @param tableName 表名。
44. @param columnName 要插入数据的列名。
45. */
46. public String assembleSql(String dataBaseName, String tableName, String columnName[]) {
47. String insertColumnName = StringUtils.join(columnName, ",");
48. String sql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE " + dataBaseName + "." + tableName + "(" + insertColumnName + ")";
49. return sql;
50. }
51.
52. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
53.
54. /**
55. * 往 StringBuilder 里追加数据。
56. *
57. @param builder StringBuilder。
58. @param object 数据。
59. */
60. public void builderAppend(StringBuilder builder, Object object) {
61. object);
62. "\t");
63. }
64.
65. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
66.
67. /**
68. * 往 StringBuilder 里追加一条数据的最后一个字段。
69. *
70. @param builder StringBuilder。
71. @param object 数据。
72. */
73. public void builderEnd(StringBuilder builder, Object object) {
74. object);
75. "\n");
76. }
77.
78. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
79.
80. /**
81. * 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL。
82. *
83. @param sql SQL语句。
84. @param builder 组装好的数据。
85. */
86. public int fastInsertData(String sql, StringBuilder builder) {
87. int rows = 0;
88. null;
89. try {
90. byte[] bytes = builder.toString().getBytes();
91. if (bytes.length > 0) {
92. new ByteArrayInputStream(bytes);
93. //批量插入数据。
94. long beginTime = System.currentTimeMillis();
95. rows = bulkLoadFromInputStream(sql, is, jdbcTemplate);
96. long endTime = System.currentTimeMillis();
97. "LOAD DATA LOCAL INFILE :【插入" + rows + "行数据至MySql中，耗时" + (endTime - beginTime) + "ms。】");
98. }
99.
100. catch (SQLException e) {
101. e.printStackTrace();
102. finally {
103. try {
104. if (null != is) {
105. is.close();
106. }
107. if (null != conn) {
108. conn.close();
109. }
110. catch (IOException | SQLException e) {
111. e.printStackTrace();
112. }
113. }
114. return rows;
115. }
116.
117. /*-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --*/
118.
119. }调试代码如下：
1. 2.public static final String DATA_BASE_NAME = "charge";
3.// 表名。
4.public static final String TABLE_NAME = "test";
5.// 要插入数据的列名。（必须与插入的数据一一对应）
6.public static final String COLUMN_NAME[] = {"id", "nums", "name"};
7.
8.
9.
10.@Override
11.public ResultContent insert(String name) {
12.StopWatch stopWatch = new StopWatch();
13. stopWatch.start();
14. 15.StringBuilder sb = new StringBuilder();
16.new ArrayList<>();
17.for(int i = 1; i < 100000; i++) {
18. loadDataInFileUtil.builderAppend(sb, UUID.randomUUID().toString());
19. loadDataInFileUtil.builderAppend(sb, i);
20. loadDataInFileUtil.builderEnd(sb, name + i);
21. }
22.
23.
24.String sql = loadDataInFileUtil.assembleSql(DATA_BASE_NAME, TABLE_NAME, COLUMN_NAME);
25.int insertRow = loadDataInFileUtil.fastInsertData(sql, sb);
26."insert应收报表数量insertRow："+insertRow);
27.
28.
29. stopWatch.stop();
30."花费时间" + stopWatch.getTotalTimeSeconds());
31.
32.
33.
34."---------方法执行结束--------------");
35.return new ResultContent(0, "success", name);
36. }经过测试插入1w条数据时候与拼装批量插入语句时间差别不大，当插入数量达到10w出现了明显的时间差：
拼装批量插入语句花费时间：6.83s
LOAD DATA LOCAL INFILE实现大批量插入花费时间：1.23s
当表格的字段更多数据量更大出现的时间差就越大。
总结：当需要进行大批量数据插入的时候，可以优先考虑LOAD DATA LOCAL INFILE实现方式。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
