Practical MySQL & MariaDB

如何将 CSV 文件导入 MySQL：使用 LOAD DATA INFILE 的完整指南及故障排除

SQL 基礎
目次

1. 将 CSV 文件导入 MySQL 的介绍

将 CSV 文件导入 MySQL 是一种强大的方式，可简化数据管理并消除手动录入的需求。例如，当您需要批量导入从多个数据源收集的信息到数据库，或自动处理来自外部系统的数据时，CSV 导入就显得非常有用。

CSV 导入的常见使用场景

  • 处理大量数据：无需手动输入成千上万的记录，您可以使用 CSV 导入快速处理它们。
  • 数据迁移：将其他系统导出的数据导入 MySQL 时非常方便。
  • 定期数据更新：需要定期更新数据的系统可以使用 CSV 文件自动化此过程。

2. 前置条件

在将 CSV 文件导入 MySQL 之前，需要进行若干设置和准备工作。这些可以确保导入过程顺畅且无错误。

2.1 必要环境

  • MySQL 服务器安装：假设 MySQL 已正确安装。确保 MySQL 在您的环境中（无论是 Windows、macOS 还是 Linux）正常运行。
  • 验证所需权限：要导入 CSV 文件，MySQL 用户必须拥有相应的权限。特别是需要 LOAD DATA INFILE 的执行权限。如果没有此权限，导入可能会被拒绝。

2.2 CSV 文件格式

  • 分隔符：CSV 文件通常使用逗号分隔，但在某些情况下可能使用制表符或分号。请事先检查使用的分隔符。
  • 检查编码：如果 CSV 文件使用了不同的字符编码（例如 UTF-8 或 Shift-JIS），在导入时可能会出现字符乱码。请提前确认文件编码，并在必要时进行转换。

3. 使用 LOAD DATA INFILE 命令导入 CSV

导入 CSV 文件到 MySQL 最常用的方法是使用 LOAD DATA INFILE 命令。该命令可以轻松将大量数据加载到数据库中。

3.1 基本语法

以下是用于将 CSV 文件导入 MySQL 的基本语法。

LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
  • FIELDS TERMINATED BY：指定字段分隔符。通常使用逗号。
  • LINES TERMINATED BY：指定行分隔符。通常使用 \n（换行符）。

3.2 示例执行

下面的示例将名为 user_data.csv 的文件导入到 users 表中。

LOAD DATA INFILE '/path/to/user_data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
  • IGNORE 1 LINES：跳过 CSV 文件的第一行（标题行）。当第一行包含列名时，此选项非常有用。

3.3 重要提示：文件路径和权限

指定文件路径时，文件必须位于 MySQL 服务器可访问的目录中。如果服务器无法访问该文件，请使用 LOAD DATA LOCAL INFILE 从客户端加载数据。

4. 常见问题解决方案

以下是 CSV 文件导入过程中常见问题的解决方案。

4.1 文件路径问题

如果文件路径未正确指定，您可能会看到类似 The MySQL server is not permitted to read from the file 的错误信息。此时，请确认服务器端的文件路径是否正确。您也可以使用 LOAD DATA LOCAL INFILE 选项加载本地文件。

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

4.2 字符编码问题

如果文件使用了不同的字符编码保存，导入时可能会出现字符乱码。为避免此问题，请事先检查文件编码，并在 MySQL 中指定相应的字符集。

SET NAMES 'utf8mb4';

5. 实践示例：CSV 导入流程

在此，我们解释将实际 CSV 文件导入 MySQL 的逐步过程。

5.1 准备 CSV 文件

创建一个 CSV 文件（data.csv），内容如下。

id,name,age
1,Taro Yamada,28
2,Hanako Sato,34
3,Ichiro Tanaka,45

5.2 执行命令

运行以下命令，将创建的 CSV 文件导入 users 表。

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

此命令从 CSV 文件的第二行开始导入数据到 users 表。

6. 高级：错误处理与重复数据防止

本节解释在 CSV 导入过程中如何处理错误以及如何处理重复数据。

6.1 处理重复数据

如果具有相同键的数据已存在，可以使用 REPLACE 选项用新数据覆盖重复行。

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
REPLACE;

6.2 错误处理

如果在导入过程中出现错误但希望继续处理，可使用 IGNORE 选项。该选项会跳过有问题的行并导入其余有效数据。

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
IGNORE;

7. 结论

将 CSV 文件导入 MySQL 是高效处理大型数据集和在系统之间迁移数据的强大工具。通过了解基本流程并实施适当的错误处理和重复防止策略，您可以实现稳定可靠的数据导入。

在后续文章中，我们还将详细解释如何从 MySQL 数据库导出数据。继续提升您的数据管理技能。