Linux系统下MySQL的导入数据的命令语句Load Data InFile的用法详解

语法格式

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, 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
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

LOW_PRIORITY

如果你指定关键词LOW_PRIORITY,这个只对那些只采用了表级别锁（如MYISAM)的引擎有影响，比如InnoDB使用的是行锁，不受这个影响，具体的来说，使用了LOW_PRIORITY，则本操作会在其它线程完成之后再操作。

如果你指定关键词 low_priority，那么 MySQL 将会等到没有其他人读这个表的时候，才会插入数据。

CONCURRENT

CONCURRENT 会和其它线程同时进行，这个对性能是有一些影响。

LOCAL

这是个非常重要的关键字，指明了文件的位置，简单的说，如果指定了 local，则表示文件位于客户端所在主机的位置，如果没有指定，则表示文件在数据库服务所在主机上，即数据库服务器上。同时，这个关键字的使用还会影响到 load data 命令对于错误数据的处理方式。

所谓客户端，就是用来访问数据库和管理数据库的客户端软件，例如 Navicat Premium 客户端。客户端是安装在用户自己的主机上，通过客户端连接上远程数据库服务器后（即登录数据库），可以直接通过客户端的图形化界面对数据库进行管理，也可以通过客户端的命令行界面对数据库发送命令。

例如下面的命令语句来导入数据：

mysql> load data low_priority local
infile "/Users/liaowenxiong/Desktop/student.txt"
into table student
columns terminated by ','
lines terminated by '\n' 
ignore 1 lines 
(id,age,name); 
Query OK, 3 rows affected (0.10 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

注：/Users/liaowenxiong/Desktop/student.txt 这个是文件在客户端所在主机的文件路径。

1.如果是指定了 local，则数据从客户端读取，文档中的说法是会在服务端的临时目录下创建一份文件的副本，但我在测试的时候并没有发现。file_name 必须是文件在客户端所在主机的绝对路径（例如：/Users/liaowenxiong/Desktop/student.txt），由于涉及到数据传输，所以这种方式会相对来说慢一些。

我不使用 Navicat Premium 的命令终端，使用本地主机的命令终端远程登录数据库后，执行下面的命令：

load data low_priority local
infile "student.txt"
into table student
columns terminated by ','
lines terminated by '\n' 
ignore 1 lines 
(id,age,name); 

注：这里指定了关键词 local，直接使用文件名称。此时使用的是本地主机命令终端远程连接服务器，所以这种情况下的本地其实就是服务器本地，默认是在用户的家目录下查找文件，当前我登录的是 root 用户，那么就会在 /root/ 目录下查找文件。

2.如果未指定 local，则文件应该是直接在服务端，这种情况下如果文件名使用的是相对路径，则又分两种情况，一种是文件名前没有相对目录，默认是在当前数据库对应的目录下查找。什么意思呢？假设你当前使用的数据库名称叫 test，那么在 MySQL 的数据根目录下就会有一个目录名称为 test，那么命令就会在这个 test 目录下查找这个文件；如果你在文件名称前面加上了目录名称，例如，ab/student.txt，那么命令不会在 test 目录下查找，但是依旧在 MySQL 的数据根目录下查找，即会在 MySQL 数据根目录下查找文件路径 ab/student.txt。

如何查看 MySQL 数据库的数据根目录，请参见《Linux 下如何查找 MySQL 数据库的数据根目录呢？》

不指定关键词 local 直接使用文件名执行 load data infile 语句：

mysql> load data low_priority
    -> infile "student.txt"
    -> into table student
    -> columns terminated by ','
    -> lines terminated by '\n' 
    -> ignore 1 lines 
    -> (id,age,name); 
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/test/student.txt' (Errcode: 2 - No such file or directory)

我们看到，提示无法获取文件 student.txt 的状态，默认是在 /var/lib/mysql/test/ 目录查找文件，其中 test 是当前数据库名称，/var/lib/mysql 是 MySQL 的数据根目录。

不知道关键词 local ，在文件名称前面再加上一个目录，结果会如何：

mysql> load data low_priority
    -> infile "ab/student.txt"
    -> into table student
    -> columns terminated by ','
    -> lines terminated by '\n' 
    -> ignore 1 lines 
    -> (id,age,name);
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/ab/student.txt' (Errcode: 2 - No such file or directory)

我们看到提示信息，并没有在当前数据库下查找文件，而是在数据根目录下根据你指定的相对路径查找文件。

3.如果指定了 local，则当某条数据处理有误时，系统把这个错误记录为一个 warning，不会影响下一条数据的处理，因为涉及到数据传输。而如果没有指定 local，则默认情况下，遇到错误后不会继续执行。

REPLACE 或 IGNORE

当前的数据跟表中的数据有惟一性冲突的时候，采用什么样的方式，是替换已有还是忽略当前。特别需要说明的是，当这两种方式都未指定时，如果数据来自于客户端，则重复的数据会忽略，如果来源于服务端，则命令将终止执行。

服务端执行语句，不指定 REPLACE 或 IGNORE：

mysql> load data low_priority
    -> infile "/root/student.txt"
    -> into table student
    -> columns terminated by ','
    -> lines terminated by '\n' 
    -> ignore 1 lines 
    -> (id,age,name);
ERROR 1062 (23000): Duplicate entry '00000000000000000001' for key 'PRIMARY'

服务端执行导入语句，如果导入文档中存在与数据表主键重复的记录，则直接报错。

客户端执行语句，不指定 REPLACE 或 IGNORE：

mysql> load data low_priority local
infile "/Users/liaowenxiong/Desktop/student.txt"
into table student
columns terminated by ','
lines terminated by '\n' 
ignore 1 lines 
(id,age,name); 
Query OK, 0 rows affected (0.08 sec)
Records: 3  Deleted: 0  Skipped: 3  Warnings: 3

从上面执行结果看，客户端执行导入语句后，会把导入文档中与数据表主键重复的记录忽略，即跳过。

PARTITION

指定具体的分区，由于之前数据库中没用到过分区，个人对这块也不熟悉，所以暂时不解释，等到了解了再补充

CHARACTER SET

指定导入文件的字符编码，如果文件的字符编码和数据库的编码不一致，可能会出现乱码的问题。所以要注意的是，这里指定的是文件的字符编码，而不是数据库的字符编码

对导入文件的字段进行格式描述

    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]

