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

文章目录

• 一、ON DUPLICATE KEY UPDATE的介绍
• 二、ON DUPLICATE KEY UPDATE的使用
• • 2.1、案例一：根据主键id进行更新
  • 2.2、案例二：根据唯一索引进行更新（常用）
  • 2.3、案例三：没有主键或唯一键字段值相同就插入
  • 2.4、案例四：主键与唯一键字段同时存在
• 三、ON DUPLICATE KEY UPDATE的注意事项
• • 3.1、on dupdate key update之后values的使用事项
  • 3.2、对values使用判断
  • 3.3、唯一索引大小写敏感问题
• 四、ON DUPLICATE KEY UPDATE与mybatis联合使用
• • 4.1、写法一：与values()联合使用
  • 4.2、写法二：使用#{}
• 五、ON DUPLICATE KEY UPDATE的缺点及坑
• • 5.1、ON DUPLICATE KEY UPDATE每次更新导致id不连续
  • 5.2、death lock死锁

有时候由于业务需求，可能需要先去根据某一字段值查询数据库中是否有记录，有则更新，没有则插入。这个时候就可以用到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());