MySQL的文本導入之load data local

數據文件格式：

導入命令：?

過程：?

幫助：

官網：?

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

基本語法：

load data ?[low_priority] [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?語句從一個文本文件中以很高的速度讀入一個表中。?使用這個命令之前，mysqld進程（服務）必須已經在運行。?為了安全原因，當讀取位于服務器上的 文本文件時，文件必須處于數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用?load data infile?，在服務器主機上你必須有?file?的權限。

1 ??如果你指定關鍵詞?low_priority?，?那么MySQL將會等到沒有其他人讀這個表的時候，?
才把插入數據。可以使用如下的命令：??
load data ?low_priority infile "/home/mark/data sql" into table Orders;

?

2 ??如果指定?local?關鍵詞，則表明從客戶主機讀文件。如果?local?沒指定，文件必須位于服務器上。

?

3 ?replace?和?ignore?關鍵詞控制對現有的唯一鍵記錄的重復的處理。如果你指定?replace?，?
新行將代替有相同的 唯一鍵值的現有行。如果你指定?ignore?，跳過有唯一鍵的現有行的?
重復行的輸入。如果你不指定任何一個選項，當找到重復鍵時，出現一個錯誤，?
并且文本 文件的余下部分被忽略。例如：

load data ?low_priority infile "/home/mark/data sql" replace into table Orders;

?

4??分隔符

（1） fields關鍵字指定了文件記段的分割格式，如果用到這個關鍵字，MySQL剖析器希望?
看到至少有下面的一個選項：??
terminated by?分隔符：意思是以什么字符作為分隔符?
enclosed by?字段括起字符?
escaped by?轉義字符

terminated by?描述字段的分隔符，默認 情況下是tab字符（\t）??
enclosed by描述的是字段的括起字符。?
escaped by?描述的轉義字符。默認的是反斜杠 （backslash：\ ） ?

例如：load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';

（2）lines 關鍵字指定了每條記錄的分隔符默認為?'\n'?即為換行符

如果兩個字段都指定了 那?fields?必須在?lines?之前。?如果不指定?fields?關鍵字缺省值 與如果你這樣?
寫的相同：??fields terminated by'\t' enclosed by ’ '' ‘ escaped by'\\'

如果你不指定一個?lines?子句，缺省值與 如果你這樣寫的相同：??lines terminated by'\n'

例如：?load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';

5????load data infile??可以按指定的列把文件導入到數據庫中。 當我們要把數據的一部分內容?
導入的時候，，需要加入一些欄目（列/字段/field）到MySQL數據庫中，以適應一些額外的?
需要。比方說，我們要從 Access數據庫升級到MySQL數據庫的時候

下面的例子顯示了如何向指 定的欄目(field)中導入數據：??
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);

6????當在服務器主機上尋找文件時，服 務器使用下列規則：??
（?1?）如果給出一個絕對路徑名，服務器使用該路徑名。??
（?2?）如果給出一個有一個或多個前置部件的相對路徑名，服務器相對服務器的數據目錄?
搜索文件。????
（?3?）如果給出一個沒有前置部件的一個 文件名，服務器在當前數據庫的數據庫目錄尋找文件。??
例如：??/myfile txt”?="color: red">給出的文件是從服務器的數據目錄讀取，而作為?“myfile txt”?給出的一個文 件?
是從當前數據庫的數據庫目錄下讀取。

??

注意：字段中的空值用?\N?表示

我的文章一般淺顯易懂，不會搞那么深入讓大家很難理解。（其實我水平也不咋樣)

LOAD DATA INFILE 一直被認為是MySQL很強大的一個數據導入工具，因為他速度非常的快。?
不過有幾個問題一定要注意?
1、編碼。?
2、靈活導入導出。

LOAD DATA [LOW_PRIORITY] [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在MySQL3.22.6或以后版本中可用。）

為了?安全原因，當讀取位于服務器上的文本文件時，文件必須處于數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用LOAD DATA INFILE，?在服務器主機上你必須有file的權限。見6.5 由MySQL提供的權限。

如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執行被推遲到沒有其他客戶讀取表后。

使用LOCAL將比讓服務器直接存取文件慢些，因為文件的內容必須從客戶主機傳送到服務器主機。在另一方面，你不需要file權限裝載本地文件。

你也可以使用mysqlimport實用程序裝載數據文件；它由發送一個LOAD DATA INFILE命令到服務器來運作。 --local選項使得mysqlimport從客戶主機上讀取數據。如果客戶和服務器支持壓縮協議，你能指定--compress在較慢的?網絡上獲得更好的性能。

當在服務器主機上尋找文件時，服務器使用下列規則：

如果給出一個絕對路徑名，服務器使用該路徑名。??
如果給出一個有一個或多個前置部件的相對路徑名，服務器相對服務器的數據目錄搜索文件。??
如果給出一個沒有前置部件的一個文件名，服務器在當前數據庫的數據庫目錄尋找文件。??
注意這些規則意味著一個像“./myfile.txt”給出的文件是從服務器的數據目錄讀取，而作為“myfile.txt”給出的一個文件是從當前數據庫的數據庫目錄下讀取。也要注意，對于下列哪些語句，對db1文件從數據庫目錄讀取，而不是db2：

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

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

如果你使用LOCAL關鍵詞從一個本地文件裝載數據，服務器沒有辦法在操作的當中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，?
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 '\\'，對作為一條單個的反斜線被讀取的值，你必須指定2條反斜線值。

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

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

當你與LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE將一個數據庫的數據寫進一個文件并且隨后馬上將文件讀回數據庫時，兩個命令的字段和處理選項必須匹配，否則，LOAD DATA INFILE將不能正確解釋文件的內容。假定你使用SELECT ... INTO OUTFILE將由逗號分隔的字段寫入一個文件：

mysql> SELECT * FROM table1 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值可以是超過一個字符。例如，寫入由回車換行符對（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

對于輸入，ENCLOSED BY字符如果存在，它從字段值的尾部被剝去。（不管是否指定OPTIONALLY都是這樣；OPTIONALLY對于輸入解釋不起作用)由ENCLOSED BY字符領先的ESCAPED BY字符出現被解釋為當前字段值的一部分。另外，出現在字段中重復的ENCLOSED 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字符不是空的，它被用于前綴在輸出上的下列字符：

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處理的規則。

對于更多關于“\”- 轉義句法的信息，在某些情況下，字段和行處理選項相互作用：

如果LINES TERMINATED BY是一個空字符串并且FIELDS TERMINATED BY是非空的，行也用FIELDS TERMINATED BY終止。?
如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都是空的('')，一個固定行(非限定的)格式被使用。用固定行格式，在字段之間不使用分隔符。相反，列值只用列的“顯示”寬度被寫入和讀出。例如，如果列被聲明為INT(7)，列的值使用7個字符的字段被寫入。對于輸入，列值通過讀取7個字符獲得。固定行格式也影響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和空字符串在表中不能區分，因為他們都作為空字符串被寫入。如果在讀回文件時需要能區分這兩者，你應該不使用固定行格式。?
一些不被LOAD DATA INFILE支持的情況：

固定長度的行(FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空)和BLOB或TEXT列。?
如果你指定一個分隔符與另一個相同，或是另一個的前綴，LOAD DATA INFILE不能正確地解釋輸入。例如，下列FIELDS子句將導致問題：??
FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果FIELDS ESCAPED BY是空的，一個包含跟隨FIELDS TERMINATED BY值之后的FIELDS ENCLOSED BY或LINES TERMINATED BY的字段值將使得LOAD DATA INFILE過早地終止讀取一個字段或行。這是因為LOAD DATA INFILE不能正確地決定字段或行值在哪兒結束。?
下列例子裝載所有persondata表的行：

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

沒有指定字段表，所以LOAD DATA INFILE期望輸入行對每個表列包含一個字段。使用缺省FIELDS和LINES值。

如果你希望僅僅裝載一張表的某些列，指定一個字段表：

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

如果在輸入文件中的字段順序不同于表中列的順序，你也必須指定一個字段表。否則，MySQL不能知道如何匹配輸入字段和表中的列。

如果一個行有很少的字段，對于不存在輸入字段的列被設置為缺省值。

如果字段值缺省，空字段值有不同的解釋：

對于字符串類型，列被設置為空字符串。??
對于數字類型，列被設置為0。??
對于日期和時間類型，列被設置為該類型的適當“零”值。??
如果列有一個NULL，或(只對第一個TIMESTAMP列)在指定一個字段表時，如果TIMESTAMP列從字段表省掉，TIMESTAMP列只被設置為當前的日期和時間。

如果輸入行有太多的字段，多余的字段被忽略并且警告數字加1。

LOAD DATA INFILE認為所有的輸入是字符串，因此你不能像你能用INSERT語句的ENUM或SET列的方式使用數字值。所有的ENUM和SET值必須作為字符串被指定！

如果你正在使用C API，當LOAD DATA INFILE查詢完成時，你可通過調用API函數mysql_info()得到有關查詢的信息。信息字符串的格式顯示在下面：

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0?
當值通過INSERT語句插入時，在某些情況下出現警告，除了在輸入行中有太少或太多的字段時，LOAD DATA INFILE也產生警告。警告沒被?存儲在任何地方；警告數字僅能用于表明一切是否順利。如果你得到警告并且想要確切知道你為什么得到他們，一個方法是使用SELECT ... INTO OUTFILE到另外一個文件并且把它與你的原版輸入文件比較

load data語句詳解?
1、語法解析?
1.1. 必選子句或關鍵字?
1.2. 可選子句或關鍵字?
1.2.1. LOW_PRIORITY關鍵字?
1.2.2. LOCAL關鍵字?
1.2.2.1. 使用與不使用local關鍵字的流程?
1.2.2.2. 使用local關鍵字的錯誤處理?
1.2.3. REPLACE與IGNORE關鍵字?
1.2.4. PARTITION子句?
1.2.5. CHARACTER SET charset_name子句?
1.2.6. FIELDS（與COLUMNS關鍵字相同）和LINES子句?
1.2.6.1. FIELDS關鍵字及其子句詳解?
1.2.6.2. LINES 關鍵字及其子句詳解?
1.2.6.3. FIELDS和LINES注意事項?
1.2.7. IGNORE number {LINES | ROWS}子句?
1.2.8. (col_name_or_user_var,…)指定字段名稱的子句?
1.2.8. SET col_name = expr,…子句?
2、批量導出和批量導入?
2.1. 使用mysqldump批量導出?
2.2. 使用mysqimport批量導出?
2.2.1. mysqlimport語法及其參數說明?
2.2.2. mysqlimport用法演示示例?
2.2.2.1. 單表導入?
2.2.2.2.多表導入?
3、總結

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. admin@localhost : (none) 09:28:29> use xiaoboluo

2. Database changed

3. admin@localhost : xiaoboluo 09:28:31> show tables;

4. +---------------------+

5. | Tables_in_xiaoboluo |

6. +---------------------+

7. | test |

8. | test2 |

9. +---------------------+

10. 2 rows in set (0.00 sec)

11. 
    

12. 
    

13. admin@localhost : xiaoboluo 09:36:07> create table test3(id int unsigned not null primary key auto_increment,test varchar(100),test2 varchar(100));

14. Query OK, 0 rows affected (0.01 sec)

15. 
    

16. 
    

17. admin@localhost : xiaoboluo 09:36:47> insert into test3(test,test2) values('a string','100.20'),('a string containing a , comma','102.20'),('a string containing a " quote','102.20'),\

18. ('a string containing a ", quote and comma','102.20');

19. 
    

20. 
    

21. Query OK, 4 rows affected (0.00 sec)

22. Records: 4 Duplicates: 0 Warnings: 0

23. 
    

24. 
    

25. admin@localhost : xiaoboluo 09:40:33> select * from test3;

26. +----+------------------------------------------+--------+

27. | id | test | test2 |

28. +----+------------------------------------------+--------+

29. | 2 | a string | 100.20 |

30. | 4 | a string containing a , comma | 102.20 |

31. | 6 | a string containing a " quote | 102.20 |

32. | 8 | a string containing a ", quote and comma | 102.20 |

33. +----+------------------------------------------+--------+

34. 4 rows in set (0.00 sec)

1、語法解析

• 查看語法幫助信息

1. admin@localhost : xiaoboluo 10:14:21> help load data;

2. Name: 'LOAD DATA'

3. Description:

4. Syntax:

5. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

6. [REPLACE | IGNORE]

7. INTO TABLE tbl_name

8. [PARTITION (partition_name,...)]

9. [CHARACTER SET charset_name]

10. [{FIELDS | COLUMNS}

11. [TERMINATED BY 'string']

12. [[OPTIONALLY] ENCLOSED BY 'char']

13. [ESCAPED BY 'char']

14. ]

15. [LINES

16. [STARTING BY 'string']

17. [TERMINATED BY 'string']

18. ]

19. [IGNORE number {LINES | ROWS}]

20. [(col_name_or_user_var,...)]

21. [SET col_name = expr,...]

22. .....

• 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. # 執行select ...into outfile語句導出文本文件

2. admin@localhost : xiaoboluo 10:11:19> select * from test3;

3. +----+------------------------------------------+--------+

4. | id | test | test2 |

5. +----+------------------------------------------+--------+

6. | 2 | a string | 100.20 |

7. | 4 | a string containing a , comma | 102.20 |

8. | 6 | a string containing a " quote | 102.20 |

9. | 8 | a string containing a ", quote and comma | 102.20 |

10. +----+------------------------------------------+--------+

11. 4 rows in set (0.00 sec)

12. 
    

13. 
    

14. admin@localhost : xiaoboluo 10:31:10> select * from test3 into outfile "/tmp/test3.txt";

15. Query OK, 4 rows affected (0.00 sec)

16. 
    

17. 
    

18. # 使用python查看一下數據中的特殊符號，其中\t是制表符，用于字段分割，\n是換行符，用于行分割

19. >>> f = open('/tmp/test3.txt','r')

20. 
    

21. >>> data = f.readlines()

22. >>> data

23. ['2\ta string\t100.20\n', '4\ta string containing a , comma\t102.20\n', '6\ta string containing a " quote\t102.20\n', '8\ta string containing a ", quote and comma\t102.20\n']

