What is recorded here is a bug that happened a long time ago. It mainly introduces to you the relevant information about unique constraints and NULL in MySQL. The introduction in the article is very detailed and has certain reference and learning value for everyone. Friends who need it Let’s take a look below.
Preface
A requirement I made before, a simplified description is to accept 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, unique indexes were added to several columns in the table.
CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A, B, C);
At this time, NULL values are not allowed in columns A, B, and C, and the uniqueness constraint also works.
Later, due to changes in requirements, the previous uniqueness constraint was modified and an additional column was added. (I won’t go into details as to why.)
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, allowing NULL, and the default value is NULL. The reason why the default value is NULL is because not all records have this time. If you forcibly set a Magic Value (such as '1970-01-01 08:00:00') as the default value, it will look strange.
Blue Queen. . . Something went 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 SQLs can all be executed successfully, and there will be multiple identical records in the database. According to our previous idea, the 'Duplicate key' exception should be thrown when executing the last two SQLs.
After checking it, I found out that the official MySQL document has clearly stated this. 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 table below that no matter what type of storage engine is used, multiple NULLs are allowed to exist when creating a unique key. . . .
If you think about it carefully, it is actually quite reasonable. After all, NULL is considered to represent "unknown" in MySQL. In SQL, the comparison between any value and NULL returns NULL instead of TRUE, even the comparison between NULL and NULL returns NULL.
So we can only fix it. . . The solution is quite simple and crude. Just refresh the online data, set "1970-01-01 08:00:00" as the default value, and then change that column to not allow NULL, ahem.
Many people have discussed this issue on the MySQL official website. Some people think it is a bug of MySQL, while others think it is a feature. A link is attached.
MySQL Bugs: #8173: unique index allows duplicates with null values
The above is the detailed content of Detailed explanation of NULL in MySQL. For more information, please follow other related articles on the PHP Chinese website!

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version
Useful JavaScript development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment