MySQL中ON DUPLICATE KEY UPDATE优雅解决存在更新/不存在插入难题

目录

• 前言
• 一、基本概念
• 1、什么是 ON DUPLICATE KEY UPDATE
• 2、工作原理
• 3、基本语法
• 二、使用场景
• 1、计数器更新
• 2、配置项更新
• 3、购物车商品更新
• 三、高级用法
• 1、条件更新
• 2、多表关联
• 3、批量操作优化
• 四、其他处理冲突的方案
• 1、REPLACE INTONorZq
• 2、INSERT IGNORE

前言

在日常的数据库操作中，我们经常会遇到这样的场景：“如果数据存在，就更新它；如果不存在，就插入一条新的”。这种模式通常被称为 “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、基本语法

基本语法格式如下：

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),
    ...;

触发条件：只有当插入操作违反了 主键（PRIMARY KEY） 或 唯一索引（UNIQUE INDEX） 约束时，UPDATE 部分才会被执行

二、使用场景

1、计数器更新

最常见的应用场景是实现计数器功能，如文章浏览量、点赞数等

INSERT INTO article_views (article_id, view_count) 
VALUES (123, 1) 
ON DUPLICATE KEY UPDATE 
	view_count = view_count + 1;

2、配置项更新

当需要更新或插入android配置项时

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、购物车商品更新

添加商品到购物车，已存在则更新数量

INSERT INTO shopping_cart (user_id, product_id, quantity) 
VALUES (123, 456, 2)
ON DUPLICATE KEY UPDATE 
    quantity = quantity + VALUES(quantity),
    added_at = CURRENT_TIMESTAMP;

必须添加主键或唯一索引，否则ON DUPLICATE KEY UPDATE将不会触发，语句会正常执行插入操作（如果无其他错误）

三、高级用法

1、条件更新

在 ON DUPLICATE KEY UPDATE子句中使用条件表达式

-- 这个例子只在新的价格更低时才更新价格
INSERT INTO products (product_id, price, last_updated) 
VALUES (101, 99.99, NOW()) 
ON DUPLICATE KEY UPDATE 
    price = IF(VApythonLUES(price) < price, VALUES(price), price),
    last_updated = NOW();

2、多表关联

虽然不能直接在 ON DUPLICATE KEY UPDATE中使用多表，但可以结合子查询

INSERT INTO user_stats (user_id, login_count) 
SELECT 123, 1 FROM dual
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 123)
ON DUPLICATE KEY UPDATE 
	login_count = login_count + 1;

3、批量操作优化

对于大量数据的批量插入/更新，考虑以下优化

INSERT INTO log_entries (user_id, action, timestamp) 
VALUES 
    (1, 'login', NOW()),
    (2, 'view', NOW()),
    (3, 'purchase', NOW())
ON DUPLICATE KEY UjavascriptPDATE 
    action = VALUES(action), 
    timestamp = VALUES(timestamp);

当表有多个唯一约束时，任何唯一键冲突都会触发UPDATE

四、其他处理冲突的方案

1、REPLACE INTO

实际上是先DELETE再INSERT，主键会有变化

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

到此这篇关于MySQL中ON DUPLICATE KEY UPDATE优雅解决存在更新/不存在插入难题的文章就介绍到这了,更多相关MySQL ON DUPLICATE KEY UPDATE内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！