MySQL 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');