MySQL 使用 load data 导入 CSV 数据常见错误及解决方案

在日常数据库运维工作中，使用load data命令导入 CSV 数据是提升效率的常用手段，但过程中往往会遇到各种报错。本文结合实际案例，详细解析导入过程中常见错误的成因及解决方法，帮助读者快速定位并解决问题。

一、场景再现与初始配置

1. 测试环境准备

• CSV 文件内容：包含三个字段，其中两个为日期时间格式，一个为字符串，字段值用双引号包裹，以逗号分隔。

"2022-01-01 00:00:00","A","2022-02-01 00:00:00"
"2022-01-02 00:00:00","B","2022-02-02 00:00:00"

• 数据库表结构：表t包含自增 ID、两个datetime类型字段（c1、c3）和一个varchar类型字段（c2）。
• 初始导入命令：

load data local infile '/home/mysql/online.csv' 
into table test fields terminated by ',' lines terminated by '\n' 
(c1, c2, c3) 
set c1=date_format(@c1, '%Y-%m-%d %H:%i:%s'), 
c3=date_format(@c3, '%Y-%m-%d %H:%i:%s');

 

二、常见错误及解决步骤

1. 本地数据加载权限问题（ERROR 3948）

• 报错信息：Loading local data is disabled; this must be enabled on both the client and server sides
• 解决方法：
  1. 开启服务器端配置：

  set global local_infile=1;
  

   
  2. 客户端登录时添加参数：

  mysql -u用户名 -p密码 --local-infile=1
  

   

2. 数据截断错误（Warning 1265）

• 报错信息：Data truncated for column 'c1' at row 1
• 成因分析：导入命令中直接将文件字段映射到表字段，未使用变量接收原始数据，导致格式转换失败。
• 解决方法：用@变量接收原始数据，再通过set子句转换：

load data local infile '/home/mysql/online.csv' 
into table t fields terminated by ',' lines terminated by '\n' 
(@c1, c2, @c3)  -- 用变量接收需要转换的字段
set c1=date_format(@c1, '%Y-%m-%d %H:%i:%s'), 
c3=date_format(@c3, '%Y-%m-%d %H:%i:%s');

 

3. 日期格式错误（Error 1292）

• 报错信息：Incorrect datetime value: '"2022-01-01 00:00:00"'
• 成因分析：CSV 字段被双引号包裹，且行尾包含回车符（\x0D），导致日期字符串格式异常。
• 解决方法：
  1. 用enclosed by '"'去除双引号包裹：
  2. 调整行终止符为\r\n（兼容 Windows 换行格式）：
  3. 改用str_to_date函数转换字符串为日期（date_format用于日期转字符串，此处适用反向转换）：

load data local infile '/home/mysql/online.csv' 
into table t fields 
terminated by ',' enclosed by '"'  -- 处理双引号
lines terminated by '\r\n'  -- 处理回车符
(@c1, c2, @c3) 
set c1=str_to_date(@c1, '%Y-%m-%d %H:%i:%s'), 
c3=str_to_date(@c3, '%Y-%m-%d %H:%i:%s');

 

三、关键知识点总结

1. load data参数解析：
   • terminated by：指定字段分隔符（如逗号）
   • enclosed by：去除字段的包裹符（如双引号）
   • lines terminated by：定义行终止符（\n适用于 Linux，\r\n适用于 Windows）
2. 日期函数区别：
   • date_format(日期, 格式)：将日期转换为指定格式的字符串
   • str_to_date(字符串, 格式)：将字符串按指定格式转换为日期
3. CSV 文件特性：
   • 用文本编辑器打开可见双引号和分隔符，Excel 打开自动隐藏格式符，易造成视觉误解
   • 不同操作系统换行符不同（Windows 为\r\n，Linux 为\n）

四、问题排查思路

1. 遇到导入错误时，优先执行show warnings;查看详细报错信息，定位字段或数据格式问题。
2. 检查文件格式：通过cat -v 文件名查看不可见字符（如回车符^M即\r）。
3. 分步调试：先解决格式问题（分隔符、包裹符），再处理数据类型转换，最后验证换行符适配性。

通过以上步骤，可高效解决load data导入 CSV 数据时的常见问题，提升数据迁移效率。实际生产中需结合具体数据格式灵活调整参数，积累对错误提示的解读能力至关重要。