MySQL Query to CSV [duplicate]

Question

Is there an easy way to run a MySQL query from the linux command line and output the results in csv format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee
list.csv select id, concat("\"",name,"\"") as name from students EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Answer 1

You can use "INTO OUTFILE"

I.e.

SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; 

This will create a file "ca.csv" with a comma between each line. There are a few other options like escaping field values, but that should be enough to get you started.

The whole thing:

mysql -u uid -ppwd -D dbname -e "SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

EDIT :- A link for you to look at: SELECT INTO

To escape the fields, you would need to add FIELDS ESCAPED BY '"' - This would add double quotes