MySQL数据库实战教程-52-唯一性约束

发布网友发布时间：2022-04-13 03:28

共1个回答

懂视网时间：2022-04-13 03:56

这里记录的是很久之前的一个 bug 了，主要给大家介绍了关于MySQL中唯一性约束与NULL的相关资料，文中介绍的非常详细，对大家具有一定的参考学习价值，需要的朋友们下面来一起看看吧。

前言

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

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

这时 A,B,C 三列都是不允许 NULL 值的，唯一性约束也是 work 的。

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

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

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

蓝后。。。就出问题了。加了 D 之后，唯一性约束基本就失效了。

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

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

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

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 中认为 NULL 代表着“未知”。在 SQL 中，任何值与 NULL 的比较返回值都是 NULL 而不是 TRUE, 就算 NULL 与 NULL 的比较也是返回 NULL。

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

MySQL 官网上也有蛮多人讨论过这个问题，一部分人认为这是 MySQL 的 bug，另一部分则认为是一个 feature，附上链接。

MySQL Bugs: #8173: unique index allows duplicates with null values

总结