1.FIELDS 和 COLUMNS，指定接下来描述的是导入文件中的字段或者列格式，两个任选一个即可。 2.TERMINATED BY 'string' 指定导入文件中的字段间分隔符是 ‘string’ 3.ENCLOSED BY 'char' 指定字段值由什么符号包围 4.ESCAPED BY 'char' 指定转义符号

注意：在不指定这个参数的情况下，默认的字段分隔符是水平制表符 \t，默认字段值无任何值包围，默认转义字符为 \。

指定行的起始符号和终止符号

 [LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]

1.LINES 指定接下来描述的是导入文件中的行格式 2.STARTING BY 'string' 指定行开始的符号 3.TERMINATED BY 'string' 指定行结尾的符号

如果导入文件的数据记录没有起始符号，而每行记录都是换行产生的，那么这个选项可以省略。因为默认情况下，行的起始符为空，终止符为 \n，对于 Windows 产生的文本文件来说，需要指定换行符为 \r\n 。

IGNORE number LINES

忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么必须忽略列名，即忽略文件的前 1 行。需要注意的是这里是行的数量，而不是行号。

指定数据表的列名

有的时候我们不需要给数据表中的所有列都填充值，这个时候就可以指定数据表中的列名，用 () 将列名括起来。 就是将导入文件的字段依次序对应数据库表中列，比方说导入文件中的第一个字段要存储到数据表的哪列中，这个列名必须明确。

[(col_name_or_user_var,…)]

注意： 1.这里也可以是用户自定义的用户表达式 2.这里是指定数据库中表的列名，不是导入文档中的列名

获取表达式的值存储到指定的列中

[SET col_name = expr,…]

如果在前一步中指定了用户表达式，那么相应就可以使用列名等于用户表达式的方式来指定，这个我没有用过，给出一个官方的示例如下：

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

开启/关闭功能

LOAD DATA INFILE 这个导入数据命令语句是可以开启和关闭的，必须开启此功能才能使用，使用如下命令查看功能开启状态：

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.01 sec)

如果是关闭的，则输入以下命令将其开启：

set global local_infile=ON;

参考示例 示例一

student.txt 的内容如下：

"id"	"name"
"00000000000000000001"	"liaowenxiong"
"00000000000000000002"	"liudehua"
"00000000000000000003"	"zhangxueyou"

