MySQL：如何让含NULL值的两列唯一索引限制重复NULL插入？

MySQL的唯一索引默认将NULL视为“不同值”，因此多条(name='x', date=NULL)的记录不会触发唯一约束。以下是几种可行的解决方法：

方法一：使用函数索引 #

通过IFNULL()函数将所有NULL值转换为一个业务中不会使用的固定值，让NULL被视为同一个值纳入约束：

CREATE UNIQUE INDEX name_date_unique ON `local-db`.device(name, IFNULL(date, '1970-01-01'));

注意：请替换'1970-01-01'为你业务中绝对不会出现的日期值，避免和真实数据冲突。

方法二：将date字段设为NOT NULL #

从根源上消除NULL值的存在，需要修改表结构并处理现有数据：

1. 先更新表中已有的NULL值：

UPDATE `local-db`.device SET date = '1970-01-01' WHERE date IS NULL;

2. 修改date字段为NOT NULL并设置默认值：

ALTER TABLE `local-db`.device MODIFY COLUMN date DATE NOT NULL DEFAULT '1970-01-01';

3. 重建唯一索引（若原索引已存在，先删除再创建）：

DROP INDEX name_date_unique ON `local-db`.device;
CREATE UNIQUE INDEX name_date_unique ON `local-db`.device(name, date);

这种方法适合允许调整字段约束的场景，彻底解决NULL值带来的约束失效问题。

方法三：使用虚拟列（Generated Column） #

创建自动处理NULL的虚拟列，基于该列和name创建唯一索引，无需修改原字段的属性：

1. 添加存储型虚拟列：

ALTER TABLE `local-db`.device ADD COLUMN date_normalized DATE GENERATED ALWAYS AS (IFNULL(date, '1970-01-01')) STORED;

2. 创建唯一索引：

CREATE UNIQUE INDEX name_date_unique ON `local-db`.device(name, date_normalized);

虚拟列会自动同步原date字段的变化，NULL值会被统一转换，既保留原字段允许NULL的特性，又实现了约束需求。

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