Mysql on duplicate key update用法及优缺点

好的，阿杰，我们来详细讲解 MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 用法，包括语法、示例、优缺点以及使用场景。

---

1️⃣ 概念

INSERT ... ON DUPLICATE KEY UPDATE 是 MySQL 提供的一种语法，用于 插入新记录，当插入的记录与现有表中 唯一索引或主键冲突 时，改为 更新已有记录。

特点：

• 避免插入重复记录报错
• 可以在单条 SQL 中完成“插入或更新”操作

---

2️⃣ 语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = new_value1,
    column2 = new_value2, ...;

• 当插入的主键或唯一索引冲突时，会执行 UPDATE
• 可以使用 VALUES(column_name) 引用插入语句中的值

---

3️⃣ 示例

假设有一张 users 表：

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

3.1 插入新记录

INSERT INTO users (id, username, email)
VALUES (1, 'ajie', 'ajie@example.com')
ON DUPLICATE KEY UPDATE
    email = VALUES(email);

• 如果 id=1 或 username='ajie' 已存在，则更新 email
• 如果不存在，则插入新记录

3.2 更新已有记录

INSERT INTO users (id, username, email)
VALUES (1, 'ajie', 'new_email@example.com')
ON DUPLICATE KEY UPDATE
    email = VALUES(email);

• 原来的 id=1 用户的 email 将被更新为 'new_email@example.com'

---

4️⃣ 使用技巧

1. 增加访问次数：

INSERT INTO page_views (page_id, views)
VALUES (101, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

• 如果页面已经存在，则访问次数累加

2. 插入或更新时间戳：

INSERT INTO user_login (user_id, last_login)
VALUES (1, NOW())
ON DUPLICATE KEY UPDATE last_login = NOW();

• 记录用户最后一次登录时间

---

5️⃣ 优缺点

✅ 优点

1. 简化 SQL：一条语句完成插入或更新，避免写复杂的判断逻辑
2. 避免重复报错：冲突时直接更新，不会报主键重复错误
3. 性能优于先 SELECT 再 INSERT/UPDATE：减少数据库操作次数

❌ 缺点

1. 触发器影响：会触发 INSERT 和 UPDATE 触发器
2. 自增列可能间隙：如果表有自增主键，冲突更新也可能产生自增列跳号
3. 无法区分插入还是更新：执行后无法直接知道是插入还是更新，需要通过 ROW_COUNT() 或其他方式判断
4. 更新字段需谨慎：不想更新的字段也可能被意外覆盖

---

6️⃣ 对比 REPLACE INTO

特性	REPLACE INTO	INSERT ... ON DUPLICATE KEY UPDATE
冲突处理	删除旧记录，再插入新记录	更新已有记录
自增列影响	会增加间隙	不会增加间隙
是否触发删除触发器	会	不会
安全性	较激进	更安全

总结：如果只需要“冲突时更新已有记录”，推荐使用 INSERT ... ON DUPLICATE KEY UPDATE，比 REPLACE INTO 更安全。

---