MySQL 导入数据之 `LOAD DATA INFILE` 用法整理

在数据库管理中，数据导入是一项常见的操作。MySQL 提供了 LOAD DATA INFILE 语句，它能够高效地将文本文件中的数据导入到数据库表中。本文将详细介绍 LOAD DATA INFILE 的用法、常见实践、最佳实践以及示例，帮助读者全面掌握这一功能。

目录#

  1. 基本语法
  2. 常见实践
    • 数据文件格式要求
    • 字段和行的分隔符设置
  3. 最佳实践
    • 权限设置
    • 数据验证与预处理
    • 批量导入优化
  4. 示例用法
    • 简单文本文件导入
    • 处理复杂格式文件
  5. 参考资料

1. 基本语法#

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name [, partition_name] ...)] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, col_name_or_user_var] ...)] [SET col_name = expr [, col_name = expr] ...]

  • LOW_PRIORITY/CONCURRENT：LOW_PRIORITY 表示如果表被其他线程使用，导入操作将延迟执行；CONCURRENT 用于 MyISAM、MEMORY 和 ARCHIVE 表，允许多个线程同时读取表（但写入操作可能受限制）。
  • LOCAL：表示从客户端主机读取文件（如果省略，默认从服务器主机读取）。
  • REPLACE/IGNORE：REPLACE 会替换已存在的行（根据唯一键判断）；IGNORE 会跳过有重复唯一键的行。
  • FIELDS 和 LINES 相关子句：用于指定字段和行的分隔、包围、转义等格式。

2. 常见实践#

数据文件格式要求#

  • 数据文件通常是纯文本格式（如 .txt、.csv 等）。
  • 每一行数据对应表中的一条记录（除非有特殊的行处理设置）。

字段和行的分隔符设置#

  • 字段分隔符（FIELDS TERMINATED BY）：例如，如果数据文件中字段用逗号分隔，可设置 FIELDS TERMINATED BY ','。
  • 行分隔符（LINES TERMINATED BY）：常见的行分隔符如 '\n'（换行符）、'\r\n'（Windows 风格换行）。

3. 最佳实践#

权限设置#

  • 确保 MySQL 用户有 FILE 权限（用于从服务器读取文件，若使用 LOCAL 则客户端也需相应权限）。可以通过 GRANT FILE ON *.* TO 'user'@'host'; 来授予权限。

数据验证与预处理#

  • 在导入前，对数据文件进行简单验证，如检查字段数量是否与表结构匹配。
  • 对于可能存在脏数据（如格式错误、超出字段类型范围等），可以先进行预处理（如使用文本编辑工具或脚本清洗数据）。

批量导入优化#

  • 对于大文件，可以分批次导入（通过 IGNORE number LINES 跳过已导入的行）。
  • 暂时禁用索引（导入完成后再重新创建）可以加快导入速度（对于 MyISAM 表，可使用 ALTER TABLE tbl_name DISABLE KEYS; 和 ALTER TABLE tbl_name ENABLE KEYS;）。

4. 示例用法#

简单文本文件导入#

假设我们有一个 employees.txt 文件，内容如下（字段用逗号分隔，每行表示一个员工记录，包含 id、name、age 字段）：

1,John Doe,30
2,Jane Smith,25

表结构为：

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    age INT
);

导入语句：

LOAD DATA INFILE '/path/to/employees.txt'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

处理复杂格式文件#

如果文件中的字符串字段用双引号包围，且字段分隔符为逗号，行分隔符为 \r\n（Windows 风格），例如 customers.csv 文件：

"1","Alice Johnson","New York",25
"2","Bob Williams","Los Angeles",35

表结构：

CREATE TABLE customers (
    id INT,
    name VARCHAR(50),
    city VARCHAR(50),
    age INT
);

导入语句：

LOAD DATA INFILE '/path/to/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

5. 参考资料#

通过以上对 LOAD DATA INFILE 的详细介绍，读者可以根据实际需求灵活运用该语句进行高效的数据导入操作，同时遵循最佳实践来确保导入过程的顺利和数据的准确性。

2026-03