Cet article explique comment rechercher des lignes dupliquées dans une table de base de données. C’est une question très courante pour les débutants. La technique de base est simple. Je vais également montrer quelques variantes, telles que la recherche de « doublons dans deux colonnes » (une question récente sur le canal IRC #mysql).
Comment trouver les lignes dupliquées
La première étape consiste à définir ce qui fait exactement d’une ligne un doublon d’une autre ligne. La plupart du temps, c’est facile: ils ont la même valeur dans certaines colonnes. Je vais prendre cela comme une définition de travail pour cet article, mais vous pouvez besoin de modifier les requêtes ci-dessous si votre notion de «dupliquer» est plus compliquée.
Pour cet article, je vais utiliser cet exemple de données:
Les deux premières lignes ont le même valeur dans la colonne day, donc si je considère qu’il s’agit de doublons, voici une requête pour les trouver. La requête utilise une clause GROUP BY pour placer toutes les lignes avec la même valeur day dans un « groupe », puis compter la taille du group:
Les lignes dupliquées ont un nombre supérieur à un. Si vous ne voulez voir que les lignes dupliquées, vous devez utiliser un HAVING clause (pas une clause WHERE), comme ceci:
C’est la technique de base: groupez par la colonne qui contient les doublons, et n’affichez que les groupes ayant plus d’une ligne.
Pourquoi ne pouvez-vous pas utiliser une clause WHERE?
A La clause WHERE filtre les lignes avant qu’elles ne soient regroupées. Une clause HAVING les filtre après le regroupement. C’est pourquoi vous ne pouvez pas utiliser de HAVING. div id = « 8c41817077 »> clause dans la requête ci-dessus.
Comment supprimer les lignes en double
Une question connexe est de savoir comment supprimer les lignes « en double » une fois que vous les trouver. Un t commun demander lors du nettoyage de mauvaises données est de supprimer tous les doublons sauf un, afin que vous puissiez mettre les bons index et clés primaires sur la table, et empêcher les doublons de rentrer dans la table.
Encore une fois, le premier la chose à faire est de vous assurer que votre définition est claire. Quelle ligne souhaitez-vous conserver exactement? Le premier? Celui avec la plus grande valeur d’une colonne? Pour cet article, je suppose que vous souhaitez conserver la « première » ligne, celle avec la plus petite valeur de la colonne id. Cela signifie que vous voulez supprimer toutes les autres lignes.
La façon la plus simple de le faire est probablement d’utiliser une table temporaire. Surtout dans MySQL, il existe certaines restrictions concernant la sélection à partir d’une table et sa mise à jour dans la même requête. Vous pouvez les contourner, comme je l’explique dans mon article Comment sélectionner une cible de mise à jour dans MySQL, mais je vais simplement éviter ces complications et utiliser une table temporaire.
La définition exacte de la tâche est pour supprimer toutes les lignes qui ont un doublon, à l’exception de la ligne avec la valeur minimale de id pour ce groupe. Vous devez donc trouver non seulement les lignes où il y en a plusieurs dans le groupe, mais également la ligne que vous souhaitez conserver. Vous pouvez le faire avec la fonction MIN(). Voici quelques requêtes pour créer la table temporaire et trouver les données dont vous avez besoin pour faire le DELETE:
Maintenant que vous avez ces données, vous pouvez procéder à la suppression les «mauvaises» lignes. Il existe de nombreuses façons de le faire, et certaines sont meilleures que d’autres (voir mon article sur les problèmes plusieurs-à-un en SQL), mais encore une fois, j’éviterai les points plus fins et vous montrerai simplement une syntaxe standard qui devrait fonctionner dans tout SGBDR prenant en charge les sous-requêtes:
Si votre SGBDR ne prend pas en charge les sous-requêtes, ou s’il est plus efficace, vous souhaiterez peut-être effectuer une suppression multi-tables. La syntaxe pour cela varie d’un système à l’autre, vous devez donc consulter la documentation de votre système. Vous devrez peut-être également faire tout cela dans une transaction pour éviter que d’autres utilisateurs ne modifient les données pendant que vous travaillez, si cela pose un problème.
Comment trouver des doublons dans plusieurs colonnes
Quelqu’un a récemment posé une question similaire à celle-ci sur le canal IRC #mysql:
J’ai un tableau avec des colonnes
betcqui relie deux autres tablesbetc, et je veux pour rechercher toutes les lignes contenant des doublons dansbouc.
Il était difficile de comprendre exactement ce que cela signifiait, mais après quelques conversations je l’ai compris: la personne voulait pouvoir mettre des index uniques sur les colonnes b et c séparément.
Il est assez facile de trouver des lignes avec des valeurs en double dans l’une ou l’autre colonne, comme je vous l’ai montré ci-dessus: il suffit de grouper par cette colonne mn et comptez la taille du groupe. Et il est facile de trouver des lignes entières qui sont des doublons exacts d’autres lignes: il vous suffit de les grouper par autant de colonnes que nécessaire.Mais il est plus difficile d’identifier les lignes qui ont une valeur b dupliquée ou une valeur c dupliquée. Prenons l’exemple de tableau suivant, qui est à peu près ce que la personne a décrit:
Maintenant, vous pouvez facilement voir qu’il y a des lignes « dupliquées » dans ce tableau, mais aucune ligne n’a en fait le même tuple {b, c}. C’est pourquoi c’est un peu plus difficile à résoudre.
Requêtes qui ne fonctionnent pas
Si vous groupez par deux colonnes, vous obtiendrez différents résultats en fonction de la façon dont vous groupez et compter. C’est là que l’utilisateur IRC était perplexe. Parfois, les requêtes trouvent des doublons mais pas d’autres. Voici quelques-unes des choses que cette personne a essayées:
Cette requête renvoie chaque ligne du tableau, avec un COUNT(*) de 1, ce qui semble être un mauvais comportement, mais ce n’est pas le cas. Pourquoi? Parce que le > 1 est à l’intérieur du COUNT(). C’est assez facile à manquer, mais cette requête est en fait la même que
Pourquoi? Parce que (b > 1) est une expression booléenne. Ce n’est pas du tout ce que vous voulez. Vous voulez
Ceci renvoie zéro ligne, bien sûr, car il n’y a pas de doublons {b, c}. La personne a essayé de nombreuses autres combinaisons de clauses HAVING et OR et AND, en les groupant par une colonne et en comptant l’autre, et ainsi de suite:
Rien n’a trouvé tous les doublons, cependant. Ce que je pense le plus frustrant, c’est que cela a partiellement fonctionné, ce qui a fait croire à la personne que c’était presque la bonne requête … peut-être qu’une autre variante pourrait l’obtenir …
En fait, il est impossible de faire avec ce type de requête GROUP BY simple. Pourquoi est-ce? En effet, lorsque vous regroupez par une colonne, vous répartissez les valeurs de l’autre colonne dans plusieurs groupes. Vous pouvez le voir visuellement en triant ces colonnes, ce que fait le regroupement. Commencez par classer par colonne b et voyez comment ils sont regroupés:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Lorsque vous commandez (groupez) par colonne b, les valeurs en double dans la colonne c sont répartis en différents groupes, vous ne pouvez donc pas les compter avec COUNT(DISTINCT c) comme la personne essayait de le faire. Les fonctions d’agrégation telles que COUNT() ne fonctionnent qu’au sein d’un groupe et n’ont pas accès aux lignes placées dans d’autres groupes. De même, lorsque vous commandez par c, les valeurs en double dans la colonne b sont réparties dans différents groupes. Il n’est pas possible de faire en sorte que cette requête fasse ce que vous souhaitez.
Quelques solutions correctes
La solution la plus simple est probablement de trouver les doublons pour chaque colonne séparément et UNION ensemble, comme ceci:
La colonne what_col dans la sortie indique dans quelle colonne la valeur dupliquée a été trouvée. Une autre approche consiste à utiliser sous-requêtes:
Ceci est probablement beaucoup moins efficace que l’approche UNION, et affichera chaque ligne dupliquée, pas seulement les valeurs qui sont dupliquées. Une autre approche encore consiste à effectuer des auto-jointures contre des sous-requêtes groupées dans la clause FROM. C’est plus compliqué à écrire correctement, mais cela peut être nécessaire pour certaines données complexes ou pour plus d’efficacité:
N’importe laquelle de ces requêtes fera l’affaire, et je suis sûr qu’il existe d’autres moyens. Si vous pouvez utiliser UNION, c’est probablement le plus simple.
