MySQL--loaddata infile、outfile into及mysqldump高效导入导出数据_mysql
【学习配景】在日常工作和学习MySQL时，经常涉及到MySQL数据的导入和导出，分享几种常用又方便的方式：
（1）MySQL命令行source命令
（3）语法into outfile和load data infile
（3）MySQL目录bin下的mysqldump工具
本文将会先容以及测试这几种MySQL导入导出数据的方式及使用注意事项，参数可能会比力多，大家可以学习最常用的就好，这里分享出来，希望能帮助到有必要的小同伴~
进入正文~
学习目录
[*]测试数据
[*]一、命令source实现
[*]
[*]2.1 导入数据
[*]2.2 导出数据
[*]二、 into oufile和load data infile实现
[*]
[*]2.1 into outfile
[*]
[*]2.1.1 简单导出数据
[*]2.1.2 带格式导出数据
[*]2.1.3 导出注意事项
[*]2.2 load data infile
[*]
[*]2.2.1 简单导入数据
[*]2.2.2 带格式导入数据
[*]三、工具mysqldump实现
[*]
[*]3.1 导出
[*]
[*]3.1.1 数据库
[*]3.1.2 数据表
[*]3.2 导入数据
测试数据
本文以Windows下操作为例，Linux也是一样的方法，区别在于路径语法不同而已~
创建一个MySQL数据库test和数据表demo_info，方便举行测试~
create database if not exists test default character set utf8 collate utf8_general_ci;
use test;
-- 创建测试表
create table test.demo_info(
id int(7) primary key not null auto\_increment,
name varchar(255) not null,
sex char(1) not null,
age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table test.demo_info comment '测试表';
alter table test.demo_info modify column id int(7) not null auto\_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年龄';
一、命令source实现
2.1 导入数据
（1）准备insert.sql内容如下：
use test;
insert into test.demo_info(name,sex,age) values('张一','1',21);
insert into test.demo_info(name,sex,age) values('张二','0',22);
insert into test.demo_info(name,sex,age) values('张三','1',23);
**存放路径：**C:/Users/Administrator/Desktop/insert.sql
（2）先登录到MySQL命令行
打开cmd命令窗口，登录到MySQL命令行：
$ cd C:\Program Files\MySQL\MySQL Server 5.7\bin
$ mysql -hlocalhost -uroot -p --default-character-set=utf8
输入密码：
mysql >
（3）执行source命令导入数据：
mysql> use test;
mysql> show tables;
mysql> select * from demo_info;
mysql> source C:/Users/Administrator/Desktop/insert.sql;
注意如果你数据库没有设置字符集为utf8，而且在连接时也没有指定--default-character-set=utf8连接，那么会导致插入中文数据时乱码，提示如下：
https://img-blog.csdnimg.cn/5cd38e66b7994d4e8c086582aba191ea.png
乱码原因是，默认客户端连接编码为GBK
mysql> use test;
mysql> show variables like '%character%';
https://img-blog.csdnimg.cn/1b848e2598ad4b3aa7aee7a7a72b29d5.png
中文乱码情况的解决方案，如果不想在连接时指定字符集为utf8，可以修改mysql的配置my.ini（my.cnf）指定字符集为utf8，重启mysql服务见效~
default-character-set=utf8
character-set-server=utf8
default-character-set=utf8
2.2 导出数据
命令source导出数据紧张是通过执行导出数据的SQL语句，本质照旧使用into outfile语法来实现，这里先简单直接使用下~
（1）准备select.sql内容如下：
use test;
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';
**select.sql存放路径：**C:/Users/Administrator/Desktop/select.sql
（2）执行source命令导出数据：
mysql> source C:/Users/Administrator/Desktop/select.sql;
不过，别高兴太早，一般都会报错的，提示如下：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
原因是--secure-file-priv安全路径问题，具体往下进入到into outfile章节了解~
二、 into oufile和load data infile实现
2.1 into outfile
2.1.1 简单导出数据
导出数据通过into outfile语法实现，导入数据通过load data infile语法实现~
（1）条件条件说明
授权用户file权限：
mysql > select * from mysql.user where user='root' \G;
mysql > update mysql.user set File_priv='Y' where user='root';
mysql > select * from mysql.user where user='root' \G;
mysql > flush privileges;
如果没有授予用户的File_priv权限为Y，into outfile导出文件时会报错：
ERROR 1 (HY000): Can’t create/write to file ‘C:\Users\Administrator\Desktop\demo_info.txt’ (Errcode: 13 - Permission denied)
配置安全路径：
MySQL使用into outfile语法导出数据时，只能导出数据文件到secure-file-priv指定的安全路径下~
检察安全路径命令mysql> show variables like '%secure%';
https://img-blog.csdnimg.cn/fee32bacfffc42bfa6c912873a17a601.png
可以看到参数secure_file_priv对应的路径即为MySQL安全路径：
但是Windows下路径问题，有一个小坑，容易误导人，就是这里show显示的路径是单反斜杠\，但实际用的时候要么变成双反斜杠\\，要么改成单斜杠/，才华使用into outfile语法正常导出，否则会报错~
https://img-blog.csdnimg.cn/ba62ead4423b457f8d2cfbeaf1762b8b.png
如果指定导出文件路径不是安全路径下的，则会报错：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
简单导出测试下（非安全路径，如桌面）：
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';
报错提示如下：
https://img-blog.csdnimg.cn/db70deb762ce46dea6aac1783faa9ffd.png
简单导出测试下（安全路径）
select \* from test.demo_info into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/demo\_info.txt';
正常导出demo_info.txt数据文件（注意Windows下路径不要用单反斜杠\）
https://img-blog.csdnimg.cn/db6aa07ad19e40b9baa1e625855d8647.png
（2）配置安全路径
如果不想用默认安全路径，可以修改参数--secure-file-priv为自界说路径，修改MySQL配置文件，一般默认的配置文件路径为：
Windows：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
Linux：/etc/my.cnf
安全路径在组下找到参数secure_file_priv举行配置即可~
https://img-blog.csdnimg.cn/01c6c0930d074afa9020c725595185bf.png
这里我修改为空字符串""：
secure-file-priv=""
空字符串""体现不限定导出路径，不过必要是mysql用户有读写权限的目录，例如Linux下，你不能直接导出到/root/目录下，肯定是没权限创建数据文件的~~
（3）导出数据
简单导出测试：
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';
发现导出到桌面居然不成功，其他MySQL安装目录和D盘都可以，C盘下都不行~
https://img-blog.csdnimg.cn/3d6e8d891db04ee892890da2b95c08da.png
解决方案是按快捷键：Win 快速搜索：服务关键字，找到mysql服务，右键检察属性~
https://img-blog.csdnimg.cn/5cbee306928e4b4196271aa0be72843d.png
切换账户为本地系统账户并勾选允许服务与桌面交互~
https://img-blog.csdnimg.cn/e1613b412c394f7394dc147687fe5dc3.png
应用并重启mysql服务见效~
https://img-blog.csdnimg.cn/0bd2570cd03c4b7a9be35128aa0b9bfd.png
重新简单导出测试，导出到桌面成功：
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';
https://img-blog.csdnimg.cn/7293b31cffdb4885a18e8920b4a747f3.png
2.1.2 带格式导出数据
通过前面简单导出数据得到数据文件demo_info.txt，可以看到导出的数据占用的空间比力大
7 张一 1 21
8 张二 0 22
9 张三 1 23
如果字段的数据比力长，数据量比力大，会很浪费空间，因此必要对into outfile导出的数据文件举行格式化：
（1）MySQL命令行>
select \* from demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.del' character set utf8 fields terminated by 0x0f;
导出的数据空间完全精密，不浪费任何空间，实际使用这种方式的非常多：https://img-blog.csdnimg.cn/f3f1ee7a05ca40d0b52215413801f92e.png
（2）终端命令行：
mysql -hlocalhost -uroot -p test -e "select \* from test.demo\_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.del' character set utf8 fields terminated by 0x0f"
into outfile参数说明：
参数说明character set utf8字符集utf8，防止中文乱码，必要放在fields前面，否则报错fields域，反面常用字段有terminated/optionally/escapedterminated by 'string'设置字段数据之间的分隔符，如最常用的分隔符0x0foptionally enclosed by 'char'设置字段非数值的数据，使用什么符号引起，如英文双引号"escaped by 'char'字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"lines设置每条记载的开头starting和末端字符terminatedlines starting by 'char'设置每条记载的开头字符，默认空字符串''lines terminated by 'char'设置每条记载的末端字符默认换行符'\n' 使用enclosed by参数示例：
select \* from demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info2.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"';
https://img-blog.csdnimg.cn/e0fe3b02e0a34c1aa4791cf33121bf6e.png
使用escaped by参数示例：
例如，把张三的名字反面加个特殊符号换行符\n
update test.demo_info set name='张一\n' where id=7;
https://img-blog.csdnimg.cn/e61a0440f31b4f929cdd2c4387a4d001.png
再执行导出命令：
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info3.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"';
https://img-blog.csdnimg.cn/190fcd32693d40a386ec65ebae76bcea.png
使用lines参数示例：
update test.demo_info set name='张一' where id=7;
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info4.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';
观察每条记载的首尾数据格式：
https://img-blog.csdnimg.cn/7aa2e32c85a9488cacea6d1c0110e6fe.png
2.1.3 导出注意事项
（1）存在问题：
Linux情况下，由于使用MySQL语法into outfile导出的数据文件时，数据文件只能保存在MySQL数据库服务端，那么会导致在集群模式下，当应用和数据库分别摆设在两台不同的服务器时，会存在应用无法读取到数据文件的问题~
MySQL服务器M：/batchfile/mysql/data/test/demo_info.del;
应用服务器A： 批量程序，可能会通过shell脚本想要加载demo_info.del数据文件~
应用服务器B： 批量程序，可能会通过shell脚本想要加载demo_info.del数据文件~
（2）解决方案：
可以通过mount挂在指定目录/batchfile/为共享盘目录，实现服务器A、B、M都能拥有该目录下的数据文件的读写访问权限~
具体mount命令的使用方式，可以查询百度学习下~
2.2 load data infile
2.2.1 简单导入数据
（1）数据文件
前面通过into outfile简单导出得到demo_info.txt：
7 张一 1 21
8 张二 0 22
9 张三 1 23
（2）导入数据
load data infile 'C:/Users/Administrator/Desktop/demo\_info.txt' into table demo_info character set utf8;
https://img-blog.csdnimg.cn/504d4c3cb30142d6a6f6537c1498cbab.png
2.2.2 带格式导入数据
导入del数据文件（加载服务端文件）：
命令行mysql>
load data infile 'C:/Users/Administrator/Desktop/demo\_info.del' into table demo_info character set utf8 fields terminated by 0x0f;
load data infile参数说明：
参数说明character set utf8字符集utf8，防止中文乱码，必要放在fields前面，否则报错fields域，反面常用字段有terminated/optionally/escapedterminated by 'string'设置字段数据之间的分隔符，如最常用的分隔符0x0foptionally enclosed by 'char'设置字段非数值的数据，使用什么符号引起，如英文双引号"escaped by 'char'字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"lines设置每条记载的开头starting和末端字符terminatedlines starting by 'char'设置每条记载的开头字符，默认空字符串''lines terminated by 'char'设置每条记载的末端字符默认换行符'\n'(字段1,字段2,字段3)指定字段导入数据，注意放在整个语句最后，放前面，会报错 其实除了指定字段的参数，其他参数大多只必要跟into outfile导出参数一样，导出时有的参数，load data infile导入时该有的参数也加上就好~
比如into outfile导出最复杂的情况如下（分隔符为0x0f、非数值双引号"扩起、特殊转义符使用双引号"转义、每条记载开头是start及末端是end\n）得到数据文件demo_info_complex_data.del
update test.demo_info set name='张一\n' where id=7;
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info\_complex\_data.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start '; 可以看到demo_info_complex_data.del内容如下：
https://img-blog.csdnimg.cn/1be8342042494881b0eb131e073e00a6.png
那么要导入demo_info_complex_data.del对应的load data infile语法完整SQL语句为：
load data infile 'C:/Users/Administrator/Desktop/demo\_info\_complex\_data.del' into table demo_info character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';
其实很简单，把into outfile导出数据时character反面的参数直接copy过来就行~
https://img-blog.csdnimg.cn/356deb9699a04d9aaee377afdc1f9c56.png
Linux终端命令：
mkdir -p /batchfile/mysql/data/test/
mysql -hlocalhost -uroot -p test -e "load data infile '/batchfile/mysql/data/test/demo\_info.del' into table demo\_info character set utf8 fields terminated by 0x0f"
导入del数据文件（加载客户端本地LOCAL文件）：
命令行mysql>
load data LOCAL infile 'C:/Users/Administrator/Desktop/demo\_info.del' into table demo_info character set utf8 fields terminated by 0x0f;
Linux终端命令：
mysql -hlocalhost -uroot -p test -e "load data LOCAL infile '/batchfile/mysql/data/test/demo\_info.del' into table demo\_info character set utf8 fields terminated by 0x0f"
注意：如果MySQL服务端在Linux，load data infile默认是加载服务端路径的数据文件，指定LOCAL体现加载的是客户端的本地数据文件~
三、工具mysqldump实现
MySQL 自带mysqldump 工具，工具文件在bin目录下，不仅可以导出和导入表数据，还可以选择性的导出库表（整库、多库、单库、多表、单表）结构，是数据库备份的方途径之一~
同样本文以Windows下为例，Linux区别在于路径不同~
操作本地：mysqldump -u数据库用户 -p xxx
操作长途：mysqldump -hIP所在 -P端口号 -p xxx
3.1 导出
3.1.1 数据库
打开cmd命令窗，进入到bin目录下：
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
（1）导出所有数据库（结构+数据）
mysqldump -uroot -p --all-databases > C:/Users/Administrator/Desktop/all_databases.sql
（2）导出指定命据库（结构+数据）
mysqldump -uroot -p --databases test > test.sql
也可以指定多个数据库（结构+数据）
mysqldump -u root -p --databases test test2 > test_test2.sql
3.1.2 数据表
（1）导出指定命据表（结构+数据）
mysqldump -u root -p --set-gtid-purged=OFF test demo_info > demo_info.sql
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]