RootsLabs

More than a tool ! GitHub Google+ LinkedIn RSS

MySQL : Comment nettoyer une table de ces doublons ?

Progi1984 - Commentaires (22)

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

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

Commentaires

1. BURGAUD, le 20 janvier 2014 à 17:40

J’ai une erreur si je fais la requête, mais si je remplace DELETE par SELECT *, j’obtiens bien les doublons à supprimer.

Mon message d’erreur :
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘RIGHT OUTER JOIN ( SELECT MIN( id ) AS id, nom, prenom FROM personne ‘ at line 2

Ma table se nomme `personne` et contient 3 colonnes : id, nom et prénom.

Ma requête (enfin, la vôtre retranscrite) :
DELETE FROM personne
LEFT OUTER JOIN (
SELECT MIN( id ) AS id, nom, prenom
FROM personne
GROUP BY nom, prenom
) AS personne_1 ON personne.id = personne_1.id
WHERE personne_1.id IS NULL;

Si vous aviez une idée, cela m’intéresse (pour le moment, je n’ai pas de doublons à nettoyer mais cela fait partie des requêtes à garder sous le coude. Donc j’aimerais bien comprendre l’erreur avant d’en avoir besoin….)

Merci

2. Progi1984, le 21 janvier 2014 à 09:33

@jyburgaud : Le problème est que l’erreur que vous me retournez n’est pas sur la bonne requête.
Pourriez vous me donner la requête et l’erreur lié (même si je n’ai pas la table) ? Merci

3. BURGAUD, le 21 janvier 2014 à 13:30

Bonjour,

oui je n’en suis aperçu après avoir posté et n’ai pas osé faire un autre message.

Comme j’avais une erreur, j’ai remplacé LEFT OUTER JOIN par RIGHT OUTER JOIN pour voir. Et je me suis mélangé les pinceaux lors de la rédaction du message.

Donc la requête :
DELETE FROM personne
LEFT OUTER JOIN (
SELECT MIN( id ) AS id, nom, prenom
FROM personne
GROUP BY nom, prenom
) AS personne_1 ON personne.id = personne_1.id
WHERE personne_1.id IS NULL;

Le message d’erreur :
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘LEFT OUTER JOIN ( SELECT MIN( id ) AS id, nom, prenom FROM personne GROUP BY ‘ at line 2

Et pour être complet, la structure de ma table :
CREATE TABLE IF NOT EXISTS `personne` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘Identifiant de la personne’,
`nom` varchar(255) NOT NULL COMMENT ‘nom de la personne’,
`prenom` varchar(255) NOT NULL COMMENT ‘Prénom de la personne’,
PRIMARY KEY (`id`),
KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Personne’ AUTO_INCREMENT=13 ;

Et mes données de test :
INSERT INTO `personne` (`id`, `nom`, `prenom`) VALUES
(1, ‘MARTIN’, ‘Jean’),
(2, ‘DUPOND’, ‘Jacques’),
(3, ‘BURGAUD’, ‘Jean-Yves’),
(4, ‘BURGAUD’, ‘Solange’),
(5, ‘BURGAUD’, ‘Guillaume’),
(6, ‘BURGAUD’, ‘Laura’),
(7, ‘BURGAUD’, ‘Marion’),
(8, ‘DUPOND’, ‘Pierre’),
(9, ‘DUPOND’, ‘Jacques’),
(10, ‘MARTIN’, ‘Jean’),
(11, ‘DUPOND’, ‘Jacques’),
(12, ‘DUPONT’, ‘Jacques’);

Je me suis demandé si le problème pouvait venir de InnoDB ou du fait que je ne suis plus sous mySQL mais sous MariaDB (distrib Linux-Fedora).

[jyburgaud ~]$ mysql -V
mysql Ver 15.1 Distrib 5.5.34-MariaDB, for Linux (x86_64) using readline 5.1

Merci pour votre aide

4. Biapy, le 21 janvier 2014 à 13:42

Bonjour,

je viens de tomber sur une alternative au problème.

Tip 5 dans ce fichier d’astuces:

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

Pour purger les valeurs dupliquées d’une table, il suffit d’ajouter un index unique avec l’option ignore:

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

Merci pour votre travail

5. Progi1984, le 21 janvier 2014 à 21:34

Je suis désolé : c’était une erreur de ma part.

La bonne requête devrait être (dans ton cas)

DELETE personne
FROM personne
  LEFT OUTER JOIN (
    SELECT MIN( id ) AS id, nom, prenom
    FROM personne
    GROUP BY nom, prenom
  ) AS personne_1 ON personne.id = personne_1.id
WHERE personne_1.id IS NULL;
6. Progi1984, le 21 janvier 2014 à 22:38

@Biapy : Merci de votre retour. Je me suis permis de l’incorporer à mon article.

7. BURGAUD, le 22 janvier 2014 à 13:16

@Progi1984 : la requête fonctionne parfaitement : MERCI.

Petit commentaire sur l’ajout d’un index :

Cette solution est élégante mais a le défaut de modifier la table. Si l’apparition de doublons n’est pas anormal dans le programme, en ajoutant un index, on modifie la table qui ne peut plus recevoir de doublons. Il y a un risque de perturber les traitements.

Je préfère donc le nommage de l’index afin ensuite de le supprimer.

Si je reprends mon exemple de table `personne`

ALTER IGNORE TABLE `personne` ADD UNIQUE INDEX `indexNomPrenom` ( `nom` , `prenom` );

Puis ensuite, on enlève l’index grâce à son nom :

ALTER TABLE `personne` DROP INDEX `indexNomPrenom`;

Mes deux centimes à l’article.

8. Progi1984, le 24 janvier 2014 à 00:20

@jyburgaud : Merci de votre réponse. Je modifie l’article grâce à votre commentaire.

9. akelwood, le 24 février 2015 à 19:23

On peut aussi, si le nombre de doublons sont nombreur, la reute peut être très longue, on peut alors copier les doublon dans une table annexe

create table temp_doublon as select max(id) as id, from personne group by nom, prenom having count(*)>1;

puis executer la requete suivante

DELETE FROM p USING personne p INNER JOIN temp_doublon td ON ( p.id = td.id );

enfin

drop table temp_doublon;

10. shinz@bururo, le 7 avril 2015 à 10:25

Bonjour j’ai une erreur avec la table personne_1
#1054 – Unknown column ‘personne_1.idclass’ in ‘on clause’

que faire merci de m’aider

11. Progi1984, le 7 avril 2015 à 20:43

@shinz@bururo : Pourriez vous fournir votre requête SQL afin de pouvoir vous aider ?

12. kaad, le 27 mai 2015 à 10:23

Merciii

13. nunurs, le 18 juin 2015 à 16:54

Merci pour la requête de suppression des doublons ! De nombreux sites ne font que bêtement reprendre l’exemple SQL pour le « vendre » comme une solution MySQL.
Merci !!

14. Paulywebster, le 9 mars 2016 à 11:05

Hello,

Sympa l ‘article, à précisez que la technique 2 ne fonctionne pas sur le moteur InnoDB

15. Maroua, le 21 mai 2016 à 20:12

salut; merci bcp pour votre article
ma requete est la suivant
« DELETE dim_client FROM dim_client
LEFT OUTER JOIN(
SELECT MIN(SK_client)as’SK_client’,’code_client’,’client’
From dim_client
GROUP BY ‘code_client’,’client’
)AS table_1
ON dim_client.SK_client=table_1.SK_client
WHERE table_1.SK_client IS NULL »

mais après l’exécution elle me retourne une seule ligne….merci de m’aider pour savoir l’erreur

16. Paul, le 24 janvier 2017 à 10:27

Bonjour
Voilà je ne sais trop comment formulé ma requête (.Sql.)
Pour lister les données. Résultat final je veux Exemple
1-> Donnée de Mr Alpha
(1 Entrée) Mr Alpha :IdAlpha, Nom, Prenom …

2-> Lister Sans doublons ces Profils
(3 Entrées) IdAlpha Son profile[1], Son profile[2], Son profile[3]

3-> Lister Sans doublons ces Contrats
(2 Entrées) IdAlpha profile[1] > Contrats[1], Contrats[2]
(3 Entrées) IdAlpha profile[2] > Contrats[1], Contrats[3]
(0 Entrées) IdAlpha profile[3] > Pas de contrat

Voilà J’ai 3 Tables :
1 Employer
2 Profils
3 Contrats

$reponse = $bdd->query( »
SELECT * FROM
cnt_employer AS a ,
profil AS b ,
contrats AS c

WHERE
a.Ref_1 = ‘$Identifiant’ and
b.Ref_Employer_2 = a.Ref_1 and
c.Ref_Employer_2 = a.Ref_1
« );

Si vous avez une idée, merci pour le coup de main

17. yoahn, le 18 avril 2017 à 13:33

Bonjour,

Voila je suis débutant dans mysql et j’aimerais avoir votre aide pour la suppression de doublons, je vous explique j’ai sur ma table qui se nomme « peutetre » juste un champ qui se nomme « mail » et j’ai remarqué que dans ce champs il y avait plusieurs doublons (70 doublons) et j’aimerais juste supprimer les doublons car je ne trouve pas comment faire…
si il existe une requete pour supprimer juste les doublons du champs « mail » ca serait cool de m’aider.
En vous remerciant par avance.
Cordialement,

18. yoahn, le 18 avril 2017 à 15:58

Sachant que lorsque je met cette requête la :

SELECT COUNT(mail), mail
FROM peutetre
GROUP BY mail
HAVING COUNT(mail) > 1

ça me sort bien les doublons….
et je veux juste supprimer les doublons (parfois j’en ai 2, parfois j’en 3 etc)

merci de votre aide

19. Progi1984, le 22 août 2017 à 16:00

@Maroua : Je pense que vous devriez mettre le GROUP BY sur le SK_client et pas sur le code_client.

20. Progi1984, le 22 août 2017 à 16:03

@Paul : Bonjour, malheureusement, je ne comprends pas la question. Que souhaitez-vous faire ?

21. Progi1984, le 22 août 2017 à 16:06

@yoahn : Vous devriez essayer la deuxième technique listée ci-dessus. Elle devrait vous aider.

22. JCRCan, le 25 août 2022 à 21:09

Dans la requête:
SELECT COUNT(field), field
FROM table
GROUP BY field
HAVING COUNT(field) > 1

quelle est le résultat du SELECT que je puisse imprimer?

Ajouter un commentaire

Commentaire :