MySQL中On duplicate key update的实现示例_Mysql

1/ on duplicate key update的简介

on duplicate key update是一种mysql的语法，它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。

2/ on duplicate key update用法总结

on duplicate key update语句根据主键id或唯一键来判断当前插入是否已存在。
记录已存在时，只会更新on duplicate key update之后指定的字段。
如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。
唯一键大小写敏感，大小写不同的值被认为是两个值，执行插入。

3/ on duplicate key update用法总结

3.1 根据主键id进行更新

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

insert into tb_student(id,name,age,address,update_time) values(1001,'jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
age=values(age),
address=values(address),
update_time =now();

从执行结果可以看出，更新了id为1001的age,address两个字段，而name字段没有修改生效。由此我们可以得出重要结论：

on duplicate key update 语句根据主键id来判断当前插入是否已存在。
已存在时，只会更新on duplicate key update之后限定的字段。

3.2 根据主键id进行更新

根据唯一索引进行更新是生产中比较常用的方式，因为id一般使用的是自增，很少会先把id查询出来，然后根据id进行更新。

insert into tb_student(name,age,address,update_time) values('jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
age=values(age),
address=values(address),
update_time =now();

从执行结果看，这次没有传入id,但是age,address字段仍然更新了。

on duplicate key update 语句也可以根据唯一键来判断当前插入的记录是否已存在。

3.3 没有主键或唯一键字段值相同就插入

insert into tb_student(name,age,address,update_time) values('jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
name =values(name),
age=values(age),
address=values(address),
update_time =now();

没有主键或唯一键字段值相同，既判断当前记录不存在，新插入一条。

3.4 主键与唯一键字段同时存在

insert into tb_student(id,name,age,address,update_time) values(1001,'jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
name =values(name),
age=values(age),
address=values(address),
update_time =now();

连唯一键name也被修改了

如果传递了主键，是可以修改为唯一键字段内容

4/ on duplicate key update注意事项

4.1 on dupate key update 之后values的使用事项

on dupdate key update之后没有用values的情况，只有当使用了values后，才会更新为上下文中传入值。

4.2 对values使用判断

达到的效果是，如果传入的name值为null,则不更新。不为null则更新。

4.3 唯一索引大小写敏感问题

唯一索引大小写不敏感时

4.4 on duplicate key update每次更新导致id不连续原理

mysql中每个配置值是innodb_autoinc_lock_mode

innodb_autoinc_lock_mode中有3中模式,0、1和2，mysql5的默认配置是1.

0是每次分配自增id的时候都会锁表.
1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高
2.没有仔细看,好像是很多的不保证…不太安全.

数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into … on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update

4.5 death lock死锁

在执行insert … on duplicate key 语句时，如果不对同一个表同时进行并发的insert或者update，基本不会造成死锁。既insert … on duplicate key时尽量单线程串行进行新增或更新

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上s（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上x（排他锁），最后进行update写入。