LOAD DATA INFILE 语法

原文地址:http://blog.sina.com.cn/s/blog_539f03f00100xfxb.html

mysql 的LOAD DATA INFILE 是一个高速insert的不错的方案 篇文章语法介绍的比较详细，转载，记录。

其实我就这样用：

load data infile '/home/mark/data_update.sql' replace into table test FIELDS TERMINATED BY ',' (id,name) 

terminate[英][?t?:mineit]

以后有机会说说，利用LOAD DATA INFILE进行批量更新、插入。

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)]
LOAD DATA INFILE 语句以非常高的速度从一个文本文件中读取记录行并插入到一个表中。如果 LOCAL 关键词被指定，文件从客户端主机读取。如果 LOCAL 没有被指定，文件必须位于服务器上。由于安全性的原因，当读取位于服务器端的文本文件时，文件必须处于数据库目录或可被所有人读取的地方。同时，为了对服务器端的文件使用 LOAD DATA INFILE，你必须在服务器主机上有 FILE 权限。只有当你没有以 --local-infile=0 选项启动mysqld，或你没有禁止你的客户端程序支持 LOCAL的情况下，LOCAL 才会工作。查看章节，如果你指定关键词LOW_PRIORITY，LOAD DATA 语句的执行将会被延迟，直到没有其它的客户端正在读取表。

如果你对一个 MyISAM 表指定关键词 CONCURRENT，那么当 LOAD DATA正在执行时，其它的线程仍可以从表中检索数据。使用这个选项时，如果同时也有其它的线程正在使用表，这当然会有一点影响 LOAD DATA 的执行性能。

使用 LOCAL 将比让服务器直接访问文件要慢一些，因为文件的内容必须从客户端主机传送到服务器主机。而在另一方面，你不再需要有 FILE 权限用于装载本地文件。

你也可以使用 mysqlimport 实用程序装载数据文件；它通过发送一个 LOAD DATA INFILE 命令到服务器来动作。--local 选项使得 mysqlimport 从客户端主机读取数据文件。如果客户端与服务器支持压缩协议，你可以指定 --compress 选项，以在较慢的网络中获得更好的性能。

当从服务器主机定位文件时，服务器使用下列规则：

如果给定一个完整的路径，服务器使用该路径名。

如果给定一个有一个或多个前置构件的相对路径，服务器以相对服务器的数据目录搜索文件。

如果给定一个没有前置构件的文件名，服务器从当前数据库的数据库目录搜寻文件。

注意，这些规则意味着，一个以 `./myfile.txt' 给出的文件是从服务器的数据目录中读取的，然而，以`myfile.txt' 给出的一个文件是从当前数据库的数据目录下读取的。举例来说，下面的 LOAD DATA 语句从 db1数据库目录下读取文件 `data.txt'，因为 db1 是当前数据库，即使该语句明确地指定读取的文件被放入到 db2数据库中的一个表中：
mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

REPLACE 和 IGNORE 关键词控制对与现有的记录在唯一键值上重复的记录的处理。如果你指定 REPLACE，新的记录行将替换有相同唯一键值的现有记录行。如果你指定 IGNORE，将跳过与现有的记录行在唯一键值上重复的输入记录行。如果你没有指定任何一个选项，当重复键值出现时，将会发生一个错误，文本文件的剩余部分也将被忽略。

如果你使用 LOCAL 关键词从一个本地文件中读取数据，在此操作过程中，服务器没有办法停止文件的传送，因此缺省的处理方式就好像是 IGNORE 被指定一样。

如果你在一个空的 MyISAM 表上使用 LOAD DATA INFILE，所有非唯一索引会以一个分批方式被创建(就像REPAIR)。当有许多索引时，这通常可以使 LOAD DATA INFILE 更快一些。

LOAD DATA INFILE 的 SELECT ... INTO OUTFILE 的逆操作。使用 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'

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

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

在定位符处将行分开放到字段中。

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

将有 “\” 开头的定位符、换行符或 `\' 解释为字段值的一个文字字符。

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

在字段值间加上定位符。

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

使用 “\” 转义出现在字段值中的定位符、换行符或 `\' 字符实例。

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

注意，为了写 FIELDS ESCAPED BY '\\'，你必须指定两个反斜线，该值会作为一个反斜线被读入。

IGNORE number LINES 选项可被用于忽略文件开头处的一个列名的头：

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

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

为了将由逗号分隔的文件读回时，正确的语句应该是：
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY ',';

如果你试图用下面所示的语句读取文件，它将不会工作，因为命令 LOAD DATA INFILE 以定位符区分字段值：
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY '\t';

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

