MySQL load data載入資料

load data 載入資料
load data infile語法如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, 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
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]

load data infile語句從文字檔案中讀取資料載入到資料表中有非常高的效率。為了將資料從表中寫入文字檔案可以使用select ... into outfile，為了將資料從文字檔案載入回資料表，使用load data infile語句。對於這兩種語句fields與lines子句是相同的。這兩個子句是可選的，如果兩個子句被指定那麼fields必須在lines的前面。

也可以使用mysqlimport工具來載入資料檔案，它是透過傳送oad data infile語句給伺服器來進行操作。mysqlimport的--local選項會導致mysqlimport從客戶端主機上讀取資料檔案。如果客戶端和伺服器支援壓縮協議使用--compress選項將得到更好的效能。

檔名必須以字面值字串的形式給出。在Windows上，在路徑名中指定反斜槓為正斜槓或雙反斜槓。character_set_filesystem系統變數控制檔名的解釋。

LOAD DATA支援使用帶有一個或多個以逗號分隔的分割槽、子分割槽或是同時指定分割槽與子分割槽的名稱列表的partition選項進行顯式分割槽選擇。使用此選項時，如果檔案中的任何行不能插入到列表中指定的任何分割槽或子分割槽中，則語句會失敗，錯誤為Found a row not matching the給定的分割槽集。

對於使用MyISAM等使用表鎖的儲存引擎的分割槽表，LOAD DATA不能刪除任何分割槽鎖。這不適用於使用行級鎖的儲存引擎的表，比如InnoDB

伺服器使用由character_set_database系統變數指示的字符集來解釋檔案中的資訊。SET NAMES和character_set_client的設定不會影響輸入的解釋。如果輸入檔案的內容使用與預設不同的字符集，通常最好使用character set子句指定檔案的字符集。二進位制字符集指定不轉換。

LOAD DATA INFILE將檔案中的所有欄位解釋為具有相同的字符集，而不考慮欄位值載入到的列的資料型別。為了正確解釋檔案內容，您必須確保它是用正確的字符集編寫的。例如，如果你寫一個資料檔案使用mysqldump -T或者在mysql中發出SELECT…INTO OUTFILE語句，一定要使用--default-character-set選項輸出時所使用的字符集在當檔案被LOAD DATA INFILE載入時將要使用。

如果使用LOW_PRIORITY, LOAD DATA語句的執行將被延遲，直到沒有其他客戶端從表中讀取資料。這隻會影響僅使用表級鎖的儲存引擎(如MyISAM、memory和merge)。

如果MyISAM表指定CONCURRENT來滿足併發插入條件的(即，它中間不包含空閒塊)，那麼在執行LOAD DATA時，其他執行緒可以從表中檢索資料。即使沒有其他執行緒同時使用這個表，這個選項也會略微影響LOAD DA他的效能。

使用基於行的複製，無論MySQL版本如何，都可以併發複製。使用基於語句的複製，在MySQL 5.5.1之前不會執行併發複製(見Bug #34628)。

LOCAL關鍵字影響檔案的預期位置和錯誤處理，如後面所述。只有當伺服器和客戶機都配置為允許LOCAL時，LOCAL才能工作。例如，如果mysqld在啟動時禁用了local_infile系統變數，那麼LOCAL就不起作用

local關鍵字的影響期望在檔案中的何處被找到:
.如果local關鍵字被指定，透過客戶端主機上的客戶端程式來讀取檔案併傳送給伺服器。這個檔案可以被指定完全路徑名來指定精確位置。如果指定相對路徑名，這將會解析成啟動客戶端程式目錄的相對路徑。

當使用load data local時，在伺服器的臨時目錄中會建立一個檔案副本。這不是由tmpdir或slave_load_tmpdir的值決定的目錄，而是作業系統的臨時目錄，並且在MySQL伺服器中不能配置（通常系統臨時目錄在linux系統中是/tmp，在windows中是c:\windows\temp)。如果臨時目錄沒有足夠的空間可能會造成load data local語句失敗。