24. >>> for i in data:

25. ... print i,

26. ...

27. 2 a string 100.20

28. 4 a string containing a , comma 102.20

29. 6 a string containing a " quote 102.20

30. 8 a string containing a ", quote and comma 102.20

31. 
    

32. 
    

33. # 現在，truncate掉表test3，執行load data載入數據

34. admin@localhost : xiaoboluo 10:31:43> truncate test3;

35. Query OK, 0 rows affected (0.01 sec)

36. 
    

37. 
    

38. admin@localhost : xiaoboluo 10:36:40> load data infile '/tmp/test3.txt' into table test3;

39. Query OK, 4 rows affected (0.00 sec)

40. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

41. 
    

42. 
    

43. admin@localhost : xiaoboluo 10:36:53> select * from test3; #這里可以看到，數據到處導入正常

44. +----+------------------------------------------+--------+

45. | id | test | test2 |

46. +----+------------------------------------------+--------+

47. | 2 | a string | 100.20 |

48. | 4 | a string containing a , comma | 102.20 |

49. | 6 | a string containing a " quote | 102.20 |

50. | 8 | a string containing a ", quote and comma | 102.20 |

51. +----+------------------------------------------+--------+

52. 4 rows in set (0.00 sec)

53. 
    

54. 
    

55. admin@localhost : xiaoboluo 10:36:58>

• 如果文本文件中的數據字段與表結構中的字段定義順序不同，則使用如下語句指定載入表中的字段順序

1. # 導出文本，導出文本時不使用select *，而是使用具體的字段，把順序稍微調整一下

2. admin@localhost : xiaoboluo 10:36:58> system rm -f /tmp/test3.txt;

3. admin@localhost : xiaoboluo 10:40:46> desc test3; # 留意表的字段定義順序，這里是id, test, test2

4. +-------+------------------+------+-----+---------+----------------+

5. | Field | Type | Null | Key | Default | Extra |

6. +-------+------------------+------+-----+---------+----------------+

7. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |

8. | test | varchar(100) | YES | | NULL | |

9. | test2 | varchar(100) | YES | | NULL | |

10. +-------+------------------+------+-----+---------+----------------+

11. 3 rows in set (0.00 sec)

12. 
    

13. 
    

14. admin@localhost : xiaoboluo 10:41:35> select * from test3; # 留個表中各個字段的值大概是什么內容

15. +----+------------------------------------------+--------+

16. | id | test | test2 |

17. +----+------------------------------------------+--------+

18. | 2 | a string | 100.20 |

19. | 4 | a string containing a , comma | 102.20 |

20. | 6 | a string containing a " quote | 102.20 |

21. | 8 | a string containing a ", quote and comma | 102.20 |

22. +----+------------------------------------------+--------+

23. 4 rows in set (0.00 sec)

24. 
    

25. 
    

26. admin@localhost : xiaoboluo 10:40:17> select id,test2,test from test3 into outfile "/tmp/test3.txt";

27. Query OK, 4 rows affected (0.01 sec)

28. 
    

29. 
    

30. admin@localhost : xiaoboluo 10:40:41> system cat /tmp/test3.txt; #這里可以看到文本文件中的test字段值放到最后去了

31. 2 100.20 a string

32. 4 102.20 a string containing a , comma

33. 6 102.20 a string containing a " quote

34. 8 102.20 a string containing a ", quote and comma

35. 
    

36. 
    

37. # 現在，truncate掉表test3，執行load data載入數據

38. admin@localhost : xiaoboluo 10:47:31> truncate test3;

39. Query OK, 0 rows affected (0.01 sec)

40. 
    

41. 
    

42. admin@localhost : xiaoboluo 10:48:43> load data infile '/tmp/test3.txt' into table test3(id,test2,test);

43. Query OK, 4 rows affected (0.00 sec)

44. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

45. 
    

46. 
    

47. admin@localhost : xiaoboluo 10:49:13> select * from test3; #可以看到，使用(id,test2,test)子句指定了與文本文件中數據的字段一致的順序，導入表表中之后數據的順序是正確的

48. +----+------------------------------------------+--------+

49. | id | test | test2 |

50. +----+------------------------------------------+--------+

51. | 2 | a string | 100.20 |

52. | 4 | a string containing a , comma | 102.20 |

53. | 6 | a string containing a " quote | 102.20 |

54. | 8 | a string containing a ", quote and comma | 102.20 |

55. +----+------------------------------------------+--------+

56. 4 rows in set (0.00 sec)

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關鍵字，任意一個關閉都不能使用

1. # 登錄到數據庫，重新導出表數據到文本，并發送到10.10.30.250服務器

2. admin@localhost : xiaoboluo 10:51:57> system rm -f /tmp/test3.txt;

3. admin@localhost : xiaoboluo 11:10:19> select * from test3 into outfile "/tmp/test3.txt";

4. Query OK, 4 rows affected (0.01 sec)

5. 
   

6. 
   

7. admin@localhost : xiaoboluo 11:10:41> system scp /tmp/test3.txt 10.10.30.250:/tmp/

8. test3.txt 100% 146 0.1KB/s 00:00

9. admin@localhost : xiaoboluo 11:11:15>

10. 
    

11. 
    

12. # 登錄到10.10.30.250服務器，遠程連接10.10.30.241數據庫

13. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d ~]# mysql -uadmin -pletsg0 -h20.10.30.241

14. mysql: [Warning] Using a password on the command line interface can be insecure.

15. Welcome to the MySQL monitor. Commands end with ; or \g.

16. Your MySQL connection id is 46

17. Server version: 5.7.18-log MySQL Community Server (GPL)

18. 
    

19. 
    

20. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

21. 
    

22. 
    

23. Oracle is a registered trademark of Oracle Corporation and/or its

24. affiliates. Other names may be trademarks of their respective

25. owners.

26. 
    

27. 
    

28. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

29. 
    

30. 
    

31. mysql> use xiaoboluo

32. Reading table information for completion of table and column names

33. You can turn off this feature to get a quicker startup with -A

34. 
    

35. 
    

36. Database changed

37. mysql> system ls -lh /tmp/test3.txt;

38. -rw-r--r-- 1 root root 146 May 3 11:11 /tmp/test3.txt

39. mysql> system cat /tmp/test3.txt;

40. 2 a string 100.20

41. 4 a string containing a , comma 102.20

42. 6 a string containing a " quote 102.20

43. 8 a string containing a ", quote and comma 102.20

44. mysql> show variables like '%local%';

45. +---------------+-------+

46. | Variable_name | Value |

47. +---------------+-------+

48. | local_infile | ON |

49. +---------------+-------+

50. 1 row in set (0.00 sec)

51. 
    

52. 
    

53. mysql> set global local_infile=OFF; #關閉server端的local_infile參數

54. Query OK, 0 rows affected (0.00 sec)

55. 
    

56. 
    

57. mysql> truncate test3;

58. Query OK, 0 rows affected (0.00 sec)

59. 
    

60. 
    

61. mysql> load data local infile '/tmp/test3.txt' into table test3; #執行導入數據時報錯了

62. ERROR 1148 (42000): The used command is not allowed with this MySQL version

63. mysql> set global local_infile=ON; #重新打開server端的local_infile參數

64. Query OK, 0 rows affected (0.00 sec)

65. 
    

66. 
    

67. mysql> load data local infile '/tmp/test3.txt' into table test3; #導入成功

68. Query OK, 4 rows affected (0.00 sec)

69. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

70. 
    

71. 
    

72. mysql> select * from test3; #查看數據，可以看到數據已成功導入表

73. +----+------------------------------------------+--------+

74. | id | test | test2 |

75. +----+------------------------------------------+--------+

76. | 2 | a string | 100.20 |

77. | 4 | a string containing a , comma | 102.20 |

78. | 6 | a string containing a " quote | 102.20 |

79. | 8 | a string containing a ", quote and comma | 102.20 |

80. +----+------------------------------------------+--------+

81. 4 rows in set (0.00 sec)

82. 
    

83. 
    

84. # 對于客戶端連接server時使用local_infile=0參數，在執行導入數據時也會報相同的錯誤，這里不做演示，示例命令：mysql -uadmin -pletsg0 -h20.10.30.241 --local-infile=0

• 使用local關鍵字與不使用local關鍵字時load data語句加載文本文件的流程

  • 如果指定了LOCAL，則該客戶端程序在客戶端主機上讀取load data語句需要的文件并將其發送到服務器。該文件可以使用完整路徑名稱來指定其位置。也可以使用相對路徑，使用相對路徑時，路徑前綴為使用客戶端程序時的工作目錄，當使用帶有LOAD DATA的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，數據解析碰到重復鍵時默認情況下會終止操作

1. admin@localhost : xiaoboluo 11:46:09> select * from test3 into outfile '/tmp/test3.txt';

2. Query OK, 4 rows affected (0.00 sec)

3. 
   

4. 
   

5. admin@localhost : xiaoboluo 01:40:32> desc test3; #留意id是主鍵

6. +-------+------------------+------+-----+---------+----------------+

7. | Field | Type | Null | Key | Default | Extra |

8. +-------+------------------+------+-----+---------+----------------+

9. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |

10. | test | varchar(100) | YES | | NULL | |

11. | test2 | varchar(100) | YES | | NULL | |

12. +-------+------------------+------+-----+---------+----------------+

13. 3 rows in set (0.00 sec)

14. 
    

15. 
    

16. admin@localhost : xiaoboluo 01:40:51> select * from test3; # 留意id列值是2，4，6，8

17. +----+------------------------------------------+--------+

18. | id | test | test2 |

19. +----+------------------------------------------+--------+

20. | 2 | a string | 100.20 |

21. | 4 | a string containing a , comma | 102.20 |

22. | 6 | a string containing a " quote | 102.20 |

23. | 8 | a string containing a ", quote and comma | 102.20 |

24. +----+------------------------------------------+--------+

25. 4 rows in set (0.00 sec)

26. 
    

27. 
    

28. admin@localhost : xiaoboluo 01:40:57> system cat /tmp/test3.txt; #留意導出的文本中的第一列數值與表中的id列值相同

29. 2 a string 100.20

30. 4 a string containing a , comma 102.20

31. 6 a string containing a " quote 102.20

32. 8 a string containing a ", quote and comma 102.20

33. 
    

34. 
    

35. admin@localhost : xiaoboluo 01:41:21> load data infile '/tmp/test3.txt' into table test3; #執行load data時，不使用local關鍵字直接報主鍵沖突

36. ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

37. admin@localhost : xiaoboluo 01:41:32> show warnings;

38. +-------+------+---------------------------------------+

39. | Level | Code | Message |

40. +-------+------+---------------------------------------+

41. | Error | 1062 | Duplicate entry '2' for key 'PRIMARY' |

42. +-------+------+---------------------------------------+

43. 1 row in set (0.00 sec)

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

1. admin@localhost : xiaoboluo 01:46:52> load data local infile '/tmp/test3.txt' into table test3; #加上local關鍵字再次執行，可以發現不報錯了，但是Skipped: 4 Warnings: 4表示跳過了4行數據導入，\

2. 注意：如果不使用local關鍵字，在默認的sql_mode下無法導入，否則就需要去掉嚴格的sql_mode

3. 
   

4. 
   

5. Query OK, 0 rows affected, 4 warnings (0.00 sec)

6. Records: 4 Deleted: 0 Skipped: 4 Warnings: 4

7. 
   

8. 
   

9. Warning (Code 1062): Duplicate entry '2' for key 'PRIMARY'

10. Warning (Code 1062): Duplicate entry '4' for key 'PRIMARY'

11. Warning (Code 1062): Duplicate entry '6' for key 'PRIMARY'

12. Warning (Code 1062): Duplicate entry '8' for key 'PRIMARY'

13. admin@localhost : xiaoboluo 01:47:01> show warnings;

14. +---------+------+---------------------------------------+

15. | Level | Code | Message |

16. +---------+------+---------------------------------------+

17. | Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |

18. | Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' |

19. | Warning | 1062 | Duplicate entry '6' for key 'PRIMARY' |

20. | Warning | 1062 | Duplicate entry '8' for key 'PRIMARY' |

21. +---------+------+---------------------------------------+

22. 4 rows in set (0.00 sec)

23. 
    

24. 
    

25. admin@localhost : xiaoboluo 01:47:05> select * from test3;

26. +----+------------------------------------------+--------+

27. | id | test | test2 |

28. +----+------------------------------------------+--------+

29. | 2 | a string | 100.20 |

30. | 4 | a string containing a , comma | 102.20 |

31. | 6 | a string containing a " quote | 102.20 |

32. | 8 | a string containing a ", quote and comma | 102.20 |

33. +----+------------------------------------------+--------+

34. 4 rows in set (0.00 sec)

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，見后續章節示例）

1. # 使用replace 覆蓋沖突數據行，不使用local關鍵字

2. admin@localhost : xiaoboluo 01:47:47> flush logs;

3. Query OK, 0 rows affected (0.01 sec)

4. 
   

5. 
   

6. admin@localhost : xiaoboluo 02:16:34> select * from test3;

7. +----+------------------------------------------+--------+

8. | id | test | test2 |

9. +----+------------------------------------------+--------+

10. | 2 | a string | 100.20 |

11. | 4 | a string containing a , comma | 102.20 |

12. | 6 | a string containing a " quote | 102.20 |

13. | 8 | a string containing a ", quote and comma | 102.20 |

14. +----+------------------------------------------+--------+

15. 4 rows in set (0.00 sec)

16. 
    

17. 
    

18. admin@localhost : xiaoboluo 02:16:48> show variables like '%binlog_format%';

19. +---------------+-------+

20. | Variable_name | Value |

21. +---------------+-------+

22. | binlog_format | ROW |

23. +---------------+-------+

24. 1 row in set (0.00 sec)

25. 
    

26. 
    

27. admin@localhost : xiaoboluo 02:17:26> show variables like '%tx_isolation%';

28. +---------------+----------------+

29. | Variable_name | Value |

30. +---------------+----------------+

31. | tx_isolation | READ-COMMITTED |

32. +---------------+----------------+

33. 1 row in set (0.01 sec)

34. admin@localhost : xiaoboluo 02:18:01> update test3 set test2='111111111' where id=2; #修改其中一行數據

