MySQL下使用on duplicate key update实现“有则更新,无则新增”操作

通常情况下,如果需要实现“有则更新,无则新增”这样的业务操作时,我们会先从数据库中根据某个条件(比如主键id)查询数据是否存在,然后再调用更新或者保存操作。但是在高并发下可能会带来某些问题:

1、性能花销:查询操作执行一次sql,更新或新增执行一次sql。

2、并发问题:假设2个请求同时插入1条数据,在查询阶段两个请求皆查询不到数据,都认为数据不存在,故两个请求都执行了插入操作,导致数据出现异常。

为了解决上述问题,我们可以使用on duplicate key update语句来实现,一个使用样例:

insert into t_user ( id, `name`, age ) 
value( 1, '小明', 18 )
on duplicate key update
`name` = values(name),
age = values(age)

1、在insert语句结尾加上on duplicate key update并指明需要进行更新的字段。

2、on duplicate key update只有在表有唯一索引(primary key或unique key)的情况下才有效。

3、values(col_name)函数意思是,取出当前插入语句中col_name字段对应的值。如上述语句中:values(name)的值为“小明”。

4、on duplicate key update即使执行的是更新操作,仍然会占用自增id序号。

5、上述语句的意思是:插入一条id为1,名字为小明,年龄18的记录。如果记录存在(id冲突)则更新名字和年龄字段


扩展:如果我们插入的时候需要和已经存在的数据进行比较判断该怎么实现?还是使用上述的例子:

1、插入时,如果数据已经存在,则给小明加1岁:

insert into t_user ( id, `name`, age ) 
value( 1, '小明', 18 )
on duplicate key update
age = age+1

即:引用已经存在的数据直接写字段名,引用本次插入的数据使用values函数。

2、插入时,如果数据已经存在,判断年龄大小,取年龄最大值:

insert into t_user ( id, `name`, age ) 
value( 1, '小明', 18 )
on duplicate key update
age = if(values(age)>age,values(age),age)

配合if函数使用即可。

3、插入多条数据时,on duplicate key update部分写法不变:

insert into t_user ( id, `name`, age ) 
value( 1, '小明', 18 ),
( 2, '小李', 20 )
on duplicate key update
age = if(values(age)>age,values(age),age)


支付宝搜索:344355 领取随机红包

如果文章对您有帮助的话,欢迎老板打赏↑↑↑(请我吃饭😋