当有需求,在插入新记录时,如果不存在就插入一条新记录,而存在时就更新原记录上的某些数据,这是可以使用on duplicate key update 语法。
一、使用:
demo如下:
首先创建表test
drop table if exists test;
create table test(
id int primary key auto_increment,
value1 varchar(45) not null default '',
value2 varchar(45) not null default '',
value3 varchar(45) not null default ''
);
在表上创建一个唯一索引
create unique index unq_value1_value2 on test(value1,value2);
先在数据库中插入一条数据
select * from test;
insert into test
(value1,value2,value3)
value
('123','123','123');
数据如下图:
之后可以再对表中插入同样的数据,并使用duplicate key on update语法
当出现重复key时, 将vakue3的值设置为’1234’,语句如下:
insert into test
(value1,value2,value3)
value
('123','123','123')
on duplicate key update
value3 = '1234';
执行完,查询表数据,显示如下:
可见,on duplicate key update语句生效
二、存在问题
1、但表中存在多个唯一索引时,如果插入数据对多个唯一索引都产生了冲突,且冲突不在同一条记录上时,因为mysql并不会返回所有的冲突行,仅会返回其中任意一条记录,将产生修改记录不确定都问题
冲突不在同一行,例子如下:
假设表中value1,value两个字段都有唯一索引,表中有两条记录如下:
id value1 value2
1 1 2
2 2 1
此时,执行一下语句:
insert into test
(value1,value2)
values
('1','1')
on duplicate key update
value3 = '1234'
此时,产生冲突的数据就为两条了,而mysql只会修改其中一条的记录
2、并发插入同一条数据时,将产生死锁
3、将造成主从不一致问题
参考博文如下:
博文