.如果local關鍵字沒有指定，那麼資料檔案必須儲存在伺服器上並且伺服器可以直接讀取。伺服器使用以下規則來定位檔案:

.如果檔名是絕對路徑名，伺服器將按照給定的方式使用它。

.如果檔名是包含一個或多個前導元件的相對路徑名，伺服器將相對於伺服器的資料目錄搜尋該檔案。

.如果檔名中沒有給出前導元件，伺服器將在預設資料庫的資料庫目錄中查詢該檔案。

在非local情況下，這些規則意味著從伺服器的資料目錄中讀取名為./myfile.txt的檔案，而從預設資料庫的資料庫目錄中讀取名為myfile.txt的檔案。例如，如果db1是預設資料庫，那麼下面的LOAD DATA語句將從db1的資料庫目錄中讀取檔案data.txt，即使該語句顯式地將檔案載入到db2資料庫中的一個表中:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

非local載入操作讀取位於伺服器上的文字檔案。出於安全原因，這些操作要求您擁有FILE特權。非local載入操作受secure_file_priv系統變數設定的影響。如果變數值是非空目錄名，則要載入的檔案必須位於該目錄中。如果變數值為空(不安全)，則該檔案只需要由伺服器讀取。

使用LOCAL比讓伺服器直接訪問檔案要慢一些，因為檔案的內容必須透過客戶機連線傳送到伺服器。另一方面，您不需要FILE特權來載入本地檔案。

local也會影響錯誤的處理:
.使用load data infile，資料解釋和重複鍵錯誤終止操作。
.使用load data local infile 資料解釋和重複鍵錯誤成為警告，操作繼續進行，因為伺服器沒有辦法在操作過程中停止檔案傳輸。對於重複鍵錯誤，這就相當於指定了IGNORE。

REPLACE和IGNORE關鍵字控制對在唯一鍵值上存在與現有行有重複的輸入行的處理：
.如果指定replace，輸入行將替換存在行。換句話說，與現有行的主鍵或唯一索引有相同的值。

.如果指定ignore，與現有行的唯一鍵值重複的行會被丟棄。

.如果沒有指定這些選項，那麼依據是否指定了local來決定。在沒有指定local時，當找到重複鍵值時會報錯且檔案中剩餘的內容會被忽略。當指定local時，預設的行為與指定了ignore是一樣的，這是因為伺服器不能阻止正在執行的檔案傳輸操作。

為了在載入操作時忽略外來鍵約束，可以在執行load data之前執行set foreign_key_checks=0語句。

如果對一個MyISAM空表執行load data infile操作，那麼所有非唯一索引將在單獨的一個批處理中建立（類似於repair table)。通常來說，當有許多索引的情況下這將使load data infile操作更快。在一些極端的情況下，在載入資料檔案之前可以執行alter table ... disable keys來禁用索引，在載入資料檔案之後執行alter table .. enable kyes來重建索引這要比建立索引效率更高。

對於load data infile和select ... into outfile語句來說fiels和lines子句的語法是相同的。這些子句是可選項，但如果都被指定的話fields必須在lines的前面。

如果指定fields子句，它的每一個子句(terminated by,[optionally] enclosed by和escaped by)也是可選項，除非你必須至少指定一個。

如果沒有指定fields或lines子句，預設就和下面所寫的一樣:

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

反斜槓是SQL語句中字串中的MySQL跳脫字元，因此要指定一個字面值的反斜槓，您必須指定兩個反斜槓才能將值解釋為單個反斜槓。轉義序列'\t'和'\n'分別指定製表符和換行符

換句話說，在讀取輸入時，預設值導致LOAD DATA INFILE執行如下操作:
.在換行處檢視行邊界。
.不會跳過任何行字首
.在製表符中將行拆分為欄位
.不要期望欄位用引號括起來
.將前面有跳脫字元\的字元解釋為轉義序列。例如，“\t”、“\n”、“\\”分別表示製表符、換行符、反斜槓

