MySQL允许在唯一索引字段中添加多个NULL值

今天正在吃饭，一个朋友提出了一个他面试中遇到的问题，MySQL允许在唯一索引字段中添加多个NULL值。 这个问题对于我一个非专业DBA来说，也没特地去验证过，所以正好借此机会验证一下，做个记录：

测试环境：

数据库：MySQL5.7.25
数据库引擎：InnoDB
连接工具：Navicat Premium

首先，创建数据库，并创建测试表 test：

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL COMMENT 'ID',
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

然后，我们给name字段添加一个唯一索引：

ALTER TABLE `test` ADD UNIQUE ( `name`);

最后，我们尝试插入两条name字段为null的数据：

INSERT INTO `test` VALUES (1, NULL);
INSERT INTO `test` VALUES (2, NULL);

并没有报错，说明MySQL允许在唯一索引字段中添加多个NULL值。

数据表如下：

img

点击并拖拽以移动

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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

我们可以看出，此约束不适用于除BDB存储引擎之外的空值。对于其他引擎，唯一索引允许包含空值的列有多个空值。

网友给出的解释为：

在sql server中，唯一索引字段不能出现多个null值 在mysql 的innodb引擎中，是允许在唯一索引的字段中出现多个null值的。 根据NULL的定义，**NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。**根据这个定义，多个NULL值的存在应该不违反唯一约束，所以是合理的，在oracel也是如此。

这个解释很形象，既不相等，也不不等，所以结果未知。

至于，sqlserver和MySQL其他引擎还有待验证。

Q.E.D.