Explication détaillée des contraintes uniques et des instances NULL dans MySQL

Cet article vous présente principalement les informations pertinentes sur les contraintes uniques et NULL dans MySQL. L'introduction dans l'article est très détaillée et a une certaine valeur de référence et d'apprentissage pour tous les amis qui en ont besoin peuvent y jeter un œil ci-dessous. J'espère que cela aide tout le monde.

Avant-propos

Une exigence que j'ai formulée auparavant, une description simplifiée consiste à accepter les messages MQ d'autres groupes, puis à insérer un enregistrement dans la base de données. Afin de les empêcher d'envoyer des messages répétés et d'insérer plusieurs enregistrements en double, des index uniques ont été ajoutés à plusieurs colonnes du tableau.

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

À l'heure actuelle, les trois colonnes A, B et C n'autorisent pas les valeurs NULL et la contrainte d'unicité fonctionne également.

Plus tard, en raison de changements dans les exigences, la contrainte d'unicité précédente a été modifiée et une colonne supplémentaire a été ajoutée. (Je n'entrerai pas dans les détails sur pourquoi.)

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

Le D nouvellement ajouté est de type datetime, autorisant NULL, et la valeur par défaut est NULL. La raison pour laquelle la valeur par défaut est NULL est que tous les enregistrements n'ont pas cette heure. Si vous définissez de force une valeur magique (telle que « 1970-01-01 08:00:00 ») comme valeur par défaut, cela semblera étrange.

Reine bleue. . . Quelque chose s'est mal passé. Après avoir ajouté D, la contrainte d’unicité est fondamentalement invalide.

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

Les trois SQL ci-dessus peuvent tous être exécutés avec succès et il y aura plusieurs enregistrements identiques dans la base de données. Selon notre idée précédente, l'exception 'Clé en double' devrait être levée lors de l'exécution des deux derniers SQL.

Après l'avoir vérifié, j'ai découvert que le document officiel de MySQL l'indiquait clairement. L'index unique permet l'existence de plusieurs valeurs NULL :

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.

D'après le tableau ci-dessous, cela peut également être le cas. Il convient de noter que quel que soit le type de moteur de stockage utilisé, plusieurs NULL sont autorisés à exister lors de la création d'une clé unique. . . .

Si vous y réfléchissez, c'est en fait tout à fait raisonnable. Après tout, NULL est considéré comme représentant "inconnu" dans MySQL. En SQL, toute comparaison avec NULL renvoie NULL au lieu de TRUE, même une comparaison entre NULL et NULL renvoie NULL.

Nous ne pouvons donc que le réparer. . . La solution est assez simple et grossière. Actualisez simplement les données en ligne, définissez "1970-01-01 08:00:00" comme valeur par défaut, puis modifiez cette colonne pour ne pas autoriser NULL, hum.

De nombreuses personnes ont discuté de ce problème sur le site officiel de MySQL. Certaines personnes pensent qu'il s'agit d'un bug de MySQL, tandis que d'autres pensent qu'il s'agit d'une fonctionnalité. Un lien est joint.

Bogues MySQL : #8173 : un index unique autorise les doublons avec des valeurs nulles

Recommandations associées :

Contraintes de type en PHP

Explication détaillée des contraintes, requêtes multi-tables et sous-requêtes dans MySQL

Partage d'idées de code pour les contraintes de type en PHP

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!