I was having dinner today, and a friend raised a question he encountered in the interview. MySQL allows multiple NULL values to be added to the unique index field.

For me, a non-professional DBA, I have not specifically verified this problem, so I just take this opportunity to verify it and make a record:

test environment:

Database: MySQL5.7.25

Database engine: InnoDB

Connection Tool: Navicat Premium

First, create a database and create a test table 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 'name',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

Then, we add a unique index to the name field:

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

Finally, we try to insert two pieces of data whose name field is null:

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

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

No error is reported, indicating that MySQL allows multiple NULL values to be added to the unique index field.

The data table is as follows:

The explanation given by MySQL's official documentation is:

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.

We can see that this constraint does not apply to null values in storage engines other than BDB. For other engines, a unique index allows multiple null values for columns containing null values.

The explanation given by netizens is:

In sql server, the unique index field cannot have multiple null values

In mysql's innodb engine, multiple null values are allowed in uniquely indexed fields.

According to the definition of NULL, NULL means unknown, so the results of two NULL comparisons are neither equal nor unequal, and the result is still unknown. According to this definition, the existence of multiple NULL values should not violate the unique constraint, so it is reasonable, and it is the same in oracel.

This explanation is very vivid, neither equal nor unequal, so the result is unknown.

As for, sqlserver and other MySQL engines have yet to be verified.