MySQL中ON DUPLICATE KEY UPDATE的介绍与使用、批量更新、存在即更新不存在则插入

最新推荐文章于 2024-06-11 17:04:41 发布
最新推荐文章于 2024-06-11 17:04:41 发布
阅读量1w 收藏 73
点赞数 35
分类专栏： 数据库 文章标签： 存在即更新不存在则插入 批量插入批量更新 ONDUPLICATEKEY
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49114503/article/details/136479860
版权
本文详细介绍了MySQL的ONDUPLICATEKEYUPDATE语句的使用方法，包括根据主键或唯一键更新、mybatis集成示例，以及注意事项如大小写敏感、并发可能导致的id不连续和死锁问题。
摘要由CSDN通过智能技术生成

文章目录

有时候由于业务需求，可能需要先去根据某一字段值查询数据库中是否有记录，有则更新，没有则插入。这个时候就可以用到ON DUPLICATE KEY UPDATE这个sql语句了。

以下内容基于本地windows环境mysql:8.0.34进行讲解。

一、ON DUPLICATE KEY UPDATE的介绍

基本用法：ON DUPLICATE KEY UPDATE​​是一种MySQL的语法，它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。

用法总结
1：on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。
2：记录已存在时，只会更新on duplicate key update之后指定的字段。
3：如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。
4：唯一键大小写敏感时，大小写不同的值被认为是两个值，执行插入。参见下文中的大小写敏感问题

二、ON DUPLICATE KEY UPDATE的使用

准备表结构及测试数据， 注意：name是唯一键

drop table if exists tbl_test;
create table tbl_test(
	id int primary key auto_increment,
	name varchar(30) unique not null,
	age int comment '年龄',
	address varchar(50) comment '住址',
	update_time datetime default null
) comment '测试表';

insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()),('lisi',21,'武汉',now());

测试数据如下：
在这里插入图片描述

2.1、案例一：根据主键id进行更新

on dupdate key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。

如下sql：

insert into tbl_test(id,name,age,address,update_time) values(1,'zhangsan1',201,'杭州1','2024-03-05 15:59:35')
on duplicate key update
age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name
address = values(address),
update_time=now();

在这里插入图片描述

从执行结果可以看出，更新了id为1的age，address两个字段，而name字段没有修改生效。由此我们可以得出两个重要结论：
1：on duplicate key update 语句根据主键id来判断当前插入是否已存在。
2：已存在时，只会更新on duplicate key update之后限定的字段。

2.2、案例二：根据唯一索引进行更新（常用）

根据唯一索引进行更新是生产中比较常用的方式，因为id一般使用的是自增，很少会先把id查询出来，然后根据id进行更新。

如下sql：

insert into tbl_test(name,age,address) values('zhangsan',202,'杭州2')
on duplicate key update
age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name
address = values(address),
update_time=now();

在这里插入图片描述

从执行结果看，这次没有传id，但是age,address字段仍然更新了。
由此可以得出另一个结论：
3：on duplicate key update 语句也可以根据唯一键来判断当前插入的记录是否已存在。

2.3、案例三：没有主键或唯一键字段值相同就插入

如下sql：

insert into tbl_test(name,age,address) values('zhangsan3',203,'杭州3')
on duplicate key update
age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name
address = values(address),
update_time=now();

在这里插入图片描述
这条执行就比较简单了，没有主键或唯一键字段值相同，即判断当前记录不存在，新插入一条。

注意： 这里我们发现主键id并没有连续，直接从2变成了4，具体原理可见《MySQL数据库设置主键自增、自增主键为什么不能保证连续递增》

2.4、案例四：主键与唯一键字段同时存在

如下sql:

insert into tbl_test(id,name,age,address) values(1,'zhangsan4',204,'杭州4')
on duplicate key update
name = values(name),
age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name
address = values(address),
update_time=now();

在这里插入图片描述
从上面可以看出，连唯一键name也被修改了。结论：
4：如果传递了主键，是可以修改唯一键字段内容的。
这里要注意，如果这里的name修改为 lisi,zhangsan3
会报唯一键冲突的。可以自行尝试。

三、ON DUPLICATE KEY UPDATE的注意事项

3.1、on dupdate key update之后values的使用事项

如下sql:

insert into tbl_test(name,age,address,update_time) values('zhangsan4',205,'杭州5','2024-03-05 00:00:00')
on duplicate key update
age = age,
address = '杭州',
update_time=values(update_time);

