我该如何以CSV格式输出MySQL查询结果？

下面这个Bash脚本对我很有效。它还可以选择性地获取请求表的模式。

#!/bin/bash
#
# Export MySQL data to CSV
#https://dev59.com/tHRC5IYBdhLWcg3wROtQ
#

# 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

这个方案避免了向文件写入输出，只需要安装expat，正确转义值，并且在null值的情况下输出空字符串(而不是字面意义上的NULL）。

您可以告诉MySQL以XML格式输出结果（使用--xml标志），然后将结果通过下面的C程序进行处理。

这应该也是最快的处理方式之一。

// mysql-xml-to-csv.c

#include <assert.h>
#include <ctype.h>
#include <err.h>
#include <expat.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

/*
    Example of MySQL XML output:

    <?xml version="1.0"?>

    <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" statement="SELECT id as IdNum, lastName, firstName FROM User">
        <row>
            <field name="IdNum">100040</field>
            <field name="lastName" xsi:nil="true"/>
            <field name="firsttName">Cher</field>
        </row>
    </resultset>
*/

#define BUFFER_SIZE     (1 << 16)

// These accumulate the first row column names and values until first row is entirely read (unless the "-N" flag is given)
static XML_Char **column_names;
static size_t num_column_names;
static XML_Char **first_row_values;
static size_t num_first_row_values;

// This accumulates one column's value
static XML_Char *elem_text;                     // note: not nul-terminated
static size_t elem_text_len;

// Flags
static int first_column;
static int reading_value;

// Expat callback functions
static void handle_elem_start(void *data, const XML_Char *el, const XML_Char **attr);
static void handle_elem_text(void *userData, const XML_Char *s, int len);
static void handle_elem_end(void *data, const XML_Char *el);

// Helper functions
static void output_csv_row(XML_Char **values, size_t num);
static void output_csv_text(const char *s, size_t len);
static void add_string(XML_Char ***arrayp, size_t *lengthp, const XML_Char *string, size_t len);
static void add_chars(XML_Char **strp, size_t *lenp, const XML_Char *string, size_t nchars);
static size_t xml_strlen(const XML_Char *string);
static void free_strings(XML_Char ***arrayp, size_t *lengthp);
static void usage(void);

int
main(int argc, char **argv)
{
    char buf[BUFFER_SIZE];
    int want_column_names = 1;
    XML_Parser p;
    FILE *fp;
    size_t r;
    int i;

    // Parse command line
    while ((i = getopt(argc, argv, "hN")) != -1) {
        switch (i) {
        case 'N':
            want_column_names = 0;
            break;
        case 'h':
            usage();
            exit(0);
        case '?':
        default:
            usage();
            exit(1);
        }
    }
    argv += optind;
    argc -= optind;
    switch (argc) {
    case 0:
        fp = stdin;
        break;
    case 1:
        if ((fp = fopen(argv[0], "r")) == NULL)
            err(1, "%s", argv[0]);
        break;
    default:
        usage();
        exit(1);
    }

    // Initialize arrays for column names and first row values
    if (want_column_names) {
        if ((column_names = malloc(10 * sizeof(*column_names))) == NULL)
            err(1, "malloc");
        if ((first_row_values = malloc(10 * sizeof(*first_row_values))) == NULL)
            err(1, "malloc");
    }

    // Initialize parser
    if ((p = XML_ParserCreate(NULL)) == NULL)
        errx(1, "can't initialize parser");
    XML_SetElementHandler(p, handle_elem_start, handle_elem_end);
    XML_SetCharacterDataHandler(p, handle_elem_text);

    // Process file
    while (1) {
        if ((r = fread(buf, 1, sizeof(buf), fp)) == 0 && ferror(fp))
            errx(1, "error reading input");
        if (XML_Parse(p, buf, r, r == 0) == XML_STATUS_ERROR)
            errx(1, "line %u: %s", (unsigned int)XML_GetCurrentLineNumber(p), XML_ErrorString(XML_GetErrorCode(p)));
        if (r == 0)
            break;
    }

    // Clean up
    XML_ParserFree(p);
    fclose(fp);

    // Done
    return 0;
}

static void
handle_elem_start(void *data, const XML_Char *name, const XML_Char **attr)
{
    if (strcmp(name, "row") == 0)
        first_column = 1;
    else if (strcmp(name, "field") == 0) {
        if (column_names != NULL) {
            while (*attr != NULL && strcmp(*attr, "name") != 0)
                attr += 2;
            if (*attr == NULL)
                errx(1, "\"field\" element is missing \"name\" attribute");
            add_string(&column_names, &num_column_names, attr[1], xml_strlen(attr[1]));
        } else {
            if (!first_column)
                putchar(',');
            putchar('"');
        }
        reading_value = 1;
    }
}

static void
handle_elem_text(void *userData, const XML_Char *s, int len)
{
    if (!reading_value)
        return;
    if (column_names != NULL)
        add_chars(&elem_text, &elem_text_len, s, len);
    else
        output_csv_text(s, len);
}

static void
handle_elem_end(void *data, const XML_Char *name)
{
    if (strcmp(name, "row") == 0) {
        if (column_names != NULL) {
            output_csv_row(column_names, num_column_names);
            output_csv_row(first_row_values, num_first_row_values);
            free_strings(&column_names, &num_column_names);
            free_strings(&first_row_values, &num_first_row_values);
        } else
            putchar('\n');
    } else if (strcmp(name, "field") == 0) {
        if (column_names != NULL) {
            add_string(&first_row_values, &num_first_row_values, elem_text, elem_text_len);
            free(elem_text);
            elem_text = NULL;
            elem_text_len = 0;
        } else
            putchar('"');
        first_column = 0;
        reading_value = 0;
    }
}

static void
output_csv_row(XML_Char **values, size_t num_columns)
{
    int i;

    for (i = 0; i < num_columns; i++) {
        if (i > 0)
            putchar(',');
        putchar('"');
        output_csv_text(values[i], xml_strlen(values[i]));
        putchar('"');
    }
    putchar('\n');
}

static void
output_csv_text(const XML_Char *s, size_t len)
{
    while (len-- > 0) {
        if (*s == '"')
            putchar('"');
        putchar(*s);
        s++;
    }
}

static void
add_string(XML_Char ***arrayp, size_t *lengthp, const XML_Char *string, size_t nchars)
{
    char **new_array;

    if ((new_array = realloc(*arrayp, (*lengthp + 1) * sizeof(**arrayp))) == NULL)
        err(1, "malloc");
    *arrayp = new_array;
    if (((*arrayp)[*lengthp] = malloc((nchars + 1) * sizeof(XML_Char))) == NULL)
        err(1, "malloc");
    memcpy((*arrayp)[*lengthp], string, nchars * sizeof(XML_Char));
    (*arrayp)[*lengthp][nchars] = (XML_Char)0;
    (*lengthp)++;
}

static void
add_chars(XML_Char **strp, size_t *lenp, const XML_Char *string, size_t nchars)
{
    XML_Char *new_array;

    if ((new_array = realloc(*strp, (*lenp + nchars) * sizeof(XML_Char))) == NULL)
        err(1, "malloc");
    *strp = new_array;
    memcpy(*strp + *lenp, string, nchars * sizeof(XML_Char));
    *lenp += nchars;
}

static size_t
xml_strlen(const XML_Char *string)
{
    size_t len;

    len = 0;
    while (string[len] != (XML_Char)0)
        len++;
    return len;
}

static void
free_strings(char ***arrayp, size_t *lengthp)
{
    while (*lengthp > 0)
        free((*arrayp)[--*lengthp]);
    free(*arrayp);
    *arrayp = NULL;
}

static void
usage(void)
{
    fprintf(stderr, "Usage: mysql-xml-to-csv [options] [file.xml]\n");
    fprintf(stderr, "Options:\n");
    fprintf(stderr, "  -N\tDo not output column names as the first row\n");
    fprintf(stderr, "  -h\tShow this usage info\n");
}

对于那些不经常使用C语言的人，您可以通过运行以下命令来构建此代码（假设您已安装了expat库）：

gcc mysql-xml-to-csv.c -lexpat -o mysql-xml-to-csv

使用 openSUSE 15.2 和 gcc 7.5.0 进行测试。

更新：现在作为 GitHub 上的开源项目 可用。

站在 Chris Johnson 的肩膀上，我使用自定义方言对2016年2月的答案进行了扩展，内容涉及编程。
这个 shell 管道工具不需要连接到你的数据库，可以处理输入中的随机逗号和引号，并且在 Python 2 和 Python 3 中都能很好地工作！

#!/usr/bin/env python
import csv
import sys

# fields are separated by tabs; double-quotes may occur anywhere
csv.register_dialect("mysql", delimiter="\t", quoting=csv.QUOTE_NONE)
tab_in = csv.reader(sys.stdin, dialect="mysql")
comma_out = csv.writer(sys.stdout, dialect=csv.excel)
for row in tab_in:
    # print("row: {}".format(row))
    comma_out.writerow(row)

使用该打印语句来验证输入是否被正确解析 :)
一个重要的警告：回车符号和^M（也称为控制字符M）在Linux术语中为\r。尽管批处理模式下MySQL输出可以正确转义嵌入的换行符，因此每一行都是由Linux换行符\n定义的，但MySQL不会给列数据加上任何引号。如果一个数据项有一个嵌入的回车符号，csv.reader将拒绝输入并抛出以下异常：

