MySQL 唯一索引遇到 NULL 为什么没拦住重复：从复现到生成列修复

线上用户表加了唯一索引，产品同学却反馈：有些账号明明还没有绑定邮箱，后台里出现了多条“空邮箱”的记录。第一眼看起来像索引没有生效，但我们不能直接把锅甩给数据库。MySQL 的唯一索引确实能拦住重复值，可它对 NULL 的处理和很多人的直觉不一样。

这篇文章按一次排查过程来走：先复现“唯一索引没有拦住多个空值”的现象，再看它为什么发生，最后把修复方案拆成三类。读完以后，你可以判断自己的业务到底应该允许多个 NULL，还是应该把空值也纳入唯一约束。

摘要

MySQL 唯一索引会限制相同的非空值，但多个 NULL 可以同时存在。如果业务要求“空值也只能出现一次”，需要把字段改成 NOT NULL、使用业务默认值，或通过生成列把 NULL 归一化后再建立唯一索引。

适合人群

• 遇到过“已经建唯一索引，但空值还是重复”的后端开发者。
• 正在设计用户表、租户表、第三方账号绑定表的同学。
• 需要把数据库约束和业务校验边界梳理清楚的团队。

问题现场：唯一索引为什么没拦住两个 NULL

我们先看一个很常见的表。业务希望邮箱唯一，于是给 email 建了唯一索引。用户刚注册时可能还没填邮箱，所以字段允许为空。

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(120) NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uk_email (email)
);

如果插入两个相同的非空邮箱，唯一索引会正常拦截：

INSERT INTO users (email, created_at)
VALUES ('a@example.com', NOW());

INSERT INTO users (email, created_at)
VALUES ('a@example.com', NOW());

第二条会失败，因为 a@example.com 已经存在。到这里一切都符合预期。

但如果邮箱是 NULL，情况会变成这样：

INSERT INTO users (email, created_at)
VALUES (NULL, NOW());

INSERT INTO users (email, created_at)
VALUES (NULL, NOW());

很多人第一次看到结果会愣一下：两条都能插入成功。下面这张图把这个问题链路画出来了：唯一索引存在，字段值是 NULL，插入成功，风险就暴露出来了。

先复现：同一列插入两条 NULL

我们用查询确认一下表里的真实数据：

SELECT id, email, created_at
FROM users
ORDER BY id;

你会看到两条 email 都是 NULL 的记录。这说明问题不是索引丢了，也不是 SQL 没走到数据库，而是唯一索引对 NULL 的语义本来就不是“只能出现一次”。

接着再插入两个相同的非空邮箱，就能形成对比：

INSERT INTO users (email, created_at)
VALUES ('b@example.com', NOW());

INSERT INTO users (email, created_at)
VALUES ('b@example.com', NOW());

这次第二条会被拒绝。这个对比很关键：唯一索引没有失效，它只是把 NULL 当成“不确定值”，不会把多个 NULL 判定为彼此重复。

定位原因：NULL 不是一个可比较的普通值

现在可以定位到原因了。SQL 里的 NULL 表示未知或缺失，不等同于空字符串，也不等同于数字 0。两个未知值并不能直接判断为相等，所以唯一索引允许多个 NULL 同时存在。

这件事本身没有对错，关键看业务语义。我们可以把需求分成三类：

真正容易出问题的是第三类。它看起来像“字段可空”，但业务又希望空值也被约束。此时只靠 UNIQUE KEY(email) 不够。

修复方案一：字段本来必填就改成 NOT NULL

如果业务规则明确要求邮箱必填，修复就不要绕远路。先清理历史空数据，再把字段改为 NOT NULL。

SELECT COUNT(*) AS empty_email_count
FROM users
WHERE email IS NULL;

如果结果不为 0，先决定这些历史数据怎么处理：补全、删除、迁移到待完善状态，或者拆到另一张表。不要直接改字段，否则变更会失败，也可能让业务状态变得更乱。

清理完成后再变更表结构：

ALTER TABLE users
    MODIFY email VARCHAR(120) NOT NULL;

这类方案最清晰，也最容易维护。但前提是业务真的不允许空邮箱。如果只是注册初期暂时没有邮箱，强行改成必填会把问题推给上游。

修复方案二：用生成列把空值归一化

现在看更细的场景：邮箱允许暂时为空，但同一个表里只允许有一条空邮箱记录。普通唯一索引拦不住多个 NULL，我们可以新增一个生成列，把 NULL 统一转换成固定哨兵值，再对这个生成列建立唯一索引。

CREATE TABLE users_guarded (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(120) NULL,
    email_key VARCHAR(140)
        GENERATED ALWAYS AS (COALESCE(email, '__NULL__')) STORED,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uk_email_key (email_key)
);

这里的关键点是 email_key。当 email 是非空值时，email_key 就等于邮箱本身；当 email 是 NULL 时，email_key 统一变成 __NULL__。唯一索引检查的是 email_key，第二条空邮箱就会被拦住。

验证一下：

INSERT INTO users_guarded (email, created_at)
VALUES (NULL, NOW());

INSERT INTO users_guarded (email, created_at)
VALUES (NULL, NOW());

第二条会因为 uk_email_key 冲突而失败。再插入两个相同的非空邮箱，同样会被唯一索引拦住。

INSERT INTO users_guarded (email, created_at)
VALUES ('c@example.com', NOW());

INSERT INTO users_guarded (email, created_at)
VALUES ('c@example.com', NOW());

这一步说明，生成列方案把“空值也参与唯一判断”这件事显式写进了数据库约束里，不再依赖每个入口都记得做同样的业务判断。

上线前检查：别只改表结构

表结构能兜底，但上线前还要查三个地方。

第一，历史数据是否已经合并

如果历史上已经有多条 NULL，直接新增生成列唯一索引会失败。先把冲突数据查出来：

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

这里会把多条 NULL 也查出来。处理策略要和产品语义一致：保留一条、补全邮箱、转移到待处理表，或者补一个明确状态字段。

第二，哨兵值不能和真实业务值冲突

__NULL__ 只是示例。实际项目里要选择一个不会被真实邮箱使用的值，或者增加输入校验，明确禁止用户提交这个特殊值。

第三，入口校验和数据库约束要一致

后端接口、管理后台导入、定时任务、数据修复脚本都可能写表。只在一个入口里判断“空邮箱不能重复”是不够的。数据库唯一索引负责最终兜底，应用层负责给出更友好的错误提示。

总结

这次问题的核心不是 MySQL 唯一索引失效，而是 NULL 的语义和普通值不同。普通唯一索引会拦住重复的非空值，但允许多个 NULL。

落地时可以按这条线判断：如果字段可选且空值不参与唯一判断，普通唯一索引就够；如果字段必填，改成 NOT NULL；如果空值也必须唯一，就用生成列或等价的归一化字段，把 NULL 转成可比较的固定值，再建立唯一索引。最后一定要先处理历史冲突数据，再上线约束变更。