MySQLi - Gestion des doublons

Les tableaux ou les ensembles de résultats contiennent parfois des enregistrements en double. Parfois, cela est autorisé, mais parfois il est nécessaire d'arrêter les enregistrements en double. Parfois, il est nécessaire d'identifier les enregistrements en double et de les supprimer de la table. Ce chapitre décrit comment éviter que des enregistrements en double ne se produisent dans une table et comment supprimer des enregistrements en double déjà existants.

Empêcher les doublons de se produire dans une table

Vous pouvez utiliser un PRIMARY KEY ou UNIQUEIndexer sur une table avec les champs appropriés pour arrêter les enregistrements en double. Prenons un exemple: le tableau suivant ne contient pas d'index ou de clé primaire de ce type, il autoriserait donc les enregistrements en double pour le prénom et le nom.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Pour empêcher la création de plusieurs enregistrements avec les mêmes valeurs de prénom et de nom dans cette table, ajoutez une CLÉ PRIMAIRE à sa définition. Lorsque vous faites cela, il est également nécessaire de déclarer les colonnes indexées comme étant NOT NULL, car une PRIMARY KEY n'autorise pas les valeurs NULL -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

La présence d'un index unique dans une table provoque normalement une erreur si vous insérez un enregistrement dans la table qui duplique un enregistrement existant dans la ou les colonnes qui définissent l'index.

Utilisation INSERT IGNORE plutôt que INSERT. Si un enregistrement ne duplique pas un enregistrement existant, MySQLi l'insère comme d'habitude. Si l'enregistrement est un doublon, le mot clé IGNORE indique à MySQLi de le supprimer silencieusement sans générer d'erreur.

L'exemple suivant n'entraîne pas d'erreur et en même temps, il n'insère pas d'enregistrements en double.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Utilisation REPLACEplutôt que INSÉRER. Si l'enregistrement est nouveau, il est inséré comme avec INSERT. S'il s'agit d'un doublon, le nouvel enregistrement remplace l'ancien -

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE et REPLACE doivent être choisis en fonction du comportement de gestion des doublons que vous souhaitez appliquer. INSERT IGNORE conserve le premier d'un ensemble d'enregistrements dupliqués et supprime le reste. REPLACE conserve le dernier d'une série de doublons et efface tous les précédents.

Une autre façon de renforcer l'unicité consiste à ajouter un index UNIQUE plutôt qu'une PRIMARY KEY à une table.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Compter et identifier les doublons

Voici la requête pour compter les enregistrements en double avec prénom et nom dans une table.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

Cette requête renverra une liste de tous les enregistrements en double dans la table person_tbl. En général, pour identifier les ensembles de valeurs qui sont dupliqués, procédez comme suit:

Déterminez quelles colonnes contiennent les valeurs qui peuvent être dupliquées.
Répertoriez ces colonnes dans la liste de sélection de colonnes, avec COUNT (*).
Répertoriez également les colonnes de la clause GROUP BY.
Ajoutez une clause HAVING qui élimine les valeurs uniques en exigeant que le nombre de groupes soit supérieur à un.

Élimination des doublons d'un résultat de requête:

Vous pouvez utiliser DISTINCT avec l'instruction SELECT pour découvrir les enregistrements uniques disponibles dans une table.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Une alternative à DISTINCT consiste à ajouter une clause GROUP BY qui nomme les colonnes que vous sélectionnez. Cela a pour effet de supprimer les doublons et de sélectionner uniquement les combinaisons uniques de valeurs dans les colonnes spécifiées -

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Suppression des doublons à l'aide du remplacement de table

Si vous avez des enregistrements en double dans une table et que vous souhaitez supprimer tous les enregistrements en double de cette table, voici la procédure -

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Un moyen simple de supprimer les enregistrements en double d'une table consiste à ajouter un INDEX ou PRIMAY KEY à cette table. Même si cette table est déjà disponible, vous pouvez utiliser cette technique pour supprimer les enregistrements en double et vous serez également en sécurité à l'avenir.

mysql> ALTER IGNORE TABLE person_tbl 
   -> ADD PRIMARY KEY (last_name, first_name);