mysql LOAD語句批量錄入資料

幸運的是，MySQL提供了一些方法用於批量錄入資料，使得向表中添加資料變得容易了。本節以及下一節，將介紹這些方法。本節將介紹SQL語言級的解決方案。

1、基本文法

文法：LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name LOAD DATA INFILE語句從一個文字檔中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀檔案。如果LOCAL沒指定，檔案必須位於伺服器上。(LOCAL在MySQL3.22.6或以後版本中可用。)

為了安全原因，當讀取位於伺服器上的文字檔時，檔案必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上檔案使用LOAD DATA INFILE，在伺服器主機上你必須有file的許可權。見第七章 資料庫安全。

REPLACE和IGNORE關鍵詞控制對現有的唯一鍵記錄的重複的處理。如果你指定REPLACE，新行將代替有相同的唯一索引值的現有行。如果你指定IGNORE，跳過有唯一鍵的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複鍵鍵時，出現一個錯誤，並且文字檔的餘下部分被忽略時。

如果你使用LOCAL關鍵詞從一個本地檔案裝載資料，伺服器沒有辦法在操作的當中停止檔案的傳輸，因此預設的行為好像IGNORE被指定一樣。

2、檔案的搜尋原則

當在伺服器主機上尋找檔案時，伺服器使用下列規則：

如果給出一個絕對路徑名，伺服器使用該路徑名。

如果給出一個有一個或多個前置組件的相對路徑名，伺服器相對伺服器的資料目錄搜尋檔案。

如果給出一個沒有前置組件的一個檔案名稱，伺服器在當前資料庫的資料庫目錄尋找檔案。

注意這些規則意味著一個像“./myfile.txt”給出的檔案是從伺服器的資料目錄讀取，而作為“myfile.txt”給出的一個檔案是從當前資料庫的資料庫目錄下讀取。也要注意，對於下列哪些語句，對db1檔案從資料庫目錄讀取，而不是db2：

mysql> USE db1;mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;

3、FIELDS和LINES子句的文法

如果你指定一個FIELDS子句，它的每一個子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可選的，除了你必須至少指定他們之一。

如果你不指定一個FIELDS子句，預設值與如果你這樣寫的相同：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果你不指定一個LINES子句，預設值與如果你這樣寫的相同：

LINES TERMINATED BY '\n'

換句話說，預設值導致讀取輸入時，LOAD DATA INFILE表現如下：

在分行符號處尋找行邊界

在定位器處將行分進欄位

不要期望欄位由任何引號字元封裝

將由“\”開頭的定位器、分行符號或“\”解釋是欄位值的部分字面字元

LOAD DATA INFILE能被用來讀取從外部來源獲得的檔案。例如，以dBASE格式的檔案將有由逗號分隔並用雙引號包圍的欄位。如果檔案中的行由分行符號終止，下面顯示的命令說明你將用來裝載檔案的欄位和行處理選項：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_nameFIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n';

任何欄位或行處理選項可以指定一個Null 字元串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必須是一個單個字元。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超過一個字元。例如，寫入由斷行符號分行符號對（CR+LF）終止的行，或讀取包含這樣行的一個檔案，指定一個LINES TERMINATED BY '\r\n'子句。

FIELDS [OPTIONALLY] ENCLOSED BY控制欄位的包圍字元。對於輸出(SELECT ... INTO OUTFILE)，如果你省略OPTIONALLY，所有的欄位由ENCLOSED BY字元包圍。對於這樣的輸出的一個例子(使用一個逗號作為欄位分隔符號)顯示在下面：

"1","a string","100.20"

"2","a string containing a , comma","102.20"

"3","a string containing a \" quote","102.20"

"4","a string containing a \", quote and comma","102.20"

如果你指定OPTIONALLY，ENCLOSED BY字元僅被用於包圍CHAR和VARCHAR欄位：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a \" quote",102.20

4,"a string containing a \", quote and comma",102.20

注意，一個欄位值中的ENCLOSED BY字元的出現通過用ESCAPED BY字元作為其首碼來轉義。也要注意，如果你指定一個空ESCAPED BY值，可能產生不能被LOAD DATA INFILE正確讀出的輸出。例如，如果逸出字元為空白，上面顯示的輸出顯示如下。注意到在第四行的第二個欄位包含跟隨引號的一個逗號，它(錯誤地)好象要終止欄位：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a " quote",102.20

4,"a string containing a ", quote and comma",102.20

FIELDS ESCAPED BY控制如何寫入或讀出特殊字元。如果FIELDS ESCAPED BY字元不是空的，它被用於首碼在輸出上的下列字元：

FIELDS ESCAPED BY字元

FIELDS [OPTIONALLY] ENCLOSED BY字元

FIELDS TERMINATED BY和LINES TERMINATED BY值的第一個字元

ASCII 0（實際上將後續逸出字元寫成 ASCII'0'，而不是一個零值位元組）

如果FIELDS ESCAPED BY字元是空的，沒有字元被轉義。指定一個空逸出字元可能不是一個好主意，特別是如果在你資料中的欄位值包含剛才給出的表中的任何字元。

對於輸入，如果FIELDS ESCAPED BY字元不是空的，該字元的出現被剝去並且後續字元在字面上作為欄位值的一個部分。例外是一個轉義的“0”或“N”（即，\0或\N，如果逸出字元是“\”)。這些序列被解釋為ASCII 0（一個零值位元組）和NULL。見下面關於NULL處理的規則。

總結

為資料庫裝載資料是管理員的重要職責之一，正因為重要，所以MySQL提供的方法也是非常繁多。其中主要的在本節已經列舉：

1、使用INSERT、REPLACE語句

2、使用INSERT/REPLACE…SELECT語句

3、使用LOAD DATA INFILE語句

4、使用公用程式mysqlimport