MySQL中LOAD怎么导入外部CSV格式数据

概述

MySQL提供了LOAD DATA INFILE语句，用于高效地将外部文件（如CSV格式）数据导入到数据库表中。这种方法比使用INSERT语句逐行插入要快得多，特别适合批量导入大量数据。

基本语法

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
[FIELDS
    TERMINATED BY 'delimiter'
    [OPTIONALLY] ENCLOSED BY 'char'
    ESCAPED BY 'char'
]
[LINES
    TERMINATED BY 'line_terminator'
]
[IGNORE number LINES]
[(column_list)]

参数说明

• LOCAL：指定从客户端主机读取文件，而非服务器
• file_path：CSV文件的路径（服务器或客户端）
• table_name：目标表名
• FIELDS子句：
  • TERMINATED BY：字段分隔符（默认为制表符）
  • ENCLOSED BY：字段包围符（如引号）
  • ESCAPED BY：转义字符（默认为反斜杠）
• LINES子句：
  • TERMINATED BY：行终止符（默认为换行符）
• IGNORE number LINES：跳过文件开头的指定行数
• column_list：指定导入到哪些列（可选）

实际示例

假设有一个CSV文件employees.csv，内容如下：

id,name,department,salary
1,"张三","技术部",8500
2,"李四","市场部",7500
3,"王五","人事部",6800

导入到employees表的命令：

LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, department, salary);

常见问题及解决方案

1. 文件权限问题：

   • 错误：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
   • 解决：将文件放在MySQL允许的目录中，或修改secure_file_priv系统变量

2. 字符集问题：

   • 确保文件编码与数据库字符集一致（通常UTF-8）
   • 可在LOAD语句前执行SET NAMES utf8mb4;

3. 日期格式问题：

   • 对于日期字段，确保CSV中的格式与MySQL期望的格式一致
   • 或使用STR_TO_DATE()函数转换

4. 空值处理：

   • CSV中的空值可用\N表示

性能优化建议

1. 导入前禁用索引：ALTER TABLE table_name DISABLE KEYS;
2. 导入后重建索引：ALTER TABLE table_name ENABLE KEYS;
3. 对于大文件，考虑分批导入
4. 使用LOCAL关键字会降低速度，尽可能在服务器上操作

替代方案

如果LOAD DATA INFILE不适用，还可以考虑： - 使用mysqlimport命令行工具 - 通过编程语言（如Python、PHP）编写导入脚本 - 使用ETL工具如Pentaho、Talend等

总结

LOAD DATA INFILE是MySQL中导入CSV数据最高效的方式，掌握其用法可以显著提高数据导入效率。使用时需要注意文件路径、权限、字符集等细节问题，并根据数据特点调整相应的参数设置。