平武建站

【MySQL】loaddata语句详解（二）

作者：罗小波
沃趣科技高级MySQL数据库工程师
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. 		
		
    			
			
      
				
				
    1. 
					admin@localhost : xiaoboluo 03:08:34> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ','; 
				
      2. 
				
    2. 
					Query OK, 4 rows affected (0.00 sec) 
				
      3. 
				
    3. 
					admin@localhost : xiaoboluo 03:08:37> system cat /tmp/test3.txt
				
      4. 
				
    4. 
					2,a string,100.20
				
      5. 
				
    5. 
					4,a string containing a \, comma,102.20
				
      6. 
				
    6. 
					6,a string containing a " quote,102.20 
				
      7. 
				
    7. 
					8,a string containing a "\, quote and comma,102.20
				
      8. 
			
    
	
    2.
  • 字段引用符，如果加optionally选项则只用在char、varchar和text等字符型字段上，数值类型会忽略使用引用符，如果不指定该子句，则默认不使用引用符，使用子句fields [optionally] enclosed by 'char'指定，其中char代表指定的字段引用符 
    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					# 指定字段引用符为"，不使用optionally关键字 
				
      2. 
				
    2. 
					admin@localhost : xiaoboluo 03:33:33> system rm -f /tmp/test3.txt;
				
      3. 
				
    3. 
					admin@localhost : xiaoboluo 03:37:21> select * from test3 into outfile "/tmp/test3.txt" FIELDS  ENCLOSED BY '"'; 
				
      4. 
				
    4. 
					Query OK, 5 rows affected (0.00 sec)
				
      5. 
				
    5. 
					admin@localhost : xiaoboluo 03:37:33> system cat /tmp/test3.txt
				
      6. 
				
    6. 
					"2" "a string" "100.20"
				
      7. 
				
    7. 
					"4" "a string containing a , comma" "102.20"
				
      8. 
				
    8. 
					"6" "a string containing a \" quote" "102.20"
				
      9. 
				
    9. 
					"8" "a string containing a \", quote and comma" "102.20"
				
      10. 
				
    10. 
					"10" "\\t" "102.20"
				
      11. 
				
    11. 
					# 指定字段引用符为"，使用optionally关键字，可以看到id列的字段引用符去掉了
				
      12. 
				
    12. 
					admin@localhost : xiaoboluo 03:37:41> system rm -f /tmp/test3.txt;
				
      13. 
				
    13. 
					admin@localhost : xiaoboluo 03:40:53> select * from test3 into outfile "/tmp/test3.txt" FIELDS optionally  ENCLOSED BY '"'; 
				
      14. 
				
    14. 
					Query OK, 5 rows affected (0.00 sec)
				
      15. 
				
    15. 
					admin@localhost : xiaoboluo 03:41:03> system cat /tmp/test3.txt
				
      16. 
				
    16. 
					2 "a string" "100.20" 
				
      17. 
				
    17. 
					4 "a string containing a , comma" "102.20" 
				
      18. 
				
    18. 
					6 "a string containing a \" quote" "102.20" 
				
      19. 
				
    19. 
					8 "a string containing a \", quote and comma" "102.20" 
				
      20. 
				
    20. 
					10 "\\t" "102.20
				
      21. 
			
    
	
    2.
  • 转义字符，默认为\，使用子句fields escaped by 'char' 指定，其中char代表指定的转义字符 
    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					admin@localhost : xiaoboluo 03:42:41> system rm -f /tmp/test3.txt; 
				
      2. 
				
    2. 
					admin@localhost : xiaoboluo 03:44:18> select * from test3 into outfile "/tmp/test3.txt" fields escaped by '.'; 
				
      3. 
				
    3. 
					Query OK, 5 rows affected (0.00 sec) 
				
      4. 
				
    4. 
					admin@localhost : xiaoboluo 03:44:25> system cat /tmp/test3.txt # 可以看到数据中指定的转义符.号被转义了，而数据\t没有被转义
				
      5. 
				
    5. 
					2 a string 100..20
				
      6. 
				
    6. 
					4 a string containing a , comma 102..20
				
      7. 
				
    7. 
					6 a string containing a " quote 102..20 
				
      8. 
				
    8. 
					8 a string containing a ", quote and comma 102..20
				
      9. 
				
    9. 
					10 \t 102..20
				
      10. 
				
    10. 
					admin@localhost : xiaoboluo 03:44:28> truncate test3; #清空表
				
      11. 
				
    11. 
					Query OK, 0 rows affected (0.01 sec) 
				
      12. 
				
    12. 
					admin@localhost : xiaoboluo 03:45:19> load data infile "/tmp/test3.txt" into table test3 fields escaped by '.'; #导入数据时指定转义符为.号
				
      13. 
				
    13. 
					Query OK, 5 rows affected (0.00 sec) 
				
      14. 
				
    14. 
					Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
				
      15. 
				
    15. 
					admin@localhost : xiaoboluo 03:45:40> select * from test3; #校验数据，可以看到导入数据正常
				
      16. 
				
    16. 
					+----+------------------------------------------+--------+ 
				
      17. 
				
    17. 
					| id | test | test2 | 
				
      18. 
				
    18. 
					+----+------------------------------------------+--------+ 
				
      19. 
				
    19. 
					| 2 | a string | 100.20 | 
				
      20. 
				
    20. 
					| 4 | a string containing a , comma | 102.20 | 
				
      21. 
				
    21. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      22. 
				
    22. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      23. 
				
    23. 
					| 10 | \t | 102.20 | 
				
      24. 
				
    24. 
					+----+------------------------------------------+--------+ 
				
      25. 
				
    25. 
					5 rows in set (0.00 sec) 
				
      26. 
			
    
	
    2.
