mysql导入文本数据LOAD DATA INFILE语法

mysql导入文本数据LOAD DATA INFILE语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
   [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,...]]

常用参数含义：

TERMINATED BY character //描述字段的分隔符，默认情况下是tab字符（\\t）
ENCLOSED BY character //描述的是字段的括起字符，比方以双引号括起每一个字段
ESCAPED BY character //描述的转义字符，默认的是反些杠（backslash：\\ ）

实例：

mysql>USE dbname;
mysql>LOAD DATA INFILE "/remote/server/file_name.txt" INTO TABLE table_name;

//LOCAL参数表示文件是本地的文件，服务器是远程已经登陆的服务器
mysql>LOAD DATA LOCAL INFILE "/local/server/file_name.txt" INTO TABLE table_name; 

//设置插入语句的优先级（LOW_PRIORITY 低优先级），LOAD DATA语句的执行将会被延迟，直到没有其它的客户端读取表
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/file_name.txt" INTO TABLE table_name; 

//插入数据的时候，忽略文件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/file_name.txt" IGNORE INTO TABLE table_name; 

//插入数据的时候，替代文件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/file_name.txt" REPLACE INTO TABLE table_name; 

//字段以逗号分割，字段内容用双引号标记开始和结束
mysql>LOAD DATA INFILE "/remote/server/file_name.txt" INTO INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"'; 

//按指定的列把文件导入到数据库中
mysql>LOAD DATA INFILE "/remote/server/file_name.txt" INTO TABLE table_name (column1, column2, column3);

其他知识点：
1：关于INSERT与LOAD DATA INFILE的效率和提高LOAD DATA INFILE速度的更多信息，参考管理员手册中的加速INSERT语句

2：系统变量character_set_database所指出的字符集被用来解释文件中的信息。SET NAMES和设置character_set_client不会影响输入的解释

3：用户也可以使用mysqlimport命令装载数据文件，它通过发送一个LOAD DATA INFILE 命令到服务器来实现
--local选项使得mysqlimport从客户端主机读取数据文件
--compress启用压缩，在较慢的网络中获得更好的性能，需要客户端与服务器都支持压缩协议

4：文件路径尽量使用完整路径，并注意文件权限

5：如果kernel启动时，--local-infile=0，则LOCAL不能工作，参考管理员手册中的LOAD DATA LOCAL的安全问题

6：REPLACE和IGNORE关键字处理那些与已存在的主键值重复的输入记录
如果指定了REPLACE，输入行将会代替已存在的行（也就是说，主索引值相同的行将作为存在的行）。参考REPLACE 语法
如果指定了IGNORE，与已存在行主键值重复的输入行将被跳过。
如果不指定二者中的任一个，则操作行为将依赖是否指定了LOCAL 关键字。
没有指定LOCAL，则如果发现有重复的键值，将产生一个错误，并忽略文本文件的其余部分。
如果指定了LOCAL，则缺省的操作行为将与指定了IGNORE 的相同；这是因为，在操作过程中，服务器没有办法终止文件的传送。
如果希望装载操作中忽略外键约束，可以在执行LOAD DATA 之前执行SET FOREIGN_KEY_CHECKS=0 语句。

7：加速索引创建：如果用户在一个空的GsSYS 表上使用LOAD DATA INFILE，所有非唯一索引会以分批方式被创建（就像REPAIR）。当有许多索引时，这通常可以使LOAD DATA INFILE 更快一些。正常情况下非常快，但也有极端的情况，用户可以通过在装载文件之前使用ALTER TABLE .. DISABLE KEYS 关闭它们和在装载文件之后使用ALTER TABLE .. ENABLE KEYS 重建索引，从而加速索引创建。参考管理员手册中的加速INSERT 语句 。

8：LOAD DATA INFILE 是SELECT ... INTO OUTFILE 的反操作。参考SELECT 语法。使用SELECT ... INTO OUTFILE 将数据从一个数据库写到一个文件中。使用LOAD DATA INFILE 读取文件到数据库中。

9：FIELDS 和LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在LINES 子句之前。

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

11：如果用户没有指定一个FIELDS 子句，缺省时如同使用下列语句：
mysql>... FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

12：如果用户没有指定一个LINES 子句，缺省时如同使用下列语句：
mysql>... LINES TERMINATED BY '\n' STARTING BY ''

