复合唯一索引(a,c)搭配INSERT ON DUPLICATE KEY UPDATE异常问题咨询

嘿，这个问题我之前帮好几个开发者排查过，核心其实卡在复合唯一索引对NULL值的特殊处理，再加上你大概率用了INSERT ... ON DUPLICATE KEY UPDATE语句，咱们一步步理清楚：

首先得明确两个关键细节：

1. 你肯定是用了INSERT ... ON DUPLICATE KEY UPDATE语法——如果没加这个子句，正常插入重复唯一键只会直接报错，根本不会触发更新操作。
2. MySQL（以及部分其他主流数据库）对复合唯一索引中NULL值的判定规则：虽然SQL标准里NULL = NULL是不成立的，但在复合唯一索引场景下，当非NULL列的值完全匹配，而NULL列都为NULL时，数据库会把这些条目判定为"重复键"。这和单列唯一索引允许多个NULL的逻辑完全不同！

举个最直观的例子：
假设你的表结构是这样的：

CREATE TABLE your_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  a INT NOT NULL,
  c INT NULL,
  FOREIGN KEY (a) REFERENCES related_table(id),
  UNIQUE KEY idx_a_c (a, c)
);

如果之前已经插入过一行：

INSERT INTO your_table (a, c) VALUES (5, NULL);

现在你再执行：

INSERT INTO your_table (a, c) VALUES (5, NULL) ON DUPLICATE KEY UPDATE c = c;

这时候就会触发更新而非插入，因为数据库认为(5, NULL)这个复合索引条目已经存在了。

根据你的实际需求，分两种情况处理：

• 如果你希望相同a值下允许存在多个c为NULL的行：
  • 可以把c列的NULL替换成一个业务上不可能出现的特殊值（比如-1），再构建复合唯一索引；
  • 或者放弃复合唯一索引，改用触发器来实现自定义的唯一性校验逻辑。
• 如果你确实希望相同a值下只能有一个c为NULL的行：
  • 当前的行为其实是符合预期的，你可以先执行查询确认是否已经存在对应条目：

    SELECT * FROM your_table WHERE a = [你插入的a值] AND c IS NULL;
    

  • 如果返回结果，说明确实存在重复索引，这时候INSERT ... ON DUPLICATE KEY UPDATE就会触发更新；如果误触发了更新，检查你的INSERT语句是否不小心带上了这个子句。

不同数据库对NULL在唯一索引中的处理差异很大，比如PostgreSQL里复合唯一索引的NULL会被视为不同值，但MySQL的规则就是复合索引里的NULL会被当作相同的重复键——这正是你遇到这个"插入变更新"问题的核心原因。

内容的提问来源于stack exchange，提问作者juan.benavides