相反，預設值會導致select ... into outfile執行如下操作
.在欄位之間寫製表符
.要用引號括起欄位
.使用\轉義欄位值中出現的製表符、換行符或\例項
.在行尾寫換行

如果您已經在Windows系統上生成了文字檔案，您可能必須使用LINES TERMINATED BY '\r\n'來正確讀取該檔案，因為Windows程式通常使用兩個字元作為行結束符。有些程式，如寫字板，在寫檔案時可能使用\r作為行結束符。要讀取這樣的檔案，請使用以'\r'結尾的行。

如果您想要讀入的所有行都有一個您想要忽略的公共字首，您可以使用LINES STARTING BY 'prefix_string'來跳過該字首和它之前的任何內容。如果某一行不包含字首，則跳過整行。假設您發出以下語句:

load data infile '/tmp/test.txt' into table test fields terminated by ',' lines starting by 'xxx';

如果資料檔案內容類似如下:

xxx"abc",1
something xxx"def",2
"ghi",3

上面資料檔案執行載入後行數將是("abc",1)和("def",2)。第三行將會被跳過因為它不包含字首。

ignore number lines選項可以用來忽略檔案開頭的行。例如，可以使用ignore 1 lines來跳過包含列名的首標題行:

load data infile '/tmp/test.txt' into table test ignore 1 lines;

當你使用SELECT…INTO OUTFILE與LOAD DATA INFILE一起將資料從資料庫寫入檔案，然後稍後將檔案讀回資料庫，這兩個語句的欄位和行處理選項必須匹配。否則，LOAD DATA INFILE將不能正確解釋檔案的內容。假設你使用SELECT…寫入一個以逗號分隔的欄位的檔案:

select * into outfile 'data.txt' fields terminated by ',' from table2;

要將逗號分隔的檔案讀入，正確的語句應該是:

load data infile 'data.txt' into table table2 fields terminated by ',';

如果您嘗試使用下面所示的語句讀取檔案，則不會工作，因為它指示LOAD DATA INFILE查詢欄位之間的製表符:

load data infile 'data.txt' into table table2 fields terminated by '\t';

可能的結果是，每個輸入行將被解釋為單個欄位。

LOAD DATA INFILE可用於讀取從外部來源獲得的檔案。例如，許多程式可以以逗號分隔值(CSV)格式匯出資料，例如，行中有用逗號分隔的欄位，並用雙引號括起來，並使用列名的首行。如果這樣一個檔案中的行以回車/換行對結束，這裡顯示的語句說明了用於載入檔案的欄位和行處理選項。

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

如果輸入值不一定用引號括起來，請在enclosed BY關鍵字前使用optionally。

任何欄位處理或行處理選項都可以指定一個空字串(")。如果不為空，則FIELDS[OPTIONALLY] ENCLOSEDBY和FIELDS ESCAPED BY值必須是單個字元。FIELDS TERMINATED BY、LINES started BY和LINES TERMINATED BY值可以多於一個字元。例如，要寫以回車/換行對結尾的行，或讀取包含此類行的檔案，請指定lines terminated by '\r\n'子句。

要讀取包含由%%作為行分隔符的的檔案，您可以這樣做

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);
< ?pre>
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,binary,text或enum)：

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

對於輸入，如果存在enclosed by 字元，則從欄位值的末尾刪除。(無論是否指定了optionally，這都是正確的;optionally對輸入解釋沒有影響。)出現在enclosed by字元前的escaped by字元將被解釋為當前欄位值的一部分。

如果欄位以enclosed by字元開頭，則該字元的例項只有在後跟欄位或行terminated by序列時才會被識別為終止欄位值。為了避免歧義，在欄位值中出現enclosed by字元的次數可以加倍，並被解釋為該字元的單個例項。例如，如果指定了enclosed by '"'則會像這裡所示處理引號:

