第十五讲的扩展:insert...on duplicate key update

89 阅读3分钟

作用

首先说明一下这个语句的作用

在mysql入库时,不能出现两条数据主键一致的情况,因为在两条数据的主键一致的情况下,mysql就会判定为待插入数据在数据库中存在重复数据,也就是说判断数据是否重复是根据主键来区别的。这个语句会在存在的情况下更新,不存在的情况下添加

当然你也可以不使用这个语句,但是那样的话,就需要在插入之前判断,也就是类似于

A a = select * from A where A.c=?;
if(a = null)
  insert ....
else
  update...

抛开性能问题。这样的最大问题是语句不能保证原子性,所以需要锁机制才能保证在并发下不会出现问题

范例

表结构

create table test (
id int4 primary key auto_INCREMENT,
user_no varchar(20) ,
user_name varchar(30),
user_age int4 ,
update_time date,
unique key test1 (user_no,update_time)
);

执行的语句

## 第一次调用(没有冲突,直接插入)
insert into test(user_no,user_name,user_age,update_time) values('K1808D693','小小苏',26,curdate()) 
on duplicate key update user_age=values(user_age);

id	user_no	         user_name	user_age	update_time
1	K1908D693	 小小苏	     26	             2023-02-21

# 第二次调用(有冲突,更新)
insert into test(user_no,user_name,user_age,update_time) values('K1908D693','小小苏',28,curdate()) 
on duplicate key update user_age=values(user_age);
id	user_no	         user_name	user_age	update_time
1	K1908D693	 小小苏	     28	             2023-02-21

# 第三次调用(无冲突插入,观察 id 键值,出现了很多丢失,id 直接跳到了 3insert into test(user_no,user_name,user_age,update_time) values('K1808D693','小小苏',28,curdate()-1) 
on duplicate key update user_age=values(user_age);
id	user_no	         user_name	user_age	update_time
3	K1808D693	 小小苏	     28	             2023-02-20

总结

insert...on duplicate key update语法的作用,可以分析到,当发生主键冲突的时候,可以直接进行update操作,这个update操作里面可以更新任意想要更新的列;而没有主键冲突的时候,相当于对这个表进行了一次插入操作。

Replace操作和 insert...on duplicate比较

Replace语句。使用Replace插入一条记录时,如果不重复,Replace就和Insert的功能一样,如果有重复记录,Replace就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将Delete和Insert合二为一,形成一个原子操作。这样就可以不必考虑在同时使用Delete和Insert时添加事务等复杂操作了。

在使用Replace时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则Replace就和Insert完全一样的。

在执行Replace后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了Delete删除这条记录,然后再记录用Insert来插入这条记录。

不同之处

有了上面的知识储备,这两条命令的不同之处就显而易见了,也就是表现在update的执行细节上的不一样。replace是删除记录,然后再重新insert,而insert...on duplicate key update是直接在该条记录上修改,所以二者的差别主要有以下两处:

1、当表中存在自增值的时候,且是更新表中存在某条记录,replace语法会导致自增值+1,而insert...on duplicate key update语法不会;

2、当表中的某些字段中包含默认值的时候,replace操作插入不完全字段的记录,会导致其他字段直接使用默认值,而insert...on duplicate key update操作会保留该条记录的原有值。