How to output MySQL query results in CSV format

Question

Is there an easy way to run a MySQL query from the window 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

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

Hello @kartik,

Using this command columns names will not be exported.

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

Hope it helps!!
Thank you!!