MySQL 8.0导入200万+行大CSV报1366、1265错误及导入效率问题咨询
MySQL 8.0 大体积CSV导入常见问题处理
全量消除导入报错的操作方法
三类报错的根因和对应解决方案如下:
- Conv_rate、Conv_value_cost列触发1366报错(Incorrect decimal value)
报错根因:前期用记事本做全局%替换时存在漏项——记事本的替换逻辑不会对超长行、特殊编码行做逐字符匹配,部分带%后缀的数值没有被清理干净,这类带非数字字符的内容不符合DECIMAL类型的存储要求,就算关闭严格模式也无法自动完成格式转换。
解决方式:不用逐行排查原文件的漏改内容,直接通过LOAD DATA INFILE的内置字段预处理能力完成清洗,核心语句逻辑:
注意:源数据里的百分比值去掉%之后是放大100倍的数值,需要除以100才能得到真实的比例值,否则存入的结果会比真实值高两个数量级。LOAD DATA INFILE '/本地文件存放路径/stack.csv' INTO TABLE stack FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (Search_term, Currency, Cost, Avg_CPC, @impr, Clicks, Impr_Top_Percent, Impr_Abs_Top_Percent, Conversions, Conv_value, @conv_rate, @conv_vc) SET Impressions = REPLACE(@impr, ',', ''), Conv_rate = REPLACE(@conv_rate, '%', '') / 100, Conv_value_cost = REPLACE(@conv_vc, '%', '') / 100; - Impressions列触发1265报错(Data truncated)
报错根因:该列的源数据大概率带千分位分隔逗号(比如12,345),导入时逗号被识别为字段分隔符,导致字段内容被截断,无法匹配整数类型的存储要求。
解决方式:上面语句中对Impressions列绑定临时变量、移除千分位逗号的预处理逻辑即可覆盖该问题,不需要手动修改原CSV文件。
导入耗时的合理性判断
- 200万行、12列的CSV文件,完成参数优化后用原生
LOAD DATA INFILE导入,正常耗时在3-10分钟区间,当前50分钟仅完成20%进度属于异常慢速,并非正常水平。 - 导入过慢的核心原因是使用图形化Import Wizard逐行提交事务写入,比原生批量导入的速度慢几十倍。提速可以在导入前临时调整以下会话参数,导入完成后再恢复默认配置:
SET SESSION unique_checks=0; SET SESSION foreign_key_checks=0; SET SESSION autocommit=0;
报错提示的实际影响判定
- 这类报错不是无意义的形式化提示:开启严格模式时,遇到类型不匹配会直接中断对应行的导入;就算关闭严格模式,MySQL也只会把无法识别的非数字内容存为0或者空字符串,不会自动校正带%、千分位逗号的数值格式。之前抽查1万行未发现异常,只是因为抽查样本刚好没有碰到格式异常的行,不代表全量数据没有格式问题。
列类型调整的兜底方案
- 完全可以把报错涉及的列先改成VARCHAR类型作为兜底。这种方式的优势是不会因为零散脏数据中断全量导入,等所有数据导入完成后,再通过批量UPDATE语句清理%、千分位逗号等脏字符,逐行校验数值范围,确认数据无误后再把列类型改回DECIMAL、INT等数值类型即可。
- 不建议一开始就强制用严格的数值类型建表导入,遇到零散脏数据直接断流反而会提升排查成本。
内容的提问来源于stack exchange,提问作者Stop-YouAnimal
相关产品推荐
相关产品推荐

