Comment trouver et supprimer les données dupliquées dans MySQL : guide complet avec des exemples SQL

1. Introduction

Lorsque l’on exploite une base de données, il n’est pas rare de rencontrer des problèmes tels que « des enregistrements dupliqués insérés » ou « des données qui devraient être uniques apparaissant plusieurs fois ». Dans les environnements où l’on utilise des bases de données relationnelles comme MySQL, extraire et gérer les données dupliquées est une tâche essentielle pour maintenir la précision et la qualité des données.

Par exemple, dans les tables métier principales telles que les informations des membres, les données produits et l’historique des commandes, des enregistrements dupliqués peuvent être insérés à cause d’erreurs d’utilisateur ou de dysfonctionnements du système. Si le problème n’est pas résolu, cela peut réduire la fiabilité des agrégations et des analyses, et peut également entraîner des bugs inattendus ou des problèmes opérationnels.

Pour résoudre ce « problème de données dupliquées », vous devez d’abord identifier quels enregistrements sont dupliqués, puis organiser ou supprimer ces enregistrements en fonction du contexte. Cependant, se contenter d’une simple instruction SELECT standard dans MySQL n’est souvent pas suffisant pour détecter efficacement les doublons. Des techniques SQL légèrement plus avancées et des approches pratiques sont nécessaires.

Dans cet article, nous nous concentrons sur « Comment extraire des données dupliquées dans MySQL », en couvrant tout, des requêtes SQL de base aux applications pratiques, aux considérations de performance et à la gestion courante des erreurs. Que vous soyez débutant en bases de données ou ingénieur qui écrit du SQL quotidiennement, ce guide vise à fournir des connaissances pratiques et orientées terrain.

2. Basics: Detecting Duplicates Using a Key Column

La façon la plus simple d’extraire des données dupliquées dans MySQL consiste à identifier les cas où « plusieurs enregistrements partagent la même valeur dans une colonne spécifique (colonne clé) ». Dans cette section, nous expliquons les requêtes SQL représentatives utilisées pour détecter les valeurs de clé dupliquées et leur fonctionnement.

2-1. Detecting Duplicates with GROUP BY and HAVING

La technique fondamentale pour la détection de doublons consiste à regrouper les enregistrements par une colonne spécifique à l’aide de la clause GROUP BY, puis à filtrer les groupes contenant deux enregistrements ou plus avec la clause HAVING. Voici un exemple typique :

SELECT key_column, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY key_column
HAVING COUNT(*) > 1;

Example: Extracting Duplicate Member Email Addresses

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Lorsque cette requête est exécutée, si la même adresse e‑mail a été enregistrée plusieurs fois, l’adresse e‑mail et le nombre de duplications (count) seront affichés dans le résultat.

2-2. Detecting Duplicates Across Multiple Columns

Si vous devez détecter des doublons basés sur une combinaison de deux colonnes ou plus, vous pouvez spécifier plusieurs colonnes dans la clause GROUP BY en appliquant la même logique.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Avec cette méthode, vous pouvez détecter les duplications où plusieurs conditions correspondent entièrement, par exemple « même nom complet et même date de naissance » ou « même identifiant produit et même date de commande ».

2-3. Calculating the Total Number of Duplicate Records

Si vous souhaitez comprendre l’ampleur globale de la duplication, vous pouvez utiliser une sous‑requête pour calculer le nombre total d’entrées dupliquées.

SELECT SUM(duplicate_count) AS total_duplicates
FROM (
  SELECT COUNT(*) AS duplicate_count
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
) AS duplicates;

Cette requête additionne le nombre d’entrées dupliquées sur l’ensemble des groupes de doublons.

En combinant GROUP BY et HAVING, vous pouvez extraire des données dupliquées dans MySQL de manière simple et efficace.

3. Extracting All Records That Share Duplicate Keys