13：当读取输入时，缺省的LOAD DATA INFILE 表现如下：
在换行符处寻找行的边界。
不遗漏任何行前缀。
在制表符处将行分离成字段。
不认为字段由任何引号字符封装。
将有 “\” 开头的定位符、换行符或`\' 解释为字段值的一个文字字符。

14：当写入输出时，缺省值导致SELECT ... INTO OUTFILE 表现如下：
在字段值间加上制表符。
不用任何引号字符封装字段。
使用 “\” 转义出现在字段值中的定位符、换行符或`\' 字符实例。
在行的结尾处加上换行符。
注意：
为了写FIELDS ESCAPED BY '\\'，用户必须指定两个反斜线，该值会作为一个反斜线被读入。
如果是Windows 系统的文本文件，可能必须使用LINES TERMINATED BY '\r\n' 来读取文件。

15：忽略行前缀可以跳过此前缀（和在它前面的任何内容）。如果一个行没有此前缀，则整个行都被跳过
mysql>... LINES STARTING BY 'prefix_string'

注意：prefix_string可以出现在行的中间

例如：
mysql>... LOAD DATA INFILE '/tmp/file.txt' INTO TABLE test LINES STARTING BY "abcdefg";

#cat /tmp/file.txt
"Row",0
abcdefg"Row",1
something abcdefg"Row",2

得到数据：（“row”，1）（“row”，2）

16：忽略文件开头部分前N行
mysql>... IGNORE number LINES

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

18：FIELDS [OPTIONALLY] ENCLOSED BY中的关键字OPTIONALLY
对于输出（SELECT ... INTO OUTFILE），如果用户省略单词OPTIONALLY，所有的字段被ENCLOSED BY 字符包围

#cat file.txt
"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）

#cat file.txt
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 正确读出的输出文件。例如，如果转义字符为空，上面显示的输出将变成如下显示的输出。请注意第四行的第二个字段，它包含一个跟在一个引号后的逗号，看起来像是一个字段的终止：

#cat file.txt
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 不会影响它。）如果在ENCLOSED BY 字符前存在ESCAPED BY 字符，那么它将被解释为当前字段值的一部分。

如果字段以ENCLOSED 字符开始,只要后面紧跟着字段或行TERMINATED 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

19：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 字符为空，那么将没有字符被转义并且NULL 值仍输出为NULL，而不是\N。指定一个空的转义字符可能不是一个好的方法，特别是用户的数据字段值中包含刚才列表中的任何字符时。

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

关于更多的 “\” 转义语法信息，查看文字值 。

20：在某些情况下，字段与行处理相互作用：
如果LINES TERMINATED BY 是一个空字符串，FIELDS TERMINATED BY 是非空的，则各行以FIELDS TERMINATED BY作为结尾。
如果FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 值都是空的('')，那么一个固定行(无定界符) 格式将被使用。用固定行格式时，在字段之间不使用分隔符（但是用户仍然有一个行终结符）。列值的写入和读取使用列的“显示”宽度。例如，如果一个列被定义 为INT(7)，列的值将使用7 个字符的字段被写入。对于输入，列值通过读取7 个字符来获得。

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

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

22：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 值和空字符串均无法辨别，这是因为两者都被作为空字符串写入。如果用户需要在读取文件并返回时能够分辨两者，则用户不应使用固定行格式。

23：一些不能被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 不知道字段或行值在哪里结束。

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

SET 子句中的用户变量有多种用途。

下面的例子将数据文件的第一列直接作为t1.column1 的值，并将第二列赋予一个用户变量，此变量在作为t1.column2 的值之前进行一个除法操作

mysql>LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100;

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

mysql>LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

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

mysql>LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

使用列/变量列表和SET子句要受到一下限制：
•     SET 子句中的赋值列名应当只能在赋值操作符的左边。
•     在SET 赋值语句中，可以使用子查询。此查询返回一个将被赋予列的值，它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。
•     对于列/变量列表或SET 子句，由于使用IGNORE 子句而被忽略的行就不会被处理。
•     由于用户变量没有显示宽度，当导入的数据是行固定格式时，将不能使用用户变量。

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

26：如果输入的记录行有太多的字段，多余的字段将被忽略，并增加警告的数目。
27：如果一个输入行的字段较少，没有输入字段的列将被设置为缺省值。缺省值赋值在CREATE TABLE 语法 中被描述。

28：一个空的字段值不同于字段值丢失的解释：
•     对于字符串类型，列被设置为空字符串。
•     对于数字类型，列被设置为0。
•     对于日期和时间类型，列被设置为适合列类型的“零”值。参考日期和时间类型 。

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

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

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