student.txt 的内容格式分析： 1.第一行记录是列名称，需要忽略掉 2.每列的值使用双引号括起来 3.字段值之间使用制表符分隔（一种空白字符） 4.每行记录以换行符结尾，即以换行符分隔

数据表结构如下：

mysql> desc student;
+-------------+------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                         | Null | Key | Default           | Extra                       |
+-------------+------------------------------+------+-----+-------------------+-----------------------------+
| id          | bigint(20) unsigned zerofill | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(30)                  | YES  |     | NULL              |                             |
| age         | tinyint(3) unsigned          | YES  |     | NULL              |                             |
| score       | double(4,1)                  | YES  |     | NULL              |                             |
| birthday    | date                         | YES  |     | NULL              |                             |
| insert_time | timestamp                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+------------------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

登录数据库后，执行以下命令：

mysql> load data low_priority # 如果你指定关键词 `low_priority`，那么 MySQL 将会等到没有其他人读这个表的时候，才会插入数据
infile "/root/student.txt" # /root/student.txt 这是存放在数据库服务器上的导入数据的文本文件
into table student # 这是数据表
fields terminated by '\t' # 指定字段的分隔符，用单引号括起来，`\t` 表示tab字符，也是默认的字段分隔符
enclosed by '"' # 指定字段值的括起符号，例如，字段值使用双引号括起来，那么就指定双引号'"'，注意单引号里面的双引号才是字段值的括起符号
escaped by '\\' # 指定转义符号，默认是反斜杠
lines terminated by '\n' # 指定每行记录的分隔符，默认是换行符，MacOS是`\r`，Linux是`\n`，Windows是`\r\n`
ignore 1 lines # 指定忽略前面的多少行记录，如果导入数据的文本文件中含有列名称，那么就忽略掉前面的第一行即可
(id,name); # 这里是指定数据表的字段名称，表示文本文件的第一列对应数据表的id字段，第二列对应数据表的name字段

无注释版本：

load data low_priority
infile "/root/student.txt"
into table student
fields terminated by '\t'
enclosed by '"' 
escaped by '\\'
lines terminated by '\n' 
ignore 1 lines 
(id,name); 

示例二

student.txt 的内容如下：

id	name	
00000000000000000001	liaowenxiong
00000000000000000002	liudehua
00000000000000000003	zhangxueyou

student.txt 的内容格式分析： 1.第一行记录是列名称 2.本示例中，字段值没有使用双引号括起来，所以不需要通过 enclosed by 来指定字段闭合符号。 3.字段值之间使用制表符分隔（一种空白字符） 4.每行记录以换行符结尾，即以换行符分隔

执行以下命令即可导入数据：

load data low_priority
infile "/root/student.txt"
into table student
fields terminated by '\t'
lines terminated by '\n' 
ignore 1 lines 
(id,name); 

示例三

student.txt 的内容如下：

id,age,name
00000000000000000001,18,liaowenxiong
00000000000000000002,28,liudehua
00000000000000000003,38,zhangxueyou

student.txt 的内容格式分析： 1.第一行记录是列名称，需要忽略掉 2.本示例中，字段值没有使用双引号括起来，所以不需要通过 enclosed by 来指定字段闭合符号。 3.字段值之间使用逗号分隔 4.每行记录以换行符结尾，即以换行符分隔 5.文档中的字段 age 和 name 与数据表的顺序不同

登录数据库后，执行命令语句如下：

load data low_priority
infile "/root/student.txt"
into table student
columns terminated by ','
lines terminated by '\n' 
ignore 1 lines 
(id,age,name); # 表示文档的第一列和数据表的id字段对应，第二列和数据表的age字段对应，第三列和数据表的name字段对应

示例四

student.txt 的内容如下：

id,age,name
00000000000000000001,18,liaowenxiong
00000000000000000002,28,liudehua
00000000000000000003,38,zhangxueyou

通过客户端 Navicat Premium 的命令行终端执行以下的命令：

load data low_priority local
infile "/Users/liaowenxiong/Desktop/student.txt"
into table student
columns terminated by ','
lines terminated by '\n' 
ignore 1 lines 
(id,age,name); 	

通过客户端的命令终端执行 load data infile 可以使用文件在本地的文件路径，但是要在前面指定关键词 local。

示例五

student.txt 的内容如下：

