MySQL on duplicate key update影响行数及对主键的影响

最新推荐文章于 2025-03-14 05:00:00 发布
最新推荐文章于 2025-03-14 05:00:00 发布
阅读量5.7k 收藏 10
点赞数 3
CC 4.0 BY-SA版权
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/will_be_better/article/details/88344593
本文详细解析MySQL中INSERT...ON DUPLICATE KEY UPDATE语法的行数影响原理，解释为何更新操作会显示影响2行，以及如何通过此特性区分插入与更新操作。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

今天使用到MySQL一个 INSERT ... ON DUPLICATE KEY UPDATE 的语法，发现返回的影响行数是2，很奇怪，然后从网上找，发现这篇文章

https://segmentfault.com/a/1190000017268633

豁然开朗！

 

INSERT ... ON DUPLICATE KEY UPDATE ...影响的行数是1为什么返回2？

为什么会这样呢，按理说影响行数就是1啊，看看官方文档的说明

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

官方明确说明了，插入影响1行，更新影响2行，0的话就是存在且更新前后值一样。是不是很不好理解？

其实，你要这样想就好了，这是为了区分到底是插入了还是更新了，返回1表示插入成功，2表示更新成功。

 

 

再次测试之后发现，如果自增id到10，如果经过这个SQL的更新数据之后  下一个自增id是12而不是11。

