mysql语句-load data infile

转载

chisj 2021-08-17 23:06:17

文章标签 sql mysql sed 字段数据 文章分类 MySQL 数据库

1 介绍

LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。它是 SELECT ... INTO OUTFILE 的一个补充， SELECT ... INTO OUTFILE 是将表中的数据写到一个文件， LOAD DATA INFILE 则是将文件内容导入表。二者都使用FIELDS及LINES语句，两个语句是可选的，同时出现的话FIELDS要在LINES前面。

我们也可以通过mysqlimport工具来导入数据，它本质上是发送 LOAD DATA INFILE语句到服务端。

2 语法

LOAD DATA语法如下（mysql5.6及其以上）：

[sql] view plain copy

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

3 演示数据准备

3.1 创建测试数据库及表

[sql] view plain copy

mysql> create database loaddata;
mysql> use loaddata;
mysql> create table loadtest (c1 int(10), c2 varchar(20), c3 varchar(20), c4 varchar(20));

3.2 插入测试数据

[sql] view plain copy

mysql> insert into loadtest values (100, 'column2', 'column3', 'column4');
mysql> insert into loadtest values (200, 'line2', 'line3', 'line4');

3.3 将数据导出

[sql] view plain copy

[root@localhost /]# mysqldump --tab=/tmp/ loaddata --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='#' --lines-terminated-by='\n'

查看导出数据文件

[sql] view plain copy

[root@localhost /]# cat /tmp/loadtest.txt
100,"column2","column3","column4"
200,"line2","line3","line4"

mysql语句-load data infile_sed

4 语法解释及实例分析

file_name：文件名file_name需要是字符串，在windows下，路径可以为斜杠'/' 或双反斜杠'\\' （windows下验证）。

PARTITION： MySQL 5.6.2及其后续版本，LOAD DATA INFILE开始支持PARTITION分区选项。对于分区表应采用使用表锁的存储引擎，例如myisam，不适用与行锁的存储引擎，如Innodb，因为LOAD DATA不能移除任何分区锁。

CHARACTER SET：由character_set_database系统变量指示的字符集被用于解释文件中的信息。SET NAMES和character_set_client的设置不会影响对输入的解释。假如文件中的字符编码与默认不一致，最好用CHARACTER SET语句指定编码方式，这就需要我们通过mysqldump或SELECT ... INTO OUTFILE导出文件时通过参数--default-character-set指定导出文件的编码格式。

注意：导入文件不支持ucs2, utf16, utf16le, 或 utf32编码格式。

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

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

对复制的影响：使用基于语句格式（statement-based）的复制，在mysql5.5.1之前，CONCURRENT参数不能被复制。使用基于行格式（row-based）的复制,在mysql5.1及其后续版本，都可以被正确复制。mysql5.6认为使用LOAD DATA INFILE是不安全的，当使用statement-based格式的日志时会发出警告，使用mixed格式的日志时会被存储为row格式。

LOCAL：该关键字影响文件的预期读取位置及错误句柄。需要客户端及服务端都开启该选项，如果mysqld的启动参数包含local-infile=0，那么指定该参数是无效的。

对文件查找的影响：

指定该选项：文件将会由客户端程序在客户端主机上查找，然后发送到服务端。可以指定文件的绝对路径和相对路径，指定相对路径的话会在客户端程序目录下查找。

执行导入的时候会先将文件复制到服务端的临时文件夹下（linux下是 /tmp， windows下是C:\WINDOWS\TEMP），如果临时文件夹空间不足将会导致LOAD DATA失败。

未指定该选项：文件将由服务端程序在服务端主机上查找，查找规则如下：

如果给定了一个绝对的路径名称，则服务器使用此路径名称。

如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器（数据目录）的文件。

如果给定了一个不带引导组件的文件名称，则服务器会在默认（数据库）的（数据库目录）中寻找文件。

例如：./myfile.txt会在数据库的data目录下查找，myfile.txt会在我们连接时使用的默认数据库，如test的数据目录下查找。

LOCAL对错误句柄的影响：

使用LOAD DATA INFILE，数据解释和主键冲突error会终止操作。

使用LOAD DATA LOCAL INFILE数据解释和主键冲突error会变成warning，操作继续，因为mysql server没有办法在操作过程中结束数据的传输。对于主键冲突error来讲，该方法类似于指定IGNORE。

REPLACE 与 IGNORE：控制输入的行与唯一主键的重复。

REPLACE：输入行替换已经存在的行。即与主键或唯一索引列的重复。

