使用"mysqldump"以CSV格式转储所有表 [英] Dump all tables in CSV format using 'mysqldump'

问题描述

我需要以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"

"id"，登录"，密码"，文件夹"，电子邮件" "8"，玛丽安娜"，"xxxxxxxxxx"，玛丽安娜"，" "3"，"squaredesign"，"xxxxxxxxxxxxxxxxx"，"squaredesign"，"[e-mail]" "4"，"miedziak"，"xxxxxxxxxx"，"miedziak"，"[e-mail]" "5"，"Sarko"，"xxxxxxxxx"，"Sarko"，" "6"，"Logitrans 波兰，" xxxxxxxxxxxxxx，" LogitransPoland，" "7"，"Amos"，"xxxxxxxxxxxxxxxxxxxx"，"Amos"，" "9"，"Annabelle"，"xxxxxxxxxxxxxxxx"，"Annabelle"，" "11"，祖父和 儿子，" xxxxxxxxxxxxxxxxx，"祖父和儿子，" "12"，想象 组"，"xxxxxxxxxxxxxxxx"，"ImagineGroup"，" "13"，"EduSquare.pl"，"xxxxxxxxxxxxxxxxx"，"EduSquare.pl"，" "101"，"tmp"，"xxxxxxxxxxxxxxxxxxxxx"，"_"，"[e-mail]"

"id","login","password","folder","email" "8","mariana","xxxxxxxxxx","mariana","" "3","squaredesign","xxxxxxxxxxxxxxxxx","squaredesign","[e-mail]" "4","miedziak","xxxxxxxxxx","miedziak","[e-mail]" "5","Sarko","xxxxxxxxx","Sarko","" "6","Logitrans Poland","xxxxxxxxxxxxxx","LogitransPoland","" "7","Amos","xxxxxxxxxxxxxxxxxxxx","Amos","" "9","Annabelle","xxxxxxxxxxxxxxxx","Annabelle","" "11","Brandfathers and Sons","xxxxxxxxxxxxxxxxx","BrandfathersAndSons","" "12","Imagine Group","xxxxxxxxxxxxxxxx","ImagineGroup","" "13","EduSquare.pl","xxxxxxxxxxxxxxxxx","EduSquare.pl","" "101","tmp","xxxxxxxxxxxxxxxxxxxxx","_","[e-mail]"

在该单行代码的末尾添加> 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格式转储所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！