35. Query OK, 1 row affected (0.00 sec)

36. Rows matched: 1 Changed: 1 Warnings: 0

37. 
    

38. 
    

39. admin@localhost : xiaoboluo 02:20:39> select * from test3; #查看表中的數據，留意id=2的test2字段值為111111111

40. +----+------------------------------------------+-----------+

41. | id | test | test2 |

42. +----+------------------------------------------+-----------+

43. | 2 | a string | 111111111 |

44. | 4 | a string containing a , comma | 102.20 |

45. | 6 | a string containing a " quote | 102.20 |

46. | 8 | a string containing a ", quote and comma | 102.20 |

47. +----+------------------------------------------+-----------+

48. 4 rows in set (0.00 sec)

49. 
    

50. 
    

51. admin@localhost : xiaoboluo 02:20:41> load data infile '/tmp/test3.txt' replace into table test3; #執行不帶local關鍵字但帶repalce關鍵字的語句

52. Query OK, 5 rows affected (0.00 sec)

53. Records: 4 Deleted: 1 Skipped: 0 Warnings: 0

54. 
    

55. 
    

56. admin@localhost : xiaoboluo 02:20:50> select * from test3; #查詢表中的數據，可以發現id=2的行的test2字段被覆蓋為文本文件中的值100.20 了

57. +----+------------------------------------------+--------+

58. | id | test | test2 |

59. +----+------------------------------------------+--------+

60. | 2 | a string | 100.20 |

61. | 4 | a string containing a , comma | 102.20 |

62. | 6 | a string containing a " quote | 102.20 |

63. | 8 | a string containing a ", quote and comma | 102.20 |

64. +----+------------------------------------------+--------+

65. 4 rows in set (0.00 sec)

66. 
    

67. 
    

68. # 解析最后一個binlog來查看一下（這里僅作參考，主庫內部是否是binlog中記錄的形式處理覆蓋數據有待考證）

69. $ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000004

70. 
    

71. ......

72. BEGIN

73. /*!*/;

74. # at 732

75. #170503 14:20:50 server id 3306241 end_log_pos 814 CRC32 0xb4da8deb Rows_query

76. # load data infile '/tmp/test3.txt' replace into table test3 #這里是執行的原始load data 的sql語句，要看到此內容，需要打開參數：binlog_rows_query_log_events=ON

77. # at 814

78. #170503 14:20:50 server id 3306241 end_log_pos 873 CRC32 0xe0066c03 Table_map: `xiaoboluo`.`test3` mapped to number 253

79. # at 873

80. #170503 14:20:50 server id 3306241 end_log_pos 958 CRC32 0xe95b171c Update_rows: table id 253 flags: STMT_END_F

81. ### UPDATE `xiaoboluo`.`test3` #這里可以看到發生數據沖突的行被執行了update

82. ### WHERE

83. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */

84. ### @2='a string' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

85. ### @3='111111111' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

86. ### SET

87. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */

88. ### @2='a string' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

89. ### @3='100.20' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

90. # at 958

91. #170503 14:20:50 server id 3306241 end_log_pos 989 CRC32 0x84b1f86e Xid = 364

92. COMMIT/*!*/;

93. ......

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

1. admin@localhost : xiaoboluo 02:26:45> flush logs;

2. Query OK, 0 rows affected (0.01 sec)

3. 
   

4. 
   

5. admin@localhost : xiaoboluo 02:29:18> select * from test3;

6. +----+------------------------------------------+--------+

7. | id | test | test2 |

8. +----+------------------------------------------+--------+

9. | 2 | a string | 100.20 |

10. | 4 | a string containing a , comma | 102.20 |

11. | 6 | a string containing a " quote | 102.20 |

12. | 8 | a string containing a ", quote and comma | 102.20 |

13. +----+------------------------------------------+--------+

14. 4 rows in set (0.00 sec)

15. 
    

16. 
    

17. admin@localhost : xiaoboluo 02:29:23> show variables like '%binlog_format%';

18. +---------------+-------+

19. | Variable_name | Value |

20. +---------------+-------+

21. | binlog_format | ROW |

22. +---------------+-------+

23. 1 row in set (0.00 sec)

24. 
    

25. 
    

26. admin@localhost : xiaoboluo 02:29:29> show variables like '%tx_isolation%';

27. +---------------+----------------+

28. | Variable_name | Value |

29. +---------------+----------------+

30. | tx_isolation | READ-COMMITTED |

31. +---------------+----------------+

32. 1 row in set (0.00 sec)

33. 
    

34. 
    

35. admin@localhost : xiaoboluo 02:29:34> update test3 set test2='111111111' where id=2;

36. Query OK, 1 row affected (0.00 sec)

37. Rows matched: 1 Changed: 1 Warnings: 0

38. 
    

39. 
    

40. admin@localhost : xiaoboluo 02:29:41> select * from test3;

41. +----+------------------------------------------+-----------+

42. | id | test | test2 |

43. +----+------------------------------------------+-----------+

44. | 2 | a string | 111111111 |

45. | 4 | a string containing a , comma | 102.20 |

46. | 6 | a string containing a " quote | 102.20 |

47. | 8 | a string containing a ", quote and comma | 102.20 |

48. +----+------------------------------------------+-----------+

49. 4 rows in set (0.00 sec)

50. 
    

51. 
    

52. admin@localhost : xiaoboluo 02:29:45> load data infile '/tmp/test3.txt' ignore into table test3; #這里可以看到Skipped: 4 Warnings: 4，表示4行數據都跳過了，注意：這是在sql_mode=''時導入的，\

53. 如果不修改sql_mode請使用local關鍵字

54. 
    

55. 
    

56. Query OK, 0 rows affected, 4 warnings (0.00 sec)

57. Records: 4 Deleted: 0 Skipped: 4 Warnings: 4

58. 
    

59. 
    

60. Warning (Code 1062): Duplicate entry '2' for key 'PRIMARY'

61. Warning (Code 1062): Duplicate entry '4' for key 'PRIMARY'

62. Warning (Code 1062): Duplicate entry '6' for key 'PRIMARY'

63. Warning (Code 1062): Duplicate entry '8' for key 'PRIMARY'

64. admin@localhost : xiaoboluo 02:30:04> select * from test3; #查看一下執行load data語句之后的表數據，發現id=2的test2列值還是111111111 沒有變化

65. +----+------------------------------------------+-----------+

66. | id | test | test2 |

67. +----+------------------------------------------+-----------+

68. | 2 | a string | 111111111 |

69. | 4 | a string containing a , comma | 102.20 |

70. | 6 | a string containing a " quote | 102.20 |

71. | 8 | a string containing a ", quote and comma | 102.20 |

72. +----+------------------------------------------+-----------+

73. 4 rows in set (0.00 sec)

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

1.2.6. FIELDS（與COLUMNS關鍵字相同）和LINES子句

• 以下示例中的char代表單個字符，string代表字符串（即多個字符），load data語句中，轉義字符和字段引用符只能使用單個字符，字段分隔符、行分隔符、行前綴字符都可以使用多個字符（字符串）

• 對于LOAD DATA INFILE和SELECT … INTO OUTFILE語句中，FIELDS和LINES子句的語法完全相同。兩個子句在LOAD DATA INFILE和SELECT … INTO OUTFILE語句中都是可選的，但如果兩個子句都被指定，則FIELDS必須在LINES之前，否則報語法錯誤

  • FIELDS關鍵字共有三個子句，TERMINATED BY 'string'指定字段分隔符，[OPTIONALLY] ENCLOSED BY 'char'指定字段引用符（其中使用了OPTIONALLY關鍵字之后，只在char、varchar和text等字符型字段上加字段引用符，數值型的不會加字段引用符，且OPTIONALLY 關鍵字只在導出數據時才起作用，導入數據時用于不用對數據沒有影響 ），ESCAPED BY 'char'指定轉義符，如果您指定了一個FIELDS子句，則它的每個子句也是可選的，但在你指定了FIELDS關鍵字之后，這個關鍵字的子句至少需要指定一個，后續章節會進行舉例說明

  • LINES關鍵字共有兩個子句，STARTING BY 'string'指定行前綴字符，TERMINATED BY 'string'指定行分隔符（換行符），如果你指定了LINES關鍵字，則LINES的子句都是可選的，但在你指定了LINES關鍵字之后，這個關鍵字的子句至少需要指定一個，后續章節會進行舉例說明

  • 如果在導入和導出時沒有指定FIELDS和LINES子句，則導入和導出時兩個子句的默認值相同，默認的字段分隔符為\t，行分隔符為\n（win上默認為\r\n，記事本程序上默認為\r），字段引用符為空，行前綴字符為空

• 當mysql server導出文本數據到文件時，FIELDS和LINES默認值時SELECT … INTO OUTFILE在輸出文本數據時行為如下：

  • 在文本數據各字段之間使用制表符來作為字段分隔符

  • 不使用任何引號來包圍文本數據的各字段值，即字段引用符為空

  • 使用\轉義在字段值中出現的制表符\t，換行符\n或轉義符本身\等特殊字符(即輸出的文本數據中對這些特殊字符前多加一個反斜杠)

  • 在行尾寫上換行符\n，即使用\n作為行分隔符（換行符）

  • 注意：如果您在Windows系統上生成了文本文件，則可能必須使用LINES TERMINATED BY '\r\n'來正確讀取文件，因為Windows程序通常使用兩個字符作為行終止符。某些程序（如寫字板）在寫入文件時可能會使用\r作為行終止符(要讀取這些文件，請使用LINES TERMINATED BY '\r')

  • FIELDS和LINES子句默認值時生成的純文本數據文件可以使用python代碼來讀取文件查看文件中的制表符和換行符（linux下的cat和vim等編輯器默認會解析\t為制表符,\n為換行符，所以使用這些命令可能無法看到這些特殊符號）

1. >>> f = open('/tmp/test3.txt','r')

2. >>> data = f.readlines()

3. >>> data

4. ['2,"a string","100.20"\n', '4,"a string containing a , comma","102.20"\n', '6,"a string containing a \\" quote","102.20"\n', '8,"a string containing a \\", quote and comma","102.20"\n']

5. >>> for i in data:

6. ... print i,

7. ...

8. 2,"a string","100.20"

9. 4,"a string containing a , comma","102.20"

10. 6,"a string containing a \" quote","102.20"

11. 8,"a string containing a \", quote and comma","102.20"

• 當mysql server從文本文件讀取數據時，FIELDS和LINES默認值會導致LOAD DATA INFILE的行為如下：

  • 尋找換行邊界字符\n來進行換行

  • 不跳過行前綴，把行前綴也當作數據（發生在如果導出數據時使用了行前綴，導入時沒有指定正確的行前綴或者根本沒有指定行前綴選項時）

  • 使用制表符\t來分割一行數據中的各列

  • 要注意：在FIELDS和LINES的默認值下，在解析文本文件時不會把字符串之間的引號當作真正的引號，而是當作數據

1.2.6.1. FIELDS關鍵字及其子句詳解

• 字段分隔符，默認是\t，使用子句 fields terminated by 'string' 指定，其中string代表指定的字段分隔符

1. admin@localhost : xiaoboluo 03:08:34> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',';

2. Query OK, 4 rows affected (0.00 sec)

3. 
   

4. 
   

5. admin@localhost : xiaoboluo 03:08:37> system cat /tmp/test3.txt

6. 2,a string,100.20

7. 4,a string containing a \, comma,102.20

8. 6,a string containing a " quote,102.20

9. 8,a string containing a "\, quote and comma,102.20

• 字段引用符，如果加optionally選項則只用在char、varchar和text等字符型字段上，數值類型會忽略使用引用符，如果不指定該子句，則默認不使用引用符，使用子句fields [optionally] enclosed by 'char'指定，其中char代表指定的字段引用符

1. # 指定字段引用符為"，不使用optionally關鍵字

2. admin@localhost : xiaoboluo 03:33:33> system rm -f /tmp/test3.txt;

3. admin@localhost : xiaoboluo 03:37:21> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '"';

4. Query OK, 5 rows affected (0.00 sec)

5. 
   

6. 
   

7. admin@localhost : xiaoboluo 03:37:33> system cat /tmp/test3.txt

8. "2" "a string" "100.20"

9. "4" "a string containing a , comma" "102.20"

10. "6" "a string containing a \" quote" "102.20"

11. "8" "a string containing a \", quote and comma" "102.20"

12. "10" "\\t" "102.20"

13. 
    

14. 
    

15. # 指定字段引用符為"，使用optionally關鍵字，可以看到id列的字段引用符去掉了

16. admin@localhost : xiaoboluo 03:37:41> system rm -f /tmp/test3.txt;

17. admin@localhost : xiaoboluo 03:40:53> select * from test3 into outfile "/tmp/test3.txt" FIELDS optionally ENCLOSED BY '"';

18. Query OK, 5 rows affected (0.00 sec)

19. 
    

20. 
    

21. admin@localhost : xiaoboluo 03:41:03> system cat /tmp/test3.txt

22. 2 "a string" "100.20"

23. 4 "a string containing a , comma" "102.20"

24. 6 "a string containing a \" quote" "102.20"

25. 8 "a string containing a \", quote and comma" "102.20"

26. 10 "\\t" "102.20"

• 轉義字符，默認為\，使用子句fields escaped by 'char' 指定，其中char代表指定的轉義字符

1. admin@localhost : xiaoboluo 03:42:41> system rm -f /tmp/test3.txt;

2. admin@localhost : xiaoboluo 03:44:18> select * from test3 into outfile "/tmp/test3.txt" fields escaped by '.';

3. Query OK, 5 rows affected (0.00 sec)

4. 
   

5. 
   

6. admin@localhost : xiaoboluo 03:44:25> system cat /tmp/test3.txt # 可以看到數據中指定的轉義符.號被轉義了，而數據\t沒有被轉義

7. 2 a string 100..20

8. 4 a string containing a , comma 102..20

9. 6 a string containing a " quote 102..20

10. 8 a string containing a ", quote and comma 102..20

11. 10 \t 102..20

12. admin@localhost : xiaoboluo 03:44:28> truncate test3; #清空表

13. Query OK, 0 rows affected (0.01 sec)

