【MySQL】loaddata語句詳解（二）

作者：羅小波

沃趣科技高級MySQL數(shù)據(jù)庫工程師

1.2.6. FIELDS（與COLUMNS關(guān)鍵字相同）和LINES子句

以下示例中的char代表單個字符，string代表字符串（即多個字符），load data語句中，轉(zhuǎn)義字符和字段引用符只能使用單個字符，字段分隔符、行分隔符、行前綴字符都可以使用多個字符（字符串）
對于LOAD DATA INFILE和SELECT … INTO OUTFILE語句中，F(xiàn)IELDS和LINES子句的語法完全相同。兩個子句在LOAD DATA INFILE和SELECT … INTO OUTFILE語句中都是可選的，但如果兩個子句都被指定，則FIELDS必須在LINES之前，否則報語法錯誤
從網(wǎng)站建設(shè)到定制行業(yè)解決方案，為提供成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)服務(wù)體系，各種行業(yè)企業(yè)客戶提供網(wǎng)站建設(shè)解決方案，助力業(yè)務(wù)快速發(fā)展。成都創(chuàng)新互聯(lián)公司將不斷加快創(chuàng)新步伐，提供優(yōu)質(zhì)的建站服務(wù)。
- FIELDS關(guān)鍵字共有三個子句，TERMINATED BY 'string'指定字段分隔符，[OPTIONALLY] ENCLOSED BY 'char'指定字段引用符（其中使用了OPTIONALLY關(guān)鍵字之后，只在char、varchar和text等字符型字段上加字段引用符，數(shù)值型的不會加字段引用符，且OPTIONALLY 關(guān)鍵字只在導(dǎo)出數(shù)據(jù)時才起作用，導(dǎo)入數(shù)據(jù)時用于不用對數(shù)據(jù)沒有影響），ESCAPED BY 'char'指定轉(zhuǎn)義符，如果您指定了一個FIELDS子句，則它的每個子句也是可選的，但在你指定了FIELDS關(guān)鍵字之后，這個關(guān)鍵字的子句至少需要指定一個，后續(xù)章節(jié)會進行舉例說明
- LINES關(guān)鍵字共有兩個子句，STARTING BY 'string'指定行前綴字符，TERMINATED BY 'string'指定行分隔符（換行符），如果你指定了LINES關(guān)鍵字，則LINES的子句都是可選的，但在你指定了LINES關(guān)鍵字之后，這個關(guān)鍵字的子句至少需要指定一個，后續(xù)章節(jié)會進行舉例說明
- 如果在導(dǎo)入和導(dǎo)出時沒有指定FIELDS和LINES子句，則導(dǎo)入和導(dǎo)出時兩個子句的默認值相同，默認的字段分隔符為\t，行分隔符為\n（win上默認為\r\n，記事本程序上默認為\r），字段引用符為空，行前綴字符為空
當mysql server導(dǎo)出文本數(shù)據(jù)到文件時，F(xiàn)IELDS和LINES默認值時SELECT … INTO OUTFILE在輸出文本數(shù)據(jù)時行為如下：
- 在文本數(shù)據(jù)各字段之間使用制表符來作為字段分隔符
- 不使用任何引號來包圍文本數(shù)據(jù)的各字段值，即字段引用符為空
- 使用\轉(zhuǎn)義在字段值中出現(xiàn)的制表符\t，換行符\n或轉(zhuǎn)義符本身\等特殊字符(即輸出的文本數(shù)據(jù)中對這些特殊字符前多加一個反斜杠)
- 在行尾寫上換行符\n，即使用\n作為行分隔符（換行符）
- 注意：如果您在Windows系統(tǒng)上生成了文本文件，則可能必須使用LINES TERMINATED BY '\r\n'來正確讀取文件，因為Windows程序通常使用兩個字符作為行終止符。某些程序（如寫字板）在寫入文件時可能會使用\r作為行終止符(要讀取這些文件，請使用LINES TERMINATED BY '\r')
- FIELDS和LINES子句默認值時生成的純文本數(shù)據(jù)文件可以使用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從文本文件讀取數(shù)據(jù)時，F(xiàn)IELDS和LINES默認值會導(dǎo)致LOAD DATA INFILE的行為如下：
- 尋找換行邊界字符\n來進行換行
- 不跳過行前綴，把行前綴也當作數(shù)據(jù)（發(fā)生在如果導(dǎo)出數(shù)據(jù)時使用了行前綴，導(dǎo)入時沒有指定正確的行前綴或者根本沒有指定行前綴選項時）
- 使用制表符\t來分割一行數(shù)據(jù)中的各列
- 要注意：在FIELDS和LINES的默認值下，在解析文本文件時不會把字符串之間的引號當作真正的引號，而是當作數(shù)據(jù)

