Detailed explanation of unique constraints and NULL in MySQL

Preface

A requirement I had previously made, to simplify the description, is to receive MQ messages from other groups and then insert a record into the database. In order to prevent them from sending repeated messages and inserting multiple duplicate records, a unique index is added to several columns in the table.

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

At this time, columns A, B, and C do not allow NULL values, and the uniqueness constraint also works.

Later, due to changes in requirements, the previous uniqueness constraint was modified and one more column was added. (I won’t go into details why it was added).

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

The newly added D is of type datetime, allows NULL, and has a default value of NULL. The reason why the default value is NULL is that not all records have this time. If a Magic Value (such as '1970-01-01 08:00:00') is forcibly set as the default value, it will look strange.

Blue Queen. . . That's when things go wrong. After adding D, the uniqueness constraint is basically invalid.

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

The above three SQL statements can all be executed successfully, and there will be multiple identical records in the database. According to our previous idea, a 'Duplicate key' exception should be thrown when executing the last two SQL statements.

Later, I checked and found that the official MySQL documentation has already clearly stated this point. Unique indexes allow multiple NULL values ​​to exist:

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.

It can also be seen from the table below that no matter what type of storage engine is used, multiple NULLs are allowed when creating a unique key. . . .

If you think about it, it is actually quite reasonable. After all, in MySQL, NULL represents "unknown". In SQL, any value compared with NULL returns NULL instead of TRUE, even NULL and NULL comparison also returns NULL.

So the only solution is to fix it. . . The solution is quite simple and crude. I just refreshed the online data, set "1970-01-01 08:00:00" as the default value, and then changed the column to not allow NULL. Ahem.

Many people have discussed this issue on the MySQL official website. Some people think it is a MySQL bug, while others think it is a feature. Attached is the link.

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

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.