How can I output MySQL query results in CSV format?

From Save MySQL query results into a text or CSV file:

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

Note: That syntax may need to be reordered to

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

in more recent versions of MySQL.

Using this command, columns names will not be exported.

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.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

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

Which is tab-separated. Pipe it like that to get a true CSV (thanks to user John Carter):

... .sql | sed 's/\t/,/g' > out.csv

mysql --batch, -B

Print results using tab as the column separator, with each row on anew line. With this option, mysql does not use the history file.Batch mode results in non-tabular output format and escaping ofspecial characters. Escaping may be disabled by using raw mode; seethe 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.