如何设置CSV导入时自动更新数据库中已存在主键的记录？

本文共计1133个文字，预计阅读时间需要5分钟。

相关专题

MySQL LOAD DATA INFILE 不支持 ON DUPLICATE KEY UPDATE

直接用 load data infile 导入 csv 时，哪怕表有主键或唯一索引，它也不会自动跳过/更新重复行——遇到冲突就报错 duplicate entry '...' for key 'primary'。这不是配置问题，是语法限制：mysql 就没给这个命令加 on duplicate key update 子句。

常见错误现象：
• 执行 LOAD DATA INFILE 报错退出，整批导入中断
• 改用 IGNORE 关键字（如 LOAD DATA INFILE ... IGNORE），结果是静默丢弃重复行，但不会更新已有记录的其他字段

• 如果只要“存在就跳过”，用 IGNORE 可行，但它不等于 ON DUPLICATE KEY UPDATE
• 如果要“存在就更新指定字段”，必须换方案
• LOAD DATA INFILE 的性能优势在大批量导入时明显，别轻易放弃，先看替代路径

用 INSERT ... SELECT + 临时表模拟 ON DUPLICATE KEY UPDATE

这是最贴近原生行为、且能复用 CSV 数据的实操路径：先把 CSV 导进一个结构相同但无约束的临时表，再用 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 写回目标表。

使用场景：CSV 行数在几万到百万级，需要保留 LOAD DATA 级别的吞吐，又必须做主键冲突更新

• 临时表建法：CREATE TEMPORARY TABLE tmp_import LIKE target_table（注意：TEMPORARY 表只对当前会话可见，断开即销毁）
• 导入 CSV 到临时表：LOAD DATA INFILE '/path/to/data.csv' INTO TABLE tmp_import FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
• 执行带冲突处理的写入：INSERT INTO target_table SELECT * FROM tmp_import ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2)
• 务必显式列出要更新的字段，别用 *；VALUES(colX) 指的是“本次 INSERT 中该列的值”，不是临时表里的值

Python 娱乐中用 executemany + INSERT ... ON DUPLICATE KEY UPDATE

当 CSV 不能直接由 MySQL 读取（比如文件在本地、权限受限），或需要预处理（清洗、转换、条件过滤），走 Python 是更可控的选择。关键不是“读 CSV”，而是用对 SQL 模式。

性能影响：单条 INSERT ... ON DUPLICATE KEY UPDATE 很慢；批量用 executemany 可提速 5–10 倍，但仍比 LOAD DATA INFILE 慢一到两个数量级

• SQL 模板必须写成："INSERT INTO table (a,b,c) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b)"
• 不要拼接字符串传字段名，VALUES() 函数只认列名，不接受变量
• 如果主键是自增 ID，但 CSV 提供了 ID 值，确保 INSERT 语句里明确包含 ID 字段，否则 ON DUPLICATE KEY 找不到冲突依据
• 用 pandas.read_csv 读取后转 df.values.tolist() 传给 executemany，比逐行 execute 稳定得多

唯一索引字段没设对，ON DUPLICATE KEY 就不触发

这个坑非常隐蔽：你以为主键是 id，但实际冲突发生在 email 字段上，而 email 没建唯一索引 —— 那么 ON DUPLICATE KEY UPDATE 完全不会生效，只会报错或插入重复行。

检查方法：SHOW INDEX FROM table_name WHERE Non_unique = 0，确认你要依赖冲突判断的字段确实在结果里

• 复合唯一索引也有效，比如 UNIQUE KEY idx_user_dept (user_id, dept_id)，那么 INSERT ... ON DUPLICATE KEY 会按这两个字段联合判重
• PRIMARY KEY 和 UNIQUE 索引都算“key”，但普通 INDEX 不算
• 如果表用的是 REPLACE INTO，它底层是删+插，会触发外键级联和自增 ID 变动，和 ON DUPLICATE KEY UPDATE 行为本质不同，别混用

复杂点在于冲突判定和更新逻辑必须严格绑定到索引定义，而不是业务直觉。很多人调了半天发现 SQL 没问题，最后查出是少建了一个 UNIQUE 约束。