1.2.6.1. FIELDS關(guān)鍵字及其子句詳解

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

	
			
					
			
				
				
					admin@localhost : xiaoboluo 03:08:34> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ','; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 03:08:37> system cat /tmp/test3.txt
				
				
					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

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

	
			
					
			
				
				
					# 指定字段引用符為"，不使用optionally關(guān)鍵字 
				
				
					admin@localhost : xiaoboluo 03:33:33> system rm -f /tmp/test3.txt;
				
				
					admin@localhost : xiaoboluo 03:37:21> select * from test3 into outfile "/tmp/test3.txt" FIELDS  ENCLOSED BY '"'; 
				
				
					Query OK, 5 rows affected (0.00 sec)
				
				
					admin@localhost : xiaoboluo 03:37:33> system cat /tmp/test3.txt
				
				
					"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"
				
				
					# 指定字段引用符為"，使用optionally關(guān)鍵字，可以看到id列的字段引用符去掉了
				
				
					admin@localhost : xiaoboluo 03:37:41> system rm -f /tmp/test3.txt;
				
				
					admin@localhost : xiaoboluo 03:40:53> select * from test3 into outfile "/tmp/test3.txt" FIELDS optionally  ENCLOSED BY '"'; 
				
				
					Query OK, 5 rows affected (0.00 sec)
				
				
					admin@localhost : xiaoboluo 03:41:03> system cat /tmp/test3.txt
				
				
					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

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

	
			
					
			
				
				
					admin@localhost : xiaoboluo 03:42:41> system rm -f /tmp/test3.txt; 
				
				
					admin@localhost : xiaoboluo 03:44:18> select * from test3 into outfile "/tmp/test3.txt" fields escaped by '.'; 
				
				
					Query OK, 5 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 03:44:25> system cat /tmp/test3.txt # 可以看到數(shù)據(jù)中指定的轉(zhuǎn)義符.號被轉(zhuǎn)義了，而數(shù)據(jù)\t沒有被轉(zhuǎn)義
				
				
					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
				
				
					admin@localhost : xiaoboluo 03:44:28> truncate test3; #清空表
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 03:45:19> load data infile "/tmp/test3.txt" into table test3 fields escaped by '.'; #導(dǎo)入數(shù)據(jù)時指定轉(zhuǎn)義符為.號
				
				
					Query OK, 5 rows affected (0.00 sec) 
				
				
					Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 03:45:40> select * from test3; #校驗數(shù)據(jù)，可以看到導(dǎo)入數(shù)據(jù)正常
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					5 rows in set (0.00 sec)

1.2.6.2. LINES 關(guān)鍵字及其子句詳解

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

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

	
			
					
			
				
				
					# load data語句如下 
				
				
					admin@localhost : xiaoboluo 03:48:04> system rm -f /tmp/test3.txt; 
				
				
					admin@localhost : xiaoboluo 03:54:54> select * from test3 into outfile "/tmp/test3.txt" LINES STARTING BY 'xxx'; 
				
				
					Query OK, 5 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 03:55:03> system cat /tmp/test3.txt #可以看到每行數(shù)據(jù)前面多了個行前綴字符串xxx
				
				
					xxx2 a string 100.20
				
				
					xxx4 a string containing a , comma 102.20
				
				
					xxx6 a string containing a " quote 102.20 
				
				
					xxx8 a string containing a ", quote and comma 102.20
				
				
					xxx10 \\t 102.20
				
				
					# 現(xiàn)在，到shell命令行去修改一下，增加兩行
				
				
					admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最后要加載的純文本數(shù)據(jù)內(nèi)容如下
				
				
					xxx2 a string 100.20
				
				
					xxx4 a string containing a , comma 102.20
				
				
					xxx6 a string containing a " quote 102.20 
				
				
					xxx8 a string containing a ", quote and comma 102.20
				
				
					xxx10 \\t 102.20
				
				
					12 \\t 102.20
				
				
					dfadsfasxxx14 \\t 102.20
				
				
					admin@localhost : xiaoboluo 03:59:03> truncate test3; #清空表
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 03:59:38> load data infile "/tmp/test3.txt" into table test3 LINES STARTING BY 'xxx'; #導(dǎo)入數(shù)據(jù)，指定行前綴字符為xxx
				
				
					Query OK, 6 rows affected (0.00 sec) 
				
				
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 03:59:44> select * from test3; #校驗表數(shù)據(jù)，可以看到?jīng)]有xxx行前綴的行被忽略了，而包含xxx的最后一行，從xxx開始截斷，xxx字符本身及其之前的內(nèi)容被忽略，\
				
				
					xxx之后的內(nèi)容被解析為行數(shù)據(jù)導(dǎo)入了
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					6 rows in set (0.00 sec) 
				
				
					行結(jié)束符（換行符），linux下默認為\n，使用子句lines terminated by 'string' 指定，其中string代表指定的換行符
				
				
					# 指定換行符為\r\n導(dǎo)出數(shù)據(jù)
				
				
					admin@localhost : xiaoboluo 03:59:49> system rm -f /tmp/test3.txt; 
				
				
					admin@localhost : xiaoboluo 04:02:22> select * from test3 into outfile "/tmp/test3.txt" lines terminated by '\r\n'; 
				
				
					Query OK, 6 rows affected (0.00 sec) 
				
				
					# 由于linux的一些命令本身會解析掉這些特殊字符，所以使用python來查看這個文本文件中的換行符，從下面的結(jié)果中可以看到，列表的每一個元素代表一行數(shù)據(jù)，每一個元素的\
				
				
					末尾的\r\n就是這行數(shù)據(jù)的換行符
				
				
					>>> f = open('/tmp/test3.txt','r') 
				
				
					>>> data = f.readlines() 
				
				
					>>> data
				
				
					['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', \
				
				
					'14\t\\\\t\t102.20\r\n'] 
				
				
					>>> 
				
				
					# 現(xiàn)在，把數(shù)據(jù)重新導(dǎo)入表，從下面的結(jié)果中可以看到，導(dǎo)入表中的數(shù)據(jù)正確
				
				
					admin@localhost : xiaoboluo 04:02:39> truncate test3; 
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 04:04:55> load data infile "/tmp/test3.txt" into table test3 lines terminated by '\r\n'; 
				
				
					Query OK, 6 rows affected (0.00 sec) 
				
				
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 04:05:11> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					6 rows in set (0.00 sec)

