Mysqldump以CSV格式 [英] Mysqldump in CSV format

问题描述

是否有一个命令使用mysqldump JUST输出CSV格式的每一个表格的每一行？

解决方案

首先，我可以给你一张表的答案：

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

我的解决方案只是使用 mysql （NOT mysqldump ）与 -B 参数，使用 -e 内联SELECT语句，然后按< $ c> sed ，并用CSV结尾，包括标题字段行：

示例：

  mysql -B -u用户名-p密码数据库-h dbhost -eSELECT * FROM accounts; \ 
 | seds /'/ \'/; s / \t / \，\/ g; s / ^ / \/; s / $ / \/; s / \\\
 // g
  

id，login，password电子邮件
8，mariana，xxxxxxxxxx，mariana，
3，squaredesign，xxxxxxxxxxxxxxxxx ，[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 所有表格的列表

  mysql -u username -ppassword dbname -sN -eSHOW TABLES; 
  

从这里，只有一个步骤，在 bash shell中对这些表进行迭代：

  $（mysql -u username -ppassword dbname -sN -eSHOW TABLES;）; do 
 echo .....; 
 done 
  

do 和; done 插入我在上面第1部分中写的long命令，但用 $ tb 替换您的tablename。

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/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

"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]"

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 eg. 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.

这篇关于Mysqldump以CSV格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！