MySQL INSERT..ON DUPLICATE中VALUES()与IF()结合的异常行为解析

问题场景 #

先创建测试表：

CREATE TABLE `test` (
  `id` int unsigned NOT NULL DEFAULT '0',
  `v` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

第一个执行语句符合预期：

mysql> insert into test set id=1, v=if(1 > v, 1, v) on duplicate key update v=values(v);
Query OK, 2 rows affected, 1 warning (0.02 sec)

mysql> select * from test;
+----+---+
| id | v |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

但执行第二个语句时，结果不符合预期：

mysql> insert into test set id=1, v=if(0 > v, 0, v) on duplicate key update v=values(v);
Query OK, 2 rows affected, 1 warning (0.01 sec)

mysql> select * from test;
+----+---+
| id | v |
+----+---+
|  1 | 0 |
+----+---+
1 row in set (0.00 sec)

原本预期现有v=1时，if(0 > v, 0, v)会保留v=1，v=values(v)也不会改变值，但实际v被更新为0。

核心原因解释 #

问题出在INSERT子句中引用列名的指向对象：

• 在INSERT阶段，MySQL尚未检查重复键冲突，此时if(0 > v, 0, v)里的v，是新插入行的默认值（这里v的默认值为0），而非表中已存在的行的v值。
• 因此第二个语句的INSERT部分，if(0 > 0, 0, 0)计算结果为0，VALUES(v)在ON DUPLICATE KEY UPDATE中取的就是这个计算后的0，最终将现有行的v更新为0。

对比第一个语句：INSERT阶段的if(1 > v, 1, v)中，v是新行的默认0，1>0成立，所以v被计算为1，要么插入新行，要么冲突时更新为1，符合预期。

官方文档说明（翻译） #

在ON DUPLICATE KEY UPDATE子句的赋值表达式中，可使用VALUES(col_name)函数引用INSERT部分的列值。换句话说，ON DUPLICATE KEY UPDATE子句中的VALUES(col_name)指的是若无重复键冲突时会插入的col_name的值。

正确写法（基于现有行值判断） #

如果想要基于表中已存在的行的v值进行判断，需要把IF逻辑放到ON DUPLICATE KEY UPDATE子句中，此时引用的v就是现有行的值：

insert into test set id=1, v=0 on duplicate key update v=if(0 > v, 0, v);

内容的提问来源于stack exchange，提问作者Ketu