MySQL 中 NULL 导致唯一键失效

ghimi-g

已于 2024-09-02 11:31:32 修改

阅读量2.8k

点赞数

文章标签： mysql 数据库 database

于 2022-02-28 11:11:10 首次发布

本文链接：https://blog.csdn.net/weixin_36242811/article/details/123176171

版权

本文探讨了在MySQL中，NULL值如何影响唯一键约束。当字段包含NULL时，唯一约束可能失效，允许重复记录。解决办法是避免在唯一约束字段上使用NULL，或者将NULL转换为空字符串。同时，文章解释了MySQL中NULL的特殊性质，两个NULL值被视为不相等，因此在唯一索引中可以共存。此外，还介绍了主键索引与唯一索引的区别。

摘要由CSDN通过智能技术生成

最近遇到一个问题，我为一张表中创建了一个唯一键，并且键中字段为NULL，最终导致了唯一约束失效。这里做下分析：

首先新建一张表，包含 work_no,name,age 三个字段：

DROP TABLE IF EXISTS t_emp;
CREATE TABLE t_emp(
  id int(8) not null auto_increment,
  work_no varchar(8) comment  '工号',
  name varchar(255) comment '姓名',
  age int(3) comment '年龄',
  primary key(id),
  unique key(work_no,name,age)
)engine=InnoDB,charset=UTF8mb4,comment="员工表";

从创建语句中我们可以看到，我们为工号，姓名，年龄上添加了唯一约束，现在我们尝试插入数据：

insert into t_emp(work_no,name,age) values ('9527','张三',23);
insert into t_emp(work_no,name,age) values ('9527','张三',23);

我们在尝试插入第二条数据时会报错：

(1062, “Duplicate entry ‘9527-张三-23’ for key ‘work_no’”)

可见唯一键是生效了的，现在我们尝试将 age 为 null 再次尝试下：

insert into t_emp(work_no,name,age) values ('9527','张三',NULL);
insert into t_emp(work_no,name,age) values ('9527','张三',NULL);

可以看到当 age 为空时附加在该字段上的唯一约束失效了。

首先我们可以确认 null 值的出现破坏了唯一性，原本应该被唯一约束限制到的记录被插入到了表中。

如何确保 `NULL` 不破坏唯一性？

不允许列出现 NULL 值，即NOT NULL
给列增加 DEFUALT 值，注意 DEFAULT 不能是 NULL

所以需要调整建表语句：

DROP TABLE IF EXISTS t_emp;
CREATE TABLE t_emp(
  id int(8) not null auto_increment,
  work_no varchar(8) NOT NULL DEFAULT '' comment  '工号',
  name varchar(255) NOT NULL DEFAULT '' comment '姓名',
  age int(3) NOT NULL DEFAULT 0 comment '年龄',
  primary key(id),
  unique key(work_no,name,age)
)engine=InnoDB,charset=UTF8mb4,comment="员工表";

简单来说就是不要在唯一约束上使用 NULL,但是空字符串是可以的。

如果我们再继续寻找其根本原因的话，就要探究 NULL 在 MySQL 中的实现原理了。

深度探讨

关于NULL 值是否应该被唯一约束限制到的问题，早年有人向 MySQL 开发提出过问题。具体可以参考文档,可以看到开发者给出的解释是：
在这里插入图片描述
感兴趣的童鞋可以点进去看下，关于这个问题是否是一个BUG 的问题探讨的还是很激烈的。最终结果还是显而易见的，MySQL 保留了空值不受唯一约束的限制的这个特性。开发者通过调整空值为空字符串来处理该类异常问题。

NULL 与 NULL 不相等

还记得在 MySQL 中如何以字段值为 NULL 作为条件查询吗？

SELECT * FROM t_emp WHERE age IS NULL;

NULL 在 MySQL 中作为一个特殊的存在，我们无法使用平常使用的等值查询进行查询：

MySQL root@localhost:test> select null = null;
|| null = null ||
| <null> |
1 row in set
Time: 0.001s
MySQL root@localhost:test> SELECT NULL IS NULL;
|| NULL IS NULL ||
| 1 |
1 row in set
Time: 0.001s