MySQL常用语句（六）：纯数据的导出导入

汤巫山实时趣闻 2016-12-22 22:33

工作中有时一些同事需要看某张表的数据，也许他们并不会处理使用mysqldump导出的纯数据的SQL文件，此时如果不太方便通过一些工具（比如navicat）处理，就需要通过into outfile 导出表的所有数据，并将数据呈现为类似excel的形式。相关命令如下：

1、导出数据（不指定分割符）

mysql> select * from class_info into outfile '/tmp/class_info.csv';

mysql> system cat /tmp/class_info.csv

1 1601 a 87

2 1601 b 90

3 1602 d 91

4 1602 c 85

5 1603 e 88

-------------------------------

2、导出数据，字段分隔符为",",字段引用符为“ " ”（双引号）

mysql> select * from class_info into outfile '/tmp/class_info_1.csv' fields terminated by "," enclosed by '"';

Query OK, 5 rows affected (0.00 sec)

mysql> system cat /tmp/class_info_1.csv

"1","1601","a","87"

"2","1601","b","90"

"3","1602","d","91"

"4","1602","c","85"

"5","1603","e","88"

3、导出数据，字段分隔符为","，数值型字段不加引用符，其余字段加引号

mysql> select * from class_info into outfile '/tmp/class_info_2.csv' fields terminated by "," optionally enclosed by '"';

Query OK, 5 rows affected (0.02 sec)

mysql> system cat /tmp/class_info_2.csv

1,1601,"a",87

2,1601,"b",90

3,1602,"d",91

4,1602,"c",85

5,1603,"e",88

4、数据恢复

使用load infile恢复

备份如第1种情况，恢复如下

mysql> load data infile '/tmp/class_info.csv' into table class_info;

备份如第2种情况，恢复如下

mysql> load data infile '/tmp/class_info_1.csv' into table class_info fields terminated by "," enclosed by '"';

备份如第3中情况，恢复如下

mysql> load data infile '/tmp/class_info_2.csv' into table class_info fields terminated by "," optionally enclosed by '"';

使用mysqlimport恢复

# mysqlimport -uroot -p tws /tmp/class_info.csv

Enter password:

tws.class_info: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

查看结果

# mysql -uroot -p -e "select * from tws.class_info"

当然，如果条件允许，能使用navicat 或者workbench 等工具获取MySQL表数据将更加方便。

本文为头条号作者发布，不代表今日头条立场。