MySQL常用语句（六）：纯数据的导出导入
工作中有时需要看某张表的所有数据，并且此时如果不太方便通过一些工具（比如navicat）处理，就需要通过into outfile 导出表的所有数据，并将数据呈现为类似excel的形式。相关命令如下：
1、导出数据（不指定分割符）
mysql> select * from class_info into outfile '/tmp/class_info.csv';
mysql> system cat /tmp/class_info.csv
11601a87
21601b90
31602d91
41602c85
51603e88
-------------------------------
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表数据将更加方便。
感谢分享 有用
发表评论