使用MySQL Workbench导入CSV到表时空值处理错误求助

问题背景 #

尝试使用MySQL Workbench的Table Data Import Wizard导入含约86000条记录的CSV文件到spy_eod_202201表，表结构定义如下：

CREATE TABLE `spy_eod_202201` (
  `[QUOTE_UNIXTIME]` int DEFAULT NULL,
  `[QUOTE_READTIME]` text,
  `[QUOTE_DATE]` text,
  `[QUOTE_TIME_HOURS]` double DEFAULT NULL,
  `[UNDERLYING_LAST]` double DEFAULT NULL,
  `[EXPIRE_DATE]` text,
  `[EXPIRE_UNIX]` int DEFAULT NULL,
  `[DTE]` double DEFAULT NULL,
  `[C_DELTA]` double DEFAULT NULL,
  `[C_GAMMA]` double DEFAULT NULL,
  `[C_VEGA]` double DEFAULT NULL,
  `[C_THETA]` double DEFAULT NULL,
  `[C_RHO]` double DEFAULT NULL,
  `[C_IV]` text,
  `[C_VOLUME]` text,
  `[C_LAST]` double DEFAULT NULL,
  `[C_SIZE]` text,
  `[C_BID]` double DEFAULT NULL,
  `[C_ASK]` double DEFAULT NULL,
  `[STRIKE]` double DEFAULT NULL,
  `[P_BID]` double DEFAULT NULL,
  `[P_ASK]` double DEFAULT NULL,
  `[P_SIZE]` text,
  `[P_LAST]` double DEFAULT NULL,
  `[P_DELTA]` double DEFAULT NULL,
  `[P_GAMMA]` double DEFAULT NULL,
  `[P_VEGA]` double DEFAULT NULL,
  `[P_THETA]` double DEFAULT NULL,
  `[P_RHO]` double DEFAULT NULL,
  `[P_IV]` double DEFAULT NULL,
  `[P_VOLUME]` double DEFAULT NULL,
  `[STRIKE_DISTANCE]` double DEFAULT NULL,
  `[STRIKE_DISTANCE_PCT]` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CSV文件起始内容示例：

[QUOTE_UNIXTIME], [QUOTE_READTIME], [QUOTE_DATE], [QUOTE_TIME_HOURS], [UNDERLYING_LAST], [EXPIRE_DATE], [EXPIRE_UNIX], [DTE], [C_DELTA], [C_GAMMA], [C_VEGA], [C_THETA], [C_RHO], [C_IV], [C_VOLUME], [C_LAST], [C_SIZE], [C_BID], [C_ASK], [STRIKE], [P_BID], [P_ASK], [P_SIZE], [P_LAST], [P_DELTA], [P_GAMMA], [P_VEGA], [P_THETA], [P_RHO], [P_IV], [P_VOLUME], [STRIKE_DISTANCE], [STRIKE_DISTANCE_PCT]
1641243600, 2022-01-03 16:00,2022-01-03,16,477.77,2022-01-03,1641243600,0,1,0,0,0,0, , ,0, 2 x 1,242.16,243.3,235,0,0.01, 0 x 2471,0.01,-0.0003,-0.00003,0.00049,-0.00488,0,4.60994,0,242.8,0.508
1641243600, 2022-01-03 16:00,2022-01-03,16,477.77,2022-01-03,1641243600,0,1,0,0,0,0, , ,0, 1 x 1,237.16,238.3,240,0,0.01, 0 x 2337,0.01,-0.00042,0.00004,0.00011,-0.0051,0,4.47957,0,237.8,0.498
1641243600, 2022-01-03 16:00,2022-01-03,16,477.77,2022-01-03,1641243600,0,1,0,0,0,0, , ,0, 1 x 1,232.16,233.3,245,0,0.01, 0 x 2327,0.01,-0.00036,0,0.00028,-0.00498,0,4.35078,0,232.8,0.487

导入时出现问题：当行中[P_VOLUME]字段为空时，MySQL返回1206错误，且所有导入失败的行都被错误报告为第1行。目前临时方案是将所有字段设为可空文本类型，但CSV文件过大，测试不同配置耗时久。

问题分析 #

1. 1206错误本质：The total number of locks exceeds the lock table size，InnoDB在批量导入时会为每行添加行锁，当数据量较大且存在类型转换错误时，锁资源会快速耗尽，触发该错误。
2. 错误行定位错误：MySQL Workbench的Table Data Import Wizard存在bug，当导入过程中出现连续错误时，会错误地将所有失败行标记为第1行，实际问题出现在后续包含空值的行。
3. 空值类型不兼容：[P_VOLUME]定义为DOUBLE DEFAULT NULL，但CSV中的空值是空白字符串，导入时无法直接转换为数值类型，导致插入失败，进而加剧锁资源消耗。

解决步骤 #

1. 调整MySQL配置解决锁资源不足 #

修改MySQL配置文件（Linux为/etc/my.cnf或/etc/mysql/my.cnf，Windows为my.ini），添加或调整以下参数：

# 调整InnoDB缓冲池大小，建议设为服务器内存的50%-70%
innodb_buffer_pool_size = 4G
# 延长锁等待时间
innodb_lock_wait_timeout = 600
# 增大InnoDB日志文件大小
innodb_log_file_size = 1G
# 调大日志缓冲区
innodb_log_buffer_size = 64M

修改后重启MySQL服务生效。

2. 修复字段类型与CSV空值的兼容性 #

方案一：临时修改字段类型后转换

先将[P_VOLUME]改为文本类型，导入完成后再清理数据并转换回数值类型：

-- 临时修改字段类型
ALTER TABLE spy_eod_202201 MODIFY COLUMN `[P_VOLUME]` TEXT DEFAULT NULL;
-- 导入完成后，将空白字符串转为NULL
UPDATE spy_eod_202201 SET `[P_VOLUME]` = NULL WHERE `[P_VOLUME]` = '';
-- 转换为DOUBLE类型
ALTER TABLE spy_eod_202201 MODIFY COLUMN `[P_VOLUME]` DOUBLE DEFAULT NULL;

方案二：导入时设置空字符串转NULL

在MySQL Workbench的导入向导中，进入Advanced Options，勾选Treat empty strings as NULL选项，让导入工具自动将CSV中的空白字符串转换为NULL，匹配字段的可空数值类型。

3. 优化导入流程提升效率 #

禁用索引减少锁开销

导入前先禁用表的非主键索引，导入完成后重建，避免导入过程中频繁更新索引导致的锁资源消耗：

ALTER TABLE spy_eod_202201 DISABLE KEYS;
-- 执行导入操作
ALTER TABLE spy_eod_202201 ENABLE KEYS;

使用LOAD DATA INFILE替代导入向导

LOAD DATA INFILE比图形化向导速度更快，错误定位更准确，还能直接处理空值转换：

LOAD DATA INFILE '/path/to/your/spy_data.csv'
INTO TABLE spy_eod_202201
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(`[QUOTE_UNIXTIME]`, `[QUOTE_READTIME]`, `[QUOTE_DATE]`, `[QUOTE_TIME_HOURS]`, `[UNDERLYING_LAST]`, `[EXPIRE_DATE]`, `[EXPIRE_UNIX]`, `[DTE]`, `[C_DELTA]`, `[C_GAMMA]`, `[C_VEGA]`, `[C_THETA]`, `[C_RHO]`, `[C_IV]`, `[C_VOLUME]`, `[C_LAST]`, `[C_SIZE]`, `[C_BID]`, `[C_ASK]`, `[STRIKE]`, `[P_BID]`, `[P_ASK]`, `[P_SIZE]`, `[P_LAST]`, `[P_DELTA]`, `[P_GAMMA]`, `[P_VEGA]`, `[P_THETA]`, `[P_RHO]`, `[P_IV]`, `[P_VOLUME]`, `[STRIKE_DISTANCE]`, `[STRIKE_DISTANCE_PCT]`)
SET `[P_VOLUME]` = NULLIF(`[P_VOLUME]`, '');

注意：如果MySQL无法直接读取服务器上的文件，可使用LOAD DATA LOCAL INFILE（需要确保客户端开启local_infile权限）。

4. 快速验证配置的小技巧 #

由于CSV文件过大，可先截取前1000行生成测试文件，快速验证表结构、导入配置是否正确，避免全量导入耗时过长的问题。

内容的提问来源于stack exchange，提问作者Paulo Mendes