mysql dump csv,使用mysqldump将表数据导出到csv文件中

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

踏歌西行

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

阅读量727

点赞数

文章标签： mysql dump 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","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