MySQL导入CSV时SQL语句执行一半就中断，常见原因是什么？

Question

MySQL导入CSV时SQL语句执行一半中断，常见原因包括：① **数据格式不一致**（如字段分隔符、引号嵌套、换行符混用），导致LOAD DATA INFILE或INSERT语句解析失败；② **约束冲突**（主键/唯一键重复、外键校验失败、NOT NULL字段为空），触发事务回滚或中止；③ **字符集不匹配**（CSV文件编码与表定义不一致，如UTF-8 BOM未处理，引发乱码和截断）；④ **内存或超时限制**（max_allowed_packet不足、wait_timeout/innodb_lock_wait_timeout过短）；⑤ **权限问题**（FILE权限缺失，或secure_file_priv路径限制导致本地文件无法读取）。此外，使用客户端工具（如MySQL Workbench）执行批量INSERT时，单条语句含大量VALUES可能因语法错误或长度超限而中途报错。建议优先采用LOAD DATA INFILE配合FIELDS TERMINATED BY、ENCLOSED BY等子句，并启用IGNORE或REPLACE选项容错，同时校验源文件结构与目标表DDL一致性。

Answer 1

```html

一、现象层：执行中断的直观表现与日志线索

当 MySQL 导入 CSV 时 SQL 执行“一半中断”，典型表现为：ERROR 1062 (23000): Duplicate entry、ERROR 1300 (HY000): Invalid utf8mb4 character string、ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes，或客户端静默断连。关键线索藏于 SHOW WARNINGS;、错误日志（mysqld.err）及客户端返回的第 N 行/第 M 列定位信息。

二、数据层：格式不一致——解析器的“认知错乱”

• 字段分隔符污染：CSV 中未转义的逗号（如 "Smith, Jr.",25）被误切为 3 列；
• 引号嵌套失控：双引号内含换行符（"addr:\n123 Main St"）导致 LOAD DATA INFILE 跨行读取失败；
• 混合换行符：Windows（\r\n）、Unix（\n）、Mac（\r）混用，使 LINES TERMINATED BY 失效。

验证命令：head -n 5 data.csv | cat -A 可显式暴露不可见字符。

三、语义层：约束冲突——数据库的“原则性拒绝”

冲突类型	典型报错	定位方法
主键/唯一键重复	ERROR 1062	SELECT * FROM table WHERE id = ? 预查
外键校验失败	ERROR 1452	SET FOREIGN_KEY_CHECKS = 0;（临时绕过，慎用）
NOT NULL 字段为空	ERROR 1364	LOAD DATA ... SET col = NULLIF(@col, '') 显式映射

四、编码层：字符集不匹配——无声的截断与乱码

UTF-8 BOM（EF BB BF）常被 MySQL 当作首字段内容读入，导致第一列值异常（如 id）；若表定义为 utf8mb4 而 CSV 为 GBK，则 emoji 或生僻字被替换为 ? 或引发 ERROR 1366。解决方案：

1. 用 iconv -f GBK -t UTF-8//IGNORE input.csv > output.csv 转码；
2. 导入前执行 SET NAMES utf8mb4;；
3. 在 LOAD DATA 中显式指定 CHARACTER SET utf8mb4。

五、资源层：内存与超时——系统级的硬性拦截

-- 检查并调优关键参数（需 SUPER 权限）
SHOW VARIABLES LIKE 'max_allowed_packet';     -- 建议 ≥ 512M
SHOW VARIABLES LIKE 'wait_timeout';           -- 建议 ≥ 28800（8小时）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 建议 ≥ 300（秒）
SET GLOBAL max_allowed_packet = 1073741824;  -- 动态生效（重启后失效）

六、权限层：安全策略的“合法阻断”

LOAD DATA INFILE 需同时满足：

• 用户拥有 FILE 权限：GRANT FILE ON *.* TO 'user'@'%';
• 文件路径位于 secure_file_priv 白名单内：SELECT @@secure_file_priv; 返回 /var/lib/mysql-files/？则必须将 CSV 放入该目录。

若使用 LOAD DATA LOCAL INFILE，还需客户端启用 --local-infile=1 并服务端开启 local_infile=ON（5.6.37+ 默认禁用）。

七、工具链层：客户端批量 INSERT 的隐性陷阱

MySQL Workbench / Navicat 等工具生成的单条 INSERT INTO t VALUES (...),(...),... 语句，极易因以下原因中断：

• VALUES 子句总长度超过 max_allowed_packet；
• SQL 中存在未转义单引号（O'Reilly → 语法错误）；
• 工具自动分块逻辑缺陷：某批次含非法数据，但后续批次仍尝试执行（无事务原子性保障）。

八、工程实践：高鲁棒性导入方案设计

graph TD
  A[原始CSV] --> B{预处理}
  B -->|清洗| C[awk/sed/iconv 标准化]
  B -->|采样| D[head -1000 > sample.csv]
  C --> E[LOAD DATA INFILE]
  D --> F[CREATE TEMP TABLE + INSERT SELECT]
  E --> G[启用 IGNORE/REPLACE]
  F --> H[逐行校验 + INSERT ... ON DUPLICATE KEY UPDATE]
  G --> I[结果验证：COUNT(*) vs ROW_COUNT()]
  H --> I
  I --> J[归档与审计日志]

九、诊断清单：五分钟快速归因流程

1. 运行 SELECT @@version, @@sql_mode; —— 确认严格模式是否开启；
2. 执行 SHOW CREATE TABLE target_table\G —— 对比字段数、类型、约束、字符集；
3. 用 hexdump -C data.csv | head -20 检查 BOM 和换行符；
4. 测试最小集：head -n 10 data.csv > test.csv + LOAD DATA ... IGNORE；
5. 启用通用查询日志：SET GLOBAL general_log = 'ON'; 定位最后成功语句。

十、终极建议：生产环境黄金配置模板

以下为经 200+ TB 级数据迁移验证的 LOAD DATA 模板（适配 MySQL 5.7+/8.0）：

SET NAMES utf8mb4;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE target_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, @var3, col4)
SET col3 = NULLIF(TRIM(@var3), '');
-- 后续执行：SELECT ROW_COUNT(), @@warning_count;

```