使用mysqldump将表数据导出为csv文件

Question

使用mysqldump将表数据导出为csv文件

3

我想使用mysqldump将表格数据导出到csv文件。

我想要做的是：

mysqldump --compact --no_create_info --tab=testing --fields-enclosed-by=\" --fields-terminated-by=, -uroot -proot mydatabase mytable

但我一直收到这个错误：

(Errcode: 13) when executing 'SELECT INTO OUTFILE'

我已将我的测试文件夹设置为可写（使用Ubuntu作为环境）。有人能解释一下如何将表格导出为CSV文件，或如何修改命令行以使其正常工作吗？谢谢！

- zuzuleinen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marcos · Accepted Answer

所有关于 INTO OUTFILE 或 --tab=tmpfile 的答案都存在一个问题，那就是需要在与 MySQL 服务器相同的服务器上运行 mysqldump。

我的解决方法是使用带有 -B 参数的 mysql（而不是 mysqldump），内联 SELECT 语句并使用 -e，然后使用 sed 处理 ASCII 输出，最终得到包含标题行的 CSV 文件：

示例：

 mysql -B -u username -ppassword database -h dbhost -e "SELECT * FROM accounts;" |sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

"编号","登录名","密码","文件夹","电子邮件" "8","mariana","57d40c8a954bc9e65f401592062019b1457be359","mariana","" "3","squaredesign","b01134fac01dab765bcb55ab0ca33f9ec2885a7b","squaredesign","mkobylecki@squaredesign.com" "4","miedziak","601f1889667efaebb33b8c12572835da3f027f78","miedziak","miedziak@mail.com" "5","Sarko","480225f1ac707031bae300f3f5b06dbf79ed390e","Sarko","" "6","Logitrans Poland","9033b6b3c4acbb27418d8b0b26f4c880ea6dea22","LogitransPoland","" "7","Amos","50f5604164db2d5ff0e984f973d2202d5358b6a6","Amos","" "9","Annabelle","07e832cb64e66fa03c13498a26a5f8e3bdebddf1","Annabelle","" "11","Brandfathers and Sons","f08b194668249e4cb81fbb92df846e90569f5c01","BrandfathersAndSons","" "12","Imagine Group","e86f1645504c7d5a45ed41d16ecc39ed19181773","ImagineGroup","" "13","EduSquare.pl","80c3c099f4ecc043a19f1527400d63588567a0ad","EduSquare.pl","" "101","tmp","b01134fac01dab765bcb55ab0ca33f9ec2885a7b","_","WOBC-14.squaredesign.atlassian.net@yoMama.com"

在这个一行代码的末尾添加> outfile.csv，就可以得到您的CSV文件。