How to output MySQL query results in csv format?

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

From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Answer 2

You can use REPLACE() in your query to have the quotes escaped.

Answer 3

Alternatively to the answer above, you can have a MySQL table that uses the CSV engine.

Then you will have a file on your hard disk that will always be in a CSV format which you could just copy without processing it.

Answer 4

mysql --batch, -B

Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

This will give you a tab separated file. Since commas (or strings containing comma) are not escaped it is not straightforward to change the delimiter to comma.

Answer 5

$ mysql your_database --password=foo < my_requests.sql > out.csv

Answer 6

The OUTFILE solution given by Paul Tomblin causes a file to be written on the MySQL server itself, so this will work only if you have FILE access, as well as login access or other means for retrieving the file from that box.

If you don't have such access, and tab-delimited output is a reasonable substitute for CSV (e.g., if your end goal is to import to Excel), then Serbaut's solution (using mysql --batch and optionally --raw) is the way to go.

Answer 7

The stan Alternative wordks pretty well, just make sure not to put begin and end quotes on your query.