❤️‍MySQL--【全网最细】高效导入导出数据，学会下早班❤️‍（工作必备 & 建议收藏）

【学习背景】

在日常工作和学习Mysql时，经常涉及到MySQL数据的导入和导出，分享几种常用又方便的方式：

（1）MySQL命令行source命令

（3）语法into outfile和load data infile

（3）MySQL目录bin下的mysqldump工具

本文将会介绍以及测试这几种MySQL导入导出数据的方式及使用注意事项，参数可能会比较多，大家可以学习最常用的就好，这里分享出来，希望能帮助到有需要的小伙伴~

进入正文~

@TOC

测试数据

本文以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连接，那么会导致插入中文数据时乱码，提示如下：

乱码原因是，默认客户端连接编码为GBK

mysql> use test;

mysql> show variables like '%character%';

中文乱码情况的解决方案，如果不想在连接时指定字符集为utf8，可以修改mysql的配置my.ini（my.cnf）指定字符集为utf8，重启mysql服务生效~

[client] default-character-set=utf8 [mysql] character-set-server=utf8 [mysqld] 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%';

可以看到参数secure_file_priv对应的路径即为MySQL安全路径：

==但是Windows下路径问题，有一个小坑，容易误导人，就是这里show显示的路径是单反斜杠\，但实际用的时候要么变成双反斜杠\，要么改成单斜杠/，才能使用into outfile语法正常导出，否则会报错~==

如果指定导出文件路径不是安全路径下的，则会报错：

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

报错提示如下：

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

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参数说明：

==使用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参数说明：

==其实除了指定字段的参数，其他参数大多只需要跟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

–参数说明

（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

–参数说明

（3）导出==指定==数据表（仅数据 + where条件）

mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info --where "name = '张三'" > demo_info.sql

3.2 导入数据

使用工具mysqldump本质是得到SQL语句文件，本文主要目的是介绍导入和导出表数据~

（1）导出表数据

通过前面导出的介绍，可以使用以下命令仅导出demo_info表的数据即可~

终端命令$ ：

mysqldump -hlocalhost -P3306 -uroot -p --set-gtid-purged=OFF --no-create-info test demo_info > C:/Users/Administrator/Desktop/demo_info.sql

得到demo_info数据表的SQL数据文件demo_info.sql~

（2）导入表数据

先清空表，再导入数据~

终端命令$ ：

mysqldump -hlocalhost -P3306 -uroot -p < C:/Users/Administrator/Desktop/demo_info.sql;

也可以通过最开始介绍的source命令行来执行SQL语句文件实现导入~

命令行mysql> source C:/Users/Administrator/Desktop/demo_info.sql;

或

通过终端命令$：

mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"select * from test.demo_info;" mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"delete from test.demo_info;" mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"source C:/Users/Administrator/Desktop/demo_info.sql;"

导入数据成功！！！

文章完结~~

原创不易，觉得有用的小伙伴来个一键三连（++评论 ）+关注支持一下，非常感谢~

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。