MySQL - 将 CSV 文件导入数据库

MySQL：从 CSV 文件导入数据

从外部文件批量加载数据是数据管理中的一项基本任务。MySQL 提供了一个高效的语句 LOAD DATA INFILE（载入数据文件），专门用于将数据从文本文件（包括常见的逗号分隔值 CSV 格式）直接导入到数据库表中。

导入的先决条件

在导入 CSV 文件之前，请确保满足以下条件：

• 目标表：您的数据库中必须有一个现有表，其结构与 CSV 文件中的数据匹配。
• CSV 文件：数据文件必须对 MySQL 服务器可访问。
• 文件权限：执行该语句的 MySQL 用户账户必须拥有 FILE 和 INSERT 权限。
• 列顺序：CSV 文件中的列应理想地与目标表中的列顺序相同。如果不同，您必须在 LOAD DATA 语句中指定列顺序。

LOAD DATA INFILE 语句

此命令从文本文件中读取行并以非常高的速度将其插入到表中。

语法

LOAD DATA INFILE 'path/to/your/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' -- 或 '\r\n' 用于 Windows 风格的行尾
IGNORE 1 ROWS; -- 如果 CSV 包含标题行，请使用此项

• FIELDS TERMINATED BY：指定值之间的分隔符（例如逗号）。
• ENCLOSED BY：指定用于引用字段的字符，当您的数据包含终止符字符时（例如文本字段中的逗号）这很有用。
• LINES TERMINATED BY：指定行结束序列。
• IGNORE N ROWS：跳过文件的前 N 行，通常用于忽略标题行。

关键安全考虑：secure_file_priv

现代 MySQL 安装有一个名为 secure_file_priv 的安全特性。此系统变量将文件操作（如 LOAD DATA INFILE）限制在特定目录中。如果您尝试从不同位置加载文件，将会收到错误。

要检查您的 secure_file_priv 设置，请运行：SHOW VARIABLES LIKE 'secure_file_priv';

• 如果值为目录路径（例如，/var/lib/mysql-files/），您必须将 CSV 文件放在该目录中。
• 如果值为 NULL，则文件导入/导出操作被禁用。
• 如果值为空，则没有限制（不常见且安全性较低）。

一个替代方案是 LOAD DATA LOCAL INFILE，它从客户端机器读取文件。这需要服务器和客户端都配置为允许此操作（服务器上的 local_infile=1 和客户端连接中的一个标志），因为它具有安全隐患。

分步示例

让我们将新员工数据导入到我们的数据库中。

步骤 1：CSV 文件

假设您有一个名为 new_hires.csv 的文件，内容如下，包括一个标题行：

id,first_name,last_name,department,salary
101,John,Doe,Engineering,95000.00
102,Jane,Smith,Marketing,82000.00
103,Peter,Jones,Engineering,110000.00

步骤 2：创建目标表

创建一个与 CSV 文件结构匹配的表。

CREATE TABLE new_employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

步骤 3：加载数据

将 new_hires.csv 放在 secure_file_priv 指定的目录中。然后，运行 LOAD DATA 命令。

-- 假设 secure_file_priv 为 '/var/lib/mysql-files/'
LOAD DATA INFILE '/var/lib/mysql-files/new_hires.csv'
INTO TABLE new_employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 跳过标题行

步骤 4：验证导入

检查表内容以确认数据已成功导入。

SELECT * FROM new_employees;

id	first_name	last_name	department	salary
101	John	Doe	Engineering	95000.00
102	Jane	Smith	Marketing	82000.00
103	Peter	Jones	Engineering	110000.00

使用客户端程序导入 CSV 文件

自动化 CSV 导入通常通过脚本完成。以下是如何使用现代编程实践来实现这一目标。

语法概览

Node.js
Python
Java
PHP



Using `mysql2/promise`. Note the use of `local-infile` option in the connection for `LOAD DATA LOCAL INFILE`.

const connection = await mysql.createConnection({ ...config, localInfile: true });
const sql = "LOAD DATA LOCAL INFILE ..."; // 注意 'LOCAL' 关键字
await connection.query(sql);



Using `mysql-connector-python` with `allow_local_infile=True` in the connection arguments.

connection = mysql.connector.connect(..., allow_local_infile=True)
cursor = connection.cursor()
query = "LOAD DATA LOCAL INFILE ..." # 注意 'LOCAL' 关键字
cursor.execute(query)



Using JDBC with the `allowLoadLocalInfile=true` property in the connection URL.

String url = "jdbc:mysql://localhost:3306/db?allowLoadLocalInfile=true";
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
String sql = "LOAD DATA LOCAL INFILE ..."; // 注意 'LOCAL' 关键字
stmt.execute(sql);



Using PDO with the `PDO::MYSQL_ATTR_LOCAL_INFILE` attribute set to true.

$options = [ PDO::MYSQL_ATTR_LOCAL_INFILE => true, ... ];
$pdo = new PDO($dsn, $user, $pass, $options);
$sql = "LOAD DATA LOCAL INFILE ..."; // 注意 'LOCAL' 关键字
$pdo->exec($sql);

完整示例 (Python)

此 Python 脚本演示了如何使用 LOAD DATA LOCAL INFILE 从客户端机器上传 CSV 文件。这通常比在服务器上移动文件更方便。

import mysql.connector
import os

# --- 配置 ---
DB_CONFIG = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database',
    'allow_local_infile': True  # 对 LOAD DATA LOCAL 至关重要
}
CSV_FILE_PATH = 'new_hires.csv' # 客户端机器上的路径
TABLE_NAME = 'new_employees'

def import_csv_local():
    """将本地 CSV 文件上传到 MySQL 表中。"""
    # 在继续之前，确保 CSV 文件存在
    if not os.path.exists(CSV_FILE_PATH):
        print(f"Error: CSV file not found at '{CSV_FILE_PATH}'")
        return

    try:
        with mysql.connector.connect(**DB_CONFIG) as connection:
            with connection.cursor() as cursor:
                # 如果在 Windows 上，请使用正斜杠并转义反斜杠
                safe_file_path = CSV_FILE_PATH.replace('\\', '/')

                load_query = (
                    f"LOAD DATA LOCAL INFILE '{safe_file_path}' "
                    f"INTO TABLE {TABLE_NAME} "
                    "FIELDS TERMINATED BY ',' "
                    "LINES TERMINATED BY '\n' "
                    "IGNORE 1 ROWS;"
                )

                print("Executing data import...")
                cursor.execute(load_query)
                connection.commit()
                print(f"{cursor.rowcount} rows were successfully imported into '{TABLE_NAME}'.")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")

if __name__ == "__main__":
    # 您首先需要在本地创建 CSV 文件
    # with open(CSV_FILE_PATH, 'w') as f:
    #     f.write('id,first_name,last_name,department,salary\n')
    #     f.write('101,John,Doe,Engineering,95000.00\n')
    import_csv_local()