1.2.6.3. FIELDS和LINES注意事項

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

	
			
					
			
				
				
					\0 ASCII NUL (X'00') 字符 
				
				
					\b 退格字符
				
				
					\n 換行符
				
				
					\r 回車符
				
				
					\t 制表符
				
				
					\Z ASCII 26 (Control+Z) 
				
				
					\N NULL值，如果轉(zhuǎn)義符值為空，則會直接導(dǎo)出null字符串作為數(shù)據(jù)，這在導(dǎo)入時將把null作為數(shù)據(jù)導(dǎo)入，而不是null符號

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

	
			
					
			
				
				
					# 字段引用符為"，數(shù)據(jù)中包含"，轉(zhuǎn)義符和換行符保持默認，導(dǎo)入數(shù)據(jù)時不會有任何問題 
				
				
					admin@localhost : xiaoboluo 09:46:14> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					4 rows in set (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 09:46:17> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"'; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 09:46:23> system cat /tmp/test3.txt; 
				
				
					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" # 可以看到與字段引用符相同的符號數(shù)據(jù)被轉(zhuǎn)義了
				
				
					admin@localhost : xiaoboluo 09:54:41> truncate test3; 
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					4 rows in set (0.00 sec) 
				
				
					# 如果字段引用符為"，字段分隔符為,且數(shù)據(jù)中包含字段引用符"和字段分隔符,，轉(zhuǎn)義符和換行符保持默認，這在導(dǎo)入數(shù)據(jù)時不會有任何問題
				
				
					admin@localhost : xiaoboluo 09:53:45> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 09:54:29> system cat /tmp/test3.txt; 
				
				
					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" 
				
				
					admin@localhost : xiaoboluo 09:54:41> truncate test3; 
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					4 rows in set (0.00 sec) 
				
				
					# 但是，如果在字段引用符為"，數(shù)據(jù)中包含"，字段分隔符使用逗號，換行符保持默認的情況下，轉(zhuǎn)義符使用了空串，這會導(dǎo)致在導(dǎo)入數(shù)據(jù)時，第四行無法正確解析，報錯
				
				
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					4 rows in set (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 09:58:49> select * from test3 into outfile "/tmp/test3_test.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by ''; 
				
				
					Query OK, 4 rows affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 10:00:42> system cat /tmp/test3_test.txt; 
				
				
					2,"a string","100.20" 
				
				
					4,"a string containing a , comma","102.20" 
				
				
					6,"a string containing a " quote","102.20"  #關(guān)于這一行數(shù)據(jù)，需要說明一下ENCLOSED BY子句，該子句指定的引用符號從一個FIELDS TERMINATED BY子句指定的分隔符開始，直到碰到下一個\ 
				
				
					分隔符之間且這個分隔符前面一個字符必須是字段引用符號（如果這個分隔符前面一個字符不是字段引用符，則繼續(xù)往后匹配，如第二行數(shù)據(jù)），在這之間的內(nèi)容都會被當作整個列字符串處理，\
				
				
					所以這一行數(shù)據(jù)在導(dǎo)入時不會發(fā)生解析錯誤
				
				
					8,"a string containing a ", quote and comma","102.20" #這一行因為無法正確識別的字段結(jié)束位置，所以無法導(dǎo)入，報錯終止，前面正確的行也被回滾掉（binlog_format=row）
				
				
					admin@localhost : xiaoboluo 10:00:49> truncate test3; 
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 10:01:03> load data infile '/tmp/test3_test.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by ''; 
				
				
					ERROR 1262 (01000): Row 4 was truncated; it contained more data than there were input columns
				
				
					admin@localhost : xiaoboluo 10:01:33> select * from test3; 
				
				
					Empty set (0.00 sec) 
				
				
					# 數(shù)據(jù)中包含了默認的轉(zhuǎn)義符和指定的字段分隔符，字段引用符和行分隔符使用默認值，則在數(shù)據(jù)中的轉(zhuǎn)義符和字段分隔符會被轉(zhuǎn)義（只要不為空，則不管字段分隔符和轉(zhuǎn)義字符定義為什么值，\
				
				
					都會被轉(zhuǎn)義）
				
				
					admin@localhost : xiaoboluo 03:08:45> insert into test3(test,test2) values('\\t','102.20'); 
				
				
					Query OK, 1 row affected (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 03:17:29> select * from test3; 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| id | test | test2 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					| 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 | 
				
				
					+----+------------------------------------------+--------+ 
				
				
					5 rows in set (0.00 sec) 
				
				
					admin@localhost : xiaoboluo 03:17:32> system rm -f /tmp/test3.txt; 
				
				
					admin@localhost : xiaoboluo 03:17:39> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ','; 
				
				
					Query OK, 5 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 03:17:42> system cat /tmp/test3.txt
				
				
					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

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

	
			
					
			
				
				
					# 假設(shè)您執(zhí)行SELECT ... INTO OUTFILE語句時使用了逗號作為列分隔符： 
				
				
					SELECT * INTO OUTFILE 'data.txt' 
				
				
					 FIELDS TERMINATED BY ',' 
				
				
					 FROM table2; 
				
				
					# 如果您嘗試使用\t作為列分隔符，則它將無法正常工作，因為它會指示LOAD DATA INFILE在字段之間查找制表符，可能導(dǎo)致每個數(shù)據(jù)行整行解析時被當作單個字段：
				
				
					LOAD DATA INFILE 'data.txt' INTO TABLE table2
				
				
					 FIELDS TERMINATED BY '\t'; 
				
				
					# 要正確讀取逗號分隔各列的文件，正確的語句是
				
				
					LOAD DATA INFILE 'data.txt' INTO TABLE table2
				
				
					 FIELDS TERMINATED BY ','

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

	
			
					
			
				
				
					# 如果LINES TERMINATED BY換行符指定了一個空字符，并且FIELDS TERMINATED BY字段分隔符指定的是非空的一個字符(或者使用默認值\t)，則行也會以字段分隔符作為行的結(jié)束符\ 
				
				
					（表現(xiàn)行為就是文本中最后一個字符就是字段分隔符），即整個文本看上去就是一整行數(shù)據(jù)了
				
				
					admin@localhost : xiaoboluo 04:48:35> system rm -f /tmp/test3.txt; 
				
				
					admin@localhost : xiaoboluo 04:53:59> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',' lines terminated by ''; 
				
				
					Query OK, 6 rows affected (0.00 sec) 
				
				
					# 使用python查看文本內(nèi)容，從下面的結(jié)果中可以看到，整個表的數(shù)據(jù)由于換行符為空，所以導(dǎo)致都拼接為一行了，最后行結(jié)束符使用了字段分隔符逗號
				
				
					>>> f = open('/tmp/test3.txt','r') 
				
				
					>>> data = f.readlines() 
				
				
					>>> data
				
				
					['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,'] 
				
				
					>>> 
				
				
					# 導(dǎo)入數(shù)據(jù)到表，這里新建一張表來進行導(dǎo)入測試，預(yù)防清理掉了表數(shù)據(jù)之后，文本內(nèi)容又無法正確導(dǎo)入的情況發(fā)生
				
				
					admin@localhost : xiaoboluo 04:57:52> create table test4 like test3; 
				
				
					Query OK, 0 rows affected (0.01 sec) 
				
				
					admin@localhost : xiaoboluo 04:57:59> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY ',' lines terminated by ''; 
				
				
					Query OK, 6 rows affected (0.00 sec) 
				
				
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
				
					admin@localhost : xiaoboluo 04:58:26> select * from test4; #從查詢結(jié)果上看，數(shù)據(jù)正確導(dǎo)入表test4中了
				
				
					+----+-----<            
            
                        

            新聞名稱：【MySQL】loaddata語句詳解（二）            

            URL鏈接：http://weahome.cn/article/giddis.html