问题描述
我需要以 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.
这篇关于使用 'mysqldump' 以 CSV 格式转储所有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!