好的，阿杰，我帮你详细整理一下 MySQL ON DUPLICATE KEY UPDATE 的用法、原理和优缺点，方便你在实际项目中正确使用。

MySQL ON DUPLICATE KEY UPDATE 用法详解

1️⃣ 概念

  • ON DUPLICATE KEY UPDATE 是 MySQL 提供的一种 插入或更新冲突处理语法
  • 作用：
    • 当插入记录违反 唯一索引或主键约束 时，执行 UPDATE 操作，而不是报错
  • 类似 UPSERT（插入或更新）

2️⃣ 基本语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2, ...;
  • 说明：
    • table_name：表名
    • (column1, column2, ...)：插入字段
    • VALUES(value1, value2, ...)：对应插入值
    • ON DUPLICATE KEY UPDATE：如果主键或唯一索引冲突，则更新指定列

3️⃣ 示例

示例表

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    score INT
);

示例 1：插入或更新单条记录

INSERT INTO users (id, username, score)
VALUES (1, 'Alice', 100)
ON DUPLICATE KEY UPDATE score = 100;
  • 如果 id=1 已存在 → 更新 score=100
  • 如果不存在 → 插入新记录

示例 2：使用 VALUES() 引用插入值

INSERT INTO users (id, username, score)
VALUES (1, 'Alice', 100)
ON DUPLICATE KEY UPDATE score = VALUES(score);
  • VALUES(score)：引用插入语句中的 score 值
  • 优点：无需手动重复写插入值，方便批量操作

示例 3：增加累加逻辑

INSERT INTO users (id, username, score)
VALUES (1, 'Alice', 10)
ON DUPLICATE KEY UPDATE score = score + VALUES(score);
  • 如果记录存在 → score 累加
  • 如果记录不存在 → 插入新记录

4️⃣ 使用场景

  1. 数据统计/计数
    • 累加访问量、点赞数、库存数量
  2. 去重插入
    • 避免重复插入已有唯一键的记录
  3. 同步数据
    • 从外部数据源更新表，已有记录更新，不存在插入

5️⃣ 优点

优点说明
简化操作不用先查询再决定插入或更新
支持批量批量插入可同时处理冲突
高效单条 SQL 原子操作，减少网络往返
灵活可对特定字段更新或累加

6️⃣ 缺点/注意事项

缺点/注意事项说明
仅限唯一键冲突只有主键或唯一索引冲突才会触发
MySQL 特性仅适用于 MySQL，不通用标准 SQL
触发器注意更新触发器可能重复触发
大量写入性能大批量 UPSERT 可能造成索引碎片或性能下降
VALUES() 被弃用MySQL 8.0.19 之后推荐使用 INSERT...AS new VALUES... 或 NEW 替代 VALUES()

7️⃣ 高级用法

  • 多字段冲突处理：
INSERT INTO products (id, name, stock)
VALUES (1, 'item1', 10)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock), name = VALUES(name);
  • 结合事务使用：保证插入/更新操作的原子性

💡 总结

  • ON DUPLICATE KEY UPDATE = 插入或更新冲突处理
  • 优点：原子性、简洁、可批量操作
  • 缺点：仅针对主键/唯一键冲突，MySQL 特有，批量高频写入需注意性能