14. 
    

15. 
    

16. admin@localhost : xiaoboluo 03:45:19> load data infile "/tmp/test3.txt" into table test3 fields escaped by '.'; #導入數據時指定轉義符為.號

17. Query OK, 5 rows affected (0.00 sec)

18. Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

19. 
    

20. 
    

21. admin@localhost : xiaoboluo 03:45:40> select * from test3; #校驗數據，可以看到導入數據正常

22. +----+------------------------------------------+--------+

23. | id | test | test2 |

24. +----+------------------------------------------+--------+

25. | 2 | a string | 100.20 |

26. | 4 | a string containing a , comma | 102.20 |

27. | 6 | a string containing a " quote | 102.20 |

28. | 8 | a string containing a ", quote and comma | 102.20 |

29. | 10 | \t | 102.20 |

30. +----+------------------------------------------+--------+

31. 5 rows in set (0.00 sec)

1.2.6.2. LINES 關鍵字及其子句詳解

• 行前綴字符串，使用子句lines starting by 'string' 指定，其中string代表指定的行前綴字符串，行前綴字符串在導出文本數據時使用該子句指定，在導入文本時在一行數據中如果發現了行前綴字符串，則只導入從前綴字符串開始之后的數據部分，前綴字符本身及其之前的數據被忽略掉，如果某行數據不包含行前綴字符串，則整行數據都會被忽略

如果您想要讀取的純文本文件中所有行都有一個您想要忽略的公用前綴，則可以使用LINES STARTING BY'prefix_string'來跳過這個前綴，以及前綴字符前面的任何內容。如果某行數據不包含前綴字符，則跳過整行內容，例如：

1. # load data語句如下

2. admin@localhost : xiaoboluo 03:48:04> system rm -f /tmp/test3.txt;

3. admin@localhost : xiaoboluo 03:54:54> select * from test3 into outfile "/tmp/test3.txt" LINES STARTING BY 'xxx';

4. Query OK, 5 rows affected (0.00 sec)

5. 
   

6. 
   

7. admin@localhost : xiaoboluo 03:55:03> system cat /tmp/test3.txt #可以看到每行數據前面多了個行前綴字符串xxx

8. xxx2 a string 100.20

9. xxx4 a string containing a , comma 102.20

10. xxx6 a string containing a " quote 102.20

11. xxx8 a string containing a ", quote and comma 102.20

12. xxx10 \\t 102.20

13. 
    

14. 
    

15. # 現在，到shell命令行去修改一下，增加兩行

16. admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最后要加載的純文本數據內容如下

17. xxx2 a string 100.20

18. xxx4 a string containing a , comma 102.20

19. xxx6 a string containing a " quote 102.20

20. xxx8 a string containing a ", quote and comma 102.20

21. xxx10 \\t 102.20

22. 12 \\t 102.20

23. dfadsfasxxx14 \\t 102.20

24. admin@localhost : xiaoboluo 03:59:03> truncate test3; #清空表

25. Query OK, 0 rows affected (0.01 sec)

26. 
    

27. 
    

28. admin@localhost : xiaoboluo 03:59:38> load data infile "/tmp/test3.txt" into table test3 LINES STARTING BY 'xxx'; #導入數據，指定行前綴字符為xxx

29. Query OK, 6 rows affected (0.00 sec)

30. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

31. 
    

32. 
    

33. admin@localhost : xiaoboluo 03:59:44> select * from test3; #校驗表數據，可以看到沒有xxx行前綴的行被忽略了，而包含xxx的最后一行，從xxx開始截斷，xxx字符本身及其之前的內容被忽略，\

34. xxx之后的內容被解析為行數據導入了

35. 
    

36. 
    

37. +----+------------------------------------------+--------+

38. | id | test | test2 |

39. +----+------------------------------------------+--------+

40. | 2 | a string | 100.20 |

41. | 4 | a string containing a , comma | 102.20 |

42. | 6 | a string containing a " quote | 102.20 |

43. | 8 | a string containing a ", quote and comma | 102.20 |

44. | 10 | \t | 102.20 |

45. | 14 | \t | 102.20 |

46. +----+------------------------------------------+--------+

47. 6 rows in set (0.00 sec)

• 行結束符（換行符），linux下默認為\n，使用子句lines terminated by 'string' 指定，其中string代表指定的換行符

1. # 指定換行符為\r\n導出數據

2. admin@localhost : xiaoboluo 03:59:49> system rm -f /tmp/test3.txt;

3. admin@localhost : xiaoboluo 04:02:22> select * from test3 into outfile "/tmp/test3.txt" lines terminated by '\r\n';

4. Query OK, 6 rows affected (0.00 sec)

5. 
   

6. 
   

7. # 由于linux的一些命令本身會解析掉這些特殊字符，所以使用python來查看這個文本文件中的換行符，從下面的結果中可以看到，列表的每一個元素代表一行數據，每一個元素的\

8. 末尾的\r\n就是這行數據的換行符

9. 
   

10. 
    

11. >>> f = open('/tmp/test3.txt','r')

12. >>> data = f.readlines()

13. >>> data

14. ['2\ta string\t100.20\r\n', '4\ta string containing a , comma\t102.20\r\n', '6\ta string containing a " quote\t102.20\r\n', '8\ta string containing a ", quote and comma\t102.20\r\n', '10\t\\\\t\t102.20\r\n', \

15. '14\t\\\\t\t102.20\r\n']

16. >>>

17. 
    

18. 
    

19. # 現在，把數據重新導入表，從下面的結果中可以看到，導入表中的數據正確

20. admin@localhost : xiaoboluo 04:02:39> truncate test3;

21. Query OK, 0 rows affected (0.01 sec)

22. 
    

23. 
    

24. admin@localhost : xiaoboluo 04:04:55> load data infile "/tmp/test3.txt" into table test3 lines terminated by '\r\n';

25. Query OK, 6 rows affected (0.00 sec)

26. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

27. 
    

28. 
    

29. admin@localhost : xiaoboluo 04:05:11> select * from test3;

30. +----+------------------------------------------+--------+

31. | id | test | test2 |

32. +----+------------------------------------------+--------+

33. | 2 | a string | 100.20 |

34. | 4 | a string containing a , comma | 102.20 |

35. | 6 | a string containing a " quote | 102.20 |

36. | 8 | a string containing a ", quote and comma | 102.20 |

37. | 10 | \t | 102.20 |

38. | 14 | \t | 102.20 |

39. +----+------------------------------------------+--------+

40. 6 rows in set (0.00 sec)

1.2.6.3. FIELDS和LINES注意事項

• 眾所周知，MySQL中反斜杠是SQL語句中特殊字符的轉義字符，因此在sql語句中碰到特殊字符時，您必須指定一個或者兩個反斜杠來為特殊字符轉義（如在mysql中或者一些其他程序中，\n代表換行符，\t代表制表符，\代表轉義符，那么需要使用\t來轉義制表符，\n來轉義換行符，\來轉義轉義符本身，這樣才能正確寫入數據庫或者生成導出的數據文本,使用FIELDS ESCAPED BY子句指定轉義符

• 特殊字符列表如下

1. \0 ASCII NUL (X'00') 字符

2. \b 退格字符

3. \n 換行符

4. \r 回車符

5. \t 制表符

6. \Z ASCII 26 (Control+Z)

7. \N NULL值，如果轉義符值為空，則會直接導出null字符串作為數據，這在導入時將把null作為數據導入，而不是null符號