Dans la section précédente, nous avons présenté comment lister uniquement les « valeurs de clé dupliquées ». Cependant, dans le travail réel, il est souvent nécessaire de vérifier « quels enregistrements exacts sont dupliqués, et d’inspecter tous leurs détails ». Par exemple, vous pouvez vouloir examiner les profils d’utilisateurs entièrement dupliqués ou analyser ligne par ligne les données produits dupliquées.

Dans cette section, nous expliquons des modèles SQL pratiques pour extraire tous les enregistrements qui partagent des clés dupliquées.

3-1. Extraction d’enregistrements dupliqués à l’aide d’une sous‑requête

L’approche la plus basique consiste à récupérer la liste des valeurs de clés dupliquées dans une sous‑requête, puis à extraire tous les enregistrements correspondant à ces clés.

SELECT *
FROM table_name
WHERE key_column IN (
  SELECT key_column
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
);

Exemple : Extraction de tous les enregistrements avec des adresses e‑mail dupliquées

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

Lorsque vous exécutez cette requête, elle extrait toutes les lignes de la table « users » où l’adresse e‑mail est dupliquée (y compris les colonnes telles que ID, date d’inscription, etc.).

3-2. Extraction efficace à l’aide de EXISTS

Si vous devez gérer de grands ensembles de données ou vous souciez des performances, l’utilisation de EXISTS peut également être efficace. IN et EXISTS sont similaires, mais selon le volume des données et l’indexation, l’un peut être plus rapide que l’autre.

SELECT *
FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t1.key_column = t2.key_column
  GROUP BY t2.key_column
  HAVING COUNT(*) > 1
);

Exemple : Enregistrements e‑mail dupliqués (avec EXISTS)

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1
  FROM users u2
  WHERE u1.email = u2.email
  GROUP BY u2.email
  HAVING COUNT(*) > 1
);

3-3. Remarques et considérations de performance

• Les performances des sous‑requêtes peuvent être fortement affectées lorsque le jeu de données est volumineux. Avec un indexage approprié, IN et EXISTS peuvent tous deux fonctionner à un niveau pratique.
• Cependant, si vous avez besoin de conditions complexes ou souhaitez déterminer les duplications sur plusieurs colonnes, les requêtes peuvent devenir lourdes. Validez toujours le comportement dans un environnement de test d’abord.

De cette manière, extraire tous les enregistrements correspondant à des clés dupliquées peut être réalisé à l’aide de sous‑requêtes ou de la clause EXISTS.

4. Détection des duplications sur plusieurs colonnes

Les conditions de détection des duplications ne reposent pas toujours sur une seule colonne. En pratique, il est fréquent d’exiger l’unicité sur une combinaison de plusieurs colonnes. Par exemple, vous pouvez considérer des enregistrements comme dupliqués lorsque le « nom complet + date de naissance » correspondent, ou lorsque le « ID produit + couleur + taille » sont tous identiques.

Dans cette section, nous expliquons en détail comment extraire les duplications en utilisant plusieurs colonnes.

4-1. Détection des duplications avec GROUP BY en utilisant plusieurs colonnes

Pour détecter les duplications sur plusieurs colonnes, listez les colonnes séparées par des virgules dans la clause GROUP BY. Avec HAVING COUNT(*) > 1, vous pouvez extraire uniquement les combinaisons qui apparaissent deux fois ou plus.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Exemple : Détection des duplications par « first_name » et « birthday »

SELECT first_name, birthday, COUNT(*) AS count
FROM users
GROUP BY first_name, birthday
HAVING COUNT(*) > 1;

Cette requête vous aide à identifier les cas où la combinaison « même nom » et « même date de naissance » a été enregistrée plusieurs fois.

4-2. Extraction de tous les enregistrements pour des clés multi‑colonnes dupliquées

Si vous avez besoin de tous les détails des enregistrements pour des combinaisons de clés dupliquées, vous pouvez extraire les paires dupliquées dans une sous‑requête puis récupérer toutes les lignes correspondant à ces paires.

