打开mysql文档,总会有新发现。——碧安瑶

我们在使用INSERT语句时,有时会有这样的需求,不存在就新增,存在就更新

此时我们可以使用INSERT ... ON DUPLICATE KEY UPDATE语句

就像mysql官方文档中提到的那样,我们如果将a列设为UNIQUE唯一索引或者主键时,并且当前表已经存在了a=1的数据,对于这种情况,下面两条sql的结果是相等的

当然如果不满足上方条件,则会新增一条数据

1
2
3
4
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

对于InnoDB引擎的表,此处的新增可能会触发自增列,但修改操作不会触发

如果上方的唯一索引再加一个b列,则上方第一条sql和下方sql结果相等

1
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

注意如果此处a=1 or b=2匹配多行,则只会更新一行,所以我们需要避免在有多个唯一索引的表上使用本语法

我们在表内没有满足条件的数据时执行该sql,返回影响行数为1

image-20220206145004474

我们再次执行

可以看到影响行数为2

image-20220206145033807

这说明新增操作返回1,修改操作返回2

但如果我们修改的值没有变化,则为0,例如:

1
2
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c,b=b;

image-20220206150424175

如果使用 mysql_real_connect()来连接mysql,修改的值没有变化时,还是返回1

并且如果我们触发了自增,也可以使用 LAST_INSERT_ID()函数获取自增后的值

我们还可以在ON DUPLICATE KEY UPDATE后方使用VALUES函数来获取上方INSERT语句中某列的值例如

1
2
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

上面的sql和下面这条sql执行结果也是相同的

1
2
3
4
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;

从 MySQL 8.0.20开始,不推荐使用 VALUES ()来引用新的行和列,并且在将来的 MySQL 版本中可能会删除。相反,应该使用行和列别名

Mysql8.0.19之后,我们可以给表和列取别名,例如:

1
2
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS table1
ON DUPLICATE KEY UPDATE c = table1.a+table1.b;

以及

1
2
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS table1(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;

就先写这么多吧