06.25 mybatis使用load data local infile實現導入數據到mysql數據庫

背景：

項目框架為：dubbo+zookeeper+ssm 數據庫為mysql

最近有個新的需求，要在代碼裡實現往數據庫插入大批量數據，每次插入的數據量從10萬~50萬條不等，而且每條數據有80多個字段，大概估算了一下，一條數據大小差不多是1kb，那麼每次插入的數據量大小應該在100M~500M之間。這個數據量還是很大的。

想來想去，我就先按照從開發到調試所碰到問題的順序來寫好了，到後面我再貼出代碼，供同行們參考。

1、碰到的第一個問題是：

Packet for query is too large (1139736> 1048576). You can change this value on the server by setting

the 'max_allowed_packet' variable.

剛接到需求時，根本沒有考慮到數據量的問題，就按照平時開發那樣，直接往數據庫裡執行insert，就報了上面這個錯，原來因為mysql有一個max_allowed_packet變量，可以控制其通信緩衝區的最大長度，所以當緩衝區的大小太小的時候，導致某些查詢和插入操作報錯。

解決辦法：

數據庫執行命令 show VARIABLES like '%max_allowed_packet%'; 查看數據庫max_allowed_packet變量配置時多少，顯示的結果為

+--------------------+---------+ | Variable_name | Value | +---------

-----------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+

這說明當前的配置時1M，我們需要將其設置大一些。

數據庫執行命令 set global max_allowed_packet = 4*1024*1024*10，將值設置為40M,，執行完後，關掉數據庫可視化界面，重新打開，要是命令行進行的就重啟mysql（不重啟的話是不行的，切記重啟mysql），然後接著執行命令show VARIABLES like '%max_allowed_packet%';看看有沒有設置成功。一般都是可行的！至此，第一個問題解決。

2、碰到的第二個問題是：

com.alibaba.dubbo.remoting.transport.AbstractCodec.checkPayload() ERROR Data length too large: 11557050, max payload: 8388608 java.io.IOException: Data length too large: 11557050, max payload: 838860

這個錯是dubbo相關的，為什麼會報這個錯呢？想來想去，可能是service服務端讀取到的數據量太大，服務端提供給web客戶端的數據量就過大，超過了dubbo的默認值8M，錯誤信息如上所示，天哪，第一次碰到這樣的報錯，各種查資料，最終還是解決了。

解決辦法：

方法1、 修改提供方的dubbo配置，

在dubbo.properties 中增加如下

dubbo.protocol.dubbo.payload=41943040（默認為8M，即8388608）

方法2、

在dubbo-provider.xml文件配置如下

<provider>

如上兩種方法都是將值修改為40M。

3、碰到的第三個問題：

使用mysql的load data local infile往數據庫導數據時，英文和數字都正常導入，但是，中文要麼不顯示，要麼就是亂碼，真的是搞不懂了，怎麼會這樣呢？以下是導入部分代碼：

[java] view plain copy

1. public
   void batchInsert(List<bqloan> bqLoanList) throws ClassNotFoundException, SQLException { /<bqloan>
2. //1000條一提交
3. int COMMIT_SIZE=1000;
4. //一共多少條
5. int COUNT=bqLoanList.size();
6. Connection conn= null;
7. try {
8. Class.forName("com.mysql.jdbc.Driver");
9. String url = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.url","CONF_HOME");
10. String user = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.username","CONF_HOME");
11. String password = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.password","CONF_HOME");
12. conn= DriverManager.getConnection(url,user,password);
13. conn.setAutoCommit(false);
14. String exectuteSql = "load data local infile ''into table bq_loan fields terminated by ','";
15. PreparedStatement pstmt = conn.prepareStatement(exectuteSql);
16. StringBuilder sb = new StringBuilder();
17. for
    (int i = 0; i < COUNT; i++) {
18. sb.append(getTestDataInputStream(bqLoanList.get(i)));
19. if (i % COMMIT_SIZE == 0) {
20. InputStream is = null;
21. try {
22. is = new ByteArrayInputStream(sb.toString().getBytes());
23. ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
24. pstmt.execute();
25. conn.commit();
26. sb.setLength(0);
27. } catch (UnsupportedEncodingException e) {
28. e.printStackTrace();
29. }
30. }
31. }
32. InputStream is = null;
33. try {
34. is = new ByteArrayInputStream(sb.toString().getBytes());
35. ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
36. pstmt.execute();
37. conn.commit();
38. } catch (UnsupportedEncodingException e) {
39. e.printStackTrace();
40. }
41. } catch (SQLException e) {
42. e.printStackTrace();
43. }finally{
44. conn.close();
45. }
46. }
47. }

