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参数，来指定可以读写文件的目次，将要导入的文件放在此参数指定的目次下。

1. show variables like 'secure_file_priv';

复制代码

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

1. create table person(
2. id int not null auto_increment primary key,
3. name varchar(32),
4. salary decimal(10,2),
5. remark varchar(128));
7. insert into person values(null, 'Vincent', 1000, 'AAA');
8. insert into person values(null, 'Victor', 2000, 'BBB');
9. insert into person values(null, 'Grace', 3000, 'CCC');

复制代码

数据内容如下：

1. select * from person;

复制代码

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

1. 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表）：

1. truncate table person;
3. load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person;
5. select * from peron;

复制代码

2.2 数据格式的处置惩罚

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

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

此时必要通过格式子句来告诉MySQL怎样剖析数据，默认的格式子句如下：

1. fields terminated by '\t' encolded by '' escaped by '\\'
2. 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 '”'，指定双引号之内的内容是一个字段。增长这个两个子句后，可以看到数据格式辨认乐成：

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

复制代码

三、常见导入题目标处置惩罚

除了根本导入场景，我们大概还会碰到一些其他题目大概数据加工需求，下面就是导入中常见题目标办理方法。
2.3 标题行的处置惩罚

如果文件的第一行是标题而不是数据，那么在导入时我们就必要举行忽略处置惩罚，你可以手动从文本文件中删除这一行。大概，利用ignore n lines/rows子句来告诉MySQL导入时跳过前n行，我们上面的文本中再增长一行标题：

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

1. truncate table person;
3. load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
4. fields terminated by ',' enclosed by '"'
5. ignore 1 lines;    -- 忽略第一行
7. select * from person;

复制代码

可以看到第一行标题并未导入，而是从第二行数据开始读取。
2.2 主键/唯一索引辩说的处置惩罚

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

• ignore，碰到键值辩说时 忽略数据
  
• replace，碰到键值辩说时 更新数据
  

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

在into table语句前增长一个ignore关键字，如许出现键值辩说时会忽略而不是报错：

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

复制代码

可以看到Vincent的salary并没有更新，同时日记提示忽略了3行。
在into table语句前增长一个replace关键字，如许出现键值辩说时会更新数据：

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

复制代码

这里Vincent的salary被更新成了4000，日记中的Deleted 1阐明实际操纵是将原数据删除再插入数据。如果文件每次必要更新导入，那么replace关键字就很适当。
2.3 文件和表的列数量差别或序次差别

前面每次导入时我们都只提供了表名，这就要求文本中数据的列和数据库中表的列数量要类似，而且序次是对应的。如果文件中字段序次和表差别，大概字段数量差别，那么就必要手动指定导入序次。
我们修改一下表结构，在name和salary之间增长一个extra_column，此时表的字段就比文件中字段数多了，且序次也不对应：

1. alter table person add extra_column int after name;

复制代码

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

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

复制代码

注意列名是单独放在语句末了（如果有set子句则在set语句之前），而不是紧跟在表名后。
2.4 导入部门列

如果只想将文件中的部门列导入数据库，即抛弃部门列的数据。我们也可以通过指定列名的方式来实现，通过仅指定必要导入数据的列名，而想抛弃的数据用一个变量名来占位，如许对应的列数据就不会被导入到数据库中。
比方导入数据时，仅想导入id,name,salary这3列，忽略remark列：

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

复制代码

这里用@var来占位，而不是指定remark列名，因此remark列没有数据导入，相当于仅导入了部门列。
2.5 导入过程中处置惩罚数据

除了将数据原封不动导入之外，load data infile语句还支持一个set子句让你在导入过程中对数据举行加工处置惩罚。
比方纪录导入时间，我们再增长一个列import_time，用来纪录数据导入时间：

1. alter table person add import_time timestamp;
3. truncate table person;
5. load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
6. fields terminated by ',' enclosed by '"'
7. (id,name,salary,remark)
8. set import_time=current_timestamp;
10. select * from person;

复制代码

在语句的末了，增长了一个set import_time=current_timestamp子句，它会导入时设置import_time列为当前时间戳（固然数据都不在文件中）。
对于想要加工的列，我们可以先将列赋给变量，然后对变量加工后，再通过set子句写入表的列，到达先加工后导入的结果。比方对于salary列，如果值小于3000，那么就加999：

1. truncate table person;
3. load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
4. fields terminated by ',' enclosed by '"'
5. (id,name,@sal,remark)
6. set salary=if(@sal<3000, @sal+999, @sal);
8. select * from person;

复制代码

导入时，先将值赋给变量@sal，颠末if函数的加工后，再通过set子句将加工过后的值写入salary列，可以看到Victor的salary酿成了2999。如果没有set子句，那么salary列的值就抛弃了，就是上一节导入部门列的操纵。
以上就是MySQL中load data infile语句的用法及常见题目标处置惩罚，纯熟把握后可以资助你快速将数据从文件导入数据库（一个常用的场景就是将Excel文件生存为CSV格式导入数据库）。