MySQL - 将表导出为 CSV 文件

MySQL：将数据导出到 CSV 文件

将数据导出到逗号分隔值（CSV）文件是数据分析、报表、备份或将数据迁移到其他系统的常见需求。MySQL 提供了几种方法来实现此目的，每种方法都有其自己的用例和安全考虑。

本教程涵盖两种主要方法：服务器端的 SELECT ... INTO OUTFILE 命令和更灵活的客户端脚本方法。

方法 1：使用 `SELECT ... INTO OUTFILE` 进行服务器端导出

此 SQL 命令指示 MySQL 服务器将 SELECT 查询的结果直接写入服务器文件系统上的文件。它速度极快，但带有重要的安全限制。

关键安全前提：`secure_file_priv`

出于安全原因，SELECT ... INTO OUTFILE 只能写入 secure_file_priv 系统变量配置的特定目录。在尝试导出之前，您必须检查其值：

SHOW VARIABLES LIKE 'secure_file_priv';

结果将是以下之一：

特定路径（例如，/var/lib/mysql-files/）： 这是最常见和最安全的配置。您只能将文件写入此目录。
空字符串： 这是一个安全风险。这意味着 MySQL 可以将文件写入服务器文件系统上 MySQL 进程用户具有权限的任何位置。强烈不建议使用此配置。
NULL： 这表示文件导入/导出操作已禁用。您不能使用 SELECT ... INTO OUTFILE。

在许多托管云数据库服务（如 AWS RDS 或 Google Cloud SQL）上，secure_file_priv 默认设置为 NULL，这使得此方法无法使用。在这种情况下，您必须使用客户端方法。

语法

SELECT column1, column2, ... FROM your_table
INTO OUTFILE '/path/to/your/secure/directory/filename.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

示例：导出客户数据

假设 secure_file_priv 设置为 C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/。我们将使用以下 customers 表：

CREATE TABLE customers(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NULL,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2)
);

INSERT INTO customers VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, NULL, 2000.00);

以下查询导出数据：

SELECT * FROM customers
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customers_export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

错误处理：如果目标文件已存在，该命令将失败。这是一项安全功能，可防止意外覆盖。您必须在重新运行命令之前从服务器的文件系统删除该文件。

高级技术：导出带标题的数据

INTO OUTFILE 不原生支持列标题。一种常见的变通方法是使用 UNION ALL 创建标题行：

(SELECT 'ID', 'NAME', 'AGE', 'ADDRESS', 'SALARY')
UNION ALL
(SELECT ID, NAME, AGE, ADDRESS, SALARY FROM customers)
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customers_with_headers.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

方法 2：客户端脚本（推荐且更灵活）

更现代和可移植的方法是使用 Python 或 Node.js 等语言编写脚本。此脚本连接到数据库，获取数据，然后将其写入本地 CSV 文件。此方法在任何地方都适用，包括云数据库，并为您提供了对输出格式的完全控制。

Python 示例（使用 `csv` 和 `mysql-connector-python`）

此方法健壮、安全，是应用程序驱动数据导出的行业标准。

import mysql.connector
import csv

DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}
OUTPUT_FILE = 'customers_export_from_python.csv'

def export_customers_to_csv():
    try:
        with mysql.connector.connect(**DB_CONFIG) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT ID, NAME, AGE, ADDRESS, SALARY FROM customers;")
                rows = cursor.fetchall()

                if not rows:
                    print("没有数据可导出。")
                    return

                # 从列表中第一个字典的键获取标题
                headers = rows[0].keys()

                with open(OUTPUT_FILE, 'w', newline='', encoding='utf-8') as csvfile:
                    writer = csv.DictWriter(csvfile, fieldnames=headers)
                    writer.writeheader()
                    writer.writerows(rows)

                print(f"数据已成功导出到 {OUTPUT_FILE}")

    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
    except IOError as e:
        print(f"文件错误: {e}")

export_customers_to_csv()

Node.js 示例（使用 `mysql2` 和 `fast-csv`）

对于 Node.js，像 fast-csv 这样的库可以高效地流式传输大型数据集。

// First, install dependencies: npm install mysql2 fast-csv
const mysql = require('mysql2');
const fs = require('fs');
const { format, writeToStream } = require('@fast-csv/format');

const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'your_database'
};

async function exportCustomers() {
    const connection = mysql.createConnection(dbConfig);
    const outputPath = 'customers_export_from_node.csv';
    const writeStream = fs.createWriteStream(outputPath);

    try {
        console.log('正在连接到数据库...');
        const queryStream = connection.query('SELECT * FROM customers').stream();
        const csvStream = format({ headers: true });

        console.log(`正在将数据流式传输到 ${outputPath}...`);
        queryStream.pipe(csvStream).pipe(writeStream);

        return new Promise((resolve, reject) => {
            writeStream.on('finish', () => {
                console.log('导出成功完成。');
                resolve();
            });
            writeStream.on('error', reject);
            queryStream.on('error', reject);
        });

    } catch (error) {
        console.error('发生错误:', error);
    } finally {
        connection.end();
    }
}

exportCustomers();

结论：选择哪种方法？

标准	`SELECT ... INTO OUTFILE`	客户端脚本
速度	非常快（服务器端操作）	较慢（数据通过网络传输）
灵活性	低（格式有限，固定路径）	高（完全控制格式、逻辑和输出位置）
安全性	如果配置不当，风险很高	安全（标准应用程序数据库访问）
可移植性	低（在许多云平台上失败）	高（您的应用程序可以连接到数据库的任何地方都适用）
最适合	数据库管理员一次性快速导出，需要服务器访问权限。	自动化报表、应用程序功能以及从受限/云环境中导出。