IGNORE：输入行与已经存在的行主键或唯一索引列重复，则丢弃。

假如二者都未指定：行为就会取决于是否指定了LOCAL参数。

导入数据过程中忽略（外键约束）可以在LOAD DATA前执行SET foreign_key_checks = 0 。

LINES STARTING BY 'prefix_string'：如果所有希望读入的行都含有一个我们希望忽略的共用前缀，则可以使用'prefix_string'来跳过前缀（以及该前缀

前的所有字符）。如果某行不包括前缀，则整个行被跳过

例如：

[sql] view plain copy

mysql> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY "xxx";

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

[sql] view plain copy

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

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

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

[sql] view plain copy

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

FIELDS 和 LINES：

对于LOAD DATA INFILE 和 SELECT ... INTO OUTFILE，FIELDS 和 LINES语句的语法都是相同的，二者都是可选的，但同时出现时FIELDS语句要放在LINES语句的前面。

假如我们指定了FIELDS语句，它的子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY, 和 ESCAPED BY)也是可选的，不过必须指定至少一项。

如果FIELDS 和 LINES语句我们都未指定，则默认的句法为：

[sql] view plain copy

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

所有field-或line-handling选项都可以指定一个空字符串('')。如果字符串不是空的，则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须为单一字符。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超过一个字符。例如，要编写由回车/换行成对字符作为结尾的行，或读取

包含这类行的文件，则应指定一个LINES TERMINATED BY ‘\r\n’子句。

FIELDS [OPTIONALLY] ENCLOSED BY ：对于SELECT ... INTO OUTFILE输出，如果不包含OPTIONALLY选项，所有的字段将会被ENCLOSED BY指定的字符包裹，例如：

[sql] view plain copy

"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指定的字符包裹，例如：

[sql] view plain copy

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正确读取的输出值。例如：

[sql] view plain copy

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：用来控制如何对特殊字符进行读写，如上面一个例子，导出和导入时指定FIELDS ESCAPED BY为双引号["]才能被正确的导入，导出的格式如下，对字段内的双引号["]进行了转义。

[sql] view plain copy

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指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。一些两个字符的字符串序列且第一个字符是转义字符'\'是例外，如\0,\b,\n,\r,\t,\Z\,\N等,这些字符序列本身有特殊意义。

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

对于输出：

假如FIELDS ESCAPED BY指定字符非空，字符作为以下输出的前缀。

【**********此处待完善***********】

在特定情况下，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。

不适合使用LOAD DATA INFILE的情况

使用固定行格式（即FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 均为空）列字段类型为BLOB或TEXT。

指定分隔符与其它选项前缀一样，LOAD DATA INFILE不能对输入做正确的解释。例如：

[sql] view plain copy

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

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

选择导入的列：

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

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

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

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

注意：如果sql-mode为严格事物模式STRICT_TRANS_TABLES，导入部分列会报错。

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

[sql] view plain copy

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。

[sql] view plain copy

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

mysql语句-load data infile_字段_02

方法2：把c3列设为当前时间（sql-mode使用严格事物模式STRICT_TRANS_TABLES会报错）

[sql] view plain copy

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) set c3 = CURRENT_DATE;

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

[sql] view plain copy

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

通过管道导入数据：

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

[sql] view plain copy

mkfifo /tmp/ls.dat
chmod 666 /tmp/ls.dat
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会报错

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

[sql] view plain copy

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

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

5 注意事项

LOAD DATA INFILE可以读取外源的文件，如其它数据库或程序生成的逗号分隔的CSV格式的文件。

当我们使用SELECT ... INTO OUTFILE从数据库导出数据到文件，然后通过LOAD DATA INFILE读取该文件到数据库，这两个语句的field和lines选项必须匹配，否则 LOAD DATA INFILE将不能正确的解释文件的内容。

6 常见错误

6.1 sql_mode为STRICT_TRANS_TABLES，即严格事务模式时，容易出错，例如：

[sql] view plain copy

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) set c3 = CURRENT_DATE;
R 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

查看sql_mode

[sql] view plain copy

mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

取消严格事务模式才可导入

[sql] view plain copy

set sql_mode='NO_ENGINE_SUBSTITUTION';

6.2 windows上的一些错误

[sql] view plain copy

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -uroot -pactionsky --tab=/tmp/ loaddata --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='#' --lines-terminated-by='\r\n'
mysqldump: Got error: 1083: Field separator argument is not what is expected; check the manual when executing 'SELECT INTO OUTFILE'

mysql5.5.30

****************************************************************************************