告别重复数据烦恼！MySQL ON DUPLICATE KEY UPDATE 优雅解决存在更新/不存在插入难题
目录
前言
在日常的数据库操作中，我们经常会遇到这样的场景：“如果数据存在，就更新它；如果不存在，就插入一条新的”。这种模式通常被称为 “Upsert”（Update + Insert）。在 MySQL 中，实现 Upsert 最优雅、最高效的方式之一就是使用 ON DUPLICATE KEY UPDATE 语法。
一、基本概念
1、什么是 ON DUPLICATE KEY UPDATE？
ON DUPLICATE KEY UPDATE是 MySQL 特有的一种 INSERT 语句扩展，当执行 INSERT 操作时，如果插入的数据与表中已有数据的主键(PRIMARY KEY)或唯一索引(UNIQUE INDEX)发生冲突（即要插入的值与已有记录的主键或唯一索引值相同），则不执行插入操作，而是转而执行 UPDATE 操作，更新已存在的记录。
2、工作原理
- 尝试插入：MySQL 首先尝试按照正常的 INSERT 语句插入新记录
- 检查冲突：在插入前，MySQL 会检查是否存在与待插入数据主键或唯一索引冲突的记录
- 冲突处理：
- 如果没有冲突：正常插入新记录
- 如果有冲突：不插入新记录，而是根据 ON DUPLICATE KEY UPDATE子句更新已存在的记录
3、基本语法
基本语法格式如下：
INSERTINTO table_name (column1, column2,..., columnN)VALUES(value1, value2,..., valueN)ONDUPLICATEKEYUPDATE column1 = value1, column2 = value2,...;更常用的写法是使用 VALUES() 函数来引用原本打算插入的值：
INSERTINTO table_name (column1, column2,..., columnN)VALUES(value1, value2,..., valueN)ONDUPLICATEKEYUPDATE column1 =VALUES(column1), column2 =VALUES(column2),...;⚠️触发条件：只有当插入操作违反了 主键（PRIMARY KEY） 或 唯一索引（UNIQUE INDEX） 约束时，UPDATE 部分才会被执行二、使用场景
1、计数器更新
- 最常见的应用场景是实现计数器功能，如文章浏览量、点赞数等
INSERTINTO article_views (article_id, view_count)VALUES(123,1)ONDUPLICATEKEYUPDATE view_count = view_count +1;2、配置项更新
- 当需要更新或插入配置项时
INSERTINTO system_config (config_key, config_value, last_updated)VALUES('site_title','My Website',NOW())ONDUPLICATEKEYUPDATE config_value =VALUES(config_value), last_updated =NOW();3、购物车商品更新
- 添加商品到购物车，已存在则更新数量
INSERTINTO shopping_cart (user_id, product_id, quantity)VALUES(123,456,2)ONDUPLICATEKEYUPDATE quantity = quantity +VALUES(quantity), added_at =CURRENT_TIMESTAMP;⚠️必须添加主键或唯一索引，否则ON DUPLICATE KEY UPDATE将不会触发，语句会正常执行插入操作（如果无其他错误）三、高级用法
1、条件更新
- 在 ON DUPLICATE KEY UPDATE子句中使用
条件表达式
-- 这个例子只在新的价格更低时才更新价格INSERTINTO products (product_id, price, last_updated)VALUES(101,99.99,NOW())ONDUPLICATEKEYUPDATE price =IF(VALUES(price)< price,VALUES(price), price), last_updated =NOW();2、多表关联
- 虽然不能直接在 ON DUPLICATE KEY UPDATE中使用多表，但可以
结合子查询
INSERTINTO user_stats (user_id, login_count)SELECT123,1FROM dual WHERENOTEXISTS(SELECT1FROM users WHERE id =123)ONDUPLICATEKEYUPDATE login_count = login_count +1;3、批量操作优化
- 对于大量数据的批量插入/更新，考虑以下优化
INSERTINTO log_entries (user_id,action,timestamp)VALUES(1,'login',NOW()),(2,'view',NOW()),(3,'purchase',NOW())ONDUPLICATEKEYUPDATEaction=VALUES(action),timestamp=VALUES(timestamp);⚠️当表有多个唯一约束时，任何唯一键冲突都会触发UPDATE四、其他处理冲突的方案
1、REPLACE INTO
- 实际上是先DELETE再INSERT，主键会有变化
REPLACEINTO users (email, name, login_count)VALUES('[email protected]','Test User',1);2、INSERT IGNORE
- 冲突时直接忽略，不更新
INSERTIGNOREINTO users (email, name)VALUES('[email protected]','Test User');
