本文介绍了使用"mysqldump"以CSV格式转储所有表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要以CSV格式在MySQL中转储所有表.

I need to dump all tables in MySQL in CSV format.

是否存在使用mysqldump公正以CSV格式输出每个表的每一行的命令?

Is there a command using mysqldump to just output every row for every table in CSV format?

推荐答案

首先,我可以为您提供一个表的答案:

First, I can give you the answer for one table:

所有这些INTO OUTFILE--tab=tmpfile(和-T/path/to/directory)答案的麻烦在于,它要求在与MySQL服务器相同的服务器上运行 mysqldump ,并拥有这些访问权限.

The trouble with all these INTO OUTFILE or --tab=tmpfile (and -T/path/to/directory) answers is that it requires running mysqldump on the same server as the MySQL server, and having those access rights.

我的解决方案是简单地将mysql( not mysqldump)与-B参数一起使用,将SELECT语句与-e内联,然后使用sed压缩ASCII输出. ,并以包含标题字段行的CSV结束:

My solution was simply to use mysql (not mysqldump) with the -B parameter, inline the SELECT statement with -e, then massage the ASCII output with sed, and wind up with CSV including a header field row:

示例:

 mysql -B -u username -p password database -h dbhost -e "SELECT * FROM accounts;" \
 | sed "s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

在该单行代码的末尾添加> outfile.csv,以获取该表的CSV文件.

Add a > outfile.csv at the end of that one-liner, to get your CSV file for that table.

接下来,使用

mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"

从那里开始,仅要做一个循环,例如,在Bash shell中循环访问这些表:

From there, it's only one more step to make a loop, for example, in the Bash shell to iterate over those tables:

 for tb in $(mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"); do
     echo .....;
 done

do; done之间插入我在上面的第1部分中写的长命令,但是用$tb代替您的表名.

Between the do and ; done insert the long command I wrote in Part 1 above, but substitute your tablename with $tb instead.

这篇关于使用"mysqldump"以CSV格式转储所有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-27 07:01
查看更多