id,age,name
00000000000000000001 18 liaowenxiong
00000000000000000002 28 liudehua
00000000000000000003 38 zhangxueyou

执行如下命令语句导入数据：

load data low_priority
infile "/root/student.txt"
into table student
fields terminated by ' '
lines terminated by '\n' 
ignore 1 lines 
(id,age,name);  	

特别注意：字段之间如果使用【空格】分隔，也就是按一下空格键，那么这样的分隔符号用 ' ' 表示，单引号内输入一个空格即可，不是 \t 也不是 \b。

错误汇总 secure_file_priv 参数的问题

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

在 MySQL 5.7.6 版本引入了参数 secure_file_priv，该参数用来指定 LOAD DATA、SELECT … INTO OUTFILE 语句和 LOAD_FILE() 函数的导入导出目录。

secure_file_priv 的值如下：

值作用nullMySQL 服务禁止导入和导出操作目录路径如果这个参数设为一个目录路径，MySQL 服务只允许在这个目录中执行文件的导入和导出操作。这个目录必须存在，MySQL 服务不会创建它。例如，值为 /tmp/，表示导入/导出只能发生在 /tmp/ 目录下没有值表示不对 mysqld 的导入/导出做限制

注：默认值是 /var/lib/mysql-files/

查看参数 secure_file_priv 的值，你可以输入：

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

如何解决： Windows下，在 MySQL 安装目录下找到配置文件 my.ini，打开文件后在其中的 [mysqld] 内加入 secure_file_priv ='' Centos Linux 下，修改 /etc/my.cnf 文件，在 [mysqld] 内加入 secure_file_priv = ''

然后重启 MySQL 配置才会生效。

因为 secure_file_priv 参数是只读参数，不能使用 set global 命令修改：

mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

导入的记录包含的字段值个数超过要导入的列数

ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

这个错误表示第一行被截取了，第一行包含的数据超过了输入的列数，输入的列数由后面的小括号指定(id,name)。产生这个问题的原因往往是因为报错的行被编辑过，即第一行并没有在第二个字段的位置以换行符结尾，而是后面还有字符，可能是多余的空白字符等，所以需要手动重新整理数据。

ERROR 1300 (HY000): Invalid utf8 character string: ''

导入文件的字符编码与数据表的字符编码不一致

ERROR 1300 (HY000): Invalid utf8 character string: ''

可以将导入文件的字符编码改成数据表的字符编码保存后再执行命令语句。

导入文件的字段数量与数据表的列数量不一致

ERROR 1261 (01000): Row 228 doesn't contain data for all columns

可能是因为数据库的【严格转换模式】导致，输入以下的命令查看数据库的参数 sql_mode 的值：

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

将其中的 STRICT_TRANS_TABLES 就是严格转换模式，可以将其去掉。

set sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

使用 local 与不使用 local 的差异

如果 load data 语句使用了 local 参数，则客户端使用 TCP 远程连接数据库服务器时（例如，你在自己的电脑通过命令终端使用 SSH 连接远程数据库服务器），在这种情况下所谓的本地（local）其实就是服务端主机了，那么该命令就会在服务端主机上直接读取指定的导入文件，没有 file 权限仍然能够导入文本文件，这个时候是非常危险的，因为 local 参数的内部原理是从客户端的主机读取文本文件并传送到服务端的 /tmp 目录并保存为一个临时文件，再执行 load data 语句的。

另外，要使用 local 参数，还需要看服务端和客户端是否都开启了 local_infile 功能（默认是开启的），任何一端的 local_infile 关闭了都不能使用 local 参数，会报如下的错误：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

如果 load data 语句不使用 local 参数，则用户必须要有导入文件的 file 权限才能够执行 load data 语句导入文本文件的数据到数据库中。如果没有file权限，而执行 load data 语句导入文本文件，会报没有 file 权限的错误或者报如下的错误：

ERROR 1045 (28000): Access denied for user 'test'@'%' (using password: YES)

为了安全，要限制客户端使用 local 参数在没有 file 权限的时候使用 load data 语句导入文本文件数据，需要在服务端将参数 local_infile 的值设为 OFF。

如果想全面禁止使用 load data 语句，则可以将参数 secure_file_priv 的值设为 NULL，这样就无法使用 load data infile 语句导入数据了，也无法使用 select into outfile 语句导出数据。