Mysql : NULL, comparaisons et unicité

Nous allons voir dans cet article comment MySQL gère les contraintes d’unicité et en particulier son traitement de la valeur NULL.

Imaginons une table créée de la manière suivante dans une base de données MySQL :

CREATE TABLE `test` (
`number` INT NULL ,
UNIQUE (
`number`
)
) ENGINE = innodb;

On s’attend à ce qu’on ne puisse rentrer qu’une seule fois une valeur pour ladite colonne :

INSERT INTO `test` (`number`) VALUES (1);
-- Query OK, 1 row affected (0.02 sec)
-- La ligne est correctement ajoutée.

INSERT INTO `test` (`number`) VALUES (2);
-- Query OK, 1 row affected (0.02 sec)
-- La ligne est correctement ajoutée.

INSERT INTO `test` (`number`) VALUES (2);
-- ERROR 1062 (23000): Duplicate entry '2' for key 1
-- La contrainte d'unicité n'est pas respectée, MySQL bloque l'insertion en retournant une erreur.

Jusque là, rien de difficile : on est assuré de n’avoir qu’une ligne par valeur possible. Très bien !

Maintenant, voyons ce qu’il se passe lorsque la valeur est NULL :

INSERT INTO `test` (`number`) VALUES (NULL);
-- Query OK, 1 row affected (0.03 sec)
-- La ligne est correctement ajoutée.

INSERT INTO `test` (`number`) VALUES (NULL);
-- Query OK, 1 row affected (0.01 sec)
-- La ligne est correctement ajoutée. Mais... mais... hein ?

On constate donc qu’insérer deux fois la valeur NULL ne pose aucun problème à MySQL. Ce n’est pas du tout le comportement attendu. Quelqu’un d’autre a déjà rencontré ce problème et a rempli un rapport de bug. Mais ce bug a été fermé 35 minutes après son ouverture car Not a bug

Bon, très bien, mais pourquoi ?

En fait, pour vérifier la contrainte d’unicité, MySQL vérifie l’égalité entre les valeurs. Or :

select 1 = 1;
-- +-------+
-- | 1 = 1 |
-- +-------+
-- |     1 |
-- +-------+
-- 1 row in set (0.00 sec)
-- Logique !

select 1 = 2;
-- +-------+
-- | 1 = 2 |
-- +-------+
-- |     0 |
-- +-------+
-- 1 row in set (0.00 sec)
-- Évidemment !

select null = 2;
-- +----------+
-- | null = 2 |
-- +----------+
-- |     NULL |
-- +----------+
-- 1 row in set (0.00 sec)
-- Arf...

select null = null;
-- +-------------+
-- | null = null |
-- +-------------+
-- |        NULL |
-- +-------------+
-- 1 row in set (0.00 sec)
-- Boarf...

select null is null;
-- +--------------+
-- | null is null |
-- +--------------+
-- |            1 |
-- +--------------+
-- 1 row in set (0.00 sec)
-- Effectivement...

Effectivement, NULL n’est pas une valeur en tant que telle… NULL ne veut pas dire valeur vide, NULL signifie une valeur manquante et inconnue. C’est un potentiel de valeur… mais ce n’est pas une valeur.
En fait, tout ça est logique une fois qu’on y a réfléchi et qu’on s’est trouvé face au problème.

Maintenant, de vraies questions se posent :

  • Comment représenter le vide ? (pas une valeur manquante et inconnue, juste l’absence de valeur, on connait ce qui est représenté, c’est juste Ø)
  • Comment faire pour avoir une contrainte d’unicité qui fonctionne avec ce Ø ?

J’ai un peu la question, mais pas de réponses. Et vous ?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *