MySQL 将文件导入数据库（load data Statement）

前面我们介绍过如何用select…into outfile语句将SQL查询结果导出到文件：
MySQL 将查询结果导出到文件（select … into Statement）

MySQL同时也提供互补的功能，可以使用load data infile语句将文件中的数据加载到数据库中，这个文件可以是MySQL导出的文件或其他来源。本文将介绍load data infile语句的用法及在使用过程中常见问题的解决方式。

一、load data语句简介

MySQL的load data infile语句可以从文本文件中读取数据，并且加载到数据库的表中。和select…into outfile只能导文件到本地数据库服务器不同，load data语句即可以从数据库服务器本地读取文件，也可以通过远程客户端（使用local关键字）读取，即可以远程将文件加载到数据库中。

MySQL还提供了一个mysqlimport命令行工具也可以将数据从文件加载到数据库中，其原理也是通过load data infile语句完成的。

二、用法示例

默认情况下，load data infile语句是从数据库服务器加载数据的，为了安全起见，一般MySQL都会配置secure_file_priv参数，来指定可以读写文件的目录，将要导入的文件放在此参数指定的目录下。

show variables like 'secure_file_priv';

我们先通过导出数据的方式创建一个文件，这里在示例数据库employees下新建一张测试表并插入几条数据：

create table person(
id int not null auto_increment primary key,
name varchar(32),
salary decimal(10,2),
remark varchar(128));

insert into person values(null, 'Vincent', 1000, 'AAA');
insert into person values(null, 'Victor', 2000, 'BBB');
insert into person values(null, 'Grace', 3000, 'CCC');

数据内容如下：

select * from person;

使用select…into outfile将数据导出到文件（路径就是secure_file_priv参数指定的目录），这里使用默认格式导出：

select * from person into outfile '/opt/mysql8.0.35/mysql-files/person.txt';

导出的person.txt文件内容如下（数据以tab分隔）：

2.1 基本用法

由于load data infile和select into outfile语句是互补的，所以它们的格式设定语法是一样的。select…into outfile采用默认格式导出的文件就是load data infile的默认导入格式。这种情况下，直接指定文件名及要导入表名即可（这里先清空person表）：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person;

select * from peron;

2.2 数据格式的处理

但也有很多情况数据的来源不是MySQL导出的文件，格式也不同。例如常用的CSV格式文件，我们手动将刚才文件改为CSV格式（以逗号分隔数据），且第一行数据中remark字段还额外包含了一个逗号（红框处）：

碰到这种和默认格式不同的数据，MySQL就无法解析了，如果直接导入就会报错：

此时需要通过格式子句来告诉MySQL如何解析数据，默认的格式子句如下：

fields terminated by '\t' encolded by '' escaped by '\\'
lines terminated by '\n' starting by ''

含义解释：

• fields 表示字段属性，terminated by ‘\t’ 以制表符分割字段，enclosed by ‘’ 不包裹字段，escaped by ‘\’ 反斜线表示转义符
• lines 表示行属性，terminated by ‘\n’ \n代表换行符，starting by ‘’ 行的起点字符是空。

我们分析一下这里数据的格式和默认格式的区别，字段的分隔符是逗号，因此需要 fields terminated by ‘,’，指定逗号为分隔符。同时注意第一行的remak字段是"Hello, Vincent!“，引号中逗号又是数据内容，这个逗号不能识别为分隔符，因此还需要指定 enclosed by '”'，指定双引号之内的内容是一个字段。增加这个两个子句后，可以看到数据格式识别成功：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"';

三、常见导入问题的处理

除了基础导入场景，我们可能还会遇到一些其他问题或者数据加工需求，下面就是导入中常见问题的解决方法。

2.3 标题行的处理

如果文件的第一行是标题而不是数据，那么在导入时我们就需要进行忽略处理，你可以手动从文本文件中删除这一行。或者，使用ignore n lines/rows子句来告诉MySQL导入时跳过前n行，我们上面的文本中再增加一行标题：