"The ""BIG"" boss" -> The "BIG" boss
The "BIG" boss -> The "BIG" boss
The ""BIG"" boss -> The ""BIG"" boss

fields escaped by控制如何讀取或寫入特殊字元
.對於輸入，如果fields escaped by字元不為空，則剝離出現的該字元，並按照字面意思將下面的字元作為欄位值的一部分。一些雙字元序列是例外，其中第一個字元是跳脫字元。這些序列如下表所示(跳脫字元使用\)。本節稍後將描述NULL處理規則。

如果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字元為空，則不轉義任何字元，NULL輸出為NULL，而不是\N。指定空跳脫字元可能不是一個好主意，特別是如果資料中的欄位值包含剛才給出的列表中的任何字元。

在某些情況下，欄位和行處理選項相互作用:
.如果LINES TERMINATED BY為空字串而FIELDS TERMINATED BY為非空字串，則行也會以FIELDS TERMINATED BY結束。

.如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都為空(")，則使用固定行(非分隔)格式。使用固定行格式，欄位之間不使用分隔符(但仍然可以使用行結束符)。相反，讀寫列值時使用足夠寬的欄位寬度來容納欄位中的所有值。對於TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，無論宣告的顯示寬度是多少，欄位寬度分別為4、6、8、11和20。

lines terminated by仍然用於分隔行。如果一行不包含所有欄位，則將其餘列設定為其預設值。如果你沒有行結束符，你應該把它設定為''。在這種情況下，文字檔案必須包含每一行的所有欄位。

固定行格式也影響NULL值的處理，如後面所述

NULL值的處理根據所使用的FIELDS和LINES選項而不同:
.對於預設的FIELDS和LINES值，NULL被寫為輸出的\N欄位值，而\N欄位值被讀為輸入的NULL(假設escaped by字元是\)。
.如果FIELDS ENCLOSED BY不為空，則將包含字面值NULL的欄位讀取為NULL值。這不同於在fields enclosed by字元中包含的單詞NULL，它被讀為字串'NULL'。
.如果fields escaped by為空，則NULL被寫成單詞NULL。
.使用固定行格式(當FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空時使用)，NULL被寫成空字串。這將導致表中的NULL值和空字串在寫入檔案時無法區分，因為兩者都是作為空字串寫入的。如果需要在重新讀入檔案時區分這兩者，那麼不應該使用固定行格式。

嘗試將NULL載入到NOT NULL列會導致為列的資料型別分配隱式預設值，並出現警告，或者在嚴格的SQL模式下出現錯誤。

不被load data infile所支援的情況:
.固定大小的行(fields terminated by和fields enclosed by都是空的)和BLOB或TEXT列
.如果指定一個分隔符與另一個分隔符或字首相同，則LOAD DATA INFILE無法正確解釋輸入。例如，下面的FIELDS子句會導致問題:FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果fields escaped by為空，則包含FIELDS ENCLOSED BY或LINES TERMINATED BY後跟FIELDS TERMINATED BY的欄位值將導致LOAD DATA INFILE過早停止讀取欄位或行。這是因為LOAD DATA INFILE不能正確地確定欄位或行值的結束位置。

下面的示例載入persondata表的所有列:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

預設情況下，當LOAD DATA INFILE語句末尾沒有提供欄位列表時，輸入行將包含每個表的所有欄位。如果只想載入表的部分列，請指定欄位列表:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

如果輸入檔案中欄位的順序與表中列的順序不同，還必須指定欄位列表。否則，MySQL無法判斷如何將輸入欄位與表列匹配。

列列表可以包含列名或使用者變數。對於使用者變數，SET子句允許您在將結果分配給列之前對其值執行轉換.

SET子句中的使用者變數有幾種用法。下面的示例將第一個輸入列直接用於t1.column1的值，並將第二個輸入列賦值給一個使用者變數，該變數在用於t1.column2的值之前進行除法操作

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;

SET子句可用於提供非從輸入檔案派生的值。下面的語句將column3設定為當前日期和時間

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = CURRENT_TIMESTAMP;

可以透過指定使用者變數且不將變數指定到表列來放棄輸入值:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);

