mysql插入冲突转更新ON DUPLICATE KEY UPDATE

原创已于 2022-11-21 21:51:11 修改 · 置顶 · 2k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2022-11-21 21:30:48 首次发布

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了MySQL中的INSERT ... ON DUPLICATE KEY UPDATE语法，用于在插入数据时处理主键或唯一键冲突。通过示例展示了单条记录和多条记录的插入操作，并解释了在冲突发生时如何执行更新。此外，还讨论了受影响行数的值在不同情况下的变化，以及此功能在数据同步和订单状态更新等场景中的应用。

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

背景：

mysql利用insert插入数据时，可能发生主键/唯一键冲突，若想在冲突时变更为update语句，可以借助于mysql的INSERT ... ON DUPLICATE KEY UPDATE语句

语法：

insert into table (key...) values(val...) on duplicate key update key1=val1,key2=val2

创建表user，下面的例子都以该表为主

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NULL,
  `user_name` VARCHAR(45) NULL,
  `score` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC));

INSERT INTO user (id, user_id, user_name, score) VALUES (66, 1, 'tom', 97);
INSERT INTO user (id, user_id, user_name, score) VALUES (67, 2, 'marry', 95);

id【自增主键】	user_id【唯一键】	user_name	score
66	1	tom	97
67	2	marry	95

单条记录插入：

user_id为唯一键

insert into user (user_id,user_name,score) values(1,'tom',100) on duplicate key update score = 100

前半段sql是正常的insert语句
后半段sql从on duplicate key开始，update将会在主键/唯一键冲突时执行。
即若数据库中已经存在user_id=1的数据，将会执行update操作，将user_id=1数据中的score改为100

多条记录插入：

user_id为唯一键

insert into user (user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score = values(score)

insert into user (user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score = values(user_id)

insert into user (user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score = values(user_id) + values(score)

insert into user (user_id,user_name,score) values(1,'tom',100),(2,'marry',99) 
on duplicate key update score = values(66)

多条记录插入时，需要利用values函数处理赋值
- values内可以直接使用字段名，可以是当前被赋值的字段(正常情况)，也可以是其他字段
- values内还可以是具体的常量值
- 可以使用多个数字类型的字段经过values处理后再进行运算

细节：

在使用insert into ... on duplicate key update插入/更新数据时，affected-rows在不同情况下的值不同，下面以插入单条记录为例
- 当数据成功插入时（没有冲突），affected-rows=1（仅有insert一个操作，插入n条记录就是n）
- 当数据发生冲突并更新成功时，affected-rows=2（包含delete和update操作，插入n条记录就是2*n）
- 当数据发生冲突，更新内容与原数据一致时，affected-rows=0。若开启了CLIENT_FOUND_ROWS，affected-rows=1（冲突记录数量，n条记录冲突就是n）
在冲突update时，若表中存在自增字段，自增字段也会+1（不管affects-rows是否>0）
- 若id为自增，当前id为67，先执行一条有冲突的语句，此时自增id已经到68（原数据的id不会自增，只是自增id的计数器会+1）
- 再插入一条新的记录，此时新纪录的id为69

# 插入冲突数据，执行update，当前最新id自增
insert into user (user_id,user_name,score) values(2,'marry',87) on duplicate key update score = 87

# 插入一条新数据
insert into user (user_id,user_name,score) values(3,'kiti',99)

id【自增主键】	user_id【唯一键】	user_name	score
66	1	tom	97
67	2	marry	95
69	3	kiti	99

使用场景：

数据时常变更，需要定时同步到mysql中，主键/唯一键一般不会修改或较少修改时，该方法可以代替delete + insert。
- 比如学生最新成绩表，需要同步最新一次考试的分数，学生id主键一般不会变更频繁，利用on duplicate key刷新学生分数
表中某些字段需要插入以及阶段性变更时，可以利用insert into ... on duplicate key 代替insert和update两种操作
- 如订单记录表，生成订单需要插入表，同时需要阶段性的修改订单状态(下单、付款、完成等等)，订单记录id又是唯一键，此时可以利用该方法实现两种业务操作
待补充......

参考mysql5.7官方文档：MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Statement