• 如果數據中包含了ENCLOSED BY '"'子句指定字段引用符號，則與字段引用符號相同數據字符也會被自動添加一個反斜杠進行轉義（如果轉義符指定為空，則可能會導致數據在導入時無法正確解析）。如果數據中包含了FIELDS TERMINATED BY 子句指定的字段分隔符，則以FIELDS ENCLOSED BY子句指定的字段引用符號為準，被引起來的整個部分作為一整列的數據，列值之間的數據包含字段分隔符不會被轉義，而是作為數據處理，但數據中包含的字段引用符會被轉義(在數據中包含了字段分隔符的情況下，如果字段引用符號沒有指定或者指定為空值，則可能在導入數據時無法正確解析）。如果數據中包含了FIELDS ESCAPED BY子句指定的轉義符，字段引用符和行分隔符使用默認值，則在數據中的轉義符會被轉義（只要不為空，則不管字段分隔符和轉義字符定義為什么值，都會被轉義），默認情況下，不建議隨意更改換行符和轉義符，除非必須且你需要校驗修改之后數據能夠正確導入

1. # 字段引用符為"，數據中包含"，轉義符和換行符保持默認，導入數據時不會有任何問題

2. admin@localhost : xiaoboluo 09:46:14> select * from test3;

3. +----+------------------------------------------+--------+

4. | id | test | test2 |

5. +----+------------------------------------------+--------+

6. | 2 | a string | 100.20 |

7. | 4 | a string containing a , comma | 102.20 |

8. | 6 | a string containing a " quote | 102.20 |

9. | 8 | a string containing a ", quote and comma | 102.20 |

10. +----+------------------------------------------+--------+

11. 4 rows in set (0.00 sec)

12. 
    

13. 
    

14. admin@localhost : xiaoboluo 09:46:17> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"';

15. Query OK, 4 rows affected (0.00 sec)

16. 
    

17. 
    

18. admin@localhost : xiaoboluo 09:46:23> system cat /tmp/test3.txt;

19. 2 "a string" "100.20"

20. 4 "a string containing a , comma" "102.20"

21. 6 "a string containing a \" quote" "102.20"

22. 8 "a string containing a \", quote and comma" "102.20" # 可以看到與字段引用符相同的符號數據被轉義了

23. 
    

24. 
    

25. admin@localhost : xiaoboluo 09:54:41> truncate test3;

26. Query OK, 0 rows affected (0.01 sec)

27. 
    

28. 
    

29. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';

30. Query OK, 4 rows affected (0.00 sec)

31. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

32. 
    

33. 
    

34. admin@localhost : xiaoboluo 09:58:45> select * from test3;

35. +----+------------------------------------------+--------+

36. | id | test | test2 |

37. +----+------------------------------------------+--------+

38. | 2 | a string | 100.20 |

39. | 4 | a string containing a , comma | 102.20 |

40. | 6 | a string containing a " quote | 102.20 |

41. | 8 | a string containing a ", quote and comma | 102.20 |

42. +----+------------------------------------------+--------+

43. 4 rows in set (0.00 sec)

44. 
    

45. 
    

46. # 如果字段引用符為"，字段分隔符為,且數據中包含字段引用符"和字段分隔符,，轉義符和換行符保持默認，這在導入數據時不會有任何問題

47. admin@localhost : xiaoboluo 09:53:45> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';

48. Query OK, 4 rows affected (0.00 sec)

49. 
    

50. 
    

51. admin@localhost : xiaoboluo 09:54:29> system cat /tmp/test3.txt;

52. 2,"a string","100.20"

53. 4,"a string containing a , comma","102.20"

54. 6,"a string containing a \" quote","102.20"

55. 8,"a string containing a \", quote and comma","102.20"

56. 
    

57. 
    

58. admin@localhost : xiaoboluo 09:54:41> truncate test3;

59. Query OK, 0 rows affected (0.01 sec)

60. 
    

61. 
    

62. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';

63. Query OK, 4 rows affected (0.00 sec)

64. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

65. 
    

66. 
    

67. admin@localhost : xiaoboluo 09:58:45> select * from test3;

68. +----+------------------------------------------+--------+

69. | id | test | test2 |

70. +----+------------------------------------------+--------+

71. | 2 | a string | 100.20 |

72. | 4 | a string containing a , comma | 102.20 |

73. | 6 | a string containing a " quote | 102.20 |

74. | 8 | a string containing a ", quote and comma | 102.20 |

75. +----+------------------------------------------+--------+

76. 4 rows in set (0.00 sec)

77. 
    

78. 
    

79. # 但是，如果在字段引用符為"，數據中包含"，字段分隔符使用逗號，換行符保持默認的情況下，轉義符使用了空串，這會導致在導入數據時，第四行無法正確解析，報錯

80. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';

81. Query OK, 4 rows affected (0.00 sec)

82. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

83. 
    

84. 
    

85. admin@localhost : xiaoboluo 09:58:45> select * from test3;

86. +----+------------------------------------------+--------+

87. | id | test | test2 |

88. +----+------------------------------------------+--------+

89. | 2 | a string | 100.20 |

90. | 4 | a string containing a , comma | 102.20 |

91. | 6 | a string containing a " quote | 102.20 |

92. | 8 | a string containing a ", quote and comma | 102.20 |

93. +----+------------------------------------------+--------+

94. 4 rows in set (0.00 sec)

95. 
    

96. 
    

97. admin@localhost : xiaoboluo 09:58:49> select * from test3 into outfile "/tmp/test3_test.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by '';

98. Query OK, 4 rows affected (0.00 sec)

99. 
    

100. 
     

101. admin@localhost : xiaoboluo 10:00:42> system cat /tmp/test3_test.txt;

102. 2,"a string","100.20"

103. 4,"a string containing a , comma","102.20"

104. 6,"a string containing a " quote","102.20" #關于這一行數據，需要說明一下ENCLOSED BY子句，該子句指定的引用符號從一個FIELDS TERMINATED BY子句指定的分隔符開始，直到碰到下一個\

105. 分隔符之間且這個分隔符前面一個字符必須是字段引用符號（如果這個分隔符前面一個字符不是字段引用符，則繼續往后匹配，如第二行數據），在這之間的內容都會被當作整個列字符串處理，\

106. 所以這一行數據在導入時不會發生解析錯誤

107. 
     

108. 
     

109. 8,"a string containing a ", quote and comma","102.20" #這一行因為無法正確識別的字段結束位置，所以無法導入，報錯終止，前面正確的行也被回滾掉（binlog_format=row）

110. admin@localhost : xiaoboluo 10:00:49> truncate test3;

111. Query OK, 0 rows affected (0.01 sec)

112. 
     

113. 
     

114. admin@localhost : xiaoboluo 10:01:03> load data infile '/tmp/test3_test.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by '';

115. ERROR 1262 (01000): Row 4 was truncated; it contained more data than there were input columns

116. admin@localhost : xiaoboluo 10:01:33> select * from test3;

117. Empty set (0.00 sec)

118. 
     

119. 
     

120. # 數據中包含了默認的轉義符和指定的字段分隔符，字段引用符和行分隔符使用默認值，則在數據中的轉義符和字段分隔符會被轉義（只要不為空，則不管字段分隔符和轉義字符定義為什么值，\

121. 都會被轉義）

122. 
     

123. 
     

124. 
     

125. admin@localhost : xiaoboluo 03:08:45> insert into test3(test,test2) values('\\t','102.20');

126. Query OK, 1 row affected (0.00 sec)

127. 
     

128. 
     

129. admin@localhost : xiaoboluo 03:17:29> select * from test3;

130. +----+------------------------------------------+--------+

131. | id | test | test2 |

132. +----+------------------------------------------+--------+

133. | 2 | a string | 100.20 |

134. | 4 | a string containing a , comma | 102.20 |

135. | 6 | a string containing a " quote | 102.20 |

136. | 8 | a string containing a ", quote and comma | 102.20 |

137. | 10 | \t | 102.20 |

138. +----+------------------------------------------+--------+

139. 5 rows in set (0.00 sec)

140. 
     

141. 
     

142. admin@localhost : xiaoboluo 03:17:32> system rm -f /tmp/test3.txt;

143. admin@localhost : xiaoboluo 03:17:39> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',';

144. Query OK, 5 rows affected (0.01 sec)

145. 
     

146. 
     

147. admin@localhost : xiaoboluo 03:17:42> system cat /tmp/test3.txt

148. 2,a string,100.20

149. 4,a string containing a \, comma,102.20

150. 6,a string containing a " quote,102.20

151. 8,a string containing a "\, quote and comma,102.20

152. 10,\\t,102.20

• 當您使用SELECT … INTO OUTFILE與LOAD DATA INFILE一起將數據從數據庫寫入文件，然后再將該文件讀回數據庫時，兩個語句的FIELDS和LINES處理選項必須匹配。否則，LOAD DATA INFILE將解析錯誤的文件內容，示例

1. # 假設您執行SELECT ... INTO OUTFILE語句時使用了逗號作為列分隔符：

2. SELECT * INTO OUTFILE 'data.txt'

3. FIELDS TERMINATED BY ','

4. FROM table2;

5. 
   

6. 
   

7. # 如果您嘗試使用\t作為列分隔符，則它將無法正常工作，因為它會指示LOAD DATA INFILE在字段之間查找制表符，可能導致每個數據行整行解析時被當作單個字段：

8. 
   

9. LOAD DATA INFILE 'data.txt' INTO TABLE table2

10. 
    

11. FIELDS TERMINATED BY '\t';

12. 
    

13. 
    

14. # 要正確讀取逗號分隔各列的文件，正確的語句是

15. LOAD DATA INFILE 'data.txt' INTO TABLE table2

16. FIELDS TERMINATED BY ',';

• 任何FIELDS和LINES處理選項都可以指定一個空字符串（''），但強烈不建議在FIELDS TERMINATED BY、FIELDS ESCAPED BY 和LINES TERMINATED BY子句中使用空串（空格不算空串）作為轉義符和換行符，可能導致許多意外的問題，除非你確定使用空串不會出現問題。如果不為空，注意FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY子句指定的值只能指定單個字符（即字段引用符號和轉義符只能使用單個字符）。但 FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY子句的值可以是多個字符（即字段分隔符和換行符、行前綴字符可以使用多個字符）。例如，指定一個LINES TERMINATED BY'\r\ n'子句，表示指定行換行符為\r\n，這個也是WIN下的換行符

1. # 如果LINES TERMINATED BY換行符指定了一個空字符，并且FIELDS TERMINATED BY字段分隔符指定的是非空的一個字符(或者使用默認值\t)，則行也會以字段分隔符作為行的結束符\

2. （表現行為就是文本中最后一個字符就是字段分隔符），即整個文本看上去就是一整行數據了

3. 
   

4. 
   

5. admin@localhost : xiaoboluo 04:48:35> system rm -f /tmp/test3.txt;

6. 
   

7. 
   

8. admin@localhost : xiaoboluo 04:53:59> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',' lines terminated by '';

9. Query OK, 6 rows affected (0.00 sec)

10. 
    

11. 
    

12. # 使用python查看文本內容，從下面的結果中可以看到，整個表的數據由于換行符為空，所以導致都拼接為一行了，最后行結束符使用了字段分隔符逗號

13. >>> f = open('/tmp/test3.txt','r')

14. >>> data = f.readlines()

15. >>> data

16. ['2,a string,100.20,4,a string containing a \\, comma,102.20,6,a string containing a " quote,102.20,8,a string containing a "\\, quote and comma,102.20,10,\\\\t,102.20,14,\\\\t,102.20,']

17. >>>

18. 
    

19. 
    

20. # 導入數據到表，這里新建一張表來進行導入測試，預防清理掉了表數據之后，文本內容又無法正確導入的情況發生

21. admin@localhost : xiaoboluo 04:57:52> create table test4 like test3;

22. Query OK, 0 rows affected (0.01 sec)

23. 
    

24. 
    

25. admin@localhost : xiaoboluo 04:57:59> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY ',' lines terminated by '';

26. Query OK, 6 rows affected (0.00 sec)

27. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

28. 
    

29. 
    

30. admin@localhost : xiaoboluo 04:58:26> select * from test4; #從查詢結果上看，數據正確導入表test4中了

31. +----+------------------------------------------+--------+

32. | id | test | test2 |

33. +----+------------------------------------------+--------+

34. | 2 | a string | 100.20 |

35. | 4 | a string containing a , comma | 102.20 |

36. | 6 | a string containing a " quote | 102.20 |

37. | 8 | a string containing a ", quote and comma | 102.20 |

38. | 10 | \t | 102.20 |

39. | 14 | \t | 102.20 |

40. +----+------------------------------------------+--------+

41. 6 rows in set (0.00 sec)

42. 
    

43. 
    

44. # 如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都為空（''），則使用固定行（非限制）格式。使用固定行格式時，字段之間使用足夠寬的空格來分割各字段。對于數據類型\

45. 是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，字段寬度分別為4,6,8,11和20個空格(無論數據類型聲明的顯示寬度如何)，對于varchar類型使用大約298個空格（這個空格數量是自己\

46. 數的。。。，猜想這個空格數量可能與字符集，varchar定義長度有關，因為我在嘗試把varchar定義為50個字符的時候，空格少了156個左右）

47. 
    

48. 
    

49. admin@localhost : xiaoboluo 04:58:31> system rm -f /tmp/test3.txt;

50. admin@localhost : xiaoboluo 05:04:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY '' lines terminated by '';

51. Query OK, 6 rows affected (0.00 sec)

52. 
    

53. 
    

54. admin@localhost : xiaoboluo 05:04:17> system cat /tmp/test3.txt #下面展示內容中把打斷空格使用...代替

55. 2 a string ... 100.20 ... 4 a string containing a , comma ... 102.20 ... 6 a string containing a " quote ...102.20 ... 8 a string containing a ", quote and comma ... 102.20 \

56. ... 10 \\t ... 102.20 ... 14 \\t ... 102.20 ... admin@localhost : xiaoboluo 05:04:35>

57. 
    

58. 
    

59. # 現在，清理掉test4表，并載入數據，從下面的結果中可以看到，導入表中之后，雖然數據是對的，但是多了非常多的空格，那么也就意味著你需要使用程序正確地處理一下這些多余的空格之后，\

60. 再執行導入

61. 
    

62. 
    

63. admin@localhost : xiaoboluo 05:06:19> truncate test4;

64. Query OK, 0 rows affected (0.01 sec)

65. 
    

66. 
    

67. admin@localhost : xiaoboluo 05:06:47> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY '' lines terminated by ''; # 注意：這是在sql_mode=''時導入的，如果不修改\

68. sql_mode請使用local關鍵字

69. 
    

70. 
    

71. Query OK, 6 rows affected, 12 warnings (0.01 sec)

72. Records: 6 Deleted: 0 Skipped: 0 Warnings: 12

73. 
    

74. 
    

75. Note (Code 1265): Data truncated for column 'test' at row 1

76. Note (Code 1265): Data truncated for column 'test2' at row 1

77. Note (Code 1265): Data truncated for column 'test' at row 2

78. Note (Code 1265): Data truncated for column 'test2' at row 2

79. Note (Code 1265): Data truncated for column 'test' at row 3

80. Note (Code 1265): Data truncated for column 'test2' at row 3

81. Note (Code 1265): Data truncated for column 'test' at row 4

82. Note (Code 1265): Data truncated for column 'test2' at row 4

83. Note (Code 1265): Data truncated for column 'test' at row 5

84. Note (Code 1265): Data truncated for column 'test2' at row 5

85. Note (Code 1265): Data truncated for column 'test' at row 6

86. Note (Code 1265): Data truncated for column 'test2' at row 6

87. admin@localhost : xiaoboluo 05:07:09> select * from test4;

88. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

89. | id | test | test2 |

90. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

91. | 2 | a string | 100.20 |

92. | 4 | a string containing a , comma | 102.20 |

93. | 6 | a string containing a " quote | 102.20 |

94. | 8 | a string containing a ", quote and comma | 102.20 |

95. | 10 | \t | 102.20 |

96. | 14 | \t | 102.20 |

97. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

98. 6 rows in set (0.00 sec)

• NULL值的處理根據使用的FIELDS和LINES選項而有所不同

1. # 對于默認的FIELDS和LINES值，NULL值被轉義為\N輸出，字段值\N讀取時使用NULL替換并輸入（假設ESCAPED BY字符為\）

2. admin@localhost : xiaoboluo 05:17:07> alter table test3 add column test3 varchar(10); #添加一個字段test3，默認值會被填充為null

3. Query OK, 0 rows affected (0.04 sec)

4. Records: 0 Duplicates: 0 Warnings: 0

5. 
   

6. 
   

7. admin@localhost : xiaoboluo 05:17:33> select * from test3; #查看表中的test3列數據

8. +----+------------------------------------------+--------+-------+

9. | id | test | test2 | test3 |

10. +----+------------------------------------------+--------+-------+

11. | 2 | a string | 100.20 | NULL |

12. | 4 | a string containing a , comma | 102.20 | NULL |

13. | 6 | a string containing a " quote | 102.20 | NULL |

14. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

15. | 10 | \t | 102.20 | NULL |

16. | 14 | \t | 102.20 | NULL |

17. +----+------------------------------------------+--------+-------+

18. 6 rows in set (0.00 sec)

19. 
    

20. 
    

21. admin@localhost : xiaoboluo 05:17:37> select * from test3 into outfile "/tmp/test3.txt"; #執行導出

22. Query OK, 6 rows affected (0.00 sec)

23. 
    

24. 
    

25. admin@localhost : xiaoboluo 05:18:02> system cat /tmp/test3.txt #查看導出的文本文件，可以發現null被轉義為\N了，這是為了避免數據字符串本身包含null值時無法正確區分數據類型的null值

26. 2 a string 100.20 \N

27. 4 a string containing a , comma 102.20 \N

28. 6 a string containing a " quote 102.20 \N

29. 8 a string containing a ", quote and comma 102.20 \N

30. 10 \\t 102.20 \N

31. 14 \\t 102.20 \N

32. 
    

33. 
    

34. # 導入數據，從結果中可以看到\N被正確解析為了數據類型的null值

35. admin@localhost : xiaoboluo 05:18:06> truncate test3;

36. Query OK, 0 rows affected (0.01 sec)

37. 
    

38. 
    

39. admin@localhost : xiaoboluo 05:20:36> load data infile '/tmp/test3.txt' into table test3;

40. Query OK, 6 rows affected (0.01 sec)

41. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

42. 
    

43. 
    

44. admin@localhost : xiaoboluo 05:20:52> select * from test3;

45. +----+------------------------------------------+--------+-------+

46. | id | test | test2 | test3 |

47. +----+------------------------------------------+--------+-------+

48. | 2 | a string | 100.20 | NULL |

49. | 4 | a string containing a , comma | 102.20 | NULL |

50. | 6 | a string containing a " quote | 102.20 | NULL |

51. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

52. | 10 | \t | 102.20 | NULL |

53. | 14 | \t | 102.20 | NULL |

54. +----+------------------------------------------+--------+-------+

55. 6 rows in set (0.00 sec)

56. 
    

57. 
    

58. # 如果FIELDS ENCLOSED BY不為空，FIELDS escaped BY為空時，則將NULL值的字面字符串作為輸出字符值。這與FIELDS ENCLOSED BY引用的字符串中包含的null值不同，\

59. 后者讀取為字符串'null'，而前者讀取到數據庫中時被當作數據類型的null值，而不是數據的字符串null

60. 
    

61. 
    

62. admin@localhost : xiaoboluo 05:20:57> update test3 set test3='null' where id=2; #更新id=2的test3列值為數據字符串的null

63. Query OK, 1 row affected (0.00 sec)

64. Rows matched: 1 Changed: 1 Warnings: 0

65. 
    

66. 
    

67. admin@localhost : xiaoboluo 05:23:14> select * from test3;

68. +----+------------------------------------------+--------+-------+

69. | id | test | test2 | test3 |

70. +----+------------------------------------------+--------+-------+

71. | 2 | a string | 100.20 | null |

72. | 4 | a string containing a , comma | 102.20 | NULL |

73. | 6 | a string containing a " quote | 102.20 | NULL |

74. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

75. | 10 | \t | 102.20 | NULL |

76. | 14 | \t | 102.20 | NULL |

77. +----+------------------------------------------+--------+-------+

78. 6 rows in set (0.00 sec)

79. 
    

80. 
    

81. admin@localhost : xiaoboluo 05:23:16> system rm -f /tmp/test3.txt;

82. admin@localhost : xiaoboluo 05:24:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '"' escaped BY ''; #指定行引用符號為雙引號"，轉義符為空導出數據

83. Query OK, 6 rows affected (0.00 sec)

84. 
    

85. 
    

86. admin@localhost : xiaoboluo 05:24:51> system cat /tmp/test3.txt #查看導出的文本文件，可以看到數據字符串的null被加了雙引號，而數據類型的null沒有加雙引號

87. "2" "a string" "100.20" "null"

88. "4" "a string containing a , comma" "102.20" NULL

89. "6" "a string containing a " quote" "102.20" NULL

90. "8" "a string containing a ", quote and comma" "102.20" NULL

91. "10" "\t" "102.20" NULL

92. "14" "\t" "102.20" NULL

93. 
    

94. 
    

95. admin@localhost : xiaoboluo 05:24:57> alter table test4 add column test3 varchar(10);

96. Query OK, 0 rows affected (0.04 sec)

97. Records: 0 Duplicates: 0 Warnings: 0

98. 
    

99. 
    

100. admin@localhost : xiaoboluo 05:26:40> truncate test4; #這里使用test4表做測試，避免無法導入的情況發生

101. Query OK, 0 rows affected (0.00 sec)

102. 
     

103. 
     

104. admin@localhost : xiaoboluo 05:26:44> load data infile '/tmp/test3.txt' into table test4 FIELDS ENCLOSED BY '"' escaped BY ''; #指定字段引用符為雙引號"，轉義符為空導入數據

105. Query OK, 6 rows affected (0.00 sec)

106. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

107. 
     

108. 
     

109. admin@localhost : xiaoboluo 05:27:02> select * from test4; #查看表test4中的數據，從結果中可以看到，數據導入正確

110. +----+------------------------------------------+--------+-------+

111. | id | test | test2 | test3 |

112. +----+------------------------------------------+--------+-------+

113. | 2 | a string | 100.20 | null |

114. | 4 | a string containing a , comma | 102.20 | NULL |

115. | 6 | a string containing a " quote | 102.20 | NULL |

116. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

117. | 10 | \t | 102.20 | NULL |

118. | 14 | \t | 102.20 | NULL |

119. +----+------------------------------------------+--------+-------+

120. 6 rows in set (0.00 sec)

121. 
     

122. 
     

123. # 使用固定行格式（當FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空時使用），將NULL寫為空字符串。這將導致表中的NULL值和空字符串在寫入文件時無法區分，\

124. 因為它們都以空字符串形式寫入文本文件。如果您需要能夠在讀取文件時將其分開，則不應使用固定行格式（即不應該使用FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空）

125. 
     

126. 
     

127. admin@localhost : xiaoboluo 05:29:11> system rm -f /tmp/test3.txt;

128. admin@localhost : xiaoboluo 05:29:22> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '' TERMINATED BY '';

129. Query OK, 6 rows affected (0.00 sec)

130. 
     

131. 
     

132. admin@localhost : xiaoboluo 05:29:43> system cat /tmp/test3.txt #從結果中看，是不是有點似曾相識呢？沒錯，前面演示過FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空的情況，\

133. 使用了固定格式來導出文本，但是這里多了數據類型的null值處理，從下面的結果中已經看不到數據類型的null了，被轉換為了空值(下面展示時把大段空格使用...代替)

134. 
     

135. 
     

136. 2 a string ... 100.20 ... null

137. 4 a string containing a , comma ... 102.20 ...

138. 6 a string containing a " quote ... 102.20 ...

139. 8 a string containing a ", quote and comma ... 102.20 ...

140. 10 \\t ... 102.20 ...

141. 14 \\t ... 102.20 ...

142. admin@localhost : xiaoboluo 05:29:46> truncate test4; #清空test4

143. Query OK, 0 rows affected (0.01 sec)

144. 
     

145. 
     

146. admin@localhost : xiaoboluo 05:34:15> load data infile "/tmp/test3.txt" into table test4 FIELDS ENCLOSED BY '' TERMINATED BY ''; #執行導入文本到test4表，注意：這是在sql_mode=''時導入的，\

147. 如果不修改sql_mode請使用local關鍵字

148. 
     

149. 
     

150. Query OK, 6 rows affected, 24 warnings (0.01 sec)

151. Records: 6 Deleted: 0 Skipped: 0 Warnings: 24

152. 
     

153. 
     

154. Note (Code 1265): Data truncated for column 'test' at row 1

155. Note (Code 1265): Data truncated for column 'test2' at row 1

156. Note (Code 1265): Data truncated for column 'test3' at row 1

157. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns

158. Note (Code 1265): Data truncated for column 'test' at row 2

159. Note (Code 1265): Data truncated for column 'test2' at row 2

160. Note (Code 1265): Data truncated for column 'test3' at row 2

161. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns

162. Note (Code 1265): Data truncated for column 'test' at row 3

163. Note (Code 1265): Data truncated for column 'test2' at row 3

164. Note (Code 1265): Data truncated for column 'test3' at row 3

165. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns

166. Note (Code 1265): Data truncated for column 'test' at row 4

167. Note (Code 1265): Data truncated for column 'test2' at row 4

168. Note (Code 1265): Data truncated for column 'test3' at row 4

169. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns

170. Note (Code 1265): Data truncated for column 'test' at row 5

171. Note (Code 1265): Data truncated for column 'test2' at row 5

172. Note (Code 1265): Data truncated for column 'test3' at row 5

173. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns

174. Note (Code 1265): Data truncated for column 'test' at row 6

175. Note (Code 1265): Data truncated for column 'test2' at row 6

176. Note (Code 1265): Data truncated for column 'test3' at row 6

177. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns

178. admin@localhost : xiaoboluo 05:34:35> select * from test4; #查詢test4表，從下面的結果中可以看到，原本test2字段的數據被導入到了test3字段，而test3字段的內容被截斷了。。

179. +----+----------------------------------------------------+----------------------------------------------------+------------+

180. | id | test | test2 | test3 |

181. +----+----------------------------------------------------+----------------------------------------------------+------------+

182. | 2 | a string | | 100.20 |

183. | 4 | a string containing a , comma | | 102.20 |

184. | 6 | a string containing a " quote | | 102.20 |

185. | 8 | a string containing a ", quote and comma | | 102.20 |

186. | 10 | \t | | 102.20 |

187. | 14 | \t | | 102.20 |

188. +----+----------------------------------------------------+----------------------------------------------------+------------+

189. 6 rows in set (0.00 sec)

190. 
     

191. 
     

192. # 注意：如果使用多字節字符集，固定大小格式可能不起作用（我在測試時使用的字符集是utf8，沒有測試出來這里說不起作用是啥意思）

• load data執行時如果表中有外鍵、輔助索引、唯一索引，那么會導致加載數據的時間變慢，因為索引也需要一同更新，可以使用對應參數關閉外鍵檢查、唯一索引檢查甚至關閉索引

  • 要在加載操作期間忽略外鍵約束，可以在執行load data語句之前執行SET foreign_key_checks = 0語句，執行完畢之后執行SET foreign_key_checks = 1或斷開會話重連

  • 要在加載操作期間忽略唯一索引約束，可以在執行load data語句之前執行set unique_checks=0語句，執行完畢之后執行set unique_checks=1或斷開會話重連

  • 在某些極端情況下（比如表中索引過多），您可以在執行load data語句之前通過執行ALTER TABLE … DISABLE KEYS語句關閉創建索引，在執行完load data語句之后執行ALTER TABLE … ENABLE KEYS來重新創建索引，注意該語句不能關閉主鍵索引

• 如果在sql_mode設置為嚴格模式下，且不使用local和ignore關鍵字時，碰到缺少字段值會直接報錯終止，但在sql_mode設置為嚴格模式下，使用了local和ignore關鍵字時，則行為與不使用嚴格模式類似??
  LOAD DATA INFILE將所有輸入視為字符串，因此您不能認為load data語句會像INSERT語句那樣插入ENUM或SET列的數值。所有ENUM和SET值必須指定為字符串

• LOAD DATA INFILE不支持的場景

  • 固定大小的行（FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空）不支持BLOB或TEXT列

  • 如果FIELDS TERMINATED BY和LINES STARTING BY指定相同的字符，則LOAD DATA INFILE無法正確解析

  • 如果FIELDS ESCAPED BY為空，則字段中包含了FIELDS ENCLOSED BY或LINES TERMINATED BY或FIELDS TERMINATED BY的字符時會導致LOAD DATA INFILE語句拒絕讀取字段并報錯。這是因為LOAD DATA INFILE無法正確確定字段或行在哪里結束

• PS：在Unix上，如果需要LOAD DATA從管道讀取數據，可以使用以下方法（該示例將/目錄的列表加載到表db1.t1中，find命令掛后臺持續查找內容并生成ls.dat文件，mysql 客戶端使用-e選項來執行load data這個文件到表）：

1. mkfifo /mysql/data/db1/ls.dat

2. chmod 666 /mysql/data/db1/ls.dat

3. find / -ls> /mysql/data/db1/ls.dat＆

4. mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

1.2.7. IGNORE number {LINES | ROWS}子句

• 忽略輸入文件中的前number行數據，使用子句ignore number lines指定忽略文本的前number行，在某些情況下生成的文本（如：mysql -e "select …." > xx.txt中）帶有字段名稱，在導入時會把這一行字段名稱也當作數據，所以需要忽略掉這行字段名稱

1. admin@localhost : xiaoboluo 05:34:41> system cat /tmp/test3.txt

2. id test test2 test3

3. 2 a string 100.20 null

4. 4 a string containing a , comma 102.20 NULL

5. 6 a string containing a " quote 102.20 NULL

6. 8 a string containing a ", quote and comma 102.20 NULL

7. 10 \\t 102.20 NULL

8. 14 \\t 102.20 NULL

9. admin@localhost : xiaoboluo 05:41:35> truncate test4;

10. Query OK, 0 rows affected (0.01 sec)

11. 
    

12. 
    

13. admin@localhost : xiaoboluo 05:41:41> load data infile "/tmp/test3.txt" into table test4 ignore 1 lines; #載入文本時指定ignore 1 lines子句忽略文本中的前1行數據

14. Query OK, 6 rows affected (0.00 sec)

15. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

16. 
    

17. 
    

18. admin@localhost : xiaoboluo 05:42:22> select * from test4; #查詢表test4中的數據，從下面的結果中可以看到數據正確

19. +----+------------------------------------------+--------+-------+

20. | id | test | test2 | test3 |

21. +----+------------------------------------------+--------+-------+

22. | 2 | a string | 100.20 | null |

23. | 4 | a string containing a , comma | 102.20 | NULL |

24. | 6 | a string containing a " quote | 102.20 | NULL |

25. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

26. | 10 | \t | 102.20 | NULL |

27. | 14 | \t | 102.20 | NULL |

28. +----+------------------------------------------+--------+-------+

29. 6 rows in set (0.00 sec)

• LOAD DATA INFILE可用于讀取外部數據源文件。例如，許多程序可以以逗號分隔的值（CSV）格式導出數據，字段用逗號分隔，并包含在雙引號內，并帶有一個字段列名的初始行。如果這樣一個文件中的數據行的換行符再使用回車符，則load data語句可以這樣編寫：

1. LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name

2. FIELDS TERMINATED BY ',' ENCLOSED BY '"'

3. LINES TERMINATED BY '\r\n'

4. IGNORE 1 LINES;

5. # 如果輸入值不一定包含在引號內，請在ENCLOSED BY關鍵字之前使用OPTIONALLY，如：OPTIONALLY ENCLOSED BY '"'，加上OPTIONALLY 可能會忽略數值類型的字段的引用符號，\

6. 另外，如果你的csv文件第一行是數據而不是列名，那就不能使用IGNORE 1 LINES子句

1.2.8. (col_name_or_user_var,…)指定字段名稱的子句

• 默認情況下，如果使用load data語句時表名后邊不帶字段，那么會把整個表的字段數據都導入到數據庫中，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata;

• 如果只想加載某些列，請指定列列表，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata（col1，col2，…）; ，要注意：如果輸入文件中的字段值順序與表中列的順序不同，你需要對load data語句中的tb_name后跟的字段順序做一下調整以對應文本文件中的字段順序。否則，MySQL不能判斷如何與表中的順序對齊，列出列名時可以在tb_name后指定具體的列名，也可以使用表達式生成值指定給某個列名(使用set語句指定一個表達式，復制給一個變量，詳見1.2.9小節)，如果沒有set語句，建議列名寫在tb_name表名后邊，方便理解，有set語句時就跟set語句寫在一起

• 鄭州不孕不育醫院：http://yyk.39.net/zz3/zonghe/1d427.html
  

  • 如果發現文件中的列順序和表中的列順序不符，或者只想加載部分列，在命令中加上列的順序時指定的字段名也不一定非要放在緊跟著表名，可以放在語句最后面也可以，如：load data infile "/tmp/filename.txt" into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);如果只需要導入一個字段id，則把 (id,content,name)換做(id)即可

  • 使用示例參考1.1小節的“如果文本文件中的數據字段與表結構中的字段定義順序不同，則使用如下語句指定載入表中的字段順序”演示部分