SELECT *
FROM table_name t1
WHERE (col1, col2) IN (
  SELECT col1, col2
  FROM table_name
  GROUP BY col1, col2
  HAVING COUNT(*) > 1
);

Exemple : Enregistrements complets pour les duplications de « first_name » et « birthday »

SELECT *
FROM users u1
WHERE (first_name, birthday) IN (
  SELECT first_name, birthday
  FROM users
  GROUP BY first_name, birthday
  HAVING COUNT(*) > 1
);

En utilisant cette requête, par exemple, si la combinaison « Taro Tanaka / 1990-01-01 » est enregistrée plusieurs fois, vous pouvez récupérer toutes les lignes détaillées associées.

4-3. Détection des duplications exactes (COUNT DISTINCT)

Si vous souhaitez estimer « combien de lignes sont des duplicatas exacts sur plusieurs colonnes », vous pouvez également utiliser l’agrégation avec COUNT(DISTINCT ...).

SELECT COUNT(*) - COUNT(DISTINCT col1, col2) AS duplicate_count
FROM table_name;

Ce SQL fournit un compte approximatif des lignes entièrement dupliquées dans la table.

4-4. Remarques

• Même pour la détection de duplicatas sur plusieurs colonnes, un indexage approprié peut améliorer considérablement la vitesse des requêtes .
• S’il y a de nombreuses colonnes impliquées ou que des valeurs NULL sont présentes, vous pouvez obtenir des résultats de duplicata inattendus. Concevez vos conditions avec soin.

De cette manière, détecter et extraire les duplicatas sur plusieurs colonnes peut être géré de façon flexible avec un SQL bien conçu.

5. Suppression des enregistrements en double (DELETE)

Une fois que vous pouvez extraire les données en double, l’étape suivante consiste à supprimer les duplicatas inutiles. En pratique, une approche courante est de conserver un seul enregistrement parmi les duplicatas et de supprimer le reste. Cependant, lors de la suppression automatique des duplicatas dans MySQL, vous devez cibler la suppression avec précision afin d’éviter une perte de données non intentionnelle.

Dans cette section, nous expliquons les méthodes sûres courantes pour supprimer les données en double ainsi que les précautions clés.

5-1. Suppression des duplicatas avec une sous-requête + DELETE

Si vous souhaitez ne conserver que l’enregistrement le plus « ancien » ou le plus « récent » et supprimer les autres, une instruction DELETE avec une sous-requête peut être utile.

Exemple : Conserver l’enregistrement avec l’ID le plus petit (le plus ancien) et supprimer les autres

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Cette requête ne conserve que le plus petit id (l’enregistrement enregistré en premier) pour chaque email, et supprime toutes les autres lignes qui partagent le même email.

5-2. Comment éviter l’erreur spécifique à MySQL (Erreur 1093)

Dans MySQL, vous pouvez rencontrer l’erreur 1093 lorsque vous essayez de DELETE depuis une table tout en référencant la même table dans une sous-requête. Dans ce cas, vous pouvez éviter l’erreur en encapsulant le résultat de la sous-requête comme une table dérivée (ensemble de résultats temporaire).

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY email
  ) AS temp_ids
);

En encapsulant la sous-requête avec SELECT * FROM (...) AS alias, vous pouvez prévenir l’erreur et supprimer en toute sécurité.

5-3. Suppression des duplicatas pour des clés multi‑colonnes

Si vous souhaitez supprimer les duplicatas basés sur une combinaison de plusieurs colonnes, utilisez GROUP BY avec plusieurs colonnes et supprimez tout sauf l’enregistrement représentatif.

Exemple : Pour les duplicatas par « first_name » et « birthday », supprimez tout sauf le premier enregistrement

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY first_name, birthday
  ) AS temp_ids
);

5-4. Mesures de sécurité et bonnes pratiques pour la suppression

Supprimer les duplicatas est une opération à haut risque qui peut supprimer définitivement des données. Assurez‑vous de suivre ces meilleures pratiques :

