【MySQL】load data語句詳解（一）

作者  羅小波
沃趣科技高階MySQL資料庫工程師

---

load data語句詳解

• 背景

  • 資料庫版本：MySQL 5.7.18
  • 伺服器資訊：本地到處匯入在10.10.30.241上演示，local遠端匯入在10.10.30.250上演示
  • 資料庫引數配置：
    * 雙一，secure_file_priv=''，log-bin，binlog_format=row，隔離級別RC，sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  • 參考資料：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

• 製造測試資料
  

1、語法解析

• 檢視語法幫助資訊
• 
  

• load data語句載入的資料來源可以是mysqldump匯出的純文字資料檔案，也可以是使用SELECT … INTO OUTFILE '/path/xx.txt';語句生成的單表純文字資料檔案，或者其他的方式生成的txt（只要生成的純文字資料列按指定分隔符分割的純文字資料檔案即可）

• 從上面的幫助資訊可以看到整個load data語句的語法結構，其中load data infile 'file.txt' into table tb_name; 是最基本的使用語句結構，其餘的都為可選子句

1.1. 必選子句或關鍵字

• load data語句簡單示例

• 如果文字檔案中的資料欄位與表結構中的欄位定義順序相同，則直接使用如下語句載入即可

• 如果文字檔案中的資料欄位與表結構中的欄位定義順序不同，則使用如下語句指定載入表中的欄位順序

1.2. 可選子句或關鍵字

• 以下演示部分只針對部分子句或關鍵字做演示，並不是全部，悉知

1.2.1. LOW_PRIORITY關鍵字

• 如果load data語句使用了LOW_PRIORITY關鍵字，則在碰到其他會話操作相同表時，則會延遲執行LOAD DATA語句，直到其他會話操作表結束為止。這僅影響使用表級鎖定的儲存引擎（如MyISAM，MEMORY和MERGE），對於innodb儲存引擎不起作用，因為innodb引擎是行級鎖，對於load data新插入的不同的資料行之間的操作不會發生衝突。本小節不做演示，更多資訊參考連結：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.2. LOCAL關鍵字 1.2.2.1. 使用與不使用local關鍵字的流程

• 如果要載入的文字檔案不在mysql server資料庫本身的本地磁碟，客戶端也不是從mysql server本機登入的，則需要使用local關鍵字，指定mysql server從client host本地載入該檔案，需要mysql server端使用local_infile=true(或者設定為1，不設定時預設為1)啟動，以及客戶端連線mysql server時也使用local_infile=true(或者設定為1，不指定時預設為1)連線才能使用，server和client必須都開啟這個引數才能使用local關鍵字，任意一個關閉都不能使用