new-line character seen in unquoted field -
do you need to open the file in universal-newline mode?

请不要@我说我应该使用通用文件模式通过重新打开sys.stdin.fileno以'rU'模式。 我试过了，它会导致嵌入的\r字符被视为记录结束标记，因此一个输入记录会不正确地转换为许多不完整的输出记录。
我没有找到Python解决Python csv.reader模块这个限制的方法。 我认为根本原因是csv.reader实现/限制在他们的文档中指出，csv.reader:

The reader is hard-coded to recognise either '\r' or '\n' as end-of-line,
and ignores lineterminator.

我能提供的一个不太令人满意的解决方案是，在 Python 的 csv.reader 查看数据之前，将每个 \r 字符更改为两个字符序列 '\n'。我使用了 sed 命令。以下是一个包含 MySQL select 和上述 Python 脚本的管道示例：

mysql -u user db --execute="select * from table where id=12345" \
  | sed -e 's/\r/\\n/g' \
  | mysqlTsvToCsv.py

经过一段时间的挣扎，我认为Python不是正确的解决方案。如果你能接受Perl，我认为artfulrobot提供的单行脚本可能是最有效和最简单的解决方案。

对于那些想以CSV格式下载查询结果但没有服务器文件权限的人。

首先，这不是一个Linux命令。以下是步骤：

