如何解決Mysql大量數據導入遇到的問題

這篇文章主要介紹如何解決Mysql大量數據導入遇到的問題，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

在項目中，經常會碰到往數據庫中導入大量數據，以便利用sql進行數據分析。在導入數據的過程中會碰到一些需要解決的問題，這里結合導入一個大約4G的txt數據的實踐，把碰到的問題以及解決方法展現出來，一方面自己做個總結記錄，另一方面希望對那些碰到相同問題的朋友有個參考。

我導入的數據是百科的txt文件，文件大小有4G多，數據有6500萬余條，每條數據通過換行符分隔。每條數據包含三個字段，字段之間通過Tab分隔。將數據取出來的方法我采用的是用一個TripleData類來存放這三個字段，字段都用String，然后將多條數據存到List<TripleData>中，再將List<TripleData>存入mysql數據庫，分批將所有數據存到mysql數據庫中。

以上是一個大概的思路，下面是具體導入過程中碰到的問題。

1 數據庫連接的亂碼及兼容問題。

數據中如果有中文的話，一定要把鏈接數據庫的url設置編碼的參數，url設置為如下的形式。

URL="jdbc:mysql://"+IP+":"+PORT+"/"+DB_NAME+"?useSSL=false&useUnicode=true&characterEncoding=utf-8";

把編碼設置為UTF-8是解決亂碼問題，設置useSSL是解決JDBC與mysql的兼容問題。如果不設置useSSL，會報錯。類似于

Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

這樣的錯誤信息。主要是mysql版本比較高，JDBC版本比較低，需要兼容。

2 utf8mb4編碼問題

在導入數據的過程中，還會碰到類似于

SQLException :Incorrect string value: '\xF0\xA1\x8B\xBE\xE5\xA2...' for column 'name'

這樣的錯誤信息，這是由于mysql中設置的utf-8是默認3個字節的，對于一般的數據是沒有問題的，如果是大的數據量，里面難免會包含一些微信表情，或者特殊字符，它們占了4個字節，utf-8不能處理，所以報錯。解決的辦法就是mysql在5.5.3以后的版本引入了4個字節的utf-8編碼，也就是utf8mb4，需要對mysql的編碼重新設置。

可以按照以下步驟進行操作，一是對要修改的數據庫進行備份，雖然utf8mb4是向下兼容utf8的，但為了以防操作不當，還是需要防患于未然，做好備份工作。二是要修改數據庫的字符集編碼為utf8mb4—UTF-8 Unicode，排序規則utf8mb4_general_ci。以上修改我是使用navicat進行修改的，如何用命令行修改，大家可以自行查找。三是要修改配置文件my.ini，在mysql安裝的根目錄下。加入以下設置。

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[mysql]
default-character-set = utf8mb4

修改完成后，需要重新啟動mysql，使修改生效。

然后再進行數據的導入工作，應該就可以正常導入了。

3 大批量導入的時間效率問題

由于我們的數據量比較大，我們把數據進行了分割，我把6500萬條數據分為500個文件，每個文件大約11萬條數據，將這11萬條數據放到ArrayList<TripleObject>中，然后批量導入。大概的思路是采用“insert into tb (...) values(...),(...)...;”的方法，用insert一次性插入，這樣時間會節約很多時間。示例方法如下。

public static void insertSQL(String sql,List<TripleObject> tripleObjectList) throws SQLException{
    Connection conn=null;
    PreparedStatement psts=null;
    try {
      conn=DriverManager.getConnection(Common.URL, Common.DB_USERNAME, Common.DB_PASSWORD);
      conn.setAutoCommit(false); // 設置手動提交 
      // 保存sql后綴
      StringBuffer suffix = new StringBuffer();
      int count = 0; 
      psts=conn.prepareStatement("");
      String s="";
      String p="";
      String o="";
      while (count<tripleObjectList.size()) {
        s=tripleObjectList.get(count).getSubject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        p=tripleObjectList.get(count).getPredicate().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        o=tripleObjectList.get(count).getObject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        suffix.append("('" +s +"','"+p+"','"+ o+"'),");
        count++;
      }
      // 構建完整SQL
      String allsql = sql + suffix.substring(0, suffix.length() - 1);
      // 添加執行SQL
      psts.addBatch(allsql);
      psts.executeBatch(); // 執行批量處理 
      conn.commit(); // 提交 
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(psts!=null){
        psts.close();
      }
      if(conn!=null){
        conn.close();
      }
    }
  }

這種方法的優點是導入數據花費的時間會很少，6500萬條數據，用了正好1個小時。缺點是如果數據中有一大段的句子，需要對里面的逗號，括號，反斜線等進行處理，這里需要進行衡量，是否使用該方法。

如果正常插入，也就是使用“insert into tb (...) values(...);insert into tb (...) values(...);……”的形式，則不用處理特殊的符號，但花費的時間會很長，我測試了一下，11萬條數據大約需要12分鐘左右，導入6500萬條數據大概要100個小時。

我們采用的是第一種方法，數據大概可以查看就可以，對數據要求沒有那么嚴格，節約了時間。

以上是“如何解決Mysql大量數據導入遇到的問題”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！