LOAD DATA INFILE 导入数据

作者: ahcj_11 | 来源:发表于2017-08-27 22:24 被阅读866次

标签（空格分隔）： mysql

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并写入一个表中。文件名称必须为一个文字字符串。
LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的相对语句。把表的数据备份到文件使用SELECT ... INTO OUTFILE，从备份文件恢复表数据，使用 LOAD DATA INFILE。

1 语法

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,...)]

标准示例

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'

只载入一个表的部分列

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2)

2 LOW_PRIORITY | CONCURRENT 关键字

2.1 LOW_PRIORITY

该参数适用于表锁存储引擎，比如MyISAM, MEMORY, 和 MERGE，在写入过程中如果有客户端程序读表，写入将会延后，直至没有任何客户端程序读表再继续写入。

2.2 CONCURRENT

使用该参数，允许在写入过程中其它客户端程序读取表内容。

3 L0CAL关键字

L0CAL关键字影响数据文件定位和错误处理。只有当 mysql-server 和 mysql-client 同时在配置中指定允许使用,L0CAL关键字才会生效。如果 mysqld 的 local_infile系统变量设置为 disabled，L0CAL关键字将不会生效。详见Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

L0CAL关键字影响在哪里找到数据文件：

3.1 LOCAL load operations

如果指定了LOCAL,数据文将被客户端程序从客户端主机读取，然后发送给服务器主机。文件路径可以使用绝对路径或相对路径。如果使用相对路径，数据文件实际路径相对于客户端程序启动时的当前路径。

使用LOCAL,将在服务器主机的临时目录创建一个数据文件的副本（linux 使用 /tmp,windows 使用 C:\WINDOWS\TEMP），如果临时目录剩余空间不足，将导致语句执行失败。

3.2 Non-LOCAL load operations

如果没有指定LOCAL，数据五年间必须位于服务器值机上，直接由 mysql-server 读取。mysql-serve 使用如下规则来定位文件:

1. 数据文件使用绝对路径，直接使用

2. 数据文件使用相对路径并且有前导的部分，将相对于 mysql-server 的数据目录查找，例如 ./myfile.txt

3. 数据文件使用相对路径并且没有前导的部分，将相对于默认数据库的数据文件目录查找,例如 myfile.txt

根据上面的规则，./myfile.txt 将被定位到 mysql-server 的 data directory,而 myfile.txt 将被定位到 default database 的 database directory。

如果 db1 时默认数据库，则下面的语句将从 db1 的数据库目录读取 data.txt,即使明确指定把数据装载到 db2

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Non-LOCAL load operations 从服务器主机上读取数据文将。出于安全原因，这个操作需要文件权限。如果secure_file_priv 系统变量的value 不为空，数据文件必须放在该变量指定的目录。如果该变量为空，数据文件必须可读。

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

3.3 L0CAL关键字对错误处理的影响

使用 LOAD DATA INFILE,data-interpretation 和 duplicate-key 错误会终止操作
使用 LOAD DATA LOCAL INFILE，data-interpretation 和 duplicate-key 错误会发出警告，操作将继续执行。对于duplicate-key错误，效果和指定了 IGNORE 关键字一样。

4 REPLACE | IGNORE 关键字

REPLACE和IGNORE关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE，新行将代替有相同的唯一键值的现有行。如果你指定IGNORE，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键键时，出现一个错误，并且文本文件的余下部分被忽略时。

5 FIELDS 子句

5.1 基本用法

如果你指定一个FIELDS子句，它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可选的，但是你必须至少指定一个。

如果你不指定 FIELDS 或 LINES ，缺省值为：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

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

在 \n 处寻找行边界
在 \t 处将行分进字段
不要期望字段由任何引号字符封装
将由“\”开头的定位符、换行符或“\”解释成转义序列。例如 \t, \n, and \ 分别解释成定位符，换行，反斜杠。

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

在字段之间写 \t
2 .不用任何引号字符封装字段
使用“\”转义出现在字段中的定位符、换行符或“\”字符
在行尾处写换行符

5.2 FIELDS [OPTIONALLY] ENCLOSED BY

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，只有string数据类型（如 CHAR, BINARY, TEXT, 或 ENUM）的字段才会被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

注意，如果在字段值内出现ENCLOSED BY字符，则通过使用ESCAPED BY字符作为前缀，对ENCLOSED BY字符进行转义。另外，要注意，如果指定了一个空ESCAPED BY值，则可能会生成不能被LOAD DATAINFILE 正确读取的输出值。例如：

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

5.3 FIELDS ESCAPED BY

用来控制如何对特殊字符进行读写，如上面一个例子，导出和导入时指定FIELDS ESCAPED 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

对于输入：
假如FIELDS ESCAPED BY指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。一些两个字符的字符串序列且第一个字符是转义字符的例外。这些字符序列见下表。

Character| Escape Sequence

