The best answers to the question “How can I output MySQL query results in CSV format?” in the category Dev.
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.
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
From Save MySQL query results into a text or CSV file:
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';
Note: That syntax may need to be reordered to
SELECT order_id,product_name,qty INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM orders WHERE 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.
Here’s a fairly gnarly way of doing it:
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/"https://stackoverflow.com/"/;s/\t/","/g;s/^/"/;s/$/"/;s/\n//g" > vehicle_categories.csv
It works pretty well. Once again, though, a regular expression proves write-only.
- s/// means substitute what’s between the first // with what’s between the second //
- the “g” at the end is a modifier that means “all instance, not just first”
- ^ (in this context) means beginning of line
- $ (in this context) means end of line
So, putting it all together:
s/'/\'/ Replace ' with \' s/\t/\",\"/g Replace all \t (tab) with "," s/^/\"/ at the beginning of the line place a " s/$/\"/ At the end of the line, place a " s/\n//g Replace all \n (newline) with nothing
 I found it somewhere and can’t take any credit.
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.