关于Mysql 的on duplicate key update操作，导致主键不连续自增的问题
飞越蓝天的专栏
06-19 816
2、使用ON DUPLICATE KEY UPDATE时，如果将行作为新行插入，则每行的受影响行值为 1，如果更新现有行，则为 2，如果将现有行设置为其当前值，则为 0。（换句话说就是默认级别为1，在进行insert操作时此时会锁表所以只能影响一行，但是ON DUPLICATE KEY UPDATE本质是先删除后插入，遇到重复是会进行回滚所以它占了两个索引，一切是以主键索引自增为基础的）如果更新操作，受到影响行的值为2；简单说，就是数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入。
MySQL中的ON DUPLICATE KEY UPDATE详解与应用
fudaihb的博客
09-17 4474
MySQL 作为最流行的开源关系型数据库之一，在处理数据插入和更新时提供了丰富的功能。`ON DUPLICATE KEY UPDATE` 是 MySQL 提供的一种强大的机制，它允许在执行 `INSERT` 操作时，如果发生主键或唯一键冲突，能够选择性地执行更新操作。这种功能在实际业务中有着广泛的应用，尤其是在数据的去重、同步、日志记录等场景下。 本文将从以下几个方面详细介绍 `ON DUPLICATE KEY UPDATE` 的使用方法和其背后的工作原理，并提供多个实际应用场景示例，帮助你更好地理解和掌
MySQL ON DUPLICATE KEY UPDATE影响行数
CoderJia的学习之路
07-16 769
经常使用 `ON DUPLICATE KEY UPDATE` 来插入数据或者更新已存在的记录，使用到 MySQL 一个 `INSERT ... ON DUPLICATE KEY UPDATE` 的语法，明明只更新了两条记录，返回的影响行数竟然是 4
Java开发第一坑：记一次MySQL ON DUPLICATE KEY UPDATE影响行数异常排查：从现象到解决的全过程
编程技术探索者，分享C/C++、C#、Java、数据库等开发经验，聚焦实战技巧与AI兴趣，助力编程爱好者成长。
03-14 1512
Java开发第一坑：记一次MySQL ON DUPLICATE KEY UPDATE影响行数异常排查：从现象到解决的全过程
on duplicate key update
atino的博客
10-24 194
on duplicate key update，字段值更新前后相同，则不执行更新操作，影响行数是0
深入mysql ON DUPLICATE KEY UPDATE 语法的分析
01-21
mysql “ON DUPLICATE KEY UPDATE” 语法如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列...
mysql ON DUPLICATE KEY UPDATE语句示例
09-10
在MySQL数据库中，`ON DUPLICATE KEY UPDATE` 是一个非常实用的特性，它允许你在插入新数据时，如果遇到重复的唯一键值，自动执行更新操作，而不是抛出错误。这个功能大大简化了数据处理的流程，特别是当你需要在一...
mysql 中 insert 的ON DUPLICATE KEY UPDATE
scropio0zry的博客
06-20 427
平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件。此时插入数据的时候，经常会有这样的情况：我们想向数据库插入一条记录：    若数据表中存在以相同主键的记录，我们就更新该条记录，否则就插入一条新的记录。逻辑上我们需要怎么写：$result = mysql_query('select * from xxx where id = 1'); $ro...
INSERT INTO .. ON DUPLICATE KEY更新多行记录
weixin_30647065的博客
08-13 100
现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。 举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在...
数据库｜一文教你解决on duplicate key update引发的索引数据不一致问题
在数字化道路无限探索
01-24 2096
使用insert into ... on duplicate key update操作时遇到的小问题
insert...on duplicate key update语法详解
热门推荐
赵英超的博客
02-10 4万+
一.作用和使用场景 在mysql入库时，不能出现两条数据主键一致的情况，因为在两条数据的主键一致的情况下，mysql就会判定为待插入数据在数据库中存在重复数据，也就是说判断数据是否重复是根据主键来区别的。 但是有一些场景，如日志文件解析入库，消息队列接收数据入库等情况下可能解析到或者接收到待插入的重复数据存在重复数据则更新，不存在则插入。 这时如下语句的写法就派上用场了，on dupli...
MySQL插入语句insert into,insert ignore into,insert into ... on duplicate key update,replace into-解决唯一键约束
ThinkWon的博客
06-08 1万+
文章目录常用插入语句insert intoinsert ignore intoinsert into ... on duplicate key updatereplace into使用场景总结 当MySQL表字段设置unique key或者primary key时，被约束的字段就必须是唯一的。新插入数据直接使用insert into，如果出现唯一性冲突，就会抛出异常。我们可以根据需求选择合适的插入语句。 为了演示，我们先新建一张user表，SQL语句如下，其中user_id为主键，username为唯一索引
mysql插入重复数据的三种处理方式（DUPLICATE、IGNORE、REPLACE ）
牟云飞的博客
09-01 5307
向myf_deal_data 表插入数据，如果myf_deal_data 表的主键字段（ ID 字段）已经存在 111这个值，那么这条语句就会被忽略，不会插入数据，也不会报错。这条语句意味着，如果myf_deal_data表，存在id为111的数据，那么就将 PTDATA 和REPORTTIME 的值。如果数据出现重复，则忽略这个操作，不会报错，但也不会插入重复的数据。如果数据出现重复，则更新重复数据的值。注意：多线程并发会导致死锁的可能。
MySQL on duplicate key update 的使用
u014704496的专栏
11-05 431
第二种： INSERT 中ON DUPLICATE KEY UPDATE的使用 如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：mysql>INSERT INTO table (a,b,c) VALUE
MySQL INSERT ON DUPLICATE KEY UPDATE
qq_32740259的博客
07-08 2527
MySQL INSERT ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE 用法
qq_38803590的博客
05-12 4万+
注意：ON DUPLICATE KEY UPDATE 是Mysql特有的语法，仅Mysql有效。 作用：当执行insert操作时，有已经存在的记录，执行update操作。 用法： 有一个test表，id为主键。第一次插入数据 INSERT INTO test(id,name,age)VALUES(1,'2',3),(11,'22',33) 此时表中数据增加了一条主键’id’为‘1’和‘11’的两条记录，当我们再次执行一条id为1的插入语句时，会发生什么呢？ INSERT
mysql特殊语法insert into .. on duplicate key update ..使用详解
健身变秃，coding变强
10-21 2万+
mysql特殊语法insert into .. on duplicate key update ..使用详解
ON DUPLICATE KEY UPDATE 更新所有字段除了主键
最新发布
07-20
### 使用 `ON DUPLICATE KEY UPDATE` 更新所有字段但排除主键 在 MySQL 中，当使用 `ON DUPLICATE KEY UPDATE` 语句时，如果希望更新除主键外的所有字段，可以通过显式列出需要更新的字段来实现。由于主键通常设置为 `AUTO_INCREMENT` 或具有唯一性约束，因此在插入或更新操作中不应直接修改主键字段。 #### 示例 假设有一个名为 `employees` 的表，定义如下： ```sql CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10, 2) ); ``` 现在需要插入一条记录，如果 `id` 冲突（例如，表中已存在相同的主键），则更新除主键外的所有字段： ```sql INSERT INTO employees (id, name, department, salary) VALUES (101, 'Alice', 'Engineering', 85000.00) ON DUPLICATE KEY UPDATE name = 'Alice', department = 'Engineering', salary = 85000.00; ``` 在这个例子中，如果 `id = 101` 的记录已经存在，则会更新 `name`、`department` 和 `salary` 字段；如果不存在，则插入新记录。主键 `id` 在插入时指定，但在更新时不被修改 [^4]。 #### 使用 `VALUES()` 函数简化更新语句 当插入的数据较多时，可以使用 `VALUES()` 函数来引用插入时的值，从而简化更新语句： ```sql INSERT INTO employees (id, name, department, salary) VALUES (101, 'Alice', 'Engineering', 85000.00), (102, 'Bob', 'Marketing', 75000.00) ON DUPLICATE KEY UPDATE name = VALUES(name), department = VALUES(department), salary = VALUES(salary); ``` 在这个例子中，如果 `id` 冲突，`ON DUPLICATE KEY UPDATE` 子句会使用当前插入的值来更新对应的 `name`、`department` 和 `salary` 字段 [^4]。 #### 注意事项 - `ON DUPLICATE KEY UPDATE` 需要表中存在主键或唯一索引，否则不会触发更新操作 [^1]。 - 如果更新的字段包含主键或唯一索引列，并且更新后的值与其他记录冲突，会导致插入和更新都失败。 - 使用 `ON DUPLICATE KEY UPDATE` 时，自增字段（`AUTO_INCREMENT`）可能会跳过某些值，因为每次插入操作都会尝试分配一个新的自增值，即使最终执行的是更新操作 [^3]。
评论 6
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值