mysql資料庫LOAD DATA INFILE Syntax

標籤：

1.LOAD DATA INFILE用來把一個文字檔裡的內容高速寫入到MySQL表裡，它和SELECT ... INTO FILE的操作是對應的，一個匯入、一個匯出。使用LOAD DATA INFILE需要MySQL對要匯入的檔案有讀許可權，其文法如下：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name‘    [REPLACE | IGNORE]    INTO TABLE tbl_name    [PARTITION (partition_name,...)]    [CHARACTER SET charset_name]    [{FIELDS | COLUMNS}        [TERMINATED BY ‘string‘]        [[OPTIONALLY] ENCLOSED BY ‘char‘]        [ESCAPED BY ‘char‘]    ]    [LINES        [STARTING BY ‘string‘]        [TERMINATED BY ‘string‘]    ]    [IGNORE number {LINES | ROWS}]    [(col_name_or_user_var,...)]    [SET col_name = expr,...]

SELECT ... INTO OUTFILE ‘file_name‘    [CHARACTER SET charset_name]    [{FIELDS | COLUMNS}        [TERMINATED BY ‘string‘]        [[OPTIONALLY] ENCLOSED BY ‘char‘]        [ESCAPED BY ‘char‘]    ]    [LINES        [STARTING BY ‘string‘]        [TERMINATED BY ‘string‘]    ]FROM TABLE_NAME;

[LOW_PRIORITY | CONCURRENT]
LOW_PRIORITY： 當加上此選項時，執行LOAD DATA命令將會被延遲，直到沒有沒有任何其它用戶端讀取目標表。LOW_PRIORITY選項只適用於表級鎖的儲存引擎（MyISAM、MEMORY、MERGE等）。
CONCURRENT： 並行的意思，如果指定該選項，意味著在LOAD DATA過程中，別人也可以讀取表裡的資訊。如果使用CONCURRENT選項，不管有沒有人在LOAD DATA過程中讀取目標表，都是對LOAD DATA的效能造成一定的影響。對於基於行層級的Replication，CONCURRENT選項在MySQL 5.5.1以前不可用。

 

 

[LOCAL]
LOCAL選項決定LOAD DATA檔案的位置以及LOAD DATA過程中出錯後的處理方式。
如果參數--local-infile=0，那麼LOCAL參數不可用。
加上LOCAL選項後，在LOAD DATA的時候，用戶端讀取檔案，並發送給服務端，該檔案會臨時儲存在服務端的臨時檔案夾下（系統臨時檔案夾下，而非MySQL服務的臨時檔案夾，不是tmpdir、slave_load_tmpdir所指定的檔案夾）。
使用LOCAL選項，LOAD DATA的速度會稍微慢一點。
如果不加LOCAL選項，當發生資料錯誤或者主鍵重複、唯一索引重複的時候，LOAD DATA將會中斷，而加上LOCAL後，將不會中斷，關於加上LOCAL後遇到上述錯誤的處理方式，看下面的IGNORE選項中的解釋。

 

關於檔案的路徑問題：
1、如果指定了LOCAL選項，則被匯入的檔案可以在用戶端、也可以在服務端，如果沒有指定LOCAL，則被匯入的檔案必須在服務端；
2、我們盡量使用絕對路徑來標識被匯入的檔案。如果我們使用了相對路徑：
   a)在指定LOCAL選項的情況下，相對路徑指向用戶端所在的目錄；
   b)在沒有指定LOCAL的選項下，相對路徑指向資料檔案所在目錄或MySQL服務預設庫所在目錄；
   --> 如果該相對路徑是’./a.txt’，那麼指向資料檔案所在目錄；
   -->如果該相對路徑是’a.txt’，那麼指向MySQL服務預設庫所在目錄；

 

 

[REPLACE | IGNORE]
REPLACE： 在匯入處理程序中遇到重複的行、與主鍵或唯一索引重複的行，將被替換掉
IGNORE： 在匯入處理程序中遇到重複的行、與主鍵或唯一索引重複的行，會跳過並繼續匯入
在沒有指定REPLACE和IGNORE的情況下，當遇到上述情況時，處理方法取決於是否指定了LOCAL選項：
如果沒有指定LOCAL，則會中斷匯入操作；
如果指定了LOCAL，遇到上述情況時按照IGNORE參數處理。
如果想要忽略外鍵對LOAD DATA的影響，可以在匯入前設定參數：SET foreign_key_checks = 0

 

 

[{FIELDS | COLUMNS}
[TERMINATED BY ‘string‘]
[[OPTIONALLY] ENCLOSED BY ‘char‘]
[ESCAPED BY ‘char‘]
]

FIELDS 用於定義檔案的分割欄位
TERMINATED BY：定義欄位分隔符號
ENCLOSED BY：定義欄位括起字元，比如：”WYZC”，則ENCLOSED BY ‘”’
[OPTIONALLY]：有選擇行的，如果加上此選項，則數字類型不加引號，其它類型的加引號
ESCAPED BY：定義逸出字元

 

 

 

[LINES
[STARTING BY ‘string‘]
[TERMINATED BY ‘string‘]
]

LINES定義每行的分割欄位
STARTING BY：定義每行從哪個欄位開始讀取
TERMINATED BY：定義每行的終止符

 

 

如果沒有指定FIELDS或者LINES，則預設是：

FIELDS TERMINATED BY ‘\t‘ ENCLOSED BY ‘‘ ESCAPED BY ‘\\‘
LINES TERMINATED BY ‘\n‘ STARTING BY ‘‘

 

 

[IGNORE number {LINES | ROWS}]

IGNORE number LINES選項可被用來忽略在檔案開始的一個列名字的頭：

 

 

LOAD DATA INFILE和SELECT ... INTO OUTFILE的一些Tips：
對於LOAD DATA INFILE和SELECT ... INTO OUTFILE，參數FIELDS和LINES必須一致，如果同時指定這2個參數，那麼FIELDS必須在LINES之前。
對於一個MyISAM空表，對於表裡面的非唯一性索引，在匯入的時候會分批次建立，這樣會加快LOAD DATA的速度。
有時候我們可以在匯入前禁用一個表的索引，匯入完成後再ENABLE該表的索引以加快LOAD DATA的速度。
從MySQL 5.6.2開始，LOAD DATA支援分區表的匯入。

當你與LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE將一個資料庫的資料寫進一個檔案並且隨後馬上將檔案讀回資料庫時，兩個命令的欄位和處理選項必須匹配，否則，LOAD DATA INFILE將不能正確解釋檔案的內容。假定你使用SELECT ... INTO OUTFILE將由逗號分隔的欄位寫入一個檔案：

mysql資料庫LOAD DATA INFILE Syntax