MySQL--loaddata infile、outfile into及mysqldump高效导入导出数据_mysql load outfile

最后

作为过来人，小编是整理了很多进阶架构视频资料、面试文档以及PDF的学习资料，针对上面一套系统大纲小编也有对应的相关进阶架构视频资料

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

简单导出测试下（非安全路径，如桌面）：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

报错提示如下：

简单导出测试下（安全路径）

select \* from test.demo_info into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/demo\_info.txt';

正常导出demo_info.txt数据文件（注意Windows下路径不要用单反斜杠\）

（2）配置安全路径
如果不想用默认安全路径，可以修改参数--secure-file-priv为自定义路径，修改MySQL配置文件，一般默认的配置文件路径为：
Windows：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
Linux：/etc/my.cnf

安全路径在[mysqld]组下找到参数secure_file_priv进行配置即可~

这里我修改为空字符串""：

secure-file-priv=""

空字符串""表示不限制导出路径，不过需要是mysql用户有读写权限的目录，例如Linux下，你不能直接导出到/root/目录下，肯定是没权限创建数据文件的~~

（3）导出数据
简单导出测试：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

发现导出到桌面居然不成功，其他MySQL安装目录和D盘都可以，C盘下都不行~

解决方案是按快捷键：Win 快速搜索：服务关键字，找到mysql服务，右键查看属性~

切换账户为本地系统账户并勾选允许服务与桌面交互~

应用并重启mysql服务生效~

重新简单导出测试，导出到桌面成功：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

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;

导出的数据空间完全紧密，不浪费任何空间，实际使用这种方式的非常多：
（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/escaped
terminated by 'string'	设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'	设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'	字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines	设置每条记录的开头starting和结尾字符terminated
lines 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 '"';

使用escaped by参数示例：
例如，把张三的名字后面加个特殊符号换行符\n

update test.demo_info set name='张一\n' where id=7;

再执行导出命令：

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 '"';

使用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 ';

观察每条记录的首尾数据格式：

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;

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/escaped
terminated by 'string'	设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'	设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'	字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines	设置每条记录的开头starting和结尾字符terminated
lines 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内容如下：

那么要导入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过来就行~

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

注意：这里设置--set-gtid-purged参数设置为OFF表示mysqldump备份时会记录MySQL的binlog日志，如果不加，则不会记录binlog日志，binlog日志这里不再做具体介绍，简单说明就是MySQL数据库备份、主从复制的核心日志文件~
那要不要记录binlog日志，取决于你的MySQL设置主从复制的时候用到了gtid：
mysql> show variables like '%gtid%';
参数gtid_mode为ON表明用到gtid了，当然我这里是单库没有主从因此为OFF~

所以如果是MySQL主从数据库，并且在主库使用mysqldump备份时，需要加--set-gtid-purged=OFF，以便主库记录binlog日志，否则主库没有了binlog日志，当你想在主库恢复备份的数据时，数据并不会被同步到从库~

（2）导出指定数据表（仅结构）

mysqldump -u root -p --set-gtid-purged=OFF -d test demo_info > demo_info.sql

–参数说明

参数	说明
-d	等价于--no-data，表示不包含数据，仅导出表结构

（2）导出指定数据表（仅数据）

mysqldump -u root -p --set-gtid-purged=OFF -t test demo_info > demo_info.sql

等价于：

mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info > demo_info.sql

–参数说明

参数	说明
-t	等价于--no-create-info，表示仅导出数据，不导出CREATE TABLE的表结构

总结

蚂蚁面试比较重视基础，所以Java那些基本功一定要扎实。蚂蚁的工作环境还是挺赞的，因为我面的是稳定性保障部门，还有许多单独的小组，什么三年1班，很有青春的感觉。面试官基本水平都比较高，基本都P7以上，除了基础还问了不少架构设计方面的问题，收获还是挺大的。

---

经历这次面试我还通过一些渠道发现了需要大厂真实面试主要有：蚂蚁金服、拼多多、阿里云、百度、唯品会、携程、丰巢科技、乐信、软通动力、OPPO、银盛支付、中国平安等初，中级，高级Java面试题集合，附带超详细答案，希望能帮助到大家。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

门，还有许多单独的小组，什么三年1班，很有青春的感觉。面试官基本水平都比较高，基本都P7以上，除了基础还问了不少架构设计方面的问题，收获还是挺大的。

---

经历这次面试我还通过一些渠道发现了需要大厂真实面试主要有：蚂蚁金服、拼多多、阿里云、百度、唯品会、携程、丰巢科技、乐信、软通动力、OPPO、银盛支付、中国平安等初，中级，高级Java面试题集合，附带超详细答案，希望能帮助到大家。

[外链图片转存中…(img-wCDQPDiO-1715458472195)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取