如何将MySQL查询结果输出为CSV格式

技术背景

在数据库开发和数据处理中，经常需要将MySQL查询结果以CSV格式输出。CSV（Comma-Separated Values）是一种常见的文本文件格式，便于数据的交换和处理，可被多种软件（如Excel、数据分析工具等）读取。

实现步骤

使用SELECT ... INTO OUTFILE语句

在MySQL中，可以使用SELECT ... INTO OUTFILE语句将查询结果输出到一个文件中。示例代码如下：

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';

在较新版本的MySQL中，语法可能需要调整为：

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';

需要注意的是，该文件将保存在运行MySQL的服务器上，并且MySQL进程运行的用户必须有写入该目录的权限，否则命令将失败。

使用命令行工具和管道

可以使用mysql命令结合sed、tr等工具将查询结果转换为CSV格式。

生成TSV并转换为CSV

mysql -h <hostname> -P <port> --database=<db_name> -u <username> -p \
  -e <your SQL query> \
  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' \
  > <output_file_name>.csv

处理包含特殊字符的输出

如果输出可能包含换行符和其他特殊字符，需要在sed之前添加以下处理：

| tr -d '\015' \
| sed 's/\\n/ /g;s/\\\\/\\/g;s/"/""/g' \

使用脚本语言

Python脚本

import csv
import mysql.connector

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
    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) {
            if ($comma) {
                echo ',';
            } else {
                $comma = true;
            }
            $quoted = str_replace("\"", "\"\"", $item);
            echo "\"$quoted\"";
        }
        echo "\n";
    }
?>

使用工具

MySQL Workbench

MySQL Workbench可以将记录集导出为CSV，并且能很好地处理字段中的逗号。

mycli

mycli是mysql-client的替代工具，支持使用--csv标志直接输出CSV格式。

mycli db_name --csv -e "select * from flowers" > flowers.csv

核心代码

Python实现数据导出

import csv
import mysql.connector

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="USERNAME",
    db="DATABASE_NAME",
    port=9999
)

# 定义导出函数
def export_table(connection, table_name, output_filename):
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM " + table_name)

    # 获取表头
    header = [descriptor[0] for descriptor in cursor.description]

    with open(output_filename, 'w') as csvfile:
        csv_writer = csv.writer(csvfile, dialect='excel')
        csv_writer.writerow(header)
        for row in cursor:
            csv_writer.writerow(row)

# 导出多个表
for table_name in ['table1', 'table2']:
    export_table(db, table_name, table_name + '.csv')

# 关闭数据库连接
db.close()

C语言实现XML转CSV

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

#define BUFFER_SIZE     (1 << 16)

// 全局变量
static XML_Char **column_names;
static size_t num_column_names;
static XML_Char **first_row_values;
static size_t num_first_row_values;
static XML_Char *elem_text;
static size_t elem_text_len;
static int first_column;
static int reading_value;

// Expat回调函数
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);

// 辅助函数
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;

    // 解析命令行参数
    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);
    }

    // 初始化数组
    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");
    }

    // 初始化解析器
    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);

    // 处理文件
    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;
    }

    // 清理资源
    XML_ParserFree(p);
    fclose(fp);

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

// 输出CSV行
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');
}

// 输出CSV文本
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;
}

// 计算XML字符串长度
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");
}

编译命令：

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

最佳实践

• 处理特殊字符：在生成CSV文件时，要特别注意处理字段中的逗号、引号、换行符等特殊字符，避免破坏CSV文件的格式。可以使用适当的转义方法，如将双引号替换为两个双引号。
• 权限管理：使用SELECT ... INTO OUTFILE时，要确保MySQL进程有写入目标目录的权限。同时，在临时修改文件权限后，要及时恢复默认权限，以保证系统安全。
• 脚本优化：使用脚本语言时，可以考虑使用上下文管理器（如Python的with语句）来确保资源的正确释放，避免资源泄漏。

常见问题

--secure-file-priv错误

如果遇到ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement错误，可以尝试以下解决方法：

• 确认导出路径是否在--secure-file-priv指定的目录内。
• 可以使用Python等脚本语言直接从数据库中读取数据并写入CSV文件，避免使用INTO OUTFILE。

特殊字符处理问题

当字段中包含双引号、换行符等特殊字符时，可能会导致CSV文件格式混乱。可以在导出时对这些特殊字符进行处理，如使用sed命令替换双引号、使用tr命令去除换行符等。

文件下载问题

如果需要将服务器上的CSV文件下载到本地，可以使用scp命令。但在操作前，需要确保服务器的SSH服务正常，并且有足够的权限访问目标文件。同时，要注意及时恢复文件的权限，以保证系统安全。