如何在MySQL 8.0.32中实现deleted_at为NULL时的name唯一约束

由于MySQL 8.0不支持PostgreSQL那种带WHERE条件的部分索引，你可以通过函数式唯一索引来实现需求，解决你之前联合索引失效的问题：

核心思路 #

MySQL中NULL不参与唯一约束的等值比较（多个(name, NULL)会被判定为不同条目），所以我们需要把未删除（deleted_at IS NULL）的记录映射成一个固定的非NULL值，已删除的记录映射成唯一值，这样就能保证未删除条目name的唯一性，同时允许已删除条目重复name。

具体实现SQL #

-- 创建函数式唯一索引，替代原PostgreSQL的部分索引
CREATE UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" 
ON "customer_group" ("name", IF("deleted_at" IS NULL, 0, "id"));

逻辑解释 #

• 当deleted_at IS NULL（未删除）时，索引的第二个字段固定为0，此时(name, 0)的组合必须唯一，直接实现了未删除条目name不能重复的约束。
• 当deleted_at IS NOT NULL（已删除）时，索引的第二个字段为记录唯一的id，此时(name, id)的组合天然不会重复，就算多条已删除记录name相同，也不会触发唯一约束。

为什么之前的联合索引无效 #

你尝试的(name, deleted_at)联合索引失效，是因为MySQL对NULL的处理规则：在唯一约束中，NULL被视为“未知值”，多个(name, NULL)的记录不会被判定为重复，所以无法约束未删除条目name的唯一性。

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