MySQL LOAD DATA导入CSV报ERROR 1366整数字段值错误

安全阿岳

2026-8-29

问题结论

你使用@dummy占位不需要的列、通过SET子句自定义列映射顺序的写法，MySQL 8.0完全支持，语法本身没有问题。

报错根因

报错ERROR 1366 (HY000): Incorrect integer value: 'Werdmühlestrasse 4' for column 'plz'是典型的字段解析错位导致，核心问题出在LOAD DATA配置上：

核心配置错误：你设置了ESCAPED BY '"'，这是列错位的直接原因。
当配置OPTIONALLY ENCLOSED BY '"'时，MySQL会自动识别双引号包裹字段内的连续双引号转义逻辑，不需要额外将转义字符设置为双引号。你手动把转义符设为双引号后，解析到CSV里的空字段""时，会把第一个双引号识别为转义符，第二个双引号识别为字段内容的一部分，不会将这组双引号判定为空字段的边界，后续所有字段的分隔位置全部偏移，原本属于第6列的字符串Werdmühlestrasse 4被错分配给对应plz字段的@col14变量，触发整数类型校验报错。
行识别配置错误：你贴出的CSV样例单行包含61个字段，实际是两行记录被拼接成了一行，说明你配置的LINES TERMINATED BY '\n'和源文件换行格式不匹配。如果CSV是Windows环境生成的，换行符为\r\n，仅用\n匹配行尾会导致多行被合并解析，进一步加剧列错位。
变量长度不匹配：你仅定义了34个用户变量承接列值，但从样例看实际CSV单行列数远大于34，变量数量和实际列数不匹配也会导致映射错位。

修复步骤

修正字段解析配置
删除ESCAPED BY '"'配置，使用默认转义符即可，修正后的FIELDS配置如下：

FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'

修正换行符配置
先确认源CSV的换行格式：如果是Windows生成的文件，将行终止符改为LINES TERMINATED BY '\r\n'；如果是Linux/macOS生成的文件再保留'\n'配置。
核对列数匹配变量长度
取CSV中一行完整数据，统计总字段数，调整你的用户变量列表长度，保证变量数量和CSV单行列数完全一致，不需要的列统一用@dummy占位，不能少写变量。
验证映射正确性
可以先不加SET子句，将解析出的变量值查询验证，确认每个变量对应的取值和CSV列完全对齐后，再添加SET子句做字段映射。如果你的geometry字段是MySQL空间类型，赋值时需要用空间函数转换：geometry=ST_GeomFromText(@col34)，不能直接存入WKT字符串。

修正后语句参考

LOAD DATA INFILE '/home/coder/project/geoz.adrstzh_adressen_stzh_p.csv'
INTO TABLE mainZuerichAddresses
FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- 根据实际换行格式调整为'\n'或'\r\n'
IGNORE 1 ROWS
-- 需补全所有变量，总数量和CSV单行列数完全一致
(@col1,@dummy,@col3,@col4,@dummy,@col6,@col7,@dummy,@dummy,@col10,@dummy,@dummy,@dummy,@col14,@dummy,@col16,@dummy,@dummy,@dummy,@col20,@col21,@dummy,@col23,@col24,@col25,@col26,@col27,@col28,@col29,@dummy,@dummy,@dummy,@dummy,@col34,...补全剩余占位变量...) 
SET objid=@col29,gebaeudeeingangnummer=@col1,adresse=@col6,lokalisationsname=@col3,
    hausnummer=@col4,plz=@col14,plz_ortschaft=@col16,stadtkreis=@col20,
    gebaeudenummer=@col10,statistisches_quartier=@col21,status=@col7,statistische_zone=@col23,schulkreis=@col24,verwaltungsquartier=@col25,
    roem_kath_kirchgemeinde=@col26,ev_ref_kirchgemeinde=@col27,ev_ref_kirchenkreis=@col28,geometry=ST_GeomFromText(@col34);

附你提供的CSV样例（供列数核对参考）：

"1026235","2172","Werdmühlestrasse","4","400","Werdmühlestrasse 4","3","real","BB","261AA01857","3169179","2683137.449","1247708.724","8001","0","Zürich","AA1750","","K","1","Lindenhof","13","1301","Zürichberg","Altstadt","St.Peter u Paul","St.Peter","1026238","562","Fortunagasse","15","1500","Fortunagasse 15","3","real","BB","261AA01852","140709","2683163.645","1247502.811","8001","0","Zürich","AA5297","","K","1","Lindenhof","13","1301","Zürichberg","Altstadt","St.Peter u Paul","St.Peter","1","3","3","29.0","8.539764579706915","47.373115180353350","POINT (2683163.8 1247502.8)"

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