使用 'mysqldump' 以 CSV 格式转储所有表

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

是否有使用 mysqldump 来只是以 CSV 格式输出每个表的每一行的命令?

解决方案

首先，我可以给你一个一个表的答案:

所有这些 INTO OUTFILE 或 --tab=tmpfile(和 -T/path/to/directory)答案的问题是它需要在与 MySQL 服务器相同的服务器上运行 mysqldump ，并具有这些访问权限.

我的解决方案只是使用带有 -B 参数的 mysql(not mysqldump)，内联使用 -e 的 SELECT 语句，然后使用 sed 处理 ASCII 输出，最后得到包含标题字段行的 CSV:

示例:

 mysql -B -u 用户名 -p 密码数据库 -h dbhost -e "SELECT * FROM accounts;"\|sed "s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

<块引用>

"id","login","password","folder","email"8"、玛丽安娜"、xxxxxxxxx"、玛丽安娜"、""3","squaredesign","xxxxxxxxxxxxxxxxx","squaredesign","mkobylecki@squaredesign.com""4","miedziak","xxxxxxxxx","miedziak","miedziak@mail.com""5","萨科","xxxxxxxxx","萨科","""6","Logitrans波兰","xxxxxxxxxxxxxx","LogitransPoland","""7","阿莫斯","xxxxxxxxxxxxxxxxxxxx","阿莫斯","""9","安娜贝尔","xxxxxxxxxxxxxxx","安娜贝尔",""《11》、《祖父和儿子们","xxxxxxxxxxxxxxxxx","BrandfathersAndSons","""12","想象组","xxxxxxxxxxxxxxx","ImagineGroup","""13","EduSquare.pl","xxxxxxxxxxxxxxxxx","EduSquare.pl","""101","tmp","xxxxxxxxxxxxxxxxxxxxx","_","WOBC-14.squaredesign.atlassian.net@yoMama.com"

添加<代码>>outfile.csv 在那一行的末尾，以获取该表的 CSV 文件.

接下来，获取所有表的列表

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

从那里开始，只需再执行一个循环即可，例如，在 Bash shell 中迭代这些表:

 for tb in $(mysql -u username -ppassword dbname -sN -e "SHOW TABLES;");做回声.....;完毕

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

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

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:

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.

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:

Example:

 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","login","password","folder","email" "8","mariana","xxxxxxxxxx","mariana","" "3","squaredesign","xxxxxxxxxxxxxxxxx","squaredesign","mkobylecki@squaredesign.com" "4","miedziak","xxxxxxxxxx","miedziak","miedziak@mail.com" "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","_","WOBC-14.squaredesign.atlassian.net@yoMama.com"

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

Next, get a list of all your tables with

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

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

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