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文件配置如下

<dubbo:provider id="payload" payload=" 41943040"/>

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

3、碰到的第三個問題：

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

[java] view plain copy

1. public

   void

   batchInsert(List<BqLoan> bqLoanList)

   throws

   ClassNotFoundException, SQLException {
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,Object> msg) {

2. try

   {

3. long

   startTime = DateOperation.currentTimeMills();
4. List<AssetPkgRel> loanList = (List<AssetPkgRel>)msg.get("loanList");
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);
14. // 總共的頁數

15. double

    totalPage = Math.ceil(bkList.size()/25000.0);
16. Map<String,Object> map =

    new

    HashMap<String,Object>();
17. map.put("loanList",loanList);
18. List<BqLoan> list =

    null

    ;

19. for

    (

    int

    i = 1;i <= totalPage;i++) {
20. List<BqLoan> bqLoanList =

    new

    ArrayList<>();
21. map.put("page",i);
22. PageInfo<BqLoan> pageInfo = BqLoanService.selectByLoanNo(map);
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 {
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("
    ");

25. return

    builder;
26. }

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！