我是这么做的:

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

Perl脚本(从别处截取)很好地将以制表符间隔的字段转换为CSV。

使用Tim Harding发布的解决方案，我创建了这个Bash脚本来促进这个过程(根密码是需要的，但你可以很容易地修改脚本来请求任何其他用户):

#!/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

它将创建一个名为database.table.csv的文件

这救了我几次。它又快又有效!

——批 打印结果，使用制表符作为列分隔符，每行在 新行。 ——raw禁用字符转义(\n， \t， \0和\)

例子:

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

为了完整起见，您可以转换为CSV(但要小心，因为制表符可能位于字段值内部—例如，文本字段)

文件。tsv >文件

如果您正在使用的机器上安装了PHP，则可以编写PHP脚本来完成该任务。它要求PHP安装已经安装了MySQL扩展。

你可以像这样从命令行调用PHP解释器:

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

我将包括——PHP -ini开关，因为您可能需要使用自己的PHP配置来启用MySQL扩展。在PHP 5.3.0+上，该扩展默认是启用的，因此不再需要使用配置来启用它。

然后，您可以像任何正常的PHP脚本一样编写导出脚本:

<?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;
        }

        # Quote the quotes
        $quoted = str_replace("\"", "\"\"", $item);

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

这种方法的优点是，它对包含换行文本的varchar和文本字段没有任何问题。这些字段被正确地引用，其中的换行符将被CSV阅读器解释为文本的一部分，而不是记录分隔符。这在sed之后是很难纠正的。

下面的Bash脚本适合我。它还可以选择获取所请求表的模式。

#!/bin/bash
#
# Export MySQL data to CSV
#https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#

# ANSI colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue='\033[0;34m'
red='\033[0;31m'
green='\033[0;32m' # '\e[1;32m' is too bright for white bg.
endColor='\033[0m'

#
# A colored message
#   params:
#     1: l_color - the color of the message
#     2: l_msg - the message to display
#
color_msg() {
  local l_color="$1"
  local l_msg="$2"
  echo -e "${l_color}$l_msg${endColor}"
}


#
# Error
#
# Show the given error message on standard error and exit
#
#   Parameters:
#     1: l_msg - the error message to display
#
error() {
  local l_msg="$1"
  # Use ANSI red for error
  color_msg $red "Error:" 1>&2
  color_msg $red "\t$l_msg" 1>&2
  usage
}

#
# Display usage
#
usage() {
  echo "usage: $0 [-h|--help]" 1>&2
  echo "               -o  | --output      csvdirectory"    1>&2
  echo "               -d  | --database    database"   1>&2
  echo "               -t  | --tables      tables"     1>&2
  echo "               -p  | --password    password"   1>&2
  echo "               -u  | --user        user"       1>&2
  echo "               -hs | --host        host"       1>&2
  echo "               -gs | --get-schema"             1>&2
  echo "" 1>&2
  echo "     output: output CSV directory to export MySQL data into" 1>&2
  echo "" 1>&2
  echo "         user: MySQL user" 1>&2
  echo "     password: MySQL password" 1>&2
  echo "" 1>&2
  echo "     database: target database" 1>&2
  echo "       tables: tables to export" 1>&2
  echo "         host: host of target database" 1>&2
  echo "" 1>&2
  echo "  -h|--help: show help" 1>&2
  exit 1
}

#
# show help
#
help() {
  echo "$0 Help" 1>&2
  echo "===========" 1>&2
  echo "$0 exports a CSV file from a MySQL database optionally limiting to a list of tables" 1>&2
  echo "   example: $0 --database=cms --user=scott --password=tiger  --tables=person --output person.csv" 1>&2
  echo "" 1>&2
  usage
}

domysql() {
  mysql --host $host -u$user --password=$password $database
}

getcolumns() {
  local l_table="$1"
  echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}

host="localhost"
mysqlfiles="/var/lib/mysql-files/"

# Parse command line options
while true; do
  #echo "option $1"
  case "$1" in
    # Options without arguments
    -h|--help) usage;;
    -d|--database)     database="$2" ; shift ;;
    -t|--tables)       tables="$2" ; shift ;;
    -o|--output)       csvoutput="$2" ; shift ;;
    -u|--user)         user="$2" ; shift ;;
    -hs|--host)        host="$2" ; shift ;;
    -p|--password)     password="$2" ; shift ;;
    -gs|--get-schema)  option="getschema";;
    (--) shift; break;;
    (-*) echo "$0: error - unrecognized option $1" 1>&2; usage;;
    (*) break;;
  esac
  shift
done

# Checks
if [ "$csvoutput" == "" ]
then
  error "output CSV directory is not set"
fi
if [ "$database" == "" ]
then
  error "MySQL database is not set"
fi
if [ "$user" == "" ]
then
  error "MySQL user is not set"
fi
if [ "$password" == "" ]
then
  error "MySQL password is not set"
fi

color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi

case $option in
  getschema)
   rm $csvoutput$database.schema
   for table in $tables
   do
     color_msg $blue "getting schema for $table"
     echo -n "$table:" >> $csvoutput$database.schema
     getcolumns $table >> $csvoutput$database.schema
   done
   ;;
  *)
for table in $tables
do
  color_msg $blue "exporting table $table"
  cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
  if [  "$cols" = "" ]
  then
    cols=$(getcolumns $table)
  fi
  ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
  scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
  (echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
  rm $csvoutput$table.csv.raw
done
  ;;
esac

您可以有一个使用CSV引擎的MySQL表。

然后你的硬盘上就会有一个CSV格式的文件，你可以直接复制而不用处理它。