1.2.6.2. LINES 关键字及其子句详解
  • 行前缀字符串，使用子句lines starting by 'string' 指定，其中string代表指定的行前缀字符串，行前缀字符串在导出文本数据时使用该子句指定，在导入文本时在一行数据中如果发现了行前缀字符串，则只导入从前缀字符串开始之后的数据部分，前缀字符本身及其之前的数据被忽略掉，如果某行数据不包含行前缀字符串，则整行数据都会被忽略

如果您想要读取的纯文本文件中所有行都有一个您想要忽略的公用前缀，则可以使用LINES STARTING BY'prefix_string'来跳过这个前缀，以及前缀字符前面的任何内容。如果某行数据不包含前缀字符，则跳过整行内容，例

    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					# load data语句如下 
				
      2. 
				
    2. 
					admin@localhost : xiaoboluo 03:48:04> system rm -f /tmp/test3.txt; 
				
      3. 
				
    3. 
					admin@localhost : xiaoboluo 03:54:54> select * from test3 into outfile "/tmp/test3.txt" LINES STARTING BY 'xxx'; 
				
      4. 
				
    4. 
					Query OK, 5 rows affected (0.00 sec) 
				
      5. 
				
    5. 
					admin@localhost : xiaoboluo 03:55:03> system cat /tmp/test3.txt #可以看到每行数据前面多了个行前缀字符串xxx
				
      6. 
				
    6. 
					xxx2 a string 100.20
				
      7. 
				
    7. 
					xxx4 a string containing a , comma 102.20
				
      8. 
				
    8. 
					xxx6 a string containing a " quote 102.20 
				
      9. 
				
    9. 
					xxx8 a string containing a ", quote and comma 102.20
				
      10. 
				
    10. 
					xxx10 \\t 102.20
				
      11. 
				
    11. 
					# 现在，到shell命令行去修改一下，增加两行
				
      12. 
				
    12. 
					admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最后要加载的纯文本数据内容如下
				
      13. 
				
    13. 
					xxx2 a string 100.20
				
      14. 
				
    14. 
					xxx4 a string containing a , comma 102.20
				
      15. 
				
    15. 
					xxx6 a string containing a " quote 102.20 
				
      16. 
				
    16. 
					xxx8 a string containing a ", quote and comma 102.20
				
      17. 
				
    17. 
					xxx10 \\t 102.20
				
      18. 
				
    18. 
					12 \\t 102.20
				
      19. 
				
    19. 
					dfadsfasxxx14 \\t 102.20
				
      20. 
				
    20. 
					admin@localhost : xiaoboluo 03:59:03> truncate test3; #清空表
				
      21. 
				
    21. 
					Query OK, 0 rows affected (0.01 sec) 
				
      22. 
				
    22. 
					admin@localhost : xiaoboluo 03:59:38> load data infile "/tmp/test3.txt" into table test3 LINES STARTING BY 'xxx'; #导入数据，指定行前缀字符为xxx
				
      23. 
				
    23. 
					Query OK, 6 rows affected (0.00 sec) 
				
      24. 
				
    24. 
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
      25. 
				
    25. 
					admin@localhost : xiaoboluo 03:59:44> select * from test3; #校验表数据，可以看到没有xxx行前缀的行被忽略了，而包含xxx的最后一行，从xxx开始截断，xxx字符本身及其之前的内容被忽略，\
				
      26. 
				
    26. 
					xxx之后的内容被解析为行数据导入了
				
      27. 
				
    27. 
					+----+------------------------------------------+--------+ 
				
      28. 
				
    28. 
					| id | test | test2 | 
				
      29. 
				
    29. 
					+----+------------------------------------------+--------+ 
				
      30. 
				
    30. 
					| 2 | a string | 100.20 | 
				
      31. 
				
    31. 
					| 4 | a string containing a , comma | 102.20 | 
				
      32. 
				
    32. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      33. 
				
    33. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      34. 
				
    34. 
					| 10 | \t | 102.20 | 
				
      35. 
				
    35. 
					| 14 | \t | 102.20 | 
				
      36. 
				
    36. 
					+----+------------------------------------------+--------+ 
				
      37. 
				
    37. 
					6 rows in set (0.00 sec) 
				
      38. 
				
    38. 
					行结束符（换行符），linux下默认为\n，使用子句lines terminated by 'string' 指定，其中string代表指定的换行符
				
      39. 
				
    39. 
					# 指定换行符为\r\n导出数据
				
      40. 
				
    40. 
					admin@localhost : xiaoboluo 03:59:49> system rm -f /tmp/test3.txt; 
				
      41. 
				
    41. 
					admin@localhost : xiaoboluo 04:02:22> select * from test3 into outfile "/tmp/test3.txt" lines terminated by '\r\n'; 
				
      42. 
				
    42. 
					Query OK, 6 rows affected (0.00 sec) 
				
      43. 
				
    43. 
					# 由于linux的一些命令本身会解析掉这些特殊字符，所以使用python来查看这个文本文件中的换行符，从下面的结果中可以看到，列表的每一个元素代表一行数据，每一个元素的\
				
      44. 
				
    44. 
					末尾的\r\n就是这行数据的换行符
				
      45. 
				
    45. 
					>>> f = open('/tmp/test3.txt','r') 
				
      46. 
				
    46. 
					>>> data = f.readlines() 
				
      47. 
				
    47. 
					>>> data
				
      48. 
				
    48. 
					['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', \
				
      49. 
				
    49. 
					'14\t\\\\t\t102.20\r\n'] 
				
      50. 
				
    50. 
					>>> 
				
      51. 
				
    51. 
					# 现在，把数据重新导入表，从下面的结果中可以看到，导入表中的数据正确
				
      52. 
				
    52. 
					admin@localhost : xiaoboluo 04:02:39> truncate test3; 
				
      53. 
				
    53. 
					Query OK, 0 rows affected (0.01 sec) 
				
      54. 
				
    54. 
					admin@localhost : xiaoboluo 04:04:55> load data infile "/tmp/test3.txt" into table test3 lines terminated by '\r\n'; 
				
      55. 
				
    55. 
					Query OK, 6 rows affected (0.00 sec) 
				
      56. 
				
    56. 
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
      57. 
				
    57. 
					admin@localhost : xiaoboluo 04:05:11> select * from test3; 
				
      58. 
				
    58. 
					+----+------------------------------------------+--------+ 
				
      59. 
				
    59. 
					| id | test | test2 | 
				
      60. 
				
    60. 
					+----+------------------------------------------+--------+ 
				
      61. 
				
    61. 
					| 2 | a string | 100.20 | 
				
      62. 
				
    62. 
					| 4 | a string containing a , comma | 102.20 | 
				
      63. 
				
    63. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      64. 
				
    64. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      65. 
				
    65. 
					| 10 | \t | 102.20 | 
				
      66. 
				
    66. 
					| 14 | \t | 102.20 | 
				
      67. 
				
    67. 
					+----+------------------------------------------+--------+ 
				
      68. 
				
    68. 
					6 rows in set (0.00 sec) 
				
      69. 
			
    
	
    2.
