MySQL常用语句（六）：纯数据的导出导入
发布时间：2023-01-07 12:52:28 所属栏目：MySql教程 来源：
导读： 工作中有时一些同事需要看某张表的数据，也许他们并不会处理使用mysqldump导出的纯数据的SQL文件MySQL 导出数据，此时如果不太方便通过一些工具（比如navicat）处理，就需要通过into outfile 导出表的所有数据，
|
工作中有时一些同事需要看某张表的数据，也许他们并不会处理使用mysqldump导出的纯数据的SQL文件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表数据将更加方便。 （编辑：我爱资讯网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