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