1.2.6.3. FIELDS和LINES注意事项

  • 众所周知，MySQL中反斜杠是SQL语句中特殊字符的转义字符，因此在sql语句中碰到特殊字符时，您必须指定一个或者两个反斜杠来为特殊字符转义（如在mysql中或者一些其他程序中，\n代表换行符，\t代表制表符，\代表转义符，那么需要使用\t来转义制表符，\n来转义换行符，\来转义转义符本身，这样才能正确写入数据库或者生成导出的数据文本,使用FIELDS ESCAPED BY子句指定转义符

  • 特殊字符列表如 

    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					\0 ASCII NUL (X'00') 字符 
				
      2. 
				
    2. 
					\b 退格字符
				
      3. 
				
    3. 
					\n 换行符
				
      4. 
				
    4. 
					\r 回车符
				
      5. 
				
    5. 
					\t 制表符
				
      6. 
				
    6. 
					\Z ASCII 26 (Control+Z) 
				
      7. 
				
    7. 
					\N NULL值，如果转义符值为空，则会直接导出null字符串作为数据，这在导入时将把null作为数据导入，而不是null符号
				
      8. 
			
    
	
    2.
  • 如果数据中包含了ENCLOSED BY '"'子句指定字段引用符号，则与字段引用符号相同数据字符也会被自动添加一个反斜杠进行转义（如果转义符指定为空，则可能会导致数据在导入时无法正确解析）。如果数据中包含了FIELDS TERMINATED BY 子句指定的字段分隔符，则以FIELDS ENCLOSED BY子句指定的字段引用符号为准，被引起来的整个部分作为一整列的数据，列值之间的数据包含字段分隔符不会被转义，而是作为数据处理，但数据中包含的字段引用符会被转义(在数据中包含了字段分隔符的情况下，如果字段引用符号没有指定或者指定为空值，则可能在导入数据时无法正确解析）。如果数据中包含了FIELDS ESCAPED BY子句指定的转义符，字段引用符和行分隔符使用默认值，则在数据中的转义符会被转义（只要不为空，则不管字段分隔符和转义字符定义为什么值，都会被转义），默认情况下，不建议随意更改换行符和转义符，除非必须且你需要校验修改之后数据能够正确导入 
    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					# 字段引用符为"，数据中包含"，转义符和换行符保持默认，导入数据时不会有任何问题 
				
      2. 
				
    2. 
					admin@localhost : xiaoboluo 09:46:14> select * from test3; 
				
      3. 
				
    3. 
					+----+------------------------------------------+--------+ 
				
      4. 
				
    4. 
					| id | test | test2 | 
				
      5. 
				
    5. 
					+----+------------------------------------------+--------+ 
				
      6. 
				
    6. 
					| 2 | a string | 100.20 | 
				
      7. 
				
    7. 
					| 4 | a string containing a , comma | 102.20 | 
				
      8. 
				
    8. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      9. 
				
    9. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      10. 
				
    10. 
					+----+------------------------------------------+--------+ 
				
      11. 
				
    11. 
					4 rows in set (0.00 sec) 
				
      12. 
				
    12. 
					admin@localhost : xiaoboluo 09:46:17> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"'; 
				
      13. 
				
    13. 
					Query OK, 4 rows affected (0.00 sec) 
				
      14. 
				
    14. 
					admin@localhost : xiaoboluo 09:46:23> system cat /tmp/test3.txt; 
				
      15. 
				
    15. 
					2 "a string" "100.20" 
				
      16. 
				
    16. 
					4 "a string containing a , comma" "102.20" 
				
      17. 
				
    17. 
					6 "a string containing a \" quote" "102.20" 
				
      18. 
				
    18. 
					8 "a string containing a \", quote and comma" "102.20" # 可以看到与字段引用符相同的符号数据被转义了
				
      19. 
				
    19. 
					admin@localhost : xiaoboluo 09:54:41> truncate test3; 
				
      20. 
				
    20. 
					Query OK, 0 rows affected (0.01 sec) 
				
      21. 
				
    21. 
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
      22. 
				
    22. 
					Query OK, 4 rows affected (0.00 sec) 
				
      23. 
				
    23. 
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
      24. 
				
    24. 
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
      25. 
				
    25. 
					+----+------------------------------------------+--------+ 
				
      26. 
				
    26. 
					| id | test | test2 | 
				
      27. 
				
    27. 
					+----+------------------------------------------+--------+ 
				
      28. 
				
    28. 
					| 2 | a string | 100.20 | 
				
      29. 
				
    29. 
					| 4 | a string containing a , comma | 102.20 | 
				
      30. 
				
    30. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      31. 
				
    31. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      32. 
				
    32. 
					+----+------------------------------------------+--------+ 
				
      33. 
				
    33. 
					4 rows in set (0.00 sec) 
				
      34. 
				
    34. 
					# 如果字段引用符为"，字段分隔符为,且数据中包含字段引用符"和字段分隔符,，转义符和换行符保持默认，这在导入数据时不会有任何问题
				
      35. 
				
    35. 
					admin@localhost : xiaoboluo 09:53:45> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
      36. 
				
    36. 
					Query OK, 4 rows affected (0.00 sec) 
				
      37. 
				
    37. 
					admin@localhost : xiaoboluo 09:54:29> system cat /tmp/test3.txt; 
				
      38. 
				
    38. 
					2,"a string","100.20" 
				
      39. 
				
    39. 
					4,"a string containing a , comma","102.20" 
				
      40. 
				
    40. 
					6,"a string containing a \" quote","102.20" 
				
      41. 
				
    41. 
					8,"a string containing a \", quote and comma","102.20" 
				
      42. 
				
    42. 
					admin@localhost : xiaoboluo 09:54:41> truncate test3; 
				
      43. 
				
    43. 
					Query OK, 0 rows affected (0.01 sec) 
				
      44. 
				
    44. 
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
      45. 
				
    45. 
					Query OK, 4 rows affected (0.00 sec) 
				
      46. 
				
    46. 
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
      47. 
				
    47. 
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
      48. 
				
    48. 
					+----+------------------------------------------+--------+ 
				
      49. 
				
    49. 
					| id | test | test2 | 
				
      50. 
				
    50. 
					+----+------------------------------------------+--------+ 
				
      51. 
				
    51. 
					| 2 | a string | 100.20 | 
				
      52. 
				
    52. 
					| 4 | a string containing a , comma | 102.20 | 
				
      53. 
				
    53. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      54. 
				
    54. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      55. 
				
    55. 
					+----+------------------------------------------+--------+ 
				
      56. 
				
    56. 
					4 rows in set (0.00 sec) 
				
      57. 
				
    57. 
					# 但是，如果在字段引用符为"，数据中包含"，字段分隔符使用逗号，换行符保持默认的情况下，转义符使用了空串，这会导致在导入数据时，第四行无法正确解析，报错
				
      58. 
				
    58. 
					admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ','; 
				
      59. 
				
    59. 
					Query OK, 4 rows affected (0.00 sec) 
				
      60. 
				
    60. 
					Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
				
      61. 
				
    61. 
					admin@localhost : xiaoboluo 09:58:45> select * from test3; 
				
      62. 
				
    62. 
					+----+------------------------------------------+--------+ 
				
      63. 
				
    63. 
					| id | test | test2 | 
				
      64. 
				
    64. 
					+----+------------------------------------------+--------+ 
				
      65. 
				
    65. 
					| 2 | a string | 100.20 | 
				
      66. 
				
    66. 
					| 4 | a string containing a , comma | 102.20 | 
				
      67. 
				
    67. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      68. 
				
    68. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      69. 
				
    69. 
					+----+------------------------------------------+--------+ 
				
      70. 
				
    70. 
					4 rows in set (0.00 sec) 
				
      71. 
				
    71. 
					admin@localhost : xiaoboluo 09:58:49> select * from test3 into outfile "/tmp/test3_test.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by ''; 
				
      72. 
				
    72. 
					Query OK, 4 rows affected (0.00 sec) 
				
      73. 
				
    73. 
					admin@localhost : xiaoboluo 10:00:42> system cat /tmp/test3_test.txt; 
				
      74. 
				
    74. 
					2,"a string","100.20" 
				
      75. 
				
    75. 
					4,"a string containing a , comma","102.20" 
				
      76. 
				
    76. 
					6,"a string containing a " quote","102.20"  #关于这一行数据，需要说明一下ENCLOSED BY子句，该子句指定的引用符号从一个FIELDS TERMINATED BY子句指定的分隔符开始，直到碰到下一个\ 
				
      77. 
				
    77. 
					分隔符之间且这个分隔符前面一个字符必须是字段引用符号（如果这个分隔符前面一个字符不是字段引用符，则继续往后匹配，如第二行数据），在这之间的内容都会被当作整个列字符串处理，\
				
      78. 
				
    78. 
					所以这一行数据在导入时不会发生解析错误
				
      79. 
				
    79. 
					8,"a string containing a ", quote and comma","102.20" #这一行因为无法正确识别的字段结束位置，所以无法导入，报错终止，前面正确的行也被回滚掉（binlog_format=row）
				
      80. 
				
    80. 
					admin@localhost : xiaoboluo 10:00:49> truncate test3; 
				
      81. 
				
    81. 
					Query OK, 0 rows affected (0.01 sec) 
				
      82. 
				
    82. 
					admin@localhost : xiaoboluo 10:01:03> load data infile '/tmp/test3_test.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by ''; 
				
      83. 
				
    83. 
					ERROR 1262 (01000): Row 4 was truncated; it contained more data than there were input columns
				
      84. 
				
    84. 
					admin@localhost : xiaoboluo 10:01:33> select * from test3; 
				
      85. 
				
    85. 
					Empty set (0.00 sec) 
				
      86. 
				
    86. 
					# 数据中包含了默认的转义符和指定的字段分隔符，字段引用符和行分隔符使用默认值，则在数据中的转义符和字段分隔符会被转义（只要不为空，则不管字段分隔符和转义字符定义为什么值，\
				
      87. 
				
    87. 
					都会被转义）
				
      88. 
				
    88. 
					admin@localhost : xiaoboluo 03:08:45> insert into test3(test,test2) values('\\t','102.20'); 
				
      89. 
				
    89. 
					Query OK, 1 row affected (0.00 sec) 
				
      90. 
				
    90. 
					admin@localhost : xiaoboluo 03:17:29> select * from test3; 
				
      91. 
				
    91. 
					+----+------------------------------------------+--------+ 
				
      92. 
				
    92. 
					| id | test | test2 | 
				
      93. 
				
    93. 
					+----+------------------------------------------+--------+ 
				
      94. 
				
    94. 
					| 2 | a string | 100.20 | 
				
      95. 
				
    95. 
					| 4 | a string containing a , comma | 102.20 | 
				
      96. 
				
    96. 
					| 6 | a string containing a " quote            | 102.20 | 
				
      97. 
				
    97. 
					|  8 | a string containing a ", quote and comma | 102.20 | 
				
      98. 
				
    98. 
					| 10 | \t | 102.20 | 
				
      99. 
				
    99. 
					+----+------------------------------------------+--------+ 
				
      100. 
				
    100. 
					5 rows in set (0.00 sec) 
				
      101. 
				
    101. 
					admin@localhost : xiaoboluo 03:17:32> system rm -f /tmp/test3.txt; 
				
      102. 
				
    102. 
					admin@localhost : xiaoboluo 03:17:39> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ','; 
				
      103. 
				
    103. 
					Query OK, 5 rows affected (0.01 sec) 
				
      104. 
				
    104. 
					admin@localhost : xiaoboluo 03:17:42> system cat /tmp/test3.txt
				
      105. 
				
    105. 
					2,a string,100.20
				
      106. 
				
    106. 
					4,a string containing a \, comma,102.20
				
      107. 
				
    107. 
					6,a string containing a " quote,102.20 
				
      108. 
				
    108. 
					8,a string containing a "\, quote and comma,102.20
				
      109. 
				
    109. 
					10,\\t,102.20
				
      110. 
			
    
	
    2.
  • 当您使用SELECT … INTO OUTFILE与LOAD DATA INFILE一起将数据从数据库写入文件，然后再将该文件读回数据库时，两个语句的FIELDS和LINES处理选项必须匹配。否则，LOAD DATA INFILE将解析错误的文件内容，示例 
    	
	
  1. 		
		
    			
			
      
				
				
    1. 
					# 假设您执行SELECT ... INTO OUTFILE语句时使用了逗号作为列分隔符： 
				
      2. 
				
    2. 
					SELECT * INTO OUTFILE 'data.txt' 
				
      3. 
				
    3. 
					 FIELDS TERMINATED BY ',' 
				
      4. 
				
    4. 
					 FROM table2; 
				
      5. 
				
    5. 
					# 如果您尝试使用\t作为列分隔符，则它将无法正常工作，因为它会指示LOAD DATA INFILE在字段之间查找制表符，可能导致每个数据行整行解析时被当作单个字段：
				
      6. 
				
    6. 
					LOAD DATA INFILE 'data.txt' INTO TABLE table2
				
      7. 
				
    7. 
					 FIELDS TERMINATED BY '\t'; 
				
      8. 
				
    8. 
					# 要正确读取逗号分隔各列的文件，正确的语句是
				
      9. 
				
    9. 
					LOAD DATA INFILE 'data.txt' INTO TABLE table2
				
      10. 
				
    10. 
					 FIELDS TERMINATED BY ',' 
				
      11. 
			
    
	
    2.
  • 任何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. 		
		
    			
			
      
				
				
    1. 
					# 如果LINES TERMINATED BY换行符指定了一个空字符，并且FIELDS TERMINATED BY字段分隔符指定的是非空的一个字符(或者使用默认值\t)，则行也会以字段分隔符作为行的结束符\ 
				
      2. 
				
    2. 
					（表现行为就是文本中最后一个字符就是字段分隔符），即整个文本看上去就是一整行数据了
				
      3. 
				
    3. 
					admin@localhost : xiaoboluo 04:48:35> system rm -f /tmp/test3.txt; 
				
      4. 
				
    4. 
					admin@localhost : xiaoboluo 04:53:59> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',' lines terminated by ''; 
				
      5. 
				
    5. 
					Query OK, 6 rows affected (0.00 sec) 
				
      6. 
				
    6. 
					# 使用python查看文本内容，从下面的结果中可以看到，整个表的数据由于换行符为空，所以导致都拼接为一行了，最后行结束符使用了字段分隔符逗号
				
      7. 
				
    7. 
					>>> f = open('/tmp/test3.txt','r') 
				
      8. 
				
    8. 
					>>> data = f.readlines() 
				
      9. 
				
    9. 
					>>> data
				
      10. 
				
    10. 
					['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,'] 
				
      11. 
				
    11. 
					>>> 
				
      12. 
				
    12. 
					# 导入数据到表，这里新建一张表来进行导入测试，预防清理掉了表数据之后，文本内容又无法正确导入的情况发生
				
      13. 
				
    13. 
					admin@localhost : xiaoboluo 04:57:52> create table test4 like test3; 
				
      14. 
				
    14. 
					Query OK, 0 rows affected (0.01 sec) 
				
      15. 
				
    15. 
					admin@localhost : xiaoboluo 04:57:59> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY ',' lines terminated by ''; 
				
      16. 
				
    16. 
					Query OK, 6 rows affected (0.00 sec) 
				
      17. 
				
    17. 
					Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
				
      18. 
				
    18. 
					admin@localhost : xiaoboluo 04:58:26> select * from test4; #从查询结果上看，数据正确导入表test4中了
				
      19. 
				
    19. 
					+----+-----<            
            
                            
      
                分享名称：【MySQL】loaddata语句详解（二）                
      
                当前URL：http://pwwzsj.com/article/giddis.html

其他资讯