在这里插入图片描述
on dupdate key update之后没有用values的情况
分为两种情况：
1：如果为如上面的address= “杭州”，则会一直更新为"杭州".
2：如果为如上面的age = age，则age会保持数据库中的值，不会更新。
3：只有当使用了values后，才会更新为上下文中传入的值

3.2、对values使用判断

如下sql

insert into tbl_test(id,name,age,address) values(1,'zhangsan',202,'杭州2')
on duplicate key update
name = ifnull(values(name),name),
age = values(age)

达到的效果是，如果传入的name值为null，则不更新。不为null则更新。这里与mybatis配合使用比较好。

3.3、唯一索引大小写敏感问题

思考这么一个问题：如上面name作为唯一索引，当name大小写敏感时且数据库中存储了name=“zhangsan” ，那么再插入name="ZHANGSAN"是更新还是新增？

1）：唯一索引大小写不敏感时
设置name字段为唯一索引且大小写不敏感

drop table if exists tbl_test;
create table tbl_test(
	id int primary key auto_increment,
	name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,
	age int comment '年龄',
	address varchar(50) comment '住址',
	update_time datetime default null
) comment '测试表';

insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now());
insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',21,'杭州1',now());

在这里插入图片描述
可以看到当字段为大小写不敏感时zhangsan跟ZHANGSAN被认为是同一个值，不能重复插入。

当数据库中name=zhangsan时且name字段大小写不敏感时，我们看一下name="ZHANGSAN"能否更新成功？

insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',22,'杭州2','2024-03-05 00:00:00')
on duplicate key update
age = values(age),
address = values(address),
update_time=values(update_time);

在这里插入图片描述
以上结果可以看出，当大小写不敏感时on duplicate key update是可以更新成功的，即认为是同一个值。

2）：唯一索引大小写敏感时
设置name字段为唯一索引且大小写敏感

drop table if exists tbl_test;
create table tbl_test(
	id int primary key auto_increment,
	name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin unique not null,
	age int comment '年龄',
	address varchar(50) comment '住址',
	update_time datetime default null
) comment '测试表';

insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now());
insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',21,'杭州1',now());

在这里插入图片描述
可以看到当字段为大小写敏感时zhangsan跟ZHANGSAN被认为是两个值，插入了两条记录。所以此时用on duplicate key update会执行新增操作

四、ON DUPLICATE KEY UPDATE与mybatis联合使用

4.1、写法一：与values()联合使用

注意：values后面的内容是表字段名称即带下划线，而不是实体类驼峰名称

如下sql: dept_id为主键或唯一索引