• Effectuer des sauvegardes : Sauvegardez toujours l’ensemble de la table ou les enregistrements ciblés avant de supprimer.
• Utiliser des transactions : Si possible, encapsulez l’opération dans une transaction afin de pouvoir annuler immédiatement en cas de problème.
• Confirmer les décomptes avec SELECT d’abord : Prenez l’habitude de vérifier « La cible de suppression est‑elle correcte ? » en exécutant d’abord une requête SELECT.
• Vérifier les index : Ajouter des index aux colonnes utilisées pour la détection de duplicatas améliore à la fois les performances et la précision.

Dans MySQL, vous pouvez supprimer en toute sécurité les données en double en utilisant des sous‑requêtes et des tables dérivées. Procédez toujours avec prudence, en effectuant des tests suffisants et en adoptant une stratégie de sauvegarde solide.

6. Considérations de performance et stratégie d’indexation

Lors de l’extraction ou de la suppression de données dupliquées dans MySQL, le temps d’exécution des requêtes et la charge du serveur deviennent plus problématiques à mesure que la table grandit. En particulier dans les systèmes à grande échelle ou les travaux par lots, une conception SQL axée sur les performances et l’optimisation des index sont essentielles. Dans cette section, nous expliquons des conseils pour améliorer les performances et les points clés de la conception des index dans le traitement des données dupliquées.

6-1. Choisir entre EXISTS, IN et JOIN

Les constructions SQL telles que IN, EXISTS et JOIN sont couramment utilisées pour extraire des données dupliquées, mais chacune possède des caractéristiques et des tendances de performance différentes.

• IN – Rapide lorsque le jeu de résultats de la sous‑requête est petit, mais les performances ont tendance à se dégrader à mesure que le jeu de résultats augmente.
• EXISTS – Arrête la recherche dès qu’un enregistrement correspondant est trouvé, ce qui est souvent efficace pour les grandes tables ou lorsque les correspondances sont relativement rares.
• JOIN – Utile pour récupérer de nombreuses informations en une fois, mais cela peut devenir plus lent si vous joignez des données inutiles ou si vous manquez d’indexation appropriée.

Exemple de comparaison de performances

Il est important de choisir la syntaxe optimale en fonction de votre système réel et du volume de données.

6-2. Pourquoi la conception des index est importante

Pour les colonnes utilisées dans les vérifications de doublons ou les filtres de suppression, créez toujours des index. Sans index, des scans complets de la table peuvent se produire et les performances peuvent devenir extrêmement lentes.

Exemple : Création d’un index

CREATE INDEX idx_email ON users(email);

Si vous détectez des doublons sur plusieurs colonnes, un index composite est également efficace.

CREATE INDEX idx_name_birthday ON users(first_name, birthday);

La conception des index peut modifier de façon spectaculaire les performances de lecture et l’efficacité de la recherche.
Note : Ajouter trop d’index peut ralentir les écritures et augmenter l’utilisation du stockage, il est donc important de trouver un équilibre.

6-3. Traitement par lots pour les grands ensembles de données