1.2.8. SET col_name = expr,…子句

• 將列做一定的數值轉換后再加載，使用子句set col_name = expr,.. 指定，要注意：col_name必須為表中真實的列名，expr可以是任意的表達式或者子查詢，只要返回的數據結果值能對應上表中的字段數據定義類型即可，注意，非set語句生成的列名，必須使用括號括起來，否則報語法錯誤。

1. # 如果系統將id列的文本數據加上10以后再加載到表的test3列中，可以如下操作：

2. 
   

3. admin@localhost : xiaoboluo 06:05:42> system rm -f /tmp/test3.txt;

4. admin@localhost : xiaoboluo 06:06:00> select * from test3 into outfile "/tmp/test3.txt";

5. Query OK, 6 rows affected (0.00 sec)

6. 
   

7. 
   

8. admin@localhost : xiaoboluo 06:06:04> system cat /tmp/test3.txt

9. 2 a string 100.20 null

10. 4 a string containing a , comma 102.20 \N

11. 6 a string containing a " quote 102.20 \N

12. 8 a string containing a ", quote and comma 102.20 \N

13. 10 \\t 102.20 \N

14. 14 \\t 102.20 \N

15. 
    

16. 
    

17. admin@localhost : xiaoboluo 06:07:49> truncate test4;

18. Query OK, 0 rows affected (0.01 sec)

19. 
    

20. 
    

21. admin@localhost : xiaoboluo 06:07:53> load data infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+10 ;

22. ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

23. admin@localhost : xiaoboluo 06:08:02> select * from test4; #嚴格模式下因為文本中多了一個字段被截斷了，所以拒絕導入

24. Empty set (0.00 sec)

25. 
    

26. 
    

27. admin@localhost : xiaoboluo 06:08:08> load data local infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+10 ; #可以使用local關鍵字強制進行截斷最后一個字段的null值列進行導入，\

28. 注意，如果不使用local關鍵字，那就需要修改sql_mode才能導入

29. 
    

30. 
    

31. Query OK, 6 rows affected, 6 warnings (0.01 sec)

32. Records: 6 Deleted: 0 Skipped: 0 Warnings: 6

33. 
    

34. 
    

35. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns

36. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns

37. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns

38. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns

39. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns

40. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns

41. admin@localhost : xiaoboluo 06:10:45> select * from test4;

42. +----+------------------------------------------+--------+-------+

43. | id | test | test2 | test3 |

44. +----+------------------------------------------+--------+-------+

45. | 2 | a string | 100.20 | 12 |

46. | 4 | a string containing a , comma | 102.20 | 14 |

47. | 6 | a string containing a " quote | 102.20 | 16 |

48. | 8 | a string containing a ", quote and comma | 102.20 | 18 |

49. | 10 | \t | 102.20 | 20 |

50. | 14 | \t | 102.20 | 24 |

51. +----+------------------------------------------+--------+-------+

52. 6 rows in set (0.00 sec)

53. 
    

54. 
    

55. 
    

56. 
    

57. # 或者使用txt文件中的某些列進行計算后生成新的列插入，這里演示兩個字段進行相加后導入另外一個字段中：

58. admin@localhost : xiaoboluo 06:18:37> load data local infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+test2 ; # 注意，如果不使用local關鍵字，那就需要修改sql_mode才能導入

59. Query OK, 6 rows affected, 6 warnings (0.00 sec)

60. Records: 6 Deleted: 0 Skipped: 0 Warnings: 6

61. 
    

62. 
    

63. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns

64. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns

65. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns

66. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns

67. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns

68. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns

69. admin@localhost : xiaoboluo 06:19:07> select * from test4;

70. +----+------------------------------------------+--------+-------+

71. | id | test | test2 | test3 |

72. +----+------------------------------------------+--------+-------+

73. | 2 | a string | 100.20 | 102.2 |

74. | 4 | a string containing a , comma | 102.20 | 106.2 |

75. | 6 | a string containing a " quote | 102.20 | 108.2 |

76. | 8 | a string containing a ", quote and comma | 102.20 | 110.2 |

77. | 10 | \t | 102.20 | 112.2 |

78. | 14 | \t | 102.20 | 116.2 |

79. +----+------------------------------------------+--------+-------+

80. 6 rows in set (0.00 sec)

• SET子句中使用用戶變量，用戶變量可以以多種方式使用

1. # 可以直接使用一個用戶變量并進行計算(計算表達式可以使用函數、運算符、子查詢等都允許)，然后賦值給test4列直接導入，而不需要從文件中讀取test4列數據，該列數據也允許在文件中不存在

2. admin@localhost : xiaoboluo 06:27:06> alter table test4 add column test4 varchar(20); #新建一個字段test4，用于導入set子句計算的值

3. Query OK, 0 rows affected (0.01 sec)

4. Records: 0 Duplicates: 0 Warnings: 0

5. 
   

6. 
   

7. admin@localhost : xiaoboluo 06:27:56> truncate test4;

8. Query OK, 0 rows affected (0.01 sec)

9. 
   

10. 
    

11. admin@localhost : xiaoboluo 06:28:02> set @test=200; #設置一個用戶變量

12. Query OK, 0 rows affected (0.00 sec)

13. 
    

14. 
    

15. admin@localhost : xiaoboluo 06:30:32> load data infile "/tmp/test3.txt" into table test4 (id,test,test2,test3) set test4=round(@test/100,0) ; #執行導入，使用set子句導入test4列通過表達式\

16. round(@test/100,0)計算之后的值

17. 
    

18. 
    

19. Query OK, 6 rows affected (0.00 sec)

20. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

21. 
    

22. 
    

23. admin@localhost : xiaoboluo 06:30:52> select * from test4; #查看test4表中導入的數據，從以下結果中來看，導入數據正確

24. +----+------------------------------------------+--------+-------+-------+

25. | id | test | test2 | test3 | test4 |

26. +----+------------------------------------------+--------+-------+-------+

27. | 2 | a string | 100.20 | null | 2 |

28. | 4 | a string containing a , comma | 102.20 | NULL | 2 |

29. | 6 | a string containing a " quote | 102.20 | NULL | 2 |

30. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2 |

31. | 10 | \t | 102.20 | NULL | 2 |

32. | 14 | \t | 102.20 | NULL | 2 |

33. +----+------------------------------------------+--------+-------+-------+

34. 6 rows in set (0.00 sec)

35. 
    

36. 
    

37. # SET子句可以將一個內部函數返回的值直接導入到一個指定列

38. admin@localhost : xiaoboluo 06:31:22> truncate test4;

39. Query OK, 0 rows affected (0.01 sec)

40. 
    

41. 
    

42. admin@localhost : xiaoboluo 06:40:58> load data infile "/tmp/test3.txt" into table test4 (id,test,test2,test3) set test4=now() ;

43. 
    

44. Query OK, 6 rows affected (0.00 sec)

45. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

46. 
    

47. 
    

48. admin@localhost : xiaoboluo 06:41:02> select * from test4;

49. +----+------------------------------------------+--------+-------+---------------------+

50. | id | test | test2 | test3 | test4 |

51. +----+------------------------------------------+--------+-------+---------------------+

52. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |

53. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |

54. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |

55. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |

56. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

57. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

58. +----+------------------------------------------+--------+-------+---------------------+

59. 6 rows in set (0.00 sec)

• 使用指定列名或者變量列表時SET子句的使用受以下限制：

  • SET子句中的賦值表達式賦值運算符的左側只能使用數據庫表中的真實列名

  • 您可以在SET子句中的右側使用子查詢。返回要分配給列的值的子查詢可能僅是標量子查詢。此外，在這個子查詢中您不能使用load data語句正在操作的表

  • SET子句不會處理IGNORE子句忽略的行。

  • 用固定行格式加載數據時，不能使用用戶變量，因為用戶變量值之間沒有顯示寬度

• 如果輸入行的字段太多（多過表中的字段數量），則會忽略額外的字段，并增加警告數。如果輸入行的字段太少，那么輸入字段缺少的表列被設置為其默認值，在解析文本文件時，空串字段數據與缺少字段值不同（空串會直接作為數據插入，而缺少字段時，會根據字段定義的默認值進行填充），如下：

  • 對于字符串類型，列設置為空字符串

  • 對于數字類型，列設置為0

  • 對于日期和時間類型，列將該類型設置為適當的“零”值

2、批量導出和批量導入

• 使用mysqldump批量導出生成表的txt文件，并使用mysqlimport批量導入表的txt文件到數據庫中

2.1. 使用mysqldump批量導出

• 使用mysqldump導出數據為文本的語法如下：

1. mysqldump -u username -p'xxx' -T target_dir db_name tb_name [option];

• 其中option參數是以下幾種可選參數：??
  --fields-terminated-by 'string' 字段分隔符??
  --fields-enclosed-by 'char' 字段引用符??
  --fields-optionally-enclosed-by 'char' 字段引用符，只在char,varchar,text等字段類型上生效??
  --fields-escaped-by 'char' 轉義字符??
  --lines-terminated-by 'string' 記錄結束符，即換行符

• 示例

1. $ mkdir /data/backup/

2. $ chown mysql.mysql /data/backup -R

3. $ mysqldump -uadmin -pletsg0 -h 10.10.30.241 --single-transaction --master-data=2 --triggers --routines --events xiaoboluo -T /data/backup/

4. mysqldump: [Warning] Using a password on the command line interface can be insecure.

5. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.\

6. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

7. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

8. SET @@SESSION.SQL_LOG_BIN= 0;

9. 
   

10. 
    

11. --

12. -- GTID state at the beginning of the backup

13. --

14. 
    

15. 
    

16. SET @@GLOBAL.GTID_PURGED='2016f827-2d98-11e7-bb1e-00163e407cfb:1-114';

17. 
    

18. 
    

19. --

20. -- Position to start replication or point-in-time recovery from

21. --

22. 
    

23. 
    

24. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=21737; #使用-T選項時，--master-data=2打印的binlog pos信息會直接打印在標準輸出上

25. 
    

26. 
    

27. --

28. -- Dumping events for database 'xiaoboluo'

29. --

30. 
    

31. 
    

32. --

33. -- Dumping routines for database 'xiaoboluo'

34. --

35. SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

36. 
    

37. 
    

38. $ ll /data/backup/ #可以看到mysqldump -T選項批量導出的表數據除了文本文件之外，還同時備份了表結構

39. total 32

40. -rw-r--r-- 1 root root 1526 May 3 22:45 test2.sql

41. -rw-rw-rw- 1 mysql mysql 286 May 3 22:45 test2.txt

42. -rw-r--r-- 1 root root 1549 May 3 22:45 test3.sql

43. -rw-rw-rw- 1 mysql mysql 194 May 3 22:45 test3.txt

44. -rw-r--r-- 1 root root 1600 May 3 22:45 test4.sql

45. -rw-rw-rw- 1 mysql mysql 314 May 3 22:45 test4.txt

46. -rw-r--r-- 1 mysql mysql 1493 May 3 22:45 test.sql

47. -rw-rw-rw- 1 mysql mysql 95 May 3 22:45 test.txt

2.2. 使用mysqimport批量導出

2.2.1. mysqlimport語法及其參數說明

• mysqlimport實用程序加載數據文件時，它通過向服務器發送LOAD DATA INFILE語句來實現(它實際是客戶端提供了load data infile語句的一個命令行接口)，可以使用--local選項使mysqlimport從客戶端主機（而不是mysql server主機）讀取數據文件。如果客戶端和服務器支持壓縮協議，則可以指定--compress選項以在慢速網絡中獲得更好的性能。

• 使用mysqlimport命令，語法如下：

1. mysqlimport -uroot -p 'xxx' [--local] db_name order_tab.txt [iption]

• 其中，option參數可以是如下選項

1. --fields-terminated-by=name 指定字段分隔符

2. --fields-enclosed-by=name 指定字段引用符

3. --fields-optionally-enclosed-by=name 指定字段引用符，但只在char、varchar、text字段上使用引用符

4. --fields-escaped-by=name 指定轉義字符

5. --lines-terminated-by=name 指定行記錄結束符（換行符）

6. --ignore-liens=number 忽略前幾行

7. --low-priority 碰到有其他線程update操作操作的表與導入操作表相同時，延遲執行導入操作

8. -i, --ignore 如果碰到唯一鍵沖突就忽略沖突行導入

9. 
   

10. -r, --replace 如果碰到唯一鍵沖突就覆蓋沖突行導入

11. 
    

12. -L, --local 從客戶端主機加載數據文本文件

13. 
    

14. -C, --compress 在C/S模型之間使用壓縮傳輸數據

15. 
    

16. -c, --columns=name 指定需要導入哪些列，與load data語句中一樣需要指定表定義中真實的列名，有多個列名時使用逗號分隔

17. 
    

18. --default-character-set=name 設置使用該選項指定的字符集來解析文本文件中的內容

19. 
    

20. -h, --host 指定導入server的主機IP

21. 
    

22. -p, --password[=name] 指定導入server的用戶密碼

23. 
    

24. -P, --port=# 指定導入server的監聽端口

25. 
    

26. --use-threads=# 指定多少個線程并發執行load data語句（實測單表時指定多線程時要比單線程要快，由于數據量小，測試出來的差別并不大，官方并沒有說明是基于什么級別的并發，\

27. 只寫了一句：Load files in parallel using N threads，推測可能是基于類似mydumper的并發，但是多表導入時指定多線程就明顯比單線程要快很多）

28. 
    

29. 
    

30. 
    

31. -u, --user=name 指定導入server的用戶名

32. 
    

33. -d, --delete 指定導入操作之前先把表清空（實測重復導入時加了這個選項之后可以正常執行，，通過解析binlog發現，發現binlog中記錄的第二次和第一次導入的語句完全相同是，\

34. 第二次導入時如果發現表中有沖突數據，就先執行的不帶where條件的delete，所有表先delete掉，然后再執行load data語句導入數據，另外，當與replace一起使用時，忽略replace選項）

2.2.2. mysqlimport用法演示示例

• 先執行清理server中表的數據

1. 
   

2. admin@localhost : (none) 11:08:58> use xiaoboluo

3. Database changed

4. admin@localhost : xiaoboluo 11:09:00> show tables;

5. +---------------------+

6. | Tables_in_xiaoboluo |

7. +---------------------+

8. | test |

9. | test2 |

10. | test3 |

11. | test4 |

12. +---------------------+

13. 4 rows in set (0.00 sec)

14. 
    

15. 
    

16. admin@localhost : xiaoboluo 11:09:01> select * from test;

17. +----+------+-------+

18. | id | test | test2 |

19. +----+------+-------+

20. | 2 | 1 | 2 |

21. | 4 | 2 | NULL |

22. | 6 | null | NULL |

23. | 8 | 4 | NULL |

24. | 10 | | NULL |

25. | 12 | \\t | NULL |

26. | 14 | t | NULL |

27. | 16 | \t | NULL |

28. | 18 | t | NULL |

29. | 20 | NULL | NULL |

30. | 22 | "t | NULL |

31. +----+------+-------+

32. 11 rows in set (0.00 sec)

33. 
    

34. 
    

35. admin@localhost : xiaoboluo 11:09:11> system cat /data/backup/test.txt;

36. 2 1 2

37. 4 2 \N

38. 6 null \N

39. 8 4 \N

40. 10 \ \N

41. 12 \\\\t \N

42. 14 t \N

43. 16 \\t \N

44. 18 t \N

45. 20 \N \N

46. 22 "t \N

47. 
    

48. 
    

49. admin@localhost : xiaoboluo 11:12:08> select * from test2;

50. +----+------+-------+---------------------+

51. | id | test | test2 | dt |

52. +----+------+-------+---------------------+

53. | 2 | 1 | 2 | 2017-05-02 18:47:03 |

54. | 4 | 2 | NULL | 2017-05-02 18:47:03 |

55. | 6 | null | NULL | 2017-05-02 18:47:03 |

56. | 8 | 4 | NULL | 2017-05-02 18:47:03 |

57. | 10 | | NULL | 2017-05-02 18:47:03 |

58. | 12 | \\t | NULL | 2017-05-02 18:47:03 |

59. | 14 | t | NULL | 2017-05-02 18:47:03 |

60. | 16 | \t | NULL | 2017-05-02 18:47:03 |

61. | 18 | t | NULL | 2017-05-02 18:47:03 |

62. | 20 | NULL | NULL | 2017-05-02 18:47:03 |

63. +----+------+-------+---------------------+

64. 10 rows in set (0.00 sec)

65. 
    

66. 
    

67. admin@localhost : xiaoboluo 11:12:15> system cat /data/backup/test2.txt;

68. 2 1 2 2017-05-02 18:47:03

69. 4 2 \N 2017-05-02 18:47:03

70. 6 null \N 2017-05-02 18:47:03

71. 8 4 \N 2017-05-02 18:47:03

72. 10 \ \N 2017-05-02 18:47:03

73. 12 \\\\t \N 2017-05-02 18:47:03

74. 14 t \N 2017-05-02 18:47:03

75. 16 \\t \N 2017-05-02 18:47:03

76. 18 t \N 2017-05-02 18:47:03

77. 20 \N \N 2017-05-02 18:47:03

78. admin@localhost : xiaoboluo 11:12:27> truncate test2;

79. Query OK, 0 rows affected (0.00 sec)

80. 
    

81. 
    

82. admin@localhost : xiaoboluo 11:12:32> select * from test3;

83. +----+------------------------------------------+--------+-------+

84. | id | test | test2 | test3 |

85. +----+------------------------------------------+--------+-------+

86. | 2 | a string | 100.20 | null |

87. | 4 | a string containing a , comma | 102.20 | NULL |

88. | 6 | a string containing a " quote | 102.20 | NULL |

89. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

90. | 10 | \t | 102.20 | NULL |

91. | 14 | \t | 102.20 | NULL |

92. +----+------------------------------------------+--------+-------+

93. 6 rows in set (0.00 sec)

94. 
    

95. 
    

96. admin@localhost : xiaoboluo 11:12:44> system cat /data/backup/test3.txt;

97. 2 a string 100.20 null

98. 4 a string containing a , comma 102.20 \N

99. 6 a string containing a " quote 102.20 \N

100. 8 a string containing a ", quote and comma 102.20 \N

101. 10 \\t 102.20 \N

102. 14 \\t 102.20 \N

103. admin@localhost : xiaoboluo 11:12:59> truncate test3;

104. Query OK, 0 rows affected (0.01 sec)

105. 
     

106. 
     

107. admin@localhost : xiaoboluo 11:13:03> select * from test4;

108. +----+------------------------------------------+--------+-------+---------------------+

109. | id | test | test2 | test3 | test4 |

110. +----+------------------------------------------+--------+-------+---------------------+

111. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |

112. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |

113. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |

114. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |

115. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

116. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

117. +----+------------------------------------------+--------+-------+---------------------+

118. 6 rows in set (0.00 sec)

119. 
     

120. 
     

121. admin@localhost : xiaoboluo 11:13:15> system cat /data/backup/test4.txt;

122. 2 a string 100.20 null 2017-05-03 18:41:02

123. 4 a string containing a , comma 102.20 \N 2017-05-03 18:41:02

124. 6 a string containing a " quote 102.20 \N 2017-05-03 18:41:02

125. 8 a string containing a ", quote and comma 102.20 \N 2017-05-03 18:41:02

126. 10 \\t 102.20 \N 2017-05-03 18:41:02

127. 14 \\t 102.20 \N 2017-05-03 18:41:02

128. admin@localhost : xiaoboluo 11:13:24> truncate test4;

129. Query OK, 0 rows affected (0.01 sec)

130. 
     

131. 
     

132. admin@localhost : xiaoboluo 11:13:28> flush logs;

133. Query OK, 0 rows affected (0.01 sec)

2.2.2.1. 單表導入

• 使用mysqlimport命令導入單張表

1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 ~]# mysqlimport -uadmin -pletsg0 -h20.10.30.241 xiaoboluo /data/backup/test.txt

2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.

3. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0

4. 
   

5. 
   

6. # 查看數據庫中的數據

7. admin@localhost : xiaoboluo 11:13:42> select * from test;

8. +----+------+-------+

9. | id | test | test2 |

10. +----+------+-------+

11. | 2 | 1 | 2 |

12. | 4 | 2 | NULL |

13. | 6 | null | NULL |

14. | 8 | 4 | NULL |

15. | 10 | | NULL |

16. | 12 | \\t | NULL |

17. | 14 | t | NULL |

18. | 16 | \t | NULL |

19. | 18 | t | NULL |

20. | 20 | NULL | NULL |

21. | 22 | "t | NULL |

22. +----+------+-------+

23. 11 rows in set (0.00 sec)

• 解析binlog查看里邊如何記錄的

1. $ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000006

2. .....

3. BEGIN

4. /*!*/;

5. # at 344

6. #170503 23:15:29 server id 3306241 end_log_pos 443 CRC32 0x4c1c8e8a Rows_query

7. # LOAD DATA INFILE '/data/backup/test.txt' INTO TABLE `test` IGNORE 0 LINES #mysqlimport內部調用的load data語句在這里

8. # at 443

9. #170503 23:15:29 server id 3306241 end_log_pos 501 CRC32 0x1ddc6d53 Table_map: `xiaoboluo`.`test` mapped to number 304

10. # at 501

11. #170503 23:15:29 server id 3306241 end_log_pos 631 CRC32 0xa8c4beab Write_rows: table id 304 flags: STMT_END_F

12. ### INSERT INTO `xiaoboluo`.`test` #由于binlog_format=row，所以寫到binlog中時內部把load data語句轉換為了row格式

13. ### SET

14. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */

15. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

16. ### @3='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

17. ### INSERT INTO `xiaoboluo`.`test`

18. ### SET

19. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */

20. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

21. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

22. ### INSERT INTO `xiaoboluo`.`test`

23. ### SET

24. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */

25. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

26. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

27. ### INSERT INTO `xiaoboluo`.`test`

28. ### SET

29. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */

30. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

31. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

32. ### INSERT INTO `xiaoboluo`.`test`

33. ### SET

34. ### @1=10 /* INT meta=0 nullable=0 is_null=0 */

35. ### @2='\x09' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

36. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

37. ### INSERT INTO `xiaoboluo`.`test`

38. ### SET

39. ### @1=12 /* INT meta=0 nullable=0 is_null=0 */

40. ### @2='\x5c\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

41. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

42. ### INSERT INTO `xiaoboluo`.`test`

43. ### SET

44. ### @1=14 /* INT meta=0 nullable=0 is_null=0 */

45. ### @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

46. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

47. ### INSERT INTO `xiaoboluo`.`test`

48. ### SET

49. ### @1=16 /* INT meta=0 nullable=0 is_null=0 */

50. ### @2='\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

51. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

52. ### INSERT INTO `xiaoboluo`.`test`

53. ### SET

54. ### @1=18 /* INT meta=0 nullable=0 is_null=0 */

55. ### @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

56. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

57. ### INSERT INTO `xiaoboluo`.`test`

58. ### SET

59. ### @1=20 /* INT meta=0 nullable=0 is_null=0 */

60. ### @2=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

61. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

62. ### INSERT INTO `xiaoboluo`.`test`

63. ### SET

64. ### @1=22 /* INT meta=0 nullable=0 is_null=0 */

65. ### @2='"t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

66. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */

67. # at 631

68. #170503 23:15:29 server id 3306241 end_log_pos 662 CRC32 0x0cd1a6ae Xid = 756

69. COMMIT/*!*/;

70. ......

2.2.2.2.多表導入

• 清理掉test表，并刷新一下binlog

1. admin@localhost : xiaoboluo 11:32:19> truncate test;

2. Query OK, 0 rows affected (0.01 sec)

3. 
   

4. 
   

5. admin@localhost : xiaoboluo 11:35:09> flush logs;

6. Query OK, 0 rows affected (0.01 sec)

• 使用mysqlimport導入多表

1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h20.10.30.241 --replace xiaoboluo /data/backup/*.txt

2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.

3. xiaoboluo.test2: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

4. xiaoboluo.test3: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

5. xiaoboluo.test4: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

6. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0

7. 
   

8. 
   

9. real 0m0.014s

10. user 0m0.002s

11. sys 0m0.002s

12. 
    

13. 
    

14. # 多表導入時可以使用參數--use-threads指定多個線程，明顯比單線程導入速度要快

15. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h20.10.30.241 --replace --use-threads=8 xiaoboluo /data/backup/*.txt

16. mysqlimport: [Warning] Using a password on the command line interface can be insecure.

17. xiaoboluo.test3: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

18. xiaoboluo.test2: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

19. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0

20. xiaoboluo.test4: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

21. 
    

22. 
    

23. real 0m0.007s

24. user 0m0.006s

25. sys 0m0.002s

• 校驗數據

1. admin@localhost : xiaoboluo 11:35:15> select * from test;

2. +----+------+-------+

3. | id | test | test2 |

4. +----+------+-------+

5. | 2 | 1 | 2 |

6. | 4 | 2 | NULL |

7. | 6 | null | NULL |

8. | 8 | 4 | NULL |

9. | 10 | | NULL |

10. | 12 | \\t | NULL |

11. | 14 | t | NULL |

12. | 16 | \t | NULL |

13. | 18 | t | NULL |

14. | 20 | NULL | NULL |

15. | 22 | "t | NULL |

16. +----+------+-------+

17. 11 rows in set (0.00 sec)

18. 
    

19. 
    

20. admin@localhost : xiaoboluo 11:40:31> select * from test2;

21. +----+------+-------+---------------------+

22. | id | test | test2 | dt |

23. +----+------+-------+---------------------+

24. | 2 | 1 | 2 | 2017-05-02 18:47:03 |

25. | 4 | 2 | NULL | 2017-05-02 18:47:03 |

26. | 6 | null | NULL | 2017-05-02 18:47:03 |

27. | 8 | 4 | NULL | 2017-05-02 18:47:03 |

28. | 10 | | NULL | 2017-05-02 18:47:03 |

29. | 12 | \\t | NULL | 2017-05-02 18:47:03 |

30. | 14 | t | NULL | 2017-05-02 18:47:03 |

31. | 16 | \t | NULL | 2017-05-02 18:47:03 |

32. | 18 | t | NULL | 2017-05-02 18:47:03 |

33. | 20 | NULL | NULL | 2017-05-02 18:47:03 |

34. +----+------+-------+---------------------+

35. 10 rows in set (0.00 sec)

36. 
    

37. 
    

38. admin@localhost : xiaoboluo 11:40:33> select * from test3;

39. +----+------------------------------------------+--------+-------+

40. | id | test | test2 | test3 |

41. +----+------------------------------------------+--------+-------+

42. | 2 | a string | 100.20 | null |

43. | 4 | a string containing a , comma | 102.20 | NULL |

44. | 6 | a string containing a " quote | 102.20 | NULL |

45. | 8 | a string containing a ", quote and comma | 102.20 | NULL |

46. | 10 | \t | 102.20 | NULL |

47. | 14 | \t | 102.20 | NULL |

48. +----+------------------------------------------+--------+-------+

49. 6 rows in set (0.00 sec)

50. 
    

51. 
    

52. admin@localhost : xiaoboluo 11:40:34> select * from test4;

53. +----+------------------------------------------+--------+-------+---------------------+

54. | id | test | test2 | test3 | test4 |

55. +----+------------------------------------------+--------+-------+---------------------+

56. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |

57. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |

58. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |

59. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |

60. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

61. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |

62. +----+------------------------------------------+--------+-------+---------------------+

63. 6 rows in set (0.00 sec)

• 解析binlog查看（由于內容較多，這里就不貼出來了，binlog解析的多表導入操作在binlog中記錄的就是一個表一個load data語句）

3、總結

• 關于使用local子句與不使用local子句的時候的差異

  • 如果load data語句使用了local子句，則客戶端使用TCP遠程連接mysql server時，沒有file權限仍然能夠導入文本文件，這個時候是非常危險的，因為local子句的內部原理是從客戶端的主機讀取文本文件并傳送到server端的/tmp目錄并保存為一個臨時文件，再執行load data語句的。另外，要使用local子句，還需要看server端啟動是否關閉了local_infile選項（如果不指定該選項，則服務端默認為ON），mysql client連接時是否關閉了local_infile選項（如果不指定該選項，則客戶端默認為ON），local_infile在server或client端任意一端關閉都不能使用local子句，會報錯誤：ERROR 1148 (42000): The used command is not allowed with this MySQL version

  • 如果load data語句不使用local子句，則這個時候用戶必須要有file權限才能夠執行導入文本文件（并且只能夠導入server端的本地文本文件），如果沒有file權限，可能報沒有file權限的錯誤，也可能報錯：ERROR 1045 (28000): Access denied for user 'test'@'%' (using password: YES)

  • 如果不想這么麻煩（因為要限制客戶端使用local子句在沒有file權限的時候使用load data語句，需要在server端使用local_infile=OFF來關閉，不使用local子句時，如果用戶沒有file權限，那很顯然不能夠使用load data語句，但是如果還想限制由具有file權限的用戶怎么辦？），可以使用參數secure_file_priv=null，設置為null時，全面禁止使用load data語句（不管使用local子句還是不使用都不允許執行load data語句）

• 強調一點：在mysql的主備復制架構中，load data語句被認為是不安全的，要使得load data語句安全地進行復制，在binlog_format=mixed格式下會轉為row格式記錄，在binlog_format=statement時執行load data語句不會發出警告，而是內部通過一些列的流程來處理。具體是如何處理的呢，請看下回分解《load data語句如何保證主備復制數據一致性》