mysql：如何实现插入或更新一条数据（对比 replace into 和 insert on duplicate key update）

环境：

• window10
• mysql 8.0.25
• DBeaver

参考：
《mysql：13.2.9 REPLACE Statement》
《mysql：13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement》
《博文：MySQL的INSERT ··· ON DUPLICATE KEY UPDATE使用的几种情况》
《博文：MYSQL中replace into的用法》

1. 常见问题：如何一条sql语句实现插入或更新？

在sqlserver中我们可以用 merge关键字实现（《sqlserver：merge关键字用法》）。

那么，在mysql中怎么实现呢？

mysql实现的功能稍微要弱一点，但也一般可用。
在sqlsever中我们可以根据直接where条件，当匹配到后就执行update，匹配不到就执行insert，但在mysql中我们不能写where条件，只能自动使用表的主键或唯一索引。

下面就看两种语法：replace into 和 insert on duplicate key update。

2. replace into 能满足需求吗？不能！

其实这个语句不能使用插入或更新。
它的功能是替代，即：如果有的话就先删除，然后再插入。

看下面的例子：

create table test(
	id int primary key,
	name varchar(50),
	uno varchar(50) unique,
	addr varchar(50)
)
insert into test(id,name,uno,addr) values(1,'小明','001','天明路'),(2,'小红','002','天明路2'),(3,'小刚','003','天明路3');

-- 因为不存在,这里执行插入
replace into test(id,name,uno,addr) values(4,'小军','004','天明路4')
select * from test

此时的表数据如下：

下面执行replace语句：

-- 主键和unique索引都重复了，这将导致删除两条数据后再插入一条，影响3条数据
replace into test(id,name,uno,addr) values(1,'替换两个','002','天明路222')
select  * from test

可以看到，上面删除了2条数据，只插入了一条，它不能满足我们的需求。

3. insert on duplicate key update 可以满足功能

从字面上看，这哥语句时先尝试插入，当遇到重复主键或索引的时候进行update操作。应该是满足我们需求的。

先看下面的示例：

create table test(
	id int primary key,
	name varchar(50),
	uno varchar(50) unique,
	addr varchar(50)
)
insert into test(id,name,uno,addr) values(1,'小明','001','天明路'),(2,'小红','002','天明路2'),(3,'小刚','003','天明路3');
select * from test

此时数据：

之心插入或更新：

-- 插入时故意让主键和unique索引都重复 观察效果
insert into test(id,name,uno,addr) values(2,'小红2','001','天明路55') ON DUPLICATE KEY update  name='56'
select * from test

观察数据：

可以看到，插入时，虽然主键和唯一索引uno列都重复了，但mysql以主键为主。
另外，mysql更新的时候只会作用于一条重复的行，这在mysql文档上有说明：

但mysql文档上并没有说明，当主键和其他唯一索引都冲突的时候以谁为主。
不过我试验了多次得到的结果都是以主键为主！

4. 总结

如果我们想实现插入或更新一条数据的话，我们可以约定这个表必须存在主键，然后使用 insert on duplicate key update 实现。