6.4.9 LOAD DATA INFILE 句法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
LOAD DATA INFILE 語句以非常高的速度從一個文本文件中讀取記錄行並插入到一個表中。如果 LOCAL 關鍵詞被指定,文件從客戶端主機讀取。如果 LOCAL 沒有被指定,文件必須位於服務器上。(LOCAL 在 MySQL 3.22.6 或更新的版本中被支持。)
由於安全性的原因,當讀取位於服務器端的文本文件時,文件必須處於數據庫目錄或可被所有人讀取的地方。同時,為了對服務器端的文件使用 LOAD DATA INFILE,你必須在服務器主機上有 FILE 權限。查看章節 4.2.7 由 MySQL 提供的權限。
在 MySQL 3.23.49 和 MySQL 4.0.2 中,只有當你沒有以 --local-infile=0 選項啟動 mysqld,或你沒有禁止你的客戶端程序支持 LOCAL的情況下,LOCAL 才會工作。查看章節 4.2.4 LOAD DATA LOCAL 的安全性問題.
如果你指定關鍵詞 LOW_PRIORITY,LOAD DATA 語句的執行將會被延遲,直到沒有其它的客戶端正在讀取表。
如果你對一個 MyISAM 表指定關鍵詞 CONCURRENT,那麼當 LOAD DATA正在執行時,其它的線程仍可以從表中檢索數據。使用這個選項時,如果同時也有其它的線程正在使用表,這當然會有一點影響 LOAD DATA 的執行性能。
使用 LOCAL 將比讓服務器直接訪問文件要慢一些,因為文件的內容必須從客戶端主機傳送到服務器主機。而在另一方面,你不再需要有 FILE 權限用於裝載本地文件。
如果你使用先於 MySQL 3.23.24 的版本,你不能夠以 LOAD DATA INFILE 讀取一個 FIFO 。如果你需要從一個 FIFO (例如,gunzip 的輸出文件) 中讀取,可以使用 LOAD DATA LOCAL INFILE 代替。
你也可以使用 mysqlimport 實用程序裝載數據文件;它通過發送一個 LOAD DATA INFILE 命令到服務器來動作。--local 選項使得 mysqlimport 從客戶端主機讀取數據文件。如果客戶端與服務器支持壓縮協議,你可以指定 --compress 選項,以在較慢的網絡中獲得更好的性能。
當從服務器主機定位文件時,服務器使用下列規則:
* 如果給定一個完整的路徑,服務器使用該路徑名。
* 如果給定一個有一個或多個前置構件的相對路徑,服務器以相對服務器的數據目錄搜索文件。
* 如果給定一個沒有前置構件的文件名,服務器從當前數據庫的數據庫目錄搜尋文件。
注意,這些規則意味著,一個以 `./myfile.txt' 給出的文件是從服務器的數據目錄中讀取的,然而,以 `myfile.txt' 給出的一個文件是從當前數據庫的數據目錄下讀取的。舉例來說,下面的 LOAD DATA 語句從 db1 數據庫目錄下讀取文件 `data.txt',因為 db1 是當前數據庫,即使該語句明確地指定讀取的文件被放入到 db2 數據庫中的一個表中:
mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
REPLACE 和 IGNORE 關鍵詞控制對與現有的記錄在唯一鍵值上重複的記錄的處理。如果你指定 REPLACE,新的記錄行將替換有相同唯一鍵值的現有記錄行。如果你指定 IGNORE,將跳過與現有的記錄行在唯一鍵值上重複的輸入記錄行。如果你沒有指定任何一個選項,當重複鍵值出現時,將會發生一個錯誤,文本文件的剩餘部分也將被忽略。
如果你使用 LOCAL 關鍵詞從一個本地文件中讀取數據,在此操作過程中,服務器沒有辦法停止文件的傳送,因此缺省的處理方式就好像是 IGNORE 被指定一樣。
如果你在一個空的 MyISAM 表上使用 LOAD DATA INFILE,所有非唯一索引會以一個分批方式被創建(就像 REPAIR)。當有許多索引時,這通常可以使 LOAD DATA INFILE 更快一些。
LOAD DATA INFILE 的 SELECT ... INTO OUTFILE 的逆操作。查看章節 6.4.1 SELECT 句法。使用 SELECT ... INTO OUTFILE 將數據從一個數據庫寫到一個文件中。使用 LOAD DATA INFILE 讀取文件到數據庫中。兩個命令的 FIELDS 和 LINES 子句的句法是一樣的。兩個子句都是可選的,但是如果兩個同時被指定,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 表現如下:
* 在換行符處尋找行的邊界。
* 在定位符處將行分開放到字段中。
* 不認為字段由任何引號字符封裝。
* 將有 「\」 開頭的定位符、換行符或 `\' 解釋為字段值的一個文字字符。
相反的,當寫入輸出時,缺省值導致 SELECT ... INTO OUTFILE 表現如下:
* 在字段值間加上定位符。
* 不用任何引號字符封裝字段。
* 使用 「\」 轉義出現在字段值中的定位符、換行符或 `\' 字符實例。
* 在行的結尾處加上換行符。
注意,為了寫 FIELDS ESCAPED BY '\\',你必須指定兩個反斜線,該值會作為一個反斜線被讀入。
IGNORE number LINES 選項可被用於忽略文件開頭處的一個列名的頭:
mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;
當你一前一後地使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE 將數據從一個數據庫寫到一個文件中,然後再從文件中將它讀入數據庫中時,兩個命令的字段和行處理選項必須匹配。否則,LOAD DATA INFILE 將不能正確地解釋文件內容。假設你使用 SELECT ... INTO OUTFILE 以逗號分隔字段的方式將數據寫入到一個文件中:
mysql> SELECT * INTO OUTFILE 'data.txt'
-> FIELDS TERMINATED BY ','
-> FROM ...;
為了將由逗號分隔的文件讀回時,正確的語句應該是:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
-> FIELDS TERMINATED BY ',';
如果你試圖用下面所示的語句讀取文件,它將不會工作,因為命令 LOAD DATA INFILE 以定位符區分字段值:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
-> FIELDS TERMINATED BY '\t';
可能的結果是每個輸入行將被解釋為一個單獨的字段。
LOAD DATA INFILE 也可以被用來讀取從外部來源獲得的文件。例如,dBASE 格式的文件,字段以逗號分隔並以雙引號包圍著。如果文件中的行以一個換行符終止,那麼下面所示的可以說明你將用來裝載文件的字段和行處理選項:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
任何字段和行處理選項都可以指定一個空字符串('')。如果不是空的,FIELDS [OPTIONALLY] ENCLOSED BY 和 FIELDS ESCAPED BY 值必須是一個單個字符。FIELDS TERMINATED BY 和 LINES TERMINATED BY 值可以超過一個字符。例如,為了寫入由回車換行符終止的行,或讀取包含這樣的行的文件,應該指定一個 LINES TERMINATED BY '\r\n' 子句。
舉例來說,為了讀取一個文件到一個 SQL 表中,文件以一行 %% 分隔(開玩笑的),你可以這樣做:
CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT
NOT NULL);
LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (joke);
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