Export Results From MySQL Query To CSV

To export the results of a MySQL query to a CSV file use the following command.

mysql --user=username --password -h localhost database -B < query.sql | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > query_export.csv

This assumes you have created a SQL query in a file called "query.sql".

See this link to explainshell.com output for more information about the different parts of this command.

You can also use an inline query with the -e flag.

mysql --user=username --password -h localhost database -B -e "SELECT * FROM users;" query.sql | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > query_export.csv

The regular expression in use here works in the following way.

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

Initial code taken from https://stackoverflow.com/a/5395421.