MySQL 批量插入/更新含有主键/唯一索引或者的解决方案是使用 on duplicate key update。
解决方案
insert into database.table (id, key, field) values (v1, v2, v3), (v1, v2, v3), (v1, v2, v3) on duplicate key update id=values(id),key=values(key),field=values(field);
该方案适用范围广,
既可以批量插入,
也可以批量更新,
同时能保证避免插入重复数据,
还能避免影响自增 id,
又不需要创建临时表权限,
更不会修改已存在记录的其他字段。
实际应用中 update 后跟随的字段可以进行调整,保留只需要更新的字段。
如果表中有自增 id,这个方案还有一点需要注意,insert into … values 跟随的记录数 (numberA)即为此次自增数 (numberA),即使存在重复唯一索引导致实际插入记录数(numberB)少于跟随的记录数(numberA)。
其他插入方案
1. insert ignore into
insert ignore 遇到重复数据会忽略,可以有效保证不插入重复数据,但也会忽略其他错误。
批量插入之后再进行单条更新。
# 批量插入,需要保证 NOT NULL 字段有值 insert into database.table (id, key, field) values (v1, v2, v3), (v1, v2, v3), (v1, v2, v3); # 单条更新 update database.table set field = v3 where key = v1; update database.table set field = v3 where key = v1; update database.table set field = v3 where key = v1;
2. replace into
如果表中有主键或唯一索引,replace into 会删除冲突行(已存在记录),再新插入新行。因此导致 replace into 可能会导致主键自增 1。
如果表中没有主键和唯一索引,replace into 没有意义。
如果表中没有自增主键,使用 replace into 是一个不错的选择。
使用 replace 需要注意,如果 更新的字段 不全,会被设置为缺省值,也就是会清除其他字段。
replace into database.table (id, key, field) values (v1, v2, v3), (v1, v2, v3), (v1, v2, v3);
最后,推荐使用 insert into … on duplicate key update。
on duplicate key update 是 mysql 特有语法。