1. 创建一个视图来进行查询。例如：（Create VIEW v as (Select * from user where status = 0)）
2. 该视图将在您的数据库的view部分中创建。
3. 现在将视图导出为CSV。
4. 如果您需要将表列作为CSV的标题，请将Export method:设置为Custom - display all possible options并勾选Put columns names in the first row。

如果您在尝试导出文件时遇到以下错误：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

并且您无法解决此错误，您可以通过运行此Python脚本来解决问题。

import mysql.connector
import csv

con = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="Your Password"
)

cur = con.cursor()

cur.execute("USE DbName")
cur.execute("""
select col1,col2 from table
where <cond>
""")

with open('Filename.csv',mode='w') as data:
    fieldnames=["Field1","Field2"]
    writer=csv.DictWriter(data,fieldnames=fieldnames)
    writer.writeheader()
    for i in cur:
        writer.writerow({'Field1':i[0],'Field2':i[1]})

如果您使用的计算机上安装了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和text字段的问题，这些字段包含有换行符的文本。这些字段被正确地引用，并且其中的换行符将被CSV读取器解释为文本的一部分，而不是记录分隔符。这是一件事情，后来很难用sed或其他方式进行纠正。

尝试这段代码：

SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

更多信息请参考：http://dev.mysql.com/doc/refman/5.1/en/select-into.html

这个解决方案将SQL查询放在一个heredoc中，并通过过滤器传输输出：

文件 query.sh

#!/bin/bash

mysql --defaults-group-suffix=[DATABASE_NAME] --batch << EOF | python query.py
SELECT [FIELDS]
FROM [TABLE]
EOF

这个版本的Python过滤器可以在不需要使用csv模块的情况下工作：

文件 query.sh

import sys

for line in sys.stdin:
    print(','.join(["\"" + str(element) + "\"" for element in line.rstrip('\n').split('\t')]))

这个版本的Python过滤器使用CSV模块，涉及稍微多一点的代码，但可以说更加清晰：

文件query.sh

import csv, sys

csv_reader = csv.reader(sys.stdin, delimiter='\t')
csv_writer = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)

for line in csv_reader:
    csv_writer.writerow(line)

或者您可以使用Pandas：

文件query.py

import csv, sys
import pandas as pd

df = pd.read_csv(sys.stdin, sep='\t')
df.to_csv(sys.stdout, index=False, quoting=csv.QUOTE_NONNUMERIC)

非常简单的方法：

• 只需复制屏幕上的标准MySQL输出
• 然后打开Libreoffice Calc（或Excel）
• 粘贴
• 在Windows提示符中将竖线字符|设置为字段分隔符

如果您在生产环境或任何其他无法访问文件系统的服务器上，可以使用这个简单的技巧和一点手动操作来获取您想要的内容。

步骤1. 只需将所有列都包装在CONCAT下，并使用MySQL提供的as CSVFormat选项来获取逗号分隔的结果（或使用任何分隔符）。以下是一个示例：

SELECT
    CONCAT(u.id,
            ',',
            given,
            ',',
            family,
            ',',
            email,
            ',',
            phone,
            ',',
            ua.street_number,
            ',',
            ua.route,
            ',',
            ua.locality,
            ',',
            ua.state,
            ',',
            ua.country,
            ',',
            ua.latitude,
            ',',
            ua.longitude) AS CSVFormat
FROM
    table1 u
        LEFT JOIN
    table2 ua ON u.address_id = ua.id
WHERE
    role_policy = 31 and is_active = 1;

步骤2. 从终端复制结果到文件中，并使用任何文本编辑器清除所有管道字符（这些字符形成了结果的布局）。

步骤3. 另存为 .csv 文件，就完成了。