-------|----------
\0 | An ASCII NUL (X'00') character
\b | A backspace character
\n | A newline (linefeed) character
\r | A carriage return character
\t | A tab character.
\Z | ASCII 26 (Control+Z)
\N | NULL

假如FIELDS ESCAPED BY指定字符为空，将不会发生转义序列的解释。

对于输出：
如果FIELDS ESCAPED BY指定字符非空，字符作为以下输出的前缀。
1. FIELDS ESCAPED BY 字符，例如 \
2. FIELDS [OPTIONALLY] ENCLOSED BY 字符，例如 "
3. FIELDS TERMINATED BY and LINES TERMINATED BY 的 value 的第一个字符,例如\n
4. ASCII 0

如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,字符将不会被转义，NULL 将输出为 NULL而不是 \N。指定LINES TERMINATED BY为空字符串并不是个好主意，特别当内容中包含上表列出的特殊字符时。

5.4 LINES STARTING BY

如果所有希望读入的行都含有一个我们希望忽略的共用前缀，则可以使用 LINES STARTING BY 'prefix_string' 来跳过前缀（以及该前缀前的所有字符）。如果某行不包括前缀，则整个行被跳过。
例如：

 
 LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test 
    FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

假如/tmp/test.txt文件内容如下

xxx"abc",1  
something xxx"def",2  
"ghi",3

则我们读入的内容包括("abc",1) 和 ("def",2)，第三行直接被跳过。

IGNORE number LINES

该选项可以被用于在文件的开始处忽略行。例如，我们可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行。

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

5.5 在特定情况下，field-和line-handling选项相互影响

如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,行以FIELDS TERMINATED BY指定的字符串作为结尾。
如果FIELDS TERMINATED BY 与 FIELDS ENCLOSED BY值均为空('')，将使用固定行（无分割）格式。使用固定行格式，字段之间将没有分隔符（行终止符依然可使用），列字段数据的读取和写入均按照字段定义的宽度去操作，如 TINYINT, SMALLINT, MEDIUMINT, INT, 和 BIGINT, 字段宽度分别为4, 6, 8, 11, 和 20。

5.5 不适合使用 LOAD DATA INFILE 的情况

1. 使用固定行格式（即FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 均为空）,列字段类型为BLOB或TEXT。
2. 指定分隔符与其它选项前缀一样，LOAD DATA INFILE不能对输入做正确的解释。例如：

    FIELDS TERMINATED BY '"' ENCLOSED BY '"'

3. 如果 FIELDS ESCAPED BY 为空，字段值包含 FIELDS ENCLOSED BY 指定字符，或者 LINES TERMINATED BY 的字符在 FIELDS TERMINATED BY 之前，都会导致过早的停止 LOAD DATA INFILE操作。因为LOAD DATA INFILE不能准确的确定行或列的结束。

6 IGNORE number LINES 选项

IGNORE number LINES选项可以被用于在文件的开始处忽略行。
您可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行：

LOAD DATA INFILE '/tmp/test.txt'  INTO TABLE test IGNORE 1 LINES;

7 选择导入的列

下面的语句会导入文件的所有列

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

如果我们想导入表的某些列，需要指定列的列表

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

如果输入文件与表的列顺序不同，我们必须指定一个列清单，否则mysql不能把输入文件的字段与表的列匹配起来。

 mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    ESCAPED BY '#' 
    LINES TERMINATED BY '\n' 
    (c1, c2, c4, c3);

列的清单可以包含列名或者用户变量，在写入列前我们需要使用SET语句对用户变量进行转换。对set语句及用户变量有如下使用方法：

方法1：在用户变量用于第一列之前，先把第一列的值赋予用户变量，进行除法操作后输入到c1。

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

方法2The SET clause can be used to supply values not derived from the input file. 。使用下面例子把c3列设为当前时间

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

方法3：把输入赋予用户变量，而不把用户变量赋予表中的列，来丢弃此输入值。

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

7 通过管道导入数据：

在unix系统中，如果我们想要从管道（pipe）中load data，需要用如下方法：

[root@localhost tmp]# mkfifo /tmp/ls.dat  
[root@localhost tmp]# chmod 666 /tmp/ls.dat  
[root@localhost tmp]# find / -ls > /tmp/ls.dat &  
[root@localhost tmp]# mysql -e "LOAD DATA INFILE '/tmp/ls.dat' INTO TABLE test.tb1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '#' LINES TERMINATED BY '\n'"

注意：sql-mode使用严格事物模式STRICT_TRANS_TABLES会报错

在另一窗口将数据写入管道

[root@localhost /]# cat /tmp/loadtest.txt > /tmp/ls.dat

注：可以先读或者先写管道，谁先谁后都可以，在写入管道的数据被全部读出前，处于阻塞状态。

参考
LOAD DATA INFILE Syntax
mysql导入数据load data infile用法