mysql load data 一行_【MySQL】load data语句详解（二）

作者：罗小波

沃趣科技高级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为换行符，所以使用这些命令可能无法看到这些特殊符号)

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

>>> data = f.readlines()

>>> data

['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']

>>> for i in data:

... print i,

...

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"

当mysql server从文本文件读取数据时，FIELDS和LINES默认值会导致LOAD DATA INFILE的行为如下：

寻找换行边界字符\n来进行换行

不跳过行前缀，把行前缀也当作数据(发生在如果导出数据时使用了行前缀，导入时没有指定正确的行前缀或者根本没有指定行前缀选项时)

使用制表符\t来分割一行数据中的各列

要注意：在FIELDS和LINES的默认值下，在解析文本文件时不会把字符串之间的引号当作真正的引号，而是当作数据

1.2.6.1. FIELDS关键字及其子句详解

字段分隔符，默认是\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等字符型字段上，数值类型会忽略使用引用符，如果不指定该子句，则默认不使用引用符，使用子句fields

[optionally] enclosed by 'char'指定，其中char代表指定的字段引用符

# 指定字段引用符为"，不使用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

转义字符，默认为\，使用子句fields escaped by 'char' 指定，其中char代表指定的转义字符

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)

1.2.6.2. LINES 关键字及其子句详解

行前缀字符串，使用子句lines starting by 'string'

指定，其中string代表指定的行前缀字符串，行前缀字符串在导出文本数据时使用该子句指定，在导入文本时在一行数据中如果发现了行前缀字符串，则只导入从前缀字符串开始之后的数据部分，前缀字符本身及其之前的数据被忽略掉，如果某行数据不包含行前缀字符串，则整行数据都会被忽略

如果您想要读取的纯文本文件中所有行都有一个您想要忽略的公用前缀，则可以使用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','