MySQL ON DUPLICATE KEY UPDATE 优雅解决存在更新/不存在插入难题_on duplicate key update 冲突检测-优快云博客

基本概念

ON DUPLICATE KEY UPDATE是 MySQL 特有的一种 INSERT 语句扩展，当执行 INSERT 操作时，如果插入的数据与表中已有数据的主键(PRIMARY KEY)或唯一索引(UNIQUE INDEX)发生冲突（即要插入的值与已有记录的主键或唯一索引值相同），则不执行插入操作，而是转而执行 UPDATE 操作，更新已存在的记录。

工作原理

1. 尝试插入：MySQL 首先尝试按照正常的 INSERT 语句插入新记录
2. 检查冲突：在插入前，MySQL 会检查是否存在与待插入数据主键或唯一索引冲突的记录
3. 冲突处理：
如果没有冲突：正常插入新记录
如果有冲突：不插入新记录，而是根据 ON DUPLICATE KEY UPDATE子句更新已存在的记录

基本语法

基本语法格式如下：

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

更常用的写法是使用 VALUES() 函数来引用原本打算插入的值：

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

使用场景

-- 1. 计数器更新：文章浏览量累加（最简单范式）
INSERT INTO article_views (article_id, view_count)
VALUES (123, 1)                       -- 第一次插入时 view_count = 1
ON DUPLICATE KEY UPDATE
    view_count = view_count + 1;      -- 冲突则原子+1

-- 2. 配置项更新：有就改，没有就插
INSERT INTO system_config (config_key, config_value, last_updated)
VALUES ('site_title', 'My Website', NOW())
ON DUPLICATE KEY UPDATE
    config_value = VALUES(config_value),   -- 用新值覆盖旧值
    last_updated = NOW();                  -- 同时刷新时间戳

-- 3. 购物车：数量累加 + 刷新时间戳
-- ⚠️ 必须提前建立 UNIQUE(user_id, product_id)
INSERT INTO shopping_cart (user_id, product_id, quantity, added_at)
VALUES (123, 456, 2, CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity),   -- 累加而不是覆盖
    added_at = CURRENT_TIMESTAMP;             -- 更新时间

高级用法

-- 1. 条件更新：只有新价比旧价低才降价
INSERT INTO products (product_id, price, last_updated)
VALUES (101, 99.99, NOW())
ON DUPLICATE KEY UPDATE
    price = IF(VALUES(price) < price, VALUES(price), price),  -- 条件判断
    last_updated = NOW();

-- 2. 结合子查询：用户存在才插入/更新统计
INSERT INTO user_stats (user_id, login_count)
SELECT 123, 1 FROM dual
WHERE EXISTS (SELECT 1 FROM users WHERE id = 123)   -- 确保用户真实存在
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1;

-- 3. 批量插入/更新：一条语句处理多行
-- ⚠️ 任何一行冲突都会触发对应 UPDATE
INSERT INTO log_entries (user_id, action, timestamp)
VALUES
    (1, 'login',    NOW()),
    (2, 'view',     NOW()),
    (3, 'purchase', NOW())
ON DUPLICATE KEY UPDATE
    action    = VALUES(action),
    timestamp = VALUES(timestamp);

其他处理冲突的方案

-- 1. REPLACE INTO：先删后插，主键会变，外键级联要小心
REPLACE INTO users (email, name, login_count)
VALUES ('test@example.com', 'Test User', 1);

-- 2. INSERT IGNORE：冲突直接忽略，不做任何更新
INSERT IGNORE INTO users (email, name)
VALUES ('test@example.com', 'Test User');