LoadDataInfile和IntoOutfile_MySQL-mysql教程

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char' ]

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]]

如果是Windows 系统的文本文件，可能必须使用LINES TERMINATED BY '/r/n' 来读取文件，这是因为Windows 系统的特点是使用两个字符作为行终止符。

指定Windows 路径名时，使用的是斜线而不是反斜线。如果要用反斜线，必须双写。

默认情况下，当LOAD DATA INFILE 语句后没有提供字段列时，那么LOAD DATA INFILE 认为输入行包含表列中所有的字段。如果用户希望装载表中的某些列，那指定一个字段列表：

sqlcli> LOAD DATA INFILE 'persondata.txt'

-> INTO TABLE persondata (col1,col2,...);

如果输入文件的字段顺序不同于表中列的顺序，用户也必须指定一个字段列表。否则GBase 不知道如何将输入字段与表中的列匹配。

Column 列表可以包含列名或者用户变量，并且支持SET 子句。这使得能用输入&＃20540;给用户变量赋&＃20540;， 并在赋予列的结果之前对这些&＃20540;进行变换。

SET 子句中的用户变量有多种用途。下面的例子将数据文件的第一列直接作为t1.column1 的&＃20540;，并将第二列赋予一个用户变量，此变量在作为t2.column2 的&＃20540;之前进行一个除法操作

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, @var1)

SET column2 = @var1/100;

SET 子句可以提供不是来源于输入文件的&＃20540;。下面的语句将column3 设置为当前的日期和时间：

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, column2)

SET column3 = CURRENT_TIMESTAMP;

-----------------------------------------------------------------------

LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串。关于INSERT 与LOAD DATA INFILE 的效率和提高LOAD DATA INFILE 速度的更多信息，参考管理员手册中的加速INSERT 语句 。系统变量character_set_database 所指出的字符集被用来解释文件中的信息。SET NAMES 和设置character_set_client 不会影响输入的解释。

注意：

这些规则意味着，一个以'/gsfile.txt' 给出的文件是从服务器的数据目录中读取的，然而，以`gsfile.txt' 给出的一个文件是从当前数据库的数据目录下读取的。举例来说，下面的LOAD DATA 语句从db1 数据库目录下读取文件'data.txt'，因为db1 是当前数据库，即使语句明确把文件载入到db2数据库中的表里，也会从db1 目录中读取：

sqlcli> USE db1;

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE db2.gs_table;

注意：

指定Windows 路径名时，使用的是斜线而不是反斜线。如果要用反斜线，必须双写。

出于安全的原因，当读取位于服务器上的文本文件时，文件必须位于数据库目录下或者可以被所有用户读取。也就是说，当对服务器上的文件执行LOAD DATA INFILE 时，用户必须获得FILE 权限。

LOAD DATA INFILE 是SELECT ... INTO OUTFILE 的反操作。参考SELECT 语法 。 使用SELECT ... INTO OUTFILE 将数据从一个数据库写到一个文件中。使用LOAD DATA INFILE 读取文件到数据库中。两个命令的FIELDS 和LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在LINES 子句之前。

如果用户指定一个FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

如果用户没有指定一个FIELDS 子句，缺省时如同使用下列语句：

FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//'

如果用户没有指定一个LINES 子句，缺省时如同使用下列语句：

LINES TERMINATED BY '/n' STARTING BY ''

换句话说，当读取输入时，缺省的LOAD DATA INFILE 表现如下：

在换行符处寻找行的边界。

不遗漏任何行前缀。

在制表符处将行分离成字段。

不认为字段由任何引号字符封装。

将有 “/” 开头的定位符、换行符或`/' 解释为字段&＃20540;的一个文字字符。

相反的，当写入输出时，缺省&＃20540;导致SELECT ... INTO OUTFILE 表现如下：

在字段&＃20540;间加上制表符。

不用任何引号字符封装字段。