• 使用local關鍵字與不使用local關鍵字時load data語句載入文字檔案的流程

  • 如果指定了LOCAL，則該客戶端程式在客戶端主機上讀取load data語句需要的檔案並將其傳送到伺服器。該檔案可以使用完整路徑名稱來指定其位置。也可以使用相對路徑，使用相對路徑時，路徑字首為使用客戶端程式時的工作目錄，當使用帶有LOAD DA他的LOCAL關鍵字時，會在mysql server端的臨時目錄中建立該檔案的副本(注意：這個場景下該檔案的路徑不是由tmpdir或slave_load_tmpdir的值指定的，而是作業系統的臨時目錄/tmp，並且在MySQL server中這個存放副本的路徑是不可配置的（通常，系統臨時目錄是Linux系統上的/tmp，Windows上的C:\WINDOWS\TEMP），要注意，如果在該臨時目錄下建立load檔案的副本時發現磁碟空間不夠，會導致LOAD DATA LOCAL語句執行失敗。在客戶端主機讀取文字檔案定位規則是如果指定的是絕對路徑，則使用絕對路徑，如果是相對路徑，則在登入mysql server時的工作路徑下查詢，找不到就報錯：ERROR 2 (HY000): File 'test3.txt' not found (Errcode: 2 - No such file or directory)
  • 如果未指定LOCAL，則該檔案必須位於mysql server端主機上，並由伺服器直接讀取。伺服器使用以下規則來定位檔案：
    * 如果檔名是絕對路徑名， mysql server將按照給定的路徑讀取檔案
    * 如果檔名是一個相對路徑名，則mysql server將在server端的datadir下搜尋該檔案，如果load data語句指定了庫名，則在datadir的指定庫名下搜尋文字檔案，如果沒有指定庫名，則在預設資料庫下搜尋文字檔案（load data語句沒有指定庫名時要正確執行語句必須先使用use db語句切庫，so，這個就是預設庫）
    * 如果datadir下還找不到就報錯：ERROR 13 (HY000): Can't get stat of '/datadir/xiaoboluo/test3.txt' (Errcode: 2 - No such file or directory)

• PS：

  • 非LOCAL方式只會載入位於mysql server上的文字檔案。出於安全考慮，此類操作要求您具有FILE特權。而且，非本地載入操作也受到secure_file_priv系統變數的設定。如果變數值是非空目錄名稱，則要載入的檔案必須位於該變數指定的目錄中。如果變數值為空（這是不安全的，這個時候server本地匯入由file許可權控制，client遠端主機的檔案匯入由server和client端的local_infile選項共同控制），則該檔案只能由伺服器讀取。
  • 使用LOCAL比讓伺服器直接訪問檔案要慢一些，因為檔案的內容必須通過客戶端的連線傳送到伺服器。另一方面，您不需要FILE許可權來載入本地檔案。可以使用local子句
  • 使用local關鍵字時碰到唯一鍵值衝突時的處理方式與IGNORE關鍵字相同，忽略衝突的行

1.2.2.2. 使用local關鍵字的錯誤處理

• 使用LOCAL會影響錯誤處理行為：
• 使用LOAD DATA INFILE，資料解析碰到重複鍵時預設情況下會終止操作

• 使用LOAD DATA LOCAL INFILE，資料解析碰到重複鍵時將發出警告，並且操作繼續進行，因為server端無法在客戶端操作期間停止傳輸檔案。此時處理重複鍵與指定IGNORE關鍵字相同（即忽略主鍵衝突的資料行）

1.2.3. REPLACE與IGNORE關鍵字

• REPLACE和IGNORE關鍵字控制對唯一鍵值衝突行的處理：

  • 如果指定了REPLACE關鍵字，則輸入行將覆蓋現有行。換句話說，與主鍵或唯一索引衝突的資料行將被執行覆蓋寫入，如果同時使用了local關鍵字，則與沒有使用local關鍵字行為相同
  • 如果指定了IGNORE關鍵字，則與唯一鍵值衝突的資料行將被丟棄，如果同時使用了local關鍵字，則與沒有使用local關鍵字行為相同
  • 如果不指定REPLACE和IGNORE任一選項，則行為取決於是否指定LOCAL關鍵字。沒有LOCAL，則發現主鍵或唯一索引衝突時就報錯終止load data語句執行，並忽略文字檔案的其餘部分的載入。如果使用了LOCAL關鍵字，則local關鍵字的預設行為與指定IGNORE時相同，這是因為server端無法在操作期間停止客戶端的檔案傳輸（不使用REPLACE與IGNORE關鍵字時的錯誤處理，詳見1.2.2.2小節）
• 下面對使用REPLACE與IGNORE關鍵字進行演示
• 如果文字檔案中的資料欄位有與表結構中的唯一索引、主鍵索引衝突的，則使用REPLACE關鍵字，該關鍵字會對衝突的資料進行覆蓋（內部轉換為UPDATE，見後續章節示例）

• 也可以使用IGNORE關鍵字忽略衝突的行（注意，這裡的ignore與ignore number lines子句中的ignore作用不同，不要搞混淆）

1.2.4. PARTITION子句

• LOAD DATA支援使用PARTITION選項顯式分割槽選擇，其中包含一個或多個分割槽，子分割槽或兩者名稱的逗號分隔列表。當使用此選項時，如果檔案中的任何行無法插入到列表中指定的任何分割槽或子分割槽中，則該語句將失敗，並顯示錯誤，找到與給定分割槽集不匹配的行，本小節不做演示，更多資訊參考連結：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.5. CHARACTER SET charset_name子句

• 匯入的文字資料檔名必須以文字字串形式給出。在Windows上，在路徑名稱中指定反斜槓為正斜槓或雙倍反斜槓。 character_set_filesystem系統變數控制檔名的解釋

• 伺服器使用由character_set_database系統變數指定的字符集來解釋檔案中的內容。SET NAMES語句和character_set_client系統變數的設定不影響檔案內容的字符集解析。如果輸入檔案的內容使用的字符集與server的預設值字符集不同，則建議先使用load data的CHARACTER SET子句指定解析文字檔案內容的字符集

• LOAD DATA INFILE將檔案中的所有欄位以相同的字符集進行解析，而不管載入欄位列的資料型別定義的字符集如何。為了正確解釋檔案內容，您必須確保使用正確的字符集進行匯出資料和匯入資料。例如，如果您使用mysqldump -T或通過在mysql中執行SELECT … INTO OUTFILE語句匯出資料檔案時，建議使用–default-character-set選項指定一個對應資料的字符集，以便使用Load data語句匯入資料時不會發生字符集錯亂（使用mysqlimport的–default-character-set指定匯出資料時的字符集，使用mysql命令列客戶端的–default-character-set指定匯出資料時的字符集，注：不能指定ucs2,utf16,utf16le或utf32字符集來載入資料檔案）
• 本小節不做演示，更多資訊參考連結：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2139664/，如需轉載，請註明出處，否則將追究法律責任。