LOAD DATA INFILE 也可以被用来读取从外部来源获得的文件。例如，dBASE 格式的文件，字段以逗号分隔并以双引号包围着。如果文件中的行以一个换行符终止，那么下面所示的可以说明你将用来装载文件的字段和行处理选项：
mysql> 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 值必须是一个单个字符。FIELDS TERMINATED BY 和 LINES TERMINATED BY 值可以超过一个字符。例如，为了写入由回车换行符终止的行，或读取包含这样的行的文件，应该指定一个 LINES TERMINATED BY '\r\n' 子句。

举例来说，为了读取一个文件到一个 SQL 表中，文件以一行 %% 分隔(开玩笑的)，你可以这样做：
CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY "" LINES TERMINATED BY "\n%%\n" (joke);

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 和 VARCHAR 字段：
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 字符，通过用 ESCAPED BY 字符作为其前缀对其转义。同时也要注意，如果你指定一个空的 ESCAPED BY 值，可能会产生不能被 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 字符如果存在，它将从字段值的尾部被剥离。(不管 OPTIONALLY 是否被指定，都是这样；对于输入解释，OPTIONALLY 不会影响它。) 由ESCAPED BY 字符领先于 ENCLOSED BY 字符的出现，将被解释为当前字段值的一部分。另外，在字段中出现的重复的 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 值的第一个字符。

ASCII 0 (实际上在转义字符后写上 ASCII '0'，而不是一个零值字节)

如果 FIELDS ESCAPED BY 字符为空，没有字符被转义。指定一个空的转义字符可能不是一个好的主意，特别是如果你的数据字段值中包含刚才列表中的任何字符时。

对于输入，如果 FIELDS ESCAPED BY 字符不为空，该字符的出现将会被剥离，后续的字符在字面上做为字段值的一部分。除了一个转义的 “0” 或 “N” (即，\0 或\N，如果转义字符为 `\')。这些序列被解释为 ASCII0 (一个零值字节) 和 NULL。查看下面的有关 NULL 处理的规则。

关于更多的 “\” 转义句法信息，查看章节

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

如果 LINES TERMINATED BY 是一个空字符串，FIELDS TERMINATED BY 是非空的，行也用 FIELDS TERMINATED BY 终止。

如果 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 值都是空的 ('')，一个固定行(无定界符) 格式被使用。用固定行格式时，在字段之间不使用分隔符。代替的，列值的写入和读取使用列的“显示”宽度。例如，如果一个列被定义为 INT(7)，列的值将使用 7 个字符的字段被写入。对于输入，列值通过读取 7 个字符来获得。固定行格式也影响对 NULL 值的处理；见下面。注意，如果你正在使用一个多字节的字符集，固定长度格式将不能工作。

NULL 值的处理有很多，取决于你所使用的 FIELDS 和 LINES 选项：

对于缺省的 FIELDS 和 LINES 值，输出时，NULL 被写成 \N，当读入时，\N 被作为 NULL 读入(假设ESCAPED BY 字符为 “\”)。

如果 FIELDS ENCLOSED BY 是非空的，一个字段包含文字词 NULL 的，它的值做为一个 NULL 值被读入 (这不同于被 FIELDS ENCLOSED BY 包围的词 NULL，它是被作为 'NULL' 读入的)。

如果 FIELDS ESCAPED BY 是空的，NULL 值被写为词 NULL。

用固定行格式时 (它发生于 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 两者均为空)，NULL 被写为一个空的字符串。注意，当将表中的 NULL 值和空字符串一起写到文件中时，它们将被混淆，因为它们都是作为空字符串被写入的。如果你在文件时，需要对他们两个进行区分，你不应该使用固定行格式。

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

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

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

如果 FIELDS ESCAPED BY 为空，一个字段值中包含有 FIELDS ENCLOSED BY 或 LINES TERMINATED BY 被FIELDS TERMINATED BY 跟随的值时，将会引起 LOAD DATA INFILE 过早地停止读取一个字段或一行。这是因为 LOAD DATA INFILE 不能够正确地决定字段或行值在哪里结果。

下面的例子将装载 persondata 表的所有列：
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

没有字段列被指定，因而 LOAD DATA INFILE 认为输入行包含表列中所有的字段。使用缺省的 FIELDS 和 LINES值。

如果你希望装载表中的某些列，那指定一个字段列表：
mysql> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...);

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

如果一个行有很少的字段，没有输入字段的列将被设置为缺省值。缺省值赋值在章节 中被描述。

一个空的字段值不同于字段值丢失的解释：

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

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

对于日期和时间类型，列被设置为适合列类型的“零”值。查看章节 。

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

如果对 TIMESTAMP 列指定一个 NULL 值，或者当字段列表被指定时， TIMESTAMP 在字段列表中被遗漏(仅仅第一个 TIMESTAMP 列被影响)，TIMESTAMP 列会被设置为当前的日期和时间。

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

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