如何在MySQL中实现带条件的批量插入并自动处理唯一键冲突？-mysql教程-PHP中文网

on duplicate key update用于插入时唯一键冲突则更新指定字段：必须有primary key或unique索引，update子句支持values(col)、函数及if/case条件表达式，不支持where；返回1表示插入、2表示更新，避免使用replace into以防id跳变和触发器重复执行。

如何在mysql中实现带条件的批量插入并自动处理唯一键冲突？

MySQL `INSERT ... ON DUPLICATE KEY UPDATE` 的实际用法

当表中存在 UNIQUE 索引或 PRIMARY KEY，且你想插入多条记录、冲突时更新部分字段（而非报错或跳过），INSERT ... ON DUPLICATE KEY UPDATE 是最直接的选择。它不是“忽略冲突”，而是明确指定冲突后执行什么更新逻辑。

关键前提是：必须有生效的唯一约束，否则该语法不触发更新，只做普通插入；且 UPDATE 子句里不能引用未在 VALUES() 中提供的列值（比如不能写 updated_at = NOW() 以外的表达式依赖于被插入行）。

常见错误现象：ERROR 1062 (23000): Duplicate entry 'xxx' for key 'xxx' 出现，说明没加 ON DUPLICATE KEY UPDATE，或者唯一索引建错了（比如建在了非目标字段上）。

确保目标字段上有 UNIQUE 或 PRIMARY KEY —— 仅 INDEX 不够
UPDATE 后面的赋值可以是常量、VALUES(col_name)（表示“本次想插入的值”）、函数（如 NOW()、UNIX_TIMESTAMP()）
避免在 UPDATE 中使用子查询，MySQL 8.0.19+ 才支持，低版本会报错 ERROR 1093

INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (123, 1, '2024-06-01 10:00:00'),
       (456, 1, '2024-06-01 10:05:00')
ON DUPLICATE KEY UPDATE
  login_count = login_count + VALUES(login_count),
  last_login = VALUES(last_login);

用 `REPLACE INTO` 替代？别轻易用

REPLACE INTO 看似简单：冲突时先删后插。但它本质是 DELETE + INSERT，会引发额外问题，不是“安全替代”。

典型副作用包括：自增 ID 跳变、触发器重复执行、外键级联动作被意外触发、MVCC 快照可能看到中间状态。尤其在高并发写入场景下，REPLACE 可能导致主从延迟突增或从库复制中断（因 DELETE 语句在 binlog 中可见，但业务本意只是“更新”）。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

如果表没有自增主键，REPLACE 仍会消耗一个 auto-increment 值（MySQL 内部机制）
REPLACE 不支持 UPDATE 那样的条件判断，无法实现“仅当新值更大时才更新”这类逻辑
无法获取每行是“插入”还是“替换”的状态反馈（而 INSERT ... ON DUPLICATE KEY UPDATE 的 affected_rows 返回值：1=插入，2=更新）

批量插入带条件更新：为什么不能只靠 `WHERE`？

有人试图在 ON DUPLICATE KEY UPDATE 的 SET 子句里加 WHERE，例如：last_login = VALUES(last_login) WHERE VALUES(last_login) > last_login —— 这是非法语法，MySQL 不允许。

真要实现“仅当新值满足条件才更新”，得换思路：用 VALUES() 和现有字段做比较运算，MySQL 允许在赋值右侧写表达式。

正确写法：last_login = IF(VALUES(last_login) > last_login, VALUES(last_login), last_login)
注意：所有比较都基于当前行已有值和本次 VALUES() 提供的值，不能跨行比较
性能影响：表达式计算本身开销小，但如果字段无索引、又在大表上高频执行，IF 或 CASE 不会影响索引使用，但会增加 CPU 计算量

INSERT INTO sensor_data (device_id, temp, ts)
VALUES ('D001', 23.5, '2024-06-01 12:00:00'),
       ('D002', 28.1, '2024-06-01 12:00:00')
ON DUPLICATE KEY UPDATE
  temp = GREATEST(temp, VALUES(temp)),
  ts = CASE WHEN VALUES(ts) > ts THEN VALUES(ts) ELSE ts END;