MySQL的on duplicate key update 使用说明与总结

曾经终败给现在 2023-10-04 17:32 47阅读 0赞

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

**1，on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。**  
**2，记录已存在时，只会更新on duplicate key update之后指定的字段。**  
**3，如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。**  
**4，特殊用法：参考第五、第六条。**

**一，构造测试数据**

注意里面的唯一键

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 current\_timestamp on update current\_timestamp  
) comment '测试表';

insert into tbl\_test(name,age,address) values('huahua',20,'京华市'),('caocao',21,'京海市');

效果如下：

![aa384e1fbbf7e5520ddd416737f2280b.png][]

**二，sql用法介绍1**  
on dupdate key update 语句基本功能是：**当表中没有原来记录时，就插入，有的话就更新。**

如下sql：  
insert into tbl\_test(id,name,age,address) values(1,'huahua1',201,'京华市1')  
on duplicate key update  
age = values(age),  
address = values(address);

![ce4365af26a6105624caf0b8c25912c3.png][]

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

**三，sql用法介绍2**  
如下sql：  
insert into tbl\_test(name,age,address) values('huahua',202,'京华市2')  
on duplicate key update  
age = values(age),  
address = values(address);

![21f7cc3e1d8277a1221b0e267bf9d98e.png][]

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

**三，sql用法介绍3**  
如下sql：  
insert into tbl\_test(name,age,address) values('huahua2',202,'京华市2')  
on duplicate key update  
age = values(age),  
address = values(address);

![4a7ed4ad1d03c20c95aed4bb01196711.png][]

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

**四，sql用法介绍4**  
如下sql：  
insert into tbl\_test(id,name,age,address) values(1,'huahua1',202,'京华市2')  
on duplicate key update  
name = values(name),  
age = values(age),  
address = values(address);

![e1a75afe055cf0d84e3410e5f93d14ff.png][]

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

**五，on dupdate key update之后没有用values的情况**  
分为两种情况：  
1，如果为如上面的name = "abc"，则会一直更新为"abc".  
2，如果为如上面的name = name ，则name会保持数据库中的值，不会更新。

**六，对values使用判断**  
如下sql  
insert into tbl\_test(id,name,age,address) values(1,'huahua1',202,'京华市2')  
on duplicate key update  
name = ifnull(values(name),name),  
age = values(age)  
达到的效果是，如果传入的name值为null，则不更新。不为null则更新。这里与mybatis配合使用比较好。

[aa384e1fbbf7e5520ddd416737f2280b.png]: https://img-blog.csdnimg.cn/img_convert/aa384e1fbbf7e5520ddd416737f2280b.png
[ce4365af26a6105624caf0b8c25912c3.png]: https://img-blog.csdnimg.cn/img_convert/ce4365af26a6105624caf0b8c25912c3.png
[21f7cc3e1d8277a1221b0e267bf9d98e.png]: https://img-blog.csdnimg.cn/img_convert/21f7cc3e1d8277a1221b0e267bf9d98e.png
[4a7ed4ad1d03c20c95aed4bb01196711.png]: https://img-blog.csdnimg.cn/img_convert/4a7ed4ad1d03c20c95aed4bb01196711.png
[e1a75afe055cf0d84e3410e5f93d14ff.png]: https://img-blog.csdnimg.cn/img_convert/e1a75afe055cf0d84e3410e5f93d14ff.png