How to output MySQL query results in csv format?

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

Which is tab separated. Pipe it like that to get a true CSV (thanks @therefromhere):

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

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.

Here's a fairly gnarly way of doing it. Found it somewhere, can't take any credit

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Works pretty well. Once again though a regex proves write only.

Regex Explanation:

• 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

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.

How about:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

Unix/cygwin only, pipe it through 'tr':

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

N.B.: this handles neither embedded commas, nor embedded tabs.

MySQL Workbench can export recordsets to CSV, and it seems to handle commas in fields very well. The CSV opens up in OpenOffice fine.

Here's what I do:

echo $QUERY | \
  mysql -B  $MYSQL_OPTS | \
  perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
  mail -s 'report' person@address

The perl script (sniped from elsewhere) does a nice job of converting the tab spaced fields to CSV.

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.

If there is a PHP installed on the machine you are using, you can write a PHP script to do that. It requires the PHP installation has the mysql extension installed.

You can call PHP interpreter from the command line like so

php --php-ini path/to/php.ini your-script.php

I am including the --php-ini switch, because you may need to use your own php config that enables the mysql extension. On PHP 5.3.0+ that extension is enabled by default, so that is no longer necessary to use the config to enable it.

Then you can write your export script like any normal PHP script.

<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());

$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");

$result || die(mysql_error());

while($row = mysql_fetch_row($result)) {
  $comma = false;
  foreach ($row as $item) {
    #make it comma separated
    if ($comma) {
      echo ',';
    } else {
      $comma = true;
    }
    #quiote the quiotes
    $quoted = str_replace("\"", "\"\"", $item);

    #quiote the string
    echo "\"$quoted\"";
  }
    echo "\n";
}
?>

The advantage of this method is, that it has no problems with varchar and text fields, that have text containing newlines. Those fields are correctly quioted and those newlines in them will be interpreted by the CSV reader as a part of the text, not record separators. That is something that is hard to correct afterwards with sed or so.

Using the solution posted by Tim, I created this bash script to facilitate the process (root password is requested, but you can modify the script easily to ask for any other user):

#!/bin/bash

if [ "$1" == "" ];then
    echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
    exit
fi

DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3

echo "MySQL password:"
stty -echo
read PASS
stty echo

mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME

It will create a file named: database.table.csv

I use SQLyog for this. You can download the free version from here.