对于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
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'来跳过这个前缀，以及前缀字符前面的任何内容。如果某行数据不包含前缀字符，则跳过整行内容，例
# 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中了
+----+------------------------------------------+--------+
| 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)
# 如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都为空（''），则使用固定行（非限制）格式。使用固定行格式时，字段之间使用足够宽的空格来分割各字段。对于数据类型\
是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，字段宽度分别为4,6,8,11和20个空格(无论数据类型声明的显示宽度如何)，对于varchar类型使用大约298个空格（这个空格数量是自己\
数的。。。，猜想这个空格数量可能与字符集，varchar定义长度有关，因为我在尝试把varchar定义为50个字符的时候，空格少了156个左右）
admin@localhost : xiaoboluo 04:58:31> system rm -f /tmp/test3.txt;
admin@localhost : xiaoboluo 05:04:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY '' lines terminated by '';
Query OK, 6 rows affected (0.00 sec)
admin@localhost : xiaoboluo 05:04:17> 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 ... 14 \\t ... 102.20 ... admin@localhost : xiaoboluo 05:04:35>
# 现在，清理掉test4表，并载入数据，从下面的结果中可以看到，导入表中之后，虽然数据是对的，但是多了非常多的空格，那么也就意味着你需要使用程序正确地处理一下这些多余的空格之后，\
再执行导入
admin@localhost : xiaoboluo 05:06:19> truncate test4;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 05:06:47> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY '' lines terminated by ''; # 注意：这是在sql_mode=''时导入的，如果不修改\
sql_mode请使用local关键字
Query OK, 6 rows affected, 12 warnings (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 12
Note (Code 1265): Data truncated for column 'test' at row 1
Note (Code 1265): Data truncated for column 'test2' at row 1
Note (Code 1265): Data truncated for column 'test' at row 2
Note (Code 1265): Data truncated for column 'test2' at row 2
Note (Code 1265): Data truncated for column 'test' at row 3
Note (Code 1265): Data truncated for column 'test2' at row 3
Note (Code 1265): Data truncated for column 'test' at row 4
Note (Code 1265): Data truncated for column 'test2' at row 4
Note (Code 1265): Data truncated for column 'test' at row 5
Note (Code 1265): Data truncated for column 'test2' at row 5
Note (Code 1265): Data truncated for column 'test' at row 6
Note (Code 1265): Data truncated for column 'test2' at row 6
admin@localhost : xiaoboluo 05:07:09> select * from test4;
+----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 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)
# 对于默认的FIELDS和LINES值，NULL值被转义为\N输出，字段值\N读取时使用NULL替换并输入（假设ESCAPED BY字符为\）
admin@localhost : xiaoboluo 05:17:07> alter table test3 add column test3 varchar(10); #添加一个字段test3，默认值会被填充为null
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
admin@localhost : xiaoboluo 05:17:33> select * from test3; #查看表中的test3列数据
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | NULL |
| 4 | a string containing a , comma | 102.20 | NULL |
| 6 | a string containing a " quote | 102.20 | NULL |
| 8 | a string containing a ", quote and comma | 102.20 | NULL |
| 10 | \t | 102.20 | NULL |
| 14 | \t | 102.20 | NULL |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
admin@localhost : xiaoboluo 05:17:37> select * from test3 into outfile "/tmp/test3.txt"; #执行导出
Query OK, 6 rows affected (0.00 sec)
admin@localhost : xiaoboluo 05:18:02> system cat /tmp/test3.txt #查看导出的文本文件，可以发现null被转义为\N了，这是为了避免数据字符串本身包含null值时无法正确区分数据类型的null值
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
10 \\t 102.20 \N
14 \\t 102.20 \N
# 导入数据，从结果中可以看到\N被正确解析为了数据类型的null值
admin@localhost : xiaoboluo 05:18:06> truncate test3;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 05:20:36> load data infile '/tmp/test3.txt' into table test3;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
admin@localhost : xiaoboluo 05:20:52> select * from test3;
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | NULL |
| 4 | a string containing a , comma | 102.20 | NULL |
| 6 | a string containing a " quote | 102.20 | NULL |
| 8 | a string containing a ", quote and comma | 102.20 | NULL |
| 10 | \t | 102.20 | NULL |
| 14 | \t | 102.20 | NULL |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
# 如果FIELDS ENCLOSED BY不为空，FIELDS escaped BY为空时，则将NULL值的字面字符串作为输出字符值。这与FIELDS ENCLOSED BY引用的字符串中包含的null值不同，\
后者读取为字符串'null'，而前者读取到数据库中时被当作数据类型的null值，而不是数据的字符串null
admin@localhost : xiaoboluo 05:20:57> update test3 set test3='null' where id=2; #更新id=2的test3列值为数据字符串的null
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
admin@localhost : xiaoboluo 05:23:14> select * from test3;
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | null |
| 4 | a string containing a , comma | 102.20 | NULL |
| 6 | a string containing a " quote | 102.20 | NULL |
| 8 | a string containing a ", quote and comma | 102.20 | NULL |
| 10 | \t | 102.20 | NULL |
| 14 | \t | 102.20 | NULL |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
admin@localhost : xiaoboluo 05:23:16> system rm -f /tmp/test3.txt;
admin@localhost : xiaoboluo 05:24:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '"' escaped BY ''; #指定行引用符号为双引号"，转义符为空导出数据
Query OK, 6 rows affected (0.00 sec)
admin@localhost : xiaoboluo 05:24:51> system cat /tmp/test3.txt #查看导出的文本文件，可以看到数据字符串的null被加了双引号，而数据类型的null没有加双引号
"2" "a string" "100.20" "null"
"4" "a string containing a , comma" "102.20" NULL
"6" "a string containing a " quote" "102.20" NULL
"8" "a string containing a ", quote and comma" "102.20" NULL
"10" "\t" "102.20" NULL
"14" "\t" "102.20" NULL
admin@localhost : xiaoboluo 05:24:57> alter table test4 add column test3 varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
admin@localhost : xiaoboluo 05:26:40> truncate test4; #这里使用test4表做测试，避免无法导入的情况发生
Query OK, 0 rows affected (0.00 sec)
admin@localhost : xiaoboluo 05:26:44> load data infile '/tmp/test3.txt' into table test4 FIELDS ENCLOSED BY '"' escaped BY ''; #指定字段引用符为双引号"，转义符为空导入数据
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
admin@localhost : xiaoboluo 05:27:02> select * from test4; #查看表test4中的数据，从结果中可以看到，数据导入正确
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | null |
| 4 | a string containing a , comma | 102.20 | NULL |
| 6 | a string containing a " quote | 102.20 | NULL |
| 8 | a string containing a ", quote and comma | 102.20 | NULL |
| 10 | \t | 102.20 | NULL |
| 14 | \t | 102.20 | NULL |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
# 使用固定行格式（当FIELDS TERMINATED BY和FIELDS ENCLOSED BY都为空时使用），将NULL写为空字符串。这将导致表中的NULL值和空字符串在写入文件时无法区分，\
因为它们都以空字符串形式写入文本文件。如果您需要能够在读取文件时将其分开，则不应使用固定行格式（即不应该使用FIELDS TERMINATED BY和FIELDS ENCLOSED BY都为空）
admin@localhost : xiaoboluo 05:29:11> system rm -f /tmp/test3.txt;
admin@localhost : xiaoboluo 05:29:22> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '' TERMINATED BY '';
Query OK, 6 rows affected (0.00 sec)
admin@localhost : xiaoboluo 05:29:43> system cat /tmp/test3.txt #从结果中看，是不是有点似曾相识呢？没错，前面演示过FIELDS TERMINATED BY和FIELDS ENCLOSED BY都为空的情况，\
使用了固定格式来导出文本，但是这里多了数据类型的null值处理，从下面的结果中已经看不到数据类型的null了，被转换为了空值(下面展示时把大段空格使用...代替)
2 a string ... 100.20 ... null
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 05:29:46> truncate test4; #清空test4
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 05:34:15> load data infile "/tmp/test3.txt" into table test4 FIELDS ENCLOSED BY '' TERMINATED BY ''; #执行导入文本到test4表，注意：这是在sql_mode=''时导入的，\
如果不修改sql_mode请使用local关键字
Query OK, 6 rows affected, 24 warnings (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 24
Note (Code 1265): Data truncated for column 'test' at row 1
Note (Code 1265): Data truncated for column 'test2' at row 1
Note (Code 1265): Data truncated for column 'test3' at row 1
Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Note (Code 1265): Data truncated for column 'test' at row 2
Note (Code 1265): Data truncated for column 'test2' at row 2
Note (Code 1265): Data truncated for column 'test3' at row 2
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
Note (Code 1265): Data truncated for column 'test' at row 3
Note (Code 1265): Data truncated for column 'test2' at row 3
Note (Code 1265): Data truncated for column 'test3' at row 3
Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
Note (Code 1265): Data truncated for column 'test' at row 4
Note (Code 1265): Data truncated for column 'test2' at row 4
Note (Code 1265): Data truncated for column 'test3' at row 4
Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
Note (Code 1265): Data truncated for column 'test' at row 5
Note (Code 1265): Data truncated for column 'test2' at row 5
Note (Code 1265): Data truncated for column 'test3' at row 5
Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
Note (Code 1265): Data truncated for column 'test' at row 6
Note (Code 1265): Data truncated for column 'test2' at row 6
Note (Code 1265): Data truncated for column 'test3
load data执行时如果表中有外键、辅助索引、唯一索引，那么会导致加载数据的时间变慢，因为索引也需要一同更新，可以使用对应参数关闭外键检查、唯一索引检查甚至关闭索引
如果在sql_mode设置为严格模式下，且不使用local和ignore关键字时，碰到缺少字段值会直接报错终止，但在sql_mode设置为严格模式下，使用了local和ignore关键字时，则行为与不使用严格模式类似
LOAD DATA INFILE将所有输入视为字符串，因此您不能认为load data语句会像INSERT语句那样插入ENUM或SET列的数值。所有ENUM和SET值必须指定为字符串
LOAD DATA INFILE不支持的场景
PS：在Unix上，如果需要LOAD DATA从管道读取数据，可以使用以下方法（该示例将/目录的列表加载到表db1.t1中，find命令挂后台持续查找内容并生成ls.dat文件，mysql 客户端使用-e选项来执行load data这个文件到表）：
mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls> /mysql/data/db1/ls.dat＆
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1
admin@localhost : xiaoboluo 05:34:41> system cat /tmp/test3.txt
id test test2 test3
2 a string 100.20 null
4 a string containing a , comma 102.20 NULL
6 a string containing a " quote 102.20 NULL
8 a string containing a ", quote and comma 102.20 NULL
10 \\t 102.20 NULL
14 \\t 102.20 NULL
admin@localhost : xiaoboluo 05:41:35> truncate test4;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 05:41:41> load data infile "/tmp/test3.txt" into table test4 ignore 1 lines; #载入文本时指定ignore 1 lines子句忽略文本中的前1行数据
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
admin@localhost : xiaoboluo 05:42:22> select * from test4; #查询表test4中的数据，从下面的结果中可以看到数据正确
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | null |
| 4 | a string containing a , comma | 102.20 | NULL |
| 6 | a string containing a " quote | 102.20 | NULL |
| 8 | a string containing a ", quote and comma | 102.20 | NULL |
| 10 | \t | 102.20 | NULL |
| 14 | \t | 102.20 | NULL |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
# 如果输入值不一定包含在引号内，请在ENCLOSED BY关键字之前使用OPTIONALLY，如：OPTIONALLY ENCLOSED BY '"'，加上OPTIONALLY 可能会忽略数值类型的字段的引用符号，\
另外，如果你的csv文件第一行是数据而不是列名，那就不能使用IGNORE 1 LINES子句
如果只想加载某些列，请指定列列表，如：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语句写在一起
如果发现文件中的列顺序和表中的列顺序不符，或者只想加载部分列，在命令中加上列的顺序时指定的字段名也不一定非要放在紧跟着表名，可以放在语句最后面也可以，如：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小节的“如果文本文件中的数据字段与表结构中的字段定义顺序不同，则使用如下语句指定载入表中的字段顺序”演示部分
# 如果系统将id列的文本数据加上10以后再加载到表的test3列中，可以如下操作：
admin@localhost : xiaoboluo 06:05:42> system rm -f /tmp/test3.txt;
admin@localhost : xiaoboluo 06:06:00> select * from test3 into outfile "/tmp/test3.txt";
Query OK, 6 rows affected (0.00 sec)
admin@localhost : xiaoboluo 06:06:04> system cat /tmp/test3.txt
2 a string 100.20 null
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
10 \\t 102.20 \N
14 \\t 102.20 \N
admin@localhost : xiaoboluo 06:07:49> truncate test4;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 06:07:53> load data infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+10 ;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
admin@localhost : xiaoboluo 06:08:02> select * from test4; #严格模式下因为文本中多了一个字段被截断了，所以拒绝导入
Empty set (0.00 sec)
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值列进行导入，\
注意，如果不使用local关键字，那就需要修改sql_mode才能导入
Query OK, 6 rows affected, 6 warnings (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 6
Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns
admin@localhost : xiaoboluo 06:10:45> select * from test4;
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | 12 |
| 4 | a string containing a , comma | 102.20 | 14 |
| 6 | a string containing a " quote | 102.20 | 16 |
| 8 | a string containing a ", quote and comma | 102.20 | 18 |
| 10 | \t | 102.20 | 20 |
| 14 | \t | 102.20 | 24 |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
# 或者使用txt文件中的某些列进行计算后生成新的列插入，这里演示两个字段进行相加后导入另外一个字段中：
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才能导入
Query OK, 6 rows affected, 6 warnings (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 6
Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns
admin@localhost : xiaoboluo 06:19:07> select * from test4;
+----+------------------------------------------+--------+-------+
| id | test | test2 | test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 | 102.2 |
| 4 | a string containing a , comma | 102.20 | 106.2 |
| 6 | a string containing a " quote | 102.20 | 108.2 |
| 8 | a string containing a ", quote and comma | 102.20 | 110.2 |
| 10 | \t | 102.20 | 112.2 |
| 14 | \t | 102.20 | 116.2 |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
# 可以直接使用一个用户变量并进行计算(计算表达式可以使用函数、运算符、子查询等都允许)，然后赋值给test4列直接导入，而不需要从文件中读取test4列数据，该列数据也允许在文件中不存在
admin@localhost : xiaoboluo 06:27:06> alter table test4 add column test4 varchar(20); #新建一个字段test4，用于导入set子句计算的值
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
admin@localhost : xiaoboluo 06:27:56> truncate test4;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 06:28:02> set @test=200; #设置一个用户变量
Query OK, 0 rows affected (0.00 sec)
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列通过表达式\
round(@test/100,0)计算之后的值
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
admin@localhost : xiaoboluo 06:30:52> select * from test4; #查看test4表中导入的数据，从以下结果中来看，导入数据正确
+----+------------------------------------------+--------+-------+-------+
| id | test | test2 | test3 | test4 |
+----+------------------------------------------+--------+-------+-------+
| 2 | a string | 100.20 | null | 2 |
| 4 | a string containing a , comma | 102.20 | NULL | 2 |
| 6 | a string containing a " quote | 102.20 | NULL | 2 |
| 8 | a string containing a ", quote and comma | 102.20 | NULL | 2 |
| 10 | \t | 102.20 | NULL | 2 |
| 14 | \t | 102.20 | NULL | 2 |
+----+------------------------------------------+--------+-------+-------+
6 rows in set (0.00 sec)
# SET子句可以将一个内部函数返回的值直接导入到一个指定列
admin@localhost : xiaoboluo 06:31:22> truncate test4;
Query OK, 0 rows affected (0.01 sec)
admin@localhost : xiaoboluo 06:40:58> load data infile "/tmp/test3.txt" into table test4 (id,test,test2,test3) set test4=now() ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
admin@localhost : xiaoboluo 06:41:02> select * from test4;
+----+------------------------------------------+--------+-------+---------------------+
| id | test | test2 | test3 | test4 |
+----+------------------------------------------+--------+-------+---------------------+
| 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |
| 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |
| 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |
| 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |
| 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
| 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
+----+------------------------------------------+--------+-------+---------------------+
6 rows in set (0.00 sec)
使用指定列名或者变量列表时SET子句的使用受以下限制：
如果输入行的字段太多（多过表中的字段数量），则会忽略额外的字段，并增加警告数。如果输入行的字段太少，那么输入字段缺少的表列被设置为其默认值，在解析文本文件时，空串字段数据与缺少字段值不同（空串会直接作为数据插入，而缺少字段时，会根据字段定义的默认值进行填充），如下：
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/28218939/viewspace-2139721/
