Une base de donnée bien pensée dès le départ n'est pas censée contenir de doublons, mais comme l'erreur est humaine, je vous propose une méthode de suppression de vos doublons MySQL.
Création de la table
Pour l'exemple je vous propose de créer une table 'membre' simple :
[sql]CREATE TABLE membre (
id int(10) NOT NULL AUTO_INCREMENT,
nom varchar(30),
prenom varchar(30),
age int(3),
datecreation timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);[/sql]
Nous admettrons que l'unicité est basée sur la composition du nom et prénom, c'est à dire que deux personnes physiquement différentes ne peuvent avoir le même couple de nom+prénom (ce qui dans la réalité n'est bien sûr pas valable).
Insertion de champs avec un doublon
Insérons 4 champs, les deux premiers étant identiques (doublons) :
[sql]INSERT INTO membre (nom,prenom,age) values ('durand','marc',34);
INSERT INTO membre (nom,prenom,age) values ('durand','marc',34);
INSERT INTO membre (nom,prenom,age) values ('hurni','michael',22);
INSERT INTO membre (nom,prenom,age) values ('morin','luc',52);[/sql]
Vérifions que les champs soient bien présents dans la base :
[sql]SELECT * FROM membre;
+----+--------+---------+------+---------------------+
| id | nom | prenom | age | datecreation |
+----+--------+---------+------+---------------------+
| 1 | durand | marc | 34 | 2008-11-21 20:16:57 |
| 2 | durand | marc | 34 | 2008-11-21 20:16:57 |
| 3 | hurni | michael | 22 | 2008-11-21 20:16:57 |
| 4 | morin | luc | 52 | 2008-11-21 20:16:57 |
+----+--------+---------+------+---------------------+
[/sql]
Nous observons que Marc Durand est bien en double dans la base de données, il y a donc un doublon (en admettant qu'il n'y ait qu'un seul Marc Durand sur terre).
Création d'un index d'unicité
Cet index va permettre de supprimer les enregistrements comportant certains champs identiques, nom et prénom pour mon cas :
[sql]ALTER IGNORE TABLE membre ADD UNIQUE INDEX(nom,prenom);
Query OK, 4 rows affected (0.46 sec)
Records: 4 Duplicates: 1 Warnings: 0[/sql]
MySQL nous indique qu'un doublon a été supprimé (duplicate).
Interrogation de la table
Vérifions que le deuxième 'Marc Durand' n'est plus dans la table :
[sql]select * from membre;
+----+--------+---------+------+---------------------+
| id | nom | prenom | age | datecreation |
+----+--------+---------+------+---------------------+
| 1 | durand | marc | 34 | 2008-11-21 20:16:57 |
| 3 | hurni | michael | 22 | 2008-11-21 20:16:57 |
| 4 | morin | luc | 52 | 2008-11-21 20:16:57 |
+----+--------+---------+------+---------------------+
3 rows in set (0.00 sec)[/sql]
L'enregistrement ayant pour identifiant '2' a bien été supprimé.
Suppression de l'index
Si vous ne souhaitez pas préserver cet d'unicité, bien qu'il interdise les doublons à l'insertion, vous pouvez le supprimer :
[sql]ALTER TABLE membre DROP INDEX nom;
Query OK, 3 rows affected (0.38 sec)
Records: 3 Duplicates: 0 Warnings: 0[/sql]
Vérification de la structure en ligne de commande :
desc membre; +--------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-------------------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | nom | varchar(30) | YES | MUL | NULL | | | prenom | varchar(30) | YES | | NULL | | | age | int(3) | YES | | NULL | | | datecreation | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------+-------------+------+-----+-------------------+----------------+ 5 rows in set (0.07 sec)
Conclusion
Il existe plusieurs techniques de suppression des doublons MySQL, les plus mauvaises étant celles préconisant l'usage d'un langage tiers (php, bash, etc.) basées sur un parcours en boucle de la table car trop consommatrices en ressources. Sachez qu'il est bien plus rentable d'utiliser les fonctions natives du moteur SQL, quitte à faire fonctionner un tant soit peu ses méninges et consulter la documentation MySQL (ou autre moteur SQL suivant celui utilisé 😉 ). Cette solution étant rapide et fiable, j'espère que vous en aurez l'utilité.
J'ajoute un lien venant de chez Developpez pour compléter ce tutoriel.
Auteur : Mr Xhark
Fondateur du blog et passionné par les nouvelles techno, suivez-moi sur twitter
23 commentaires
Ah ca ca me plait ! Vraiment un très bon billet ! Quand tu veux pour un nouveau tuto sur SQL (domaine que je ne maitrise pas vraiment)
@Julien : si jamais tu luttes sur un point précis je peux éventuellement en faire un tuto 😉 J'essaierai de faire d'autres billets prochainement
super astuce Merci Beaucoup
Très bonne idée. Merci.
Merci pour l'astuce, très pratique ce petit "IGNORE"
Avec les 76 secondes de traitement, ça m'a éviter bien des soucis 😛
très bien, cette ligne de script MySQL, qui complète le script PHP interdisant l'écriture de données déjà présentes dans la base.
1.750.000 entrées dans ma base de données... Du ménage à faire, et voilà LA solution. Merci beaucoup pour ce coup de pouce.
Un grand merci !
Excellent petit tutoriel, en plus au contraire de certains ça marche.
Merci.
Intéressant,
mais quid des contraintes d'intégrités ?
Si l'enregistrement ayant pour identifiant ‘2′ est utilisé dans une autre table,
on a perdu des données...
[...] c'est google qui me vient en aide : comment... supprimer... doublons... table... mysql... Je trouve une réponse digne de ce nom et m'empresse de la mettre en [...]
Merci bcp ça marche impec si quelqu'un besion d'aide je suis disponible.
#1071 - Specified key was too long; max key length is 1000 bytes
j'ai 2 varchar de 512...
@gloarb: une varchar de 512, je ne suis pas sûr que ce soit le meilleur choix, quel est le type d'enregistrement que tu manipules ?
une petite question:
le UNIQUE INDEX(nom,prenom) ne risque t il pas de poser un problème si , pour reprendre l'exemple, il y a un autre 'luc' mais pas 'morin'?
merci.
Merci pour ce tutoriel bien clair. C'est vrai que les tutoriaux qui préconise de faire une boucle sur tous les enregistrements sont vraiment les moins intéressants. Votre code à l'avantage de pouvoir être utilisé sur plusieurs SGBD, donc il est vraiment à préconiser, encore merci pour ce partage.
@Tony: merci pour ton soutien! j'ai parcouru ton blog qui est plutôt sympa comme mémo SQL !
[...] Un blog permet également de comprendre des requêtes basées sur des cas d'étude mais aussi d'assimiler les recommandations (formatage des requêtes, boucles, suppression des doublons). [...]
Une autre solution est d'utiliser ROW_NUMBER ()
http://www.code-source.net/content/supprimer-les-lignes-dupliqu%C3%A9es-dune-table-sql-server
Bonjour,
Je me suis servi de la méthode que tu proposes pour effacer les doublons, ce qui fonctionne très bien mais je n'arrive plus à reconfigurer ma table dans sa configuraion initiale, ce quelles que soient mes tentatives... Ci-dessous le lien vers le site du zéro ou j'explicite toutes les tentatives depuis
$link = mysql_query("ALTER TABLE " . $table_eval_de_linscrit . " DROP UNIQUE INDEX(metier, entreprise, ville, prestation, departement)"); en PHP jusqu'à
DROP INDEX metier ON `tb_evals_de_linscrit_marcvart` directement en SQL sous PMA. Les cases UNIQUE restent grisées, aucun INDEX n'est coché pour les colonnes citées ; pour info. la clé primaire est id. Les messages d'erreurs vont d'une erreur de syntaxe à Can't DROP 'metier'; check that column/key exists ! As-tu une idée ? Merci.
le lien disais-je... http://fr.openclassrooms.com/forum/sujet/drop-unique-index-ne-fonctionne-pas?page=1#message-85561305
Merci pour ces indications, j'ai un gros problème de doublons en ce moment sur l'une de mes bases de données et cet article m'a bien aidé !
Toujours utile, au milieu d'articles donnant des requêtes plus longues les unes que les autres, cette solution m'a sauvé un nombre d'heures et de prises de tête incalculables. Merci !