• Si le jeu de données est de l’ordre de dizaines de milliers à des millions de lignes, il est plus sûr d’exécuter le traitement en lots plus petits plutôt que de tout gérer en une fois.
• Pour les suppressions et les mises à jour, limitez le nombre de lignes traitées par exécution (par ex., LIMIT 1000 ) et exécutez plusieurs fois afin de réduire la contention des verrous et la dégradation des performances. DELETE FROM users WHERE id IN ( -- Les 1000 premiers IDs d'enregistrements dupliqués extraits par une sous‑requête ) LIMIT 1000;

6-4. Utilisation des plans d’exécution (EXPLAIN)

Utilisez EXPLAIN pour analyser comment une requête est exécutée. Cela vous aide à vérifier si les index sont utilisés efficacement, et si un scan complet (ALL) se produit.

EXPLAIN SELECT * FROM users WHERE email IN (...);

En gardant à l’esprit les performances et la stratégie d’indexation, vous pouvez gérer le traitement des doublons de manière sûre et efficace même pour de grands ensembles de données.

7. Cas d’utilisation avancés : Gestion de scénarios complexes

Dans les environnements réels, la détection et la suppression de doublons sont souvent plus complexes qu’une simple correspondance. Vous pouvez avoir besoin d’ajouter des conditions supplémentaires, d’exécuter des opérations en toute sécurité par étapes, ou de répondre à des exigences opérationnelles plus strictes. Dans cette section, nous présentons des techniques pratiques avancées pour gérer les données dupliquées de manière sûre et flexible.

7-1. Suppression conditionnelle de doublons

Si vous souhaitez supprimer uniquement les doublons qui répondent à des conditions spécifiques, utilisez la clause WHERE de manière stratégique.

Exemple : Supprimer uniquement les enregistrements dupliqués avec le même email et status = 'withdrawn'

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    WHERE status = 'withdrawn'
    GROUP BY email
  ) AS temp_ids
)
AND status = 'withdrawn';

En ajoutant des conditions à WHERE et GROUP BY, vous pouvez contrôler précisément quels enregistrements conserver et quels supprimer.

7-2. Recommandé : Traitement par lots et exécution fractionnée

• Ne traitez pas toutes les cibles de suppression en même temps—utilisez LIMIT pour une exécution fractionnée
• Utilisez le contrôle des transactions et effectuez un rollback en cas d’erreurs inattendues
• Gérez le risque avec des sauvegardes et la journalisation DELETE FROM users WHERE id IN ( SELECT id FROM ( -- Extract duplicate record IDs filtered by conditions ) AS temp_ids ) LIMIT 500;

Cette approche réduit considérablement la charge du système.

7-3. Gestion des définitions complexes de doublons

Dans différents contextes métier, la définition de « doublon » varie. Vous pouvez combiner des sous‑requêtes, des expressions CASE et des fonctions d’agrégation pour une gestion flexible.

Exemple : ne considérer les doublons que lorsque product_id, order_date et price sont tous identiques

SELECT product_id, order_date, price, COUNT(*)
FROM orders
GROUP BY product_id, order_date, price
HAVING COUNT(*) > 1;

Pour des exigences plus avancées, comme « conserver uniquement l’enregistrement le plus récent parmi les doublons », vous pouvez utiliser des sous‑requêtes ou ROW_NUMBER() (disponible dans MySQL 8.0 et versions ultérieures).

7-4. Bonnes pratiques pour les transactions et les sauvegardes

• Enveloppez toujours les opérations DELETE ou UPDATE dans des transactions afin de pouvoir restaurer les données avec ROLLBACK en cas de problème.
• Si vous travaillez avec des tables importantes ou de gros ensembles de données, créez toujours une sauvegarde au préalable .

En maîtrisant ces techniques avancées, vous pouvez gérer le traitement des données en double de manière sûre et flexible dans n’importe quel environnement.

8. Résumé

Dans cet article, nous avons expliqué de manière systématique comment extraire et supprimer les données en double dans MySQL, des bases aux applications avancées. Passons en revue les points clés.

8-1. Points clés

• Détection des données en double Vous pouvez détecter les doublons non seulement dans une seule colonne mais aussi sur plusieurs colonnes. La combinaison de GROUP BY et HAVING COUNT(*) > 1 constitue le modèle fondamental de détection des doublons.
• Extraction de tous les enregistrements en double En utilisant des sous‑requêtes et la clause EXISTS, vous pouvez récupérer tous les enregistrements correspondant aux valeurs de clé en double.
• Suppression des enregistrements en double En utilisant MIN(id) ou MAX(id) pour conserver les lignes représentatives et en combinant des sous‑requêtes avec des instructions DELETE, vous pouvez supprimer en toute sécurité les doublons inutiles. Éviter l’erreur MySQL 1093 est également important.
• Performance et indexation Pour de grands ensembles de données ou des conditions complexes, une indexation appropriée, le traitement par lots et la vérification du plan d’exécution avec EXPLAIN sont essentiels.
• Techniques pratiques La suppression conditionnelle, l’exécution fractionnée, la gestion des transactions et les sauvegardes sont des pratiques clés pour éviter les erreurs en production.

