13518219792
对于LOAD DATA INFILE和SELECT … INTO OUTFILE语句中，FIELDS和LINES子句的语法完全相同。两个子句在LOAD DATA INFILE和SELECT … INTO OUTFILE语句中都是可选的，但如果两个子句都被指定，则FIELDS必须在LINES之前，否则报语法错误
从网站建设到定制行业解决方案，为提供成都网站设计、网站建设服务体系，各种行业企业客户提供网站建设解决方案，助力业务快速发展。成都创新互联公司将不断加快创新步伐，提供优质的建站服务。
当mysql server导出文本数据到文件时，FIELDS和LINES默认值时SELECT … INTO OUTFILE在输出文本数据时行为如下：
- 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关键字
- 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关键字，可以看到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
1.2.6.2. LINES 关键字及其子句详解
- 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 # 可以看到数据中指定的转义符.号被转义了，而数据\t没有被转义
- 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 '.'; #导入数据时指定转义符为.号
- 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; #校验数据，可以看到导入数据正常
- +----+------------------------------------------+--------+
- | 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)
如果您想要读取的纯文本文件中所有行都有一个您想要忽略的公用前缀，则可以使用LINES STARTING BY'prefix_string'来跳过这个前缀，以及前缀字符前面的任何内容。如果某行数据不包含前缀字符，则跳过整行内容，例
1.2.6.3. FIELDS和LINES注意事项
- # 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 #可以看到每行数据前面多了个行前缀字符串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
- # 现在，到shell命令行去修改一下，增加两行
- admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最后要加载的纯文本数据内容如下
- 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'; #导入数据，指定行前缀字符为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; #校验表数据，可以看到没有xxx行前缀的行被忽略了，而包含xxx的最后一行，从xxx开始截断，xxx字符本身及其之前的内容被忽略，\
- xxx之后的内容被解析为行数据导入了
- +----+------------------------------------------+--------+
- | 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)
- 行结束符（换行符），linux下默认为\n，使用子句lines terminated by 'string' 指定，其中string代表指定的换行符
- # 指定换行符为\r\n导出数据
- 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来查看这个文本文件中的换行符，从下面的结果中可以看到，列表的每一个元素代表一行数据，每一个元素的\
- 末尾的\r\n就是这行数据的换行符
- >>> 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']
- >>>
- # 现在，把数据重新导入表，从下面的结果中可以看到，导入表中的数据正确
- 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)
众所周知，MySQL中反斜杠是SQL语句中特殊字符的转义字符，因此在sql语句中碰到特殊字符时，您必须指定一个或者两个反斜杠来为特殊字符转义（如在mysql中或者一些其他程序中，\n代表换行符，\t代表制表符，\代表转义符，那么需要使用\t来转义制表符，\n来转义换行符，\来转义转义符本身，这样才能正确写入数据库或者生成导出的数据文本,使用FIELDS ESCAPED BY子句指定转义符
特殊字符列表如
- \0 ASCII NUL (X'00') 字符
- \b 退格字符
- \n 换行符
- \r 回车符
- \t 制表符
- \Z ASCII 26 (Control+Z)
- \N NULL值，如果转义符值为空，则会直接导出null字符串作为数据，这在导入时将把null作为数据导入，而不是null符号
- # 字段引用符为"，数据中包含"，转义符和换行符保持默认，导入数据时不会有任何问题
- 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" # 可以看到与字段引用符相同的符号数据被转义了
- 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)
- # 如果字段引用符为"，字段分隔符为,且数据中包含字段引用符"和字段分隔符,，转义符和换行符保持默认，这在导入数据时不会有任何问题
- 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)
- # 但是，如果在字段引用符为"，数据中包含"，字段分隔符使用逗号，换行符保持默认的情况下，转义符使用了空串，这会导致在导入数据时，第四行无法正确解析，报错
- 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" #关于这一行数据，需要说明一下ENCLOSED BY子句，该子句指定的引用符号从一个FIELDS TERMINATED BY子句指定的分隔符开始，直到碰到下一个\
- 分隔符之间且这个分隔符前面一个字符必须是字段引用符号（如果这个分隔符前面一个字符不是字段引用符，则继续往后匹配，如第二行数据），在这之间的内容都会被当作整个列字符串处理，\
- 所以这一行数据在导入时不会发生解析错误
- 8,"a string containing a ", quote and comma","102.20" #这一行因为无法正确识别的字段结束位置，所以无法导入，报错终止，前面正确的行也被回滚掉（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)
- # 数据中包含了默认的转义符和指定的字段分隔符，字段引用符和行分隔符使用默认值，则在数据中的转义符和字段分隔符会被转义（只要不为空，则不管字段分隔符和转义字符定义为什么值，\
- 都会被转义）
- 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语句时使用了逗号作为列分隔符：
- SELECT * INTO OUTFILE 'data.txt'
- FIELDS TERMINATED BY ','
- FROM table2;
- # 如果您尝试使用\t作为列分隔符，则它将无法正常工作，因为它会指示LOAD DATA INFILE在字段之间查找制表符，可能导致每个数据行整行解析时被当作单个字段：
- LOAD DATA INFILE 'data.txt' INTO TABLE table2
- FIELDS TERMINATED BY '\t';
- # 要正确读取逗号分隔各列的文件，正确的语句是
- LOAD DATA INFILE 'data.txt' INTO TABLE table2
- FIELDS TERMINATED BY ','
- # 如果LINES TERMINATED BY换行符指定了一个空字符，并且FIELDS TERMINATED BY字段分隔符指定的是非空的一个字符(或者使用默认值\t)，则行也会以字段分隔符作为行的结束符\
- （表现行为就是文本中最后一个字符就是字段分隔符），即整个文本看上去就是一整行数据了
- 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查看文本内容，从下面的结果中可以看到，整个表的数据由于换行符为空，所以导致都拼接为一行了，最后行结束符使用了字段分隔符逗号
- >>> 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,']
- >>>
- # 导入数据到表，这里新建一张表来进行导入测试，预防清理掉了表数据之后，文本内容又无法正确导入的情况发生
- 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; #从查询结果上看，数据正确导入表test4中了
- +----+-----<
分享题目：【MySQL】loaddata语句详解（二）
本文来源：http://cdweb.net/article/giddis.html