列/變數列表和SET子句的使用受以下限制:
.SET子句中的賦值運算子應該只有列名在賦值運算子的左邊。
.你可以在SET賦值的右邊使用子查詢。返回要分配給列的值的子查詢只能是標量子查詢。此外，也不能使用子查詢從正在載入的表中進行選擇
.對於列/變數列表或SET子句，不處理被IGNORE子句忽略的行
.當以固定行格式載入資料時，不能使用使用者變數，因為使用者變數沒有顯示寬度

在處理輸入行時，LOAD DATA將其分割為欄位，並根據列/變數列表和SET子句(如果存在的話)使用這些值。然後將生成的行插入到表中。如果表中有BEFORE INSERT或AFTER INSERT觸發器，則分別在插入行之前或之後啟用它們。

如果一個輸入行有太多的欄位，額外的欄位將被忽略，警告的數量將增加

如果輸入行欄位太少，則將缺少輸入欄位的表列設定為預設值。

對空欄位值的解釋與缺失欄位不同:
.對於字串型別，列被設定為空字串
.對於數字型別，列被設定為0
.對於日期和時間型別，該列被設定為該型別的適當零值

如果在INSERT或UPDATE語句中顯式地將空字串賦給字串、數字型別或日期或時間型別，則會得到相同的值

如果SQL模式被設定為限制值，那麼空欄位值或不正確欄位值的處理與剛才描述的不同。例如，如果sql_mode設定為traditional，對於數字列轉換空值或值(如'x')將導致錯誤，而不是轉換為0。(對於LOCAL或IGNORE，即使使用限制性sql_mode值，也會出現警告而不是錯誤，並且使用與非限制性SQL模式相同的最接近值行為插入行。這是因為伺服器沒有辦法在操作過程中停止檔案的傳輸。)

只有TIMESTAMP列列值為NULL和列沒有宣告為允許NULL值或者如果TIMESTAMP列的預設值是當前的時間戳，並且在指定欄位列表時從欄位列表中省略它時，TIMESTAMP列會被設定為當前日期和時間。

LOAD DATA INFILE將所有輸入視為字串，所以不能像INSERT語句那樣對ENUM或SET列使用數值。所有的ENUM和SET值必須指定為字串。BIT值不能使用二進位制表示法直接載入(例如，b'011010')。為了解決這個問題，使用SET子句去掉前導b'和末尾b'，並執行base-2到base-10的轉換，以便MySQL正確地將值載入到BIT列中:

[mysql@localhost ~]$ cat /var/lib/mysql/bit_test.txt
b'10'
b'1111111'
mysql> create table bit_test(b bit(10));
Query OK, 0 rows affected (0.10 sec)
mysql> load data infile '/var/lib/mysql/bit_test.txt'
    -> into table bit_test(@var1)
    -> set b=cast(conv(mid(@var1,3,length(@var1)-3),2,10) as unsigned);
Query OK, 2 rows affected (0.15 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

對於0b二進位制表示法中的BIT值(例如0b011010)，使用這個SET子句來去掉前導的0b:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

在Unix上，如果需要LOAD DATA從管道讀取資料，可以使用以下技術(示例將/目錄的清單載入到表db1.t1中):

mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

在這裡，您必須在單獨的終端上執行生成要載入的資料的命令和mysql命令，或者在後臺執行資料生成過程(如前面的示例所示)。如果您不這樣做，管道將阻塞，直到資料被mysql程式讀取。

當LOAD DATA INFILE語句完成時，它將返回如下格式的資訊字串

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0