Mysql必读MySQL中的唯一性约束与NULL详解

《Mysql必读MySQL中的唯一性约束与NULL详解》要点：
本文介绍了Mysql必读MySQL中的唯一性约束与NULL详解，希望对您有用。如果有疑问，可以联系我们。

前言MYSQL数据库

之前做的一个需求,简化描述下就是接受其他组的 MQ 的消息,然后在数据库里插入一条记录.为了防止他们重复发消息,插入多条重复记录,所以在表中的几个列上加了个唯一性索引.MYSQL数据库

CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A,B,C);

这时 A,C 三列都是不允许 NULL 值的,唯一性约束也是 work 的.
MYSQL数据库

后来由于需求的变化,修改了以前的唯一性约束,又多加了一列.(至于为什么加就不赘述了).MYSQL数据库

ALTER TABLE testTable
DROP INDEX IDX_UN_LOAN_PLAN_APP,ADD UNIQUE KEY `IDX_UN_LOAN_PLAN_APP` (A,C,D);

新加的 D 是类型是 datetime,允许为 NULL,默认值为 NULL.之所以默认值为 NULL,是考虑到不是所有记录都有这个时间的,如果强行设置一个 Magic Value (比如'1970-01-01 08:00:00‘)当做默认值,看起来很奇怪.MYSQL数据库

蓝后...就出问题了.加了 D 之后,唯一性约束基本就失效了.MYSQL数据库

Insert into testTable (A,D) VALUES (1,2,3,NULL); --- OK
Insert into testTable (A,NULL); --- OK

上面的三条 SQL 都是可以执行成功的,数据库中会有多条一样的记录.可按照我们以前的构想,在执行后两条 SQL 时 应该抛出 ‘Duplicate key' 的异常的.MYSQL数据库

后来查了一下,才发现其实 MySQL 官方文档上已经明确说了这一点,唯一性索引是允许多个 NULL 值的存在的：MYSQL数据库

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines,a UNIQUE index allows multiple NULL values for columns that can contain NULL.

从下表中也可以看出来不管是采用什么类型的存储引擎,在建立 unique key 的时候都是允许多个 NULL 存在的....
MYSQL数据库

细想想,其实也蛮合理,毕竟在 MySQL 中认为 NULL 代表着“未知”. 在 SQL 中,任何值与 NULL 的比较返回值都是 NULL 而不是 TRUE,就算 NULL 与 NULL 的比较也是返回 NULL.MYSQL数据库

所以只能 fix 了...解决办法也蛮简单粗暴的,直接把线上数据刷了一遍,将“1970-01-01 08:00:00”作为默认值,然后把那列改为不允许为 NULL 的了,咳咳.MYSQL数据库

MySQL 官网上也有蛮多人讨论过这个问题,一部分人认为这是 MySQL 的 bug,另一部分则认为是一个 feature,附上链接.MYSQL数据库

MySQL Bugs: #8173: unique index allows duplicates with null valuesMYSQL数据库

总结MYSQL数据库

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对编程之家PHP的支持.MYSQL数据库

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!