导入时，通过ignore 1 lines/rows语句，忽略第一行：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
ignore 1 lines;    -- 忽略第一行

select * from person;

可以看到第一行标题并未导入，而是从第二行数据开始读取。

2.2 主键/唯一索引冲突的处理

上面的示例中，我们每次导入前都执行了truncate table清空表，即每次都是往空表中导入。但如果表中已经有数据了，导入时就可能发生主键/唯一索引冲突。向已有数据的表中导入数据时如果发生了主键/唯一索引冲突，我们有2个选择：忽略或更新

• ignore，遇到键值冲突时 忽略数据
• replace，遇到键值冲突时 更新数据

手动修改一下文件内容，将Vincent的salary改为4000：

在into table语句前增加一个ignore关键字，这样出现键值冲突时会忽略而不是报错：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' ignore into table person
fields terminated by ',' enclosed by '"';

可以看到Vincent的salary并没有更新，同时日志提示忽略了3行。

在into table语句前增加一个replace关键字，这样出现键值冲突时会更新数据：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' replace into table person
fields terminated by ',' enclosed by '"';

这里Vincent的salary被更新成了4000，日志中的Deleted 1说明实际操作是将原数据删除再插入数据。如果文件每次需要更新导入，那么replace关键字就很适合。

2.3 文件和表的列数量不同或顺序不同

前面每次导入时我们都只提供了表名，这就要求文本中数据的列和数据库中表的列数量要相同，并且顺序是对应的。如果文件中字段顺序和表不同，或者字段数量不同，那么就需要手动指定导入顺序。

我们修改一下表结构，在name和salary之间增加一个extra_column，此时表的字段就比文件中字段数多了，且顺序也不对应：

alter table person add extra_column int after name;

此时导入数据就需要根据文件中字段的顺序来指定表的列名：

truncate table person;
load data infile '/opt/mysql8.0.35/mysql-files/person.txt' replace into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,remark);    -- 指定导入的列名顺序

注意列名是单独放在语句最后（如果有set子句则在set语句之前），而不是紧跟在表名后。

2.4 导入部分列

如果只想将文件中的部分列导入数据库，即丢弃部分列的数据。我们也可以通过指定列名的方式来实现，通过仅指定需要导入数据的列名，而想丢弃的数据用一个变量名来占位，这样对应的列数据就不会被导入到数据库中。

例如导入数据时，仅想导入id,name,salary这3列，忽略remark列：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,@var);

这里用@var来占位，而不是指定remark列名，因此remark列没有数据导入，相当于仅导入了部分列。

2.5 导入过程中处理数据

除了将数据原封不动导入之外，load data infile语句还支持一个set子句让你在导入过程中对数据进行加工处理。

例如记录导入时间，我们再增加一个列import_time，用来记录数据导入时间：

alter table person add import_time timestamp;

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,remark)
set import_time=current_timestamp;

select * from person;

在语句的最后，增加了一个set import_time=current_timestamp子句，它会导入时设置import_time列为当前时间戳（虽然数据都不在文件中）。

对于想要加工的列，我们可以先将列赋给变量，然后对变量加工后，再通过set子句写入表的列，达到先加工后导入的效果。例如对于salary列，如果值小于3000，那么就加999：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,@sal,remark)
set salary=if(@sal<3000, @sal+999, @sal);

select * from person;

导入时，先将值赋给变量@sal，经过if函数的加工后，再通过set子句将加工过后的值写入salary列，可以看到Victor的salary变成了2999。如果没有set子句，那么salary列的值就丢弃了，就是上一节导入部分列的操作。

以上就是MySQL中load data infile语句的用法及常见问题的处理，熟练掌握后可以帮助你快速将数据从文件导入数据库（一个常用的场景就是将Excel文件保存为CSV格式导入数据库）。