Detailed description of unique constraints and NULL in MySQL, and detailed description of null constraints in mysql

Detailed description of unique constraints and NULL in MySQL, and detailed description of null constraints in mysql

Preface

In the previous requirement, the simplified description is to accept MQ messages from other groups and insert a record in the database. To prevent them from sending messages repeatedly and insert 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. The uniqueness constraint is also work.

Later, due to changes in requirements, the previous uniqueness constraint was modified and another column was added. (I won't go into details about the reason for the addition ).

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

The newly added D is of the datetime type and can be NULL. The default value is NULL. The default Value is NULL because not all records have this time. If you forcibly set a Magic Value (such as '2017-01-01 08:00:00 ') as the default Value, it looks strange.

After Blue... The problem arises. After adding D, the uniqueness constraint is basically invalid.

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

All the preceding three SQL statements can be successfully executed, and multiple identical records are generated in the database. According to our previous ideas, the 'duplicate key' exception should be thrown when the last two SQL statements are executed.

Later, I checked it and found that the MySQL official document clearly stated this point. The unique index allows the existence of multiple NULL values:

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.

The following table also shows that no matter what type of storage engine is used, multiple NULL values are allowed when a unique key is created ....

In fact, it is quite reasonable to think about it. After all, NULL in MySQL indicates "unknown ". In SQL, the return values of any value compared with NULL are NULL rather than TRUE. Even if NULL is compared with NULL, NULL is returned.

So it can only be fixed... The solution is also very simple and rude. I directly refreshed the online data, used "08:00:00" as the default value, and then changed the column to a non-NULL value. I coughed.

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

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

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.