Shulou(Shulou.com)05/31 Report--

This article shares with you the content of the sample analysis of uniqueness constraints and NULL in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

A requirement previously made to simplify the description is to accept MQ messages from other groups and insert a record in the database. In order to prevent them from repeatedly sending 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, the NULL values are not allowed in all the three columns of AMagol B and C, and the uniqueness constraint is also work.

Later, due to the change of requirements, the previous uniqueness constraint was modified and an additional column was added. As for why it is added, I will not repeat it.

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 type datetime, NULL is allowed, and the default value is NULL. The default value is NULL, considering that not all records have this time, and it would seem strange to force a Magic Value (such as' 1970-01-01-01 08-01-01-01-08-01-01-01-08-01-01-01-08-01-01-01-01-01-01-08) as the default value.

The blue queen. Something went wrong. With the addition of D, the uniqueness constraint is basically invalid.

Insert into testTable (Arecedence Brecinct D) VALUES;-OKInsert into testTable (Arecedence Brecinct C pencil D) VALUES (1recorder 2pence3 null);-OKInsert into testTable (ArecedBrecource D) VALUES (1mae2pence3 null);-OK

The above three SQL can be executed successfully, and there will be multiple identical records in the database. However, according to our previous idea, the exception of 'Duplicate key'' should be thrown when the last two SQL are executed.

Later, I checked and found that this point has been clearly stated in the MySQL official documentation. 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.

It can also be seen from the following table that no matter what type of storage engine is used, multiple unique key are allowed to exist when establishing NULL.

When you think about it, it actually makes sense. After all, NULL stands for "unknown" in MySQL. In SQL, any comparison of a value with NULL returns a value of NULL instead of TRUE, and even a comparison of NULL with NULL returns NULL.

So we have to fix. The solution is also quite simple and rough, directly brush the online data, "1970-01-01 08:00:00" as the default value, and then change that column to not allowed to NULL, ahem.

Many people have discussed this issue on MySQL's official website. Some people think that this is MySQL's bug, while others think it is a feature.

Thank you for reading! This is the end of this article on "example Analysis of uniqueness constraints and NULL in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.