使用 “/” 转义出现在字段&＃20540;中的定位符、换行符或`/' 字符实例。

在行的结尾处加上换行符。

注意：

为了写FIELDS ESCAPED BY '//'，用户必须指定两个反斜线，该&＃20540;会作为一个反斜线被读入。

注意：

如果是Windows 系统的文本文件，可能必须使用LINES TERMINATED BY '/r/n' 来读取文件，这是因为Windows 系统的特点是使用两个字符作为行终止符。在某些程序中，在书写文件时，可能使用/r 作为行终止符，如写字板。读取这类文件时，需要用LINES TERMINATED BY '/r'。

如果所有的将要读取的行都有用户希望忽略的前缀，可以使用LINES STARTING BY 'prefix_string' 来跳过此前缀（和在它前面的任何内容）。如果一个行没有此前缀，则整个行都被跳过。注意，prefix_string 可能在行的中间！

例如：

sqlcli> LOAD DATA INFILE '/tmp/test.txt'

-> INTO TABLE test LINES STARTING BY "xxx";

用它读取包含有下面内容的文件：

xxx"Row",1

something xxx"Row",2

则可以得到数据（“row”，1）和（“row”，2）。

IGNORE number LINES 这个选项可以用来忽略文件开头部分的行。例如，可以用IGNORE 1 LINES 来跳过含有列名的的头一行：

sqlcli> LOAD DATA INFILE '/tmp/test.txt'

-> INTO TABLE test IGNORE 1 LINES;

当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。假设用户使用SELECT ... INTO OUTFILE 以逗号分隔字段的方式将数据写入到一个文件中：

sqlcli> SELECT * INTO OUTFILE 'data.txt'

-> FIELDS TERMINATED BY ','

-> FROM table2;

为了将由逗号分隔的文件读回时，正确的语句应该是：

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2

-> FIELDS TERMINATED BY ',';

如果用户试图用下面所示的语句读取文件，它将不会工作，因为命令LOAD DATA INFILE 以定位符区分字段&＃20540;：

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2

-> FIELDS TERMINATED BY '/t';

可能的结果是每个输入行将被解释为一个单独的字段。

LOAD DATA INFILE 也可以被用来读取外部源获得的文件。例如，dBASE &＃26684;式的文件，字段以逗号分隔并以双引号包围着。如果文件中的行以一个换行符终止，那么下面所示的可以说明用户将用来装载文件的字段和行处理选项：

sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name

-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'

-> LINES TERMINATED BY '/n';

任何字段和行处理选项都可以指定一个空字符串（''）。如果不是空的，FIELDS [OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPED BY &＃20540;必须是一个单个字符。FIELDS TERMINATED BY 和LINES TERMINATED BY &＃20540;可以超过一个字符。例如，为了写入由回车换行符终止的行，或读取包含这样的行的文件，应该指定一个LINES TERMINATED BY '/r/n' 子句。

FIELDS [OPTIONALLY] ENCLOSED BY 控制字段的引用。对于输出（SELECT ... INTO OUTFILE），如果用户省略单词OPTIONALLY，所有的字段被ENCLOSED BY 字符包围。这样的一个输出文件（以一个逗号作为字段分界符）示例如下：

"1","a string","100.20"

"2","a string containing a , comma","102.20"

"3","a string containing a /" quote","102.20"

"4","a string containing a /", quote and comma","102.20"

如果用户指定OPTIONALLY，ENCLOSED BY 字符仅被用于包装诸如含有字符串类型的字段（诸如CHAR，BINARY，TEXT 或ENUM）：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a /" quote",102.20

4,"a string containing a /", quote and comma",102.20

注意：

在一个字段&＃20540;中出现的ENCLOSED BY 字符，通过用ESCAPED BY 字符作为其前缀对其转义。同时也要注意，如果用户指定一个空的ESCAPED BY &＃20540;，可能会产生不能被LOAD DATA INFILE 正确读出的输出文件。例如，如果转义字符为空，上面显示的输出将变成如下显示的输出。请注意第四行的第二个字段，它包含一个跟在一个引号后的逗号，看起来 像是一个字段的终止：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a " quote",102.20

4,"a string containing a ", quote and comma",102.20

对于输入，ENCLOSED BY 字符如果存在，它将从字段&＃20540;的尾部被剥离。（不管OPTIONALLY 是否被指定，都是这样；对于输入解释，OPTIONALLY 不会影响它。）如果在ENCLOSED BY 字符前存在ESCAPED BY 字符，那么它将被解释为当前字段&＃20540;的一部分。

如果字段以ENCLOSED 字符开始,只要后面紧跟着字段或行TERMINATED BY 序列 ，这个字符实例就被认为用来终止一个字段&＃20540;。为了明确，如果在字段中要使用ENCLOSED BY 字符，可以重复写两遍该字符，那么它们会被解释成单个ENCLOSED BY字符处理。例如，如果指定ENCLOSED BY '"'，引号将做如下处理：

"The ""BIG"" boss" -> The "BIG" boss

The "BIG" boss -> The "BIG" boss

The ""BIG"" boss -> The ""BIG"" boss

FIELDS ESCAPED BY 控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY 字符不是空的，它将被用于做为下列输出字符的前缀：

FIELDS ESCAPED BY 字符

FIELDS [OPTIONALLY] ENCLOSED BY 字符 。

FIELDS TERMINATED BY 和LINES TERMINATED BY &＃20540;的第一个字符。

ASCII 0 （实际上在转义字符后写上ASCII '0'，而不是一个零&＃20540;字节）。

如果FIELDS ESCAPED BY 字符为空，那么将没有字符被转义并且NULL &＃20540;仍输出为NULL，而不是/N。指定一个空的转义字符可能不是一个好的方法，特别是用户的数据字段&＃20540;中包含刚才列表中的任何字符时。

对于输入&＃20540;，如果FIELDS ESCAPED BY 字符不是空字符，则出现这种字符时会被剥离，然后以下字符被作为字段&＃20540;的一部分。例外情况是，被转义的‘0’或‘N’（例如，/0 或/N，此时转义符为‘/’）。这些序列被理解为ASCII NUL（一个零&＃20540;字节）和NULL。用于NULL 处理的规则在本节的后部进行说明。

关于更多的 “/” 转义语法信息，查看文字&＃20540; 。

在某些情况下，字段与行处理相互作用：

如果LINES TERMINATED BY 是一个空字符串，FIELDS TERMINATED BY 是非空的，则各行以FIELDS TERMINATED BY作为结尾。

如果FIELDS TERMINATED BY 和FIELDS ENCLOSED BY &＃20540;都是空的('')，那么一个固定行(无定界符) &＃26684;式将被使用。用固定行&＃26684;式时，在字段之间不使用分隔符（但是用户仍然有一个行终结符）。列&＃20540;的写入和读取使用列的“显示”宽度。例如，如果一个列被定义 为INT(7)，列的&＃20540;将使用7 个字符的字段被写入。对于输入，列&＃20540;通过读取7 个字符来获得。

LINES TERMINATED BY 仍然用于分离行。如果一行没有包含所有的字段，那么列的剩余部分被设置为它们的默认&＃20540;。如果用户没有一个行终结符，用户应该设置它为''。在这种情况下，文本文件必须包含每行的所有的字段。

固定行&＃26684;式也影响对NULL &＃20540;的处理；见下面。注意，如果用户正在使用一个多字节的字符集，固定长度&＃26684;式将不能工作。

NULL &＃20540;的处理有很多，取决于用户所使用的FIELDS 和LINES 选项：

对于缺省的FIELDS 和LINES &＃20540;，输出时，NULL 被写成/N，当读入时，/N 被作为NULL 读入（假设ESCAPED BY 字符为 “/”）。

如果FIELDS ENCLOSED BY 不是空&＃20540;，则包含以文字词语NULL 为&＃20540;的字段被作为NULL &＃20540;读取。这与被FIELDS ENCLOSED BY 字符包围的词语NULL不同。该词语被作为字符串'NULL'读取。

如果FIELDS ESCAPED BY 是空的，NULL &＃20540;被写为词NULL。

采用固定行&＃26684;式时（当FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均为空&＃20540;时采用），NULL 被作为一个空字符串写入。注意，这会导致在被写入文件时，表中的NULL &＃20540;和空字符串均无法辨别，这是因为两者都被作为空字符串写入。如果用户需要在读取文件并返回时能够分辨两者，则用户不应使用固定行&＃26684;式。

一些不能被LOAD DATA INFILE 支持的情况：

固定尺寸的记录行（FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均为空）和BLOB 或TEXT 列。

如果用户指定一个分隔符与另一个相同，或是另一个的前缀，LOAD DATA INFILE 可能会不能正确地解释输入。例如，下列的FIELDS 子句将会产生问题：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果FIELDS ESCAPED BY 为空，一个字段&＃20540;中包含有FIELDS ENCLOSED BY 或LINES TERMINATED BY 后面紧跟着FIELDS TERMINATED BY 的&＃20540;时，将会引起LOAD DATA INFILE 过早地停止读取一个字段或一行。这是因为LOAD DATA INFILE 不知道字段或行&＃20540;在哪里结束。

下面的例子将装载persondata 表的所有列：

sqlcli> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

默认情况下，当LOAD DATA INFILE 语句后没有提供字段列时，那么LOAD DATA INFILE 认为输入行包含表列中所有的字段。如果用户希望装载表中的某些列，那指定一个字段列表：

sqlcli> LOAD DATA INFILE 'persondata.txt'

-> INTO TABLE persondata (col1,col2,...);

如果输入文件的字段顺序不同于表中列的顺序，用户也必须指定一个字段列表。否则GBase 不知道如何将输入字段与表中的列匹配。

Column 列表可以包含列名或者用户变量，并且支持SET 子句。这使得能用输入&＃20540;给用户变量赋&＃20540;， 并在赋予列的结果之前对这些&＃20540;进行变换。

SET 子句中的用户变量有多种用途。下面的例子将数据文件的第一列直接作为t1.column1 的&＃20540;，并将第二列赋予一个用户变量，此变量在作为t2.column2 的&＃20540;之前进行一个除法操作

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, @var1)

SET column2 = @var1/100;

SET 子句可以提供不是来源于输入文件的&＃20540;。下面的语句将column3 设置为当前的日期和时间：

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, column2)

SET column3 = CURRENT_TIMESTAMP;

通过将一个输入&＃20540;赋予一个用户变量，可以将此输入&＃20540;丢弃，并且不将此&＃20540;赋予表的一个列：

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, @dummy, column2, @dummy, column3);

使用列/变量列表和SET子句要受到一下限制：

SET 子句中的赋&＃20540;列名应当只能在赋&＃20540;操作符的左边。

在SET 赋&＃20540;语句中，可以使用子查询。此查询返回一个将被赋予列的&＃20540;，它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。

对于列/变量列表或SET 子句，由于使用IGNORE 子句而被忽略的行就不会被处理。

由于用户变量没有显示宽度，当导入的数据是行固定&＃26684;式时，将不能使用用户变量。

当处理输入行时，LOAD DATA 将行分为域，如果提供了列/变量列表和SET 子句，将按照它去使用这些&＃20540;。然后得到的行就插入到表中。如果此表有BEFORE INSERT 或AFTER INSERT 触发器，则插入行前后将分别激活它们。

如果输入的记录行有太多的字段，多余的字段将被忽略，并增加警告的数目。

如果一个输入行的字段较少，没有输入字段的列将被设置为缺省&＃20540;。缺省&＃20540;赋&＃20540;在CREATE TABLE 语法 中被描述。

一个空的字段&＃20540;不同于字段&＃20540;丢失的解释：

对于字符串类型，列被设置为空字符串。

对于数字类型，列被设置为0。

对于日期和时间类型，列被设置为适合列类型的“零”&＃20540;。参考日期和时间类型 。

如果在一个INSERT 或UPDATE 语句中明确地将一个空字符串赋给一个字符串、数字或日期或时间类型，用户会得到与上面相同的结果。

只有在两种情况下TIMESTAMP 列被设置为当前日期和时间。一种情况时当列有一个NULL &＃20540;（也就是/N）时；另一种情况是（仅对于第一个TIMESTAMP 列），当一个字段清单被指定时，TIMESTAMP 列会从字段清单中被略去。

LOAD DATA INFILE 认为所有的输入均是字符串，因而，对于ENUM 或SET 列，用户不能以INSERT 语句的形式为其设置数字&＃20540;。所有的ENUM 和SET 必须以字符串指定！

当LOAD DATA INFILE 查询结束时，它返回信息字符串，&＃26684;式如下：

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0