使用mysqldump将表数据导出到csv文件中 [英] Export table data into csv file using mysqldump

问题描述

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

我想做一些类似的东西：

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

但我不断收到此错误：

 （Errcode：13）
  

我使我的测试文件夹可写（我使用Ubuntu作为环境）。可以soneone解释如何导出CSV文件中的表，或如何修改我的命令shell为了工作感谢！

解决方案

所有这些 INTO OUTFILE 或 = tmpfile 的答案是，它需要在MySQL服务器上在同一服务器上运行 mysqldump 。

我的解决方案只是使用 - > mysql （NOT mysqldump B 参数，使用 -e 内嵌SELECT语句，然后使用 sed

$ b

例如：

  mysql -B -u username -ppassword database -h dbhost -eSELECT * FROM accounts;| seds /'/ \'/; s / \t / \，\ / g; s / ^ / \/; s / $ / \/; s / \\\
 // g
  

ID，登录，密码，文件夹，电子邮件
8，玛丽安娜，57d40c8a954bc9e65f401592062019b1457be359 马里亚纳，
3，squaredesign，b01134fac01dab765bcb55ab0ca33f9ec2885a7b，squaredesign，[e-mail]
4，miedziak，601f1889667efaebb33b8c12572835da3f027f78， miedziak，[e-mail]
5，萨科奇，480225f1ac707031bae300f3f5b06dbf79ed390e，萨科奇，
6，Logitrans
波兰， 9033b6b3c4acbb27418d8b0b26f4c880ea6dea22，LogitransPoland，
7，摩，50f5604164db2d5ff0e984f973d2202d5358b6a6，摩，
9，安娜贝勒，07e832cb64e66fa03c13498a26a5f8e3bdebddf1，安娜贝勒，
11，Brandfathers和
父子，f08b194668249e4cb81fbb92df846e90569f5c01，BrandfathersAndSons，
12，想象
集团， e86f1645504c7d5a45ed41d16ecc39ed19181773，ImagineGroup，
13，EduSquare.pl，80c3c099f4ecc043a19f1527400d63588567a0ad，EduSquare.pl，
101，TMP，b01134fac01dab765bcb55ab0ca33f9ec2885a7b ，_，[e-mail]

添加> outfile.csv ，以获取您的CSV文件。

I want to export a table data into a csv file using mysqldump.

I want to make something like:

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

but i keep getting this error:

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

I made my testing folder writable(I'm using Ubuntu as enviornment). Can somenone explain how to export a table in a CSV file, or how to modify my command shell in order to work? Thanks!

解决方案

The trouble with all these INTO OUTFILE or --tab=tmpfile answers is that it requires running mysqldump on the same server as the MySQL server.

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 -ppassword database -h dbhost -e "SELECT * FROM accounts;" |sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

"id","login","password","folder","email" "8","mariana","57d40c8a954bc9e65f401592062019b1457be359","mariana","" "3","squaredesign","b01134fac01dab765bcb55ab0ca33f9ec2885a7b","squaredesign","[e-mail]" "4","miedziak","601f1889667efaebb33b8c12572835da3f027f78","miedziak","[e-mail]" "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","_","[e-mail]"

Add a > outfile.csv at the end of that one-liner, to get your CSV file.

这篇关于使用mysqldump将表数据导出到csv文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！