mysql dump格式,使用'mysqldump'转储CSV格式的所有表

最新推荐文章于 2024-07-28 02:47:15 发布

一周进步

最新推荐文章于 2024-07-28 02:47:15 发布

阅读量149

点赞数

文章标签： mysql dump格式

本文介绍了如何使用`mysql`命令而非`mysqldump`来将MySQL数据库中的所有表格数据导出为CSV格式。通过在命令行中结合`SELECT * FROM`语句、`sed`命令进行文本转换，可以生成包含表头的CSV文件。首先，展示了一个针对单个表格的示例，然后给出了获取数据库中所有表格名称的命令，并演示了如何在Bash shell中使用循环遍历这些表格，逐一执行导出操作。

摘要由CSDN通过智能技术生成

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