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
Commentaires
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
@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
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
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
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;@Biapy : Merci de votre retour. Je me suis permis de l’incorporer à mon article.
@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.
@jyburgaud : Merci de votre réponse. Je modifie l’article grâce à votre commentaire.
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;
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
@shinz@bururo : Pourriez vous fournir votre requête SQL afin de pouvoir vous aider ?
Merciii
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 !!
Hello,
Sympa l ‘article, à précisez que la technique 2 ne fonctionne pas sur le moteur InnoDB
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
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
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,
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
@Maroua : Je pense que vous devriez mettre le GROUP BY sur le SK_client et pas sur le code_client.
@Paul : Bonjour, malheureusement, je ne comprends pas la question. Que souhaitez-vous faire ?
@yoahn : Vous devriez essayer la deuxième technique listée ci-dessus. Elle devrait vous aider.
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