<insert id="replaceInto">
    INSERT INTO sys_dept(
        dept_id,
        parent_id,
        status,
        update_time) 
    VALUES
    <foreach collection="deptList" item="item" separator=",">
        (#{item.deptId},
        #{item.parentId},
        #{item.status},
        #{item.updateTime})
    </foreach>
    ON DUPLICATE KEY UPDATE
        parent_id=VALUES(parent_id),
        status=VALUES(status),
        update_time=VALUES(update_time)
</insert>

4.2、写法二：使用#{}

如下sql: dept_id为主键或唯一索引

<insert id="replaceInto">
    INSERT INTO sys_dept(
        dept_id,
        parent_id,
        status,
        update_time) 
    VALUES
    <foreach collection="deptList" item="item" separator=",">
        (#{item.deptId},
        #{item.parentId},
        #{item.status},
        #{item.updateTime})
    </foreach>
    ON DUPLICATE KEY UPDATE
    <foreach collection="deptList" item="item" separator=",">
	       parent_id =  #{item.parentId},
	       status = #{item.status},
	       update_time = #{item.updateTime}
    </foreach>
</insert>

五、ON DUPLICATE KEY UPDATE的缺点及坑

5.1、ON DUPLICATE KEY UPDATE每次更新导致id不连续

如下sql：

drop table if exists tbl_test;
create table tbl_test(
	id int primary key auto_increment,
	name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,
	age int comment '年龄',
	address varchar(50) comment '住址',
	update_time datetime default null
) comment '测试表';

insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()),('李四',21,'武汉',now());

在这里插入图片描述
执行on duplicate key update进行更新，然后再插入一条新的数据

insert into tbl_test(name,age,address,update_time) values('zhangsan',22,'杭州2','2024-03-05 00:00:00')
on duplicate key update
age = values(age),
address = values(address),
update_time=values(update_time);

insert into tbl_test(name,age,address,update_time) values('王五',23,'深圳',now());

在这里插入图片描述
可以看到id自增值从2直接变成了4，造成了id的不连续。

1.ON DUPLICATE KEY UPDATE每次更新导致id不连续原理：
mysql中有个配置值是innodb_autoinc_lock_mode。
innodb_autoinc_lock_mode中有3中模式,0,1和2,mysql5的默认配置是1,

  • 0是每次分配自增id的时候都会锁表.
  • 1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高
  • 2.没有仔细看,好像是很多的不保证…不太安全.

数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into … on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update

5.2、death lock死锁

经常看到网上说ON DUPLICATE KEY UPDATE会导致死锁，确实是存在这个可能的，不过由于目前没有特别好的方案，所以也只能使用这个sql语法了。在执行insert ... on duplicate key语句时，如果不对同一个表同时进行并发的insert或者update，基本不会造成死锁。即insert ... on duplicate key时尽量单线程串行进行新增或更新

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：
在这里插入图片描述

五月天的尾巴
关注
  • 35
    点赞
  • 踩
  • 73
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
深入mysql "ON DUPLICATE KEY UPDATE" 语法的分析
09-10
本篇文章是对mysql "ON DUPLICATE KEY UPDATE"语法进行了详细的分析介绍，需要的朋友参考下
MYSQL的REPLACE和ON DUPLICATE KEY UPDATE语句介绍解决问题实例
12-15
在对看看的后台进行排序的时候,遇到了一个像这样的需求,在电影表中有ID(主键自增)和orderby(排序字段) ,假设有十条数据id分别从1-10之间,对应的orderby也是从1-10之间,我现在想把id=9的数据移动到第三的位置(id=3)的这个位置,并且保证之前的数据排列顺序(即id=3的orderby=4,id=4的orderby=5…id=8的orderby=9),这样如果用循环的形式是可以解决数据的问题,但是这样操作数据库过程太多,现在就想用一条sql语句来解决这个问题. 下面来看看Mysql的REPLACE和INSERT … ON DUPLICATE KEY UPDATE REP
[mysql]mysql通过on duplicate key update实现批量插入或更新（csdn）————程.pdf
12-04
[mysql]mysql通过on duplicate key update实现批量插入或更新（csdn）————程
ON DUPLICATE KEY UPDATE 子句
Eucalyptus's blog
06-11 625
在上面这个例子中，如果尝试插入的用户名。
MySQL的on duplicate key update实现(批量)插入或更新操作
热门推荐
zijikanwa
09-29 2万+
文章目录一. 背景二. `on duplicate key update`概述三. `on duplicate key update`的使用1. 在MySQL中的使用2. 在MyBatis中的使用参考资料 一. 背景 背景：业务上经常有这样的需求场景，如果之前有这条数据，就做更新；如果没有，就做新增。 常用的处理方案：通过主键id或者其他唯一键判断DB中是否有这条数据，再判断调用insert或update语句。这样做逻辑处理起来比较复杂，降低代码效率，而且如果并发量高，可能会存在数据问题。 二. on
Mysql批量更新: on duplicate key update
l_s_r的博客
03-14 818
是MySQL中的一个特性，允许在插入数据时如果发现主键或者唯一索引重复时，直接更新数据，支持批量更新。如上所示：在插入语句的末尾添加“ON DUPLICATE KEY UPDATE” 并指定要更新的字段及其新值。，就执行UPDATE后面的操作，VALUES(column_name)引用原始插入数据中的值。执行完这条sql后，id为1,2,3的数据的age值分别更新为：18,19,17。数据库现有条件：id为主键，id为1,2,3数据都存在。
mysql根据条件批量插入更新(on duplicate key update)
铁柱的博客
09-26 9814
一、前言       我们在做批量插入的时候，经常需要根据唯一字段判断，唯一字段相同时则进行更新，唯一字段不同时则进行插入。一般来说我们都是采用on duplicate key update 写法，只是假如我们需要、在唯一字段相同，并且满足其他条件时才进行更新，那么该怎么写这个"where"条件呢？on duplicate key upd...
Mysql中Insert into xxx on duplicate key update问题
12-15
ON DUPLICATE KEY UPDATE` 是一个非常实用的特性，它允许你在尝试插入新记录时，如果插入的数据违反了唯一性约束（如UNIQUE索引或PRIMARY KEY），则自动更新已经存在的匹配行，而不是抛出错误。这个特性是MySQL特有...
mysql ON DUPLICATE KEY UPDATE语句示例
09-10
在MySQL数据库中，`ON DUPLICATE KEY UPDATE` 是一个非常实用的特性，它允许你在插入新数据时，如果遇到重复的唯一键值，自动执行更新操作，而不是抛出错误。这个功能大大简化了数据处理的流程，特别是当你需要在一...
mysql 存在该记录则更新，不存在则插入记录的sql
09-11
当你尝试插入一行数据到表中，如果这行数据违反了唯一性约束（即主键或唯一索引字段已存在），那么`ON DUPLICATE KEY UPDATE`会触发对已有记录的更新操作。语法如下： ```sql INSERT INTO table (column1, column2,...
深入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用法
vulgarOr的博客
11-27 2710
mysql新增时Duplicate entry，自动变为更新
mysql replace into与on duplicate key update区别，oracle merge into
qq_41482600的博客
04-26 1611
如果不存在，则插入；1、organ_no, branch_no, branch_name, created_by字段都不相同。如果不存在，则插入；如果已存在则更新（先删除再插入。修改branch_no 4为8，并修改branch_name为海南分行。而这个会根据唯一索引进行查询，其他普通列不做匹配（主键也是做匹配的）2、branch_name, created_by不同。除了唯一索引branch_no不同，其他列都相同。4、修改的值包括了唯一索引值branch_no。唯一索引: branch_no。
Mysql on duplicate key update用法及优缺点
wys0127的博客
08-03 2043
在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新，在刚碰到的时候，一般思路是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。
【创新未发表】Matlab实现秃鹰优化算法BES-Kmean-Transformer-LSTM组合状态识别算法研究.rar
最新发布
07-29
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
巡检python.mp4
07-29
巡检python.mp4
三个版本企业数字化转型合集（2001-2022）（全新整理)
07-29
1、资源内容地址：https://blog.csdn.net/2301_79696294/article/details/140383422 2、代码特点：今年全新，手工精心整理，放心引用，数据来自权威，相对于其他人的控制变量数据准确很多,适合写论文做实证用 ，不会出现数据造假问题 3、适用对象：大学生，本科生，研究生小白可用，容易上手！！！ 3、课程引用: 经济学，地理学，城市规划与城市研究，公共政策与管理，社会学，商业与管理 ## 数据指标说明 整理了基本上最常见的数字化转型的度量方式，全部都是目前为止最为常用的顶刊数据用法，具体数据说明如下（全部为2021最新版本） 一、吴非版本（2007-2021） 二、袁淳版本（2007-2021）（数据丰富） 三、张永珅版本 2001-2022
【SCI一区】Matlab实现蝗虫优化算法GOA-CNN-LSTM-Attention的风电功率预测算法研究.rar
07-29
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
vue3 element+ popover弹出框点击判断后显示和隐藏
05-31
在 Vue3 中使用 Element Plus 的 Popover 组件，与 Vue2 中的使用方式略有不同。以下是在 Vue3 中使用 Element Plus Popover 组件进行点击判断后显示和隐藏的具体步骤： 1. 在需要触发弹出框的元素上，添加 `@click` 事件监听器，例如： ``` <el-button @click="showPopover">点击弹出框</el-button> ``` 2. 在 `setup` 函数中定义一个 `ref` 变量，用于控制弹出框的显示和隐藏状态，例如： ``` import { ref } from 'vue' export default { setup() { const isPopoverVisible = ref(false) const showPopover = () => { isPopoverVisible.value = true } return { isPopoverVisible, showPopover } } } ``` 3. 在需要显示弹出框的元素上，添加 `v-popover` 指令，并绑定 `isPopoverVisible` 变量，例如： ``` <el-button v-popover:pop="{ visible: isPopoverVisible }">点击弹出框</el-button> ``` 4. 在弹出框组件上，添加 `v-model:visible` 指令，并绑定 `isPopoverVisible` 变量，例如： ``` <el-popover v-model:visible="isPopoverVisible"> <p>这是弹出框的内容</p> </el-popover> ``` 这样，在点击元素时，弹出框就会显示出来，再次点击元素时，弹出框就会隐藏起来。需要注意的是，在 Vue3 中需要使用 `v-popover:pop` 指令来绑定 Popover 组件，而 `v-model:visible` 指令用于控制弹出框的显示和隐藏状态。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值