8-2. Référence rapide par cas d’utilisation

8-3. Prévention des futurs problèmes de doublons

Prévenir les doublons au moment de l’insertion est tout aussi important.

• Envisagez d’utiliser des contraintes UNIQUE lors de la conception des tables.
• Un nettoyage régulier des données et des audits aident à détecter tôt les problèmes opérationnels.

L’extraction et la suppression de données en double dans MySQL nécessitent des connaissances allant du SQL de base aux techniques avancées. Nous espérons que ce guide soutient votre maintenance de bases de données et vos opérations système.
Si vous avez des cas spécifiques ou d’autres questions, consultez la FAQ ou consultez un spécialiste de bases de données.

9. FAQ : Questions fréquentes sur l’extraction et la suppression de données en double dans MySQL

Q1. Pourquoi utiliser GROUP BY + HAVING au lieu de DISTINCT ?

DISTINCT supprime les doublons dans le jeu de résultats, mais il ne peut pas indiquer combien de fois une valeur apparaît. En combinant GROUP BY et HAVING COUNT(*) > 1, vous pouvez déterminer quelles valeurs apparaissent plusieurs fois et combien de doublons existent.

Q2. Dois-je utiliser IN ou EXISTS ?

Pour de petits ensembles de données, la différence est minime. Pour de grandes tables ou lorsque les index sont efficaces, EXISTS offre souvent de meilleures performances. Testez les deux approches dans votre environnement et vérifiez les plans d’exécution avec EXPLAIN.

Q3. Comment détecter les doublons sur plusieurs colonnes ?

Spécifiez plusieurs colonnes dans GROUP BY et utilisez HAVING COUNT(*) > 1 pour détecter les combinaisons où toutes les colonnes spécifiées correspondent. Exemple : GROUP BY first_name, birthday

Q4. J’obtiens l’erreur 1093 lors de l’exécution de DELETE. Que faire ?

MySQL génère l’erreur 1093 lorsque vous faites référence à la même table dans une sous‑requête au sein d’une instruction DELETE. Enveloppez le résultat de la sous‑requête dans une table dérivée en utilisant SELECT * FROM (...) AS alias pour éviter l’erreur.

Q5. Comment supprimer en toute sécurité les données en double ?

Créez toujours une sauvegarde avant la suppression, vérifiez les cibles avec une instruction SELECT, et utilisez des transactions lorsque c’est possible. La suppression par lots peut également être plus sûre pour les grands ensembles de données.

Q6. Que faire si les requêtes sont lentes avec de gros volumes de données ?

Créez des index sur les colonnes utilisées pour la détection des doublons. Utilisez le traitement par lots avec LIMIT et examinez les plans d’exécution avec EXPLAIN afin d’éviter les scans complets de table inutiles.

Q7. Comment prévenir fondamentalement les insertions en double ?

Définissez des contraintes UNIQUE ou des clés uniques lors de la conception de la table pour empêcher l’insertion de valeurs dupliquées. Effectuez également des vérifications périodiques des doublons et un nettoyage des données après le déploiement.

Q8. Les mêmes méthodes peuvent‑elles être utilisées dans MariaDB ou d’autres SGBDR ?

Les constructions SQL de base telles que GROUP BY, HAVING et les sous‑requêtes sont également prises en charge dans MariaDB et PostgreSQL. Cependant, les restrictions sur les sous‑requêtes DELETE et les caractéristiques de performance peuvent varier selon le produit, il faut donc toujours tester au préalable.