上面代碼就是導入部分的一個方法，怎麼導中文都不顯示，還有些字段中文為亂碼，我想肯定是字符集的問題，首先查了下數據庫字符集，(查詢命令為：show variables like '%char%';)，然後看看代碼，查詢結果顯示數據庫字符集為utf8，然後百度發現這個導入代碼得加上編碼格式：

[java] view plain copy

1. "load data local infile ''into table bq_loan fields terminated by ','";

這個加上紅色部分編碼格式設置後如下，

"load data local infile ''into table bq_loan character set utf8 fields terminated by ','";

修改完後再次導入，還是一樣，中文不顯示，有些字段中文亂碼，這就頭疼了啊，仔細檢查，加上各種百度，才發現代碼裡自己還挖了個坑，

[java] view plain copy

1. is = new ByteArrayInputStream(sb.toString().getBytes());

這個將字節數組轉換為輸入流時，括號裡將字符串轉換成字節數組時，並沒有給定轉換後的字節數組的編碼格式，所以採用的就是默認的編碼格式，我們知道不同編碼格式，單箇中英文多對應的字節數是不一樣的。所以我猜測是這個地方沒有設置，導致生成的字節數組編碼格式和數據庫編碼格式不一致，最終導致導數據時中文不顯示以及亂碼。然後給getBytes（）方法加上編碼格式，代碼如下。

[java] view plain copy

1. is = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));

加上後再進行導入數據，一切順利，數據一點兒不差的導到庫裡。

到這裡導數就順利進行了，但是想到以後業務發展壯大時，設置的dubbo的服務端給消費端提供數據量最大值還會不夠用，所以就就決定改一下代碼，最後和同事討論，建議採取分批插入，就是調用service服務端時進行分頁處理，每頁數據量設置為dubbo允許服務端給消費端提供數據量最大值的範圍內，然後每次插入數據時，就會進行分批插入，只不過和數據庫交互次數相對多幾次而已，影響不大。

還有一個，使用"load data local infile"導數據時，我是直接將查詢出來的結果（list集合）進行數據的組合，即每條數據的每個字段間使用“，”隔開，每條數據之間使用“/n”換行隔開，最終將每條數據拼接成一個字符串，然後將字符串轉換成字節數組並轉換成輸入流，然後再執行導入操作，再往後就比較簡單了。由於我不是通過文件進行導數操作，所以 load data local infile '' into table bq_loan character set utf8 fields terminated by ','" 中紅色部分的文件名地址我就不寫。

最終的結果是：

導入1萬條數據，用時5.5秒左右

導入2.6萬數據, 用時17.8秒左右

......

導入35萬條數據，用時210秒左右

導入50萬條數據，用時305秒左右

我這個每條的數據量比較大，一條大概是1kb，所以，感覺速度還行吧，能實現我的需求。

好了，下面貼出部分代碼，供大家對照參考。

[java] view plain copy

1. public void insertLoanInfo (Map<string> msg) { /<string>
2. try {
3. long startTime = DateOperation.currentTimeMills();
4. List<assetpkgrel> loanList = (List<assetpkgrel>)msg.get("loanList"); /<assetpkgrel>/<assetpkgrel>
5. String pkgName = (String) msg.get("pkgName");
6. String pkgCde = (String) msg.get("pkgCde");
7. // 備份時間
8. String bkTime = DateOperation.convertToDateStr1(DateOperation.currentTimeMills());
9. msg.put("bkTime",bkTime);
10. if (IS_ONE_KEY_ASSOCIATED.getCode().equals(msg.get("isOneKeyAssociated"))) {
11. BqLoanService.deleteByPkgCde(pkgCde);
12. }
13. List<string> bkList = BqLoanService.selectNumByLoanNo(loanList); /<string>
14. // 總共的頁數
15. double totalPage = Math.ceil(bkList.size()/25000.0);
16. Map<string> map = new HashMap<string>(); /<string>/<string>
17. map.put("loanList",loanList);
18. List<bqloan> list = null; /<bqloan>
19. for (int i = 1;i <= totalPage;i++) {
20. List<bqloan> bqLoanList = new ArrayList<>(); /<bqloan>
21. map.put("page",i);
22. PageInfo<bqloan> pageInfo = BqLoanService.selectByLoanNo(map); /<bqloan>
23. list = pageInfo.getList();
24. for (int j= 0;j < list.size();j++) {
25. BqLoan BqLoan = list.get(j);
26. BqLoan.setPkgCde(pkgCde);
27. BqLoan.setPkgName(pkgName);
28. BqLoan.setArchTm(bkTime);
29. bqLoanList.add(BqLoan);
30. }
31. // 將當前頁數據插入數據庫
32. batchInsert(bqLoanList);
33. // 當前頁插入完之後清空list
34. bqLoanList.clear();
35. }
36. long endTime = DateOperation.currentTimeMills();
37. System.out.println("===============插入總時間："+(endTime-startTime));
38. } catch (BusinessException e) {
39. logger.error("插入數據異常 "+e.getMessage());
40. } catch (SQLException e) {
41. e.printStackTrace();
42. } catch (ClassNotFoundException e) {
43. e.printStackTrace();
44. }
45. }

[java] view plain copy

1. public void batchInsert(List<bqloan> bqLoanList) throws ClassNotFoundException, SQLException { /<bqloan>
2. //1000條一提交
3. int COMMIT_SIZE=1000;
4. //一共多少條
5. int COUNT=bqLoanList.size();
6. Connection conn= null;
7. try {
8. Class.forName("com.mysql.jdbc.Driver");
9. String url = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.url","CONF_HOME");
10. String user = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.username","CONF_HOME");
11. String password = GetResourceFromProperties.GetResourceFromPropertiesFromfiles("/jdbc.properties","jdbc.password","CONF_HOME");
12. conn= DriverManager.getConnection(url,user,password);
13. conn.setAutoCommit(false);
14. String exectuteSql = "load data local infile ''into table bq_loan character set utf8 fields terminated by ','";
15. PreparedStatement pstmt = conn.prepareStatement(exectuteSql);
16. StringBuilder sb = new StringBuilder();
17. for (int i = 0; i < COUNT; i++) {
18. sb.append(getTestDataInputStream(bqLoanList.get(i)));
19. if (i % COMMIT_SIZE == 0) {
20. InputStream is = null;
21. try {
22. is = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
23. ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
24. pstmt.execute();
25. conn.commit();
26. sb.setLength(0);
27. } catch
    (UnsupportedEncodingException e) {
28. e.printStackTrace();
29. }
30. }
31. }
32. InputStream is = null;
33. try {
34. is = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
35. ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
36. pstmt.execute();
37. conn.commit();
38. } catch (UnsupportedEncodingException e) {
39. e.printStackTrace();
40. }
41. } catch (SQLException e) {
42. e.printStackTrace();
43. }finally{
44. conn.close();
45. }
46. }
47. }

[java] view plain copy

1. /**
2. * 組裝需要插入的數據，字段間以","隔開，每條數據間以"/n"隔開
3. */
4. public static StringBuilder getTestDataInputStream(BqLoan BqLoan) {
5. StringBuilder builder = new StringBuilder();
6. builder.append(BqLoan.getSeq());
7. builder.append(",");
8. builder.append(BqLoan.getLoanNumber());
9. builder.append(",");
10. builder.append(BqLoan.gettPkgCde());
11. builder.append(",");
12. builder.append(BqLoan.getPkgName());
13. builder.append(",");
14. builder.append(BqLoan.getCustemerSeq());
15. builder.append(",");
16. builder.append(BqLoan.getCustemerName());
17. builder.append(",");
18. builder.append(BqLoan.getIdType());
19. builder.append(",");
20. builder.append(BqLoan.getIdNo());
21. builder.append(",");
22. builder.append(BqLoan.getPhoneNo());
23. builder.append(",");
24. builder.append("\\n");
25. return builder;
26. }

到此結束，如果有遇到這些問題，然後這篇文章還不能夠幫助到你，可以一起再探討，歡迎騷擾。