MySQL : Comment nettoyer une table de ces doublons ?

Aujourd’hui, mon problème est du côté de ma base de données MySQL : j’ai inséré une grande quantité de lignes et je crois que j’ai des doublons dans une de mes tables. Comment vais-je vivre avec cela ? Je m’y refuse. Ma bataille du jour : me débarrasser de ces doublons mais tout en gardant au moins une ligne dans le cas des doublons.

MySQL : Lister tous les doublons

La requête suivante permet de récupérer toutes les lignes groupés par un champ identique ayant plus d’une ligne.

SELECT COUNT(field), field
FROM table
GROUP BY field
HAVING COUNT(field) > 1

Mais si j’ai plus d’un champ à regrouper ? Alors on l’ajoute au

GROUP BY

SELECT COUNT(field1), field1, field2
FROM table
GROUP BY field1, field2
HAVING COUNT(field1) > 1

MySQL : Supprimer ces doublons

Technique #1 : Utilisation de LEFT OUTER JOIN

Maintenant que l’on a récupéré nos doublons, il faut les supprimer.

Que se passe-t-il si on exécute cette requête ?

DELETE FROM table
GROUP BY field
HAVING COUNT(field) > 1

Résultat : Tous les doublons sont supprimés mais il ne reste plus aucune exemplaire de la ligne.

Comment supprimer les doublons et n’en garder qu’un exemplaire ?

Tout d’abord, on va récupérer la clé primaire la plus petite de chaque groupe de doublons de façon à le garder et effacer les autres
Ensuite, on va lier cette sous-requête à notre table principale par la clé primaire pour ne garder que les lignes qui ont soit aucun doublon, soit quand ils ont un doublon, la ligne avec la clé primaire la plus petite.
Donc les lignes qui ne seront pas liés seront les lignes à supprimer, les lignes en doublon.

DELETE table 
FROM table
LEFT OUTER JOIN (
        SELECT MIN(id) as id, field1, field2
        FROM table
        GROUP BY field1, field2
    ) AS table_1 
    ON table.id = table_1.id
WHERE table_1.id IS NULL

Technique #2 : Utilisation d’un index UNIQUE

La technique est d’ajouter un INDEX sur la table sur les champs qui ne doivent pas être dédoublonnés. Automatiquement, les doublons seront supprimés.

ALTER IGNORE TABLE table 
  ADD UNIQUE INDEX `idxTableUnique` (field1, field2);

Le problème est que l’ajout de l’index peut créer des troubles dans vos programmes liés à votre table.
Dés lors, dès la création de cette index (qui a entrainé la suppression des doublons), on le supprime :

ALTER TABLE `table` 
DROP INDEX `idxTableUnique`;

Lien : http://souptonuts.sourceforge.net/readme_mysql.htm

[EDIT 2014-01-24 20:30] FIXED : Erreur dans la requête SQL de suppression des doublons
[EDIT 2014-01-24 20:40] ADDED : Ajout d’une technique grâce à @Biapy
[EDIT 2014-01-23 23:20] IMPROVE : Précision sur la technique de @Biapy grâce au commentaire de JY Burgaud

Cet article MySQL : Comment nettoyer une table de ces doublons ? est apparu en premier sur RootsLabs.