我要评论前言
你可能听说过这样一句话：
“在 mysql 中，unique 索引允许存在多个 null 值，但只允许一个空字符串。”
这句话的结论是对的，但表述不够准确——它只说了"是什么"，没说"为什么"，容易让人误以为这是 mysql 对 null 的特殊照顾，或者是某个需要死记硬背的规则。
实际上，理解了 null 的本质语义，这个行为就是理所当然的结果。
本文从根源出发，彻底讲清楚这个问题。
一、先搞清楚 null 和 “” 到底是什么
在深入 unique 索引之前，必须先把这两个概念分清楚，很多人在这里就已经混淆了。
null —— 未知 / 不存在
null 不是一个值，它表示的是**“这个字段的值未知"或"这个字段不存在”**。
patient_user 表中： email = null → 这个患者没有填邮箱，我们不知道他有没有邮箱 email = '' → 这个患者填了邮箱，填的内容是空的 email = 'a@b.com' → 这个患者的邮箱是 a@b.com
这三种状态在业务语义上是完全不同的三件事，不能混为一谈。
“” —— 空字符串，一个确定的值
空字符串 "" 是一个真实存在的值，只是内容为空。它和 "hello"、"123" 一样，是一个确定的字符串值，只不过长度为 0。
💡 生活比喻：
null就像一张没有填写姓名栏的表格，我们不知道这个人叫什么。""就像一张姓名栏被故意留空的表格，这个人知道有姓名栏，但选择什么都不填。"张三"就是正常填写了姓名。
二、unique 索引的判断逻辑
unique 索引的核心工作是：判断新插入的值是否已经存在于索引中。
判断的依据只有一个：值是否相等（=）。
这里就引出了 null 行为不同的根本原因。
null 的比较规则：null != null
这是 sql 标准（iso/iec 9075）中明确规定的：
任何涉及 null 的比较运算，结果都不是 true 或 false，而是 unknown。
select null = null; -- 结果：null（不是 true！） select null != null; -- 结果：null select null = ''; -- 结果：null select '' = ''; -- 结果：1（true）
你可以直接在 mysql 里执行上面的语句验证。
为什么 null = null 不等于 true？
因为 null 代表"未知"，两个"未知"之间根本无法比较：
问：这个患者的邮箱 == 那个患者的邮箱吗？ 答：不知道，因为两个都不知道是什么。
就像你问"这个盒子里的东西和那个盒子里的东西一样吗"，两个盒子都没打开，无从回答，只能说"未知"。
对 unique 索引的影响
unique 索引在插入数据时，会用 = 来判断新值是否和已有值冲突：
新值 = 已有值 → true → 冲突，拒绝插入 新值 = 已有值 → false → 不冲突，允许插入 新值 = 已有值 → unknown → ???
当结果是 unknown 时，mysql 遵循 sql 标准的处理方式：无法确认冲突，视为不冲突，允许插入。
这就是为什么多个 null 可以共存的根本原因——不是 mysql 网开一面，而是 null 根本无法被判断为"相等"，unique 索引自然无从拦截。
而空字符串 "" 是一个确定的值，"" = "" 的结果是 true，第二个空字符串插入时，unique 索引能明确判断出冲突，直接报错。
三、动手验证
建表
create table test_unique (
id int auto_increment primary key,
email varchar(100),
unique key uk_email (email)
);
测试 null
insert into test_unique (email) values (null); -- ✅ 成功，id=1 insert into test_unique (email) values (null); -- ✅ 成功，id=2（第二个 null 也能插入！） insert into test_unique (email) values (null); -- ✅ 成功，id=3（第三个也没问题）
测试空字符串
insert into test_unique (email) values ('');
-- ✅ 成功，id=4
insert into test_unique (email) values ('');
-- ❌ 报错！
-- error 1062 (23000): duplicate entry '' for key 'uk_email'
查看当前数据
select * from test_unique; -- 结果： -- id | email -- 1 | null -- 2 | null -- 3 | null -- 4 | （空字符串）
三个 null 和平共处，但第二个空字符串直接被拦截，结论得证。
四、这会带来哪些实际问题？
问题一：password 字段用空字符串代替"未设置密码"的隐患
看这张表的 password 字段设计：
`password` varchar(100) not null default '' comment '密码，用空字符串代表未设置密码'
如果 password 字段加了 unique 约束（虽然一般不会），那么第二个"未设置密码"的用户就会插入失败。这个场景虽然极端，但说明了一个原则：
⚠️ 用空字符串代表"无值"状态，是有风险的设计。null 才是表达"无值"的语义正确选择。
问题二：email 字段加 unique 约束的陷阱
回到本文开头的场景，患者表的 email 字段：
`email` varchar(100) default null comment '电子邮箱', unique key `uk_email` (`email`)
由于 email 允许 null，多个患者不填邮箱没问题，可以共存。
但一旦有患者填了邮箱，就不能和任何人重复。在医疗场景里，一家人可能共用一个邮箱，这会导致第二个家庭成员注册时报唯一冲突错误，体验极差。
结论：对于"仅作为联系方式"而非"登录凭证"的邮箱字段，不应该加 unique 约束。
✅ 需要 unique：邮箱是登录账号（saas系统、企业后台） ❌ 不需要 unique：邮箱只是联系方式（医疗患者表、电商收货信息）
问题三：查询 null 不能用 =
既然 null 的比较结果是 unknown，那查询 null 值也不能用普通的等号：
-- ❌ 查不到任何结果，因为 null = null 结果是 unknown，不是 true select * from test_unique where email = null; -- ✅ 正确写法：用 is null select * from test_unique where email is null; -- ✅ 查非 null 值：用 is not null select * from test_unique where email is not null;
这是一个非常常见的 bug 来源，很多初学者在 where 条件里写 = null 然后纳闷为什么查不到数据。
五、三值逻辑（three-valued logic）
上面提到 null 的比较结果不是 true/false，而是 unknown，这背后是 sql 标准引入的三值逻辑：
| 逻辑值 | 含义 |
|---|---|
| true | 真 |
| false | 假 |
| unknown | 未知（涉及 null 的比较） |
where 条件只有结果为 true 时才返回该行，结果为 false 或 unknown 都不返回：
-- 假设 email 为 null where email = 'a@b.com' -- unknown → 不返回 where email != 'a@b.com' -- unknown → 不返回（！很多人没想到这个） where email is null -- true → 返回 where email is not null -- false → 不返回
💡 反直觉的地方：
where email != 'a@b.com'也查不到 email 为 null 的行！因为结果是 unknown，不是 true。
六、null 的正确使用姿势
适合用 null 的场景
-- 可选填的字段，不填就是真的不知道/不存在 `email` varchar(100) default null -- 未填邮箱 `birthday` date default null -- 未填生日 `avatar` varchar(255) default null -- 未上传头像
不适合用 null 的场景
-- 有业务含义的状态，不应该用 null 来表示 -- ❌ 错误：用 null 表示"未设置密码" `password` varchar(100) default null -- ✅ 正确：用空字符串表示"未设置密码"（如本表的设计） `password` varchar(100) not null default '' -- ❌ 错误：数值类统计字段用 null `available_points` int default null -- ✅ 正确：数值类字段给 0 作为默认值 `available_points` int not null default '0'
null 在聚合函数中的行为
-- null 会被 count、sum、avg 等聚合函数自动忽略 select count(email) from patient_user; -- 只统计非 null 的行 select count(*) from patient_user; -- 统计所有行（包括 email 为 null 的） select avg(weight) from patient_user; -- null 的 weight 不参与计算
⚠️ 陷阱：
count(字段名)和count(*)的结果可能不同，当字段有 null 值时，前者会少统计。
七、总结
| 对比维度 | null | “” 空字符串 |
|---|---|---|
| 语义 | 未知 / 不存在 | 确定的空值 |
| 存储 | 不占用字段存储空间（用标志位） | 占用存储空间（长度为0的字符串） |
| unique 索引 | ✅ 允许多个共存（null != null） | ❌ 只允许一个（“” == “”） |
| 查询方式 | is null / is not null | = '' / != '' |
| 参与聚合计算 | ❌ 被自动忽略 | ✅ 参与计算 |
| 适用场景 | 可选字段、真正不存在的值 | 有明确"空"语义的必填字段 |
一句话总结
🎯 null 不是值，是状态。unique 索引用
=判断冲突，而null = null的结果是 unknown，无法判断冲突，所以多个 null 能共存。空字符串是确定的值，"" = ""是 true，冲突立刻被拦截。搞清楚这一点，所有行为都是理所当然的。
到此这篇关于mysql unique索引中null和““的行为为什么不一样的文章就介绍到这了,更多相关mysql unique索引null内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论