So finden und löschen Sie doppelte Zeilen in MySQL

1. So finden Sie doppelte Zeilen

Der erste Schritt besteht darin, zu definieren, was als doppelte Zeile zählt. In den meisten Fällen ist dies einfach: Sie haben den gleichen Wert in einer Spalte. Dieser Artikel verwendet diese Definition. Vielleicht ist Ihre Definition von „Duplikat“ komplizierter und Sie müssen einige Änderungen am SQL vornehmen.

Die in diesem Artikel verwendeten Datenbeispiele sind:

Tabellentest erstellen (ID Int ungleich Null, Primärschlüssel, Tag/Datum ungleich Null);  

in Test einfügen (ID, Tag) Werte (1, „08.10.2006“);  
in Test einfügen (ID, Tag) Werte (2, „08.10.2006“);  
in Test einfügen (ID, Tag) Werte (3, „09.10.2006“);  

wähle * aus Test;  
+----+------------+  
| ID | Tag |  
+----+------------+  
| 1 | 08.10.2006 |  
| 2 | 08.10.2006 |  
| 3 | 09.10.2006 |  
+----+------------+

Die ersten beiden Zeilen haben den gleichen Wert im day . Wenn ich sie also als Duplikate behandle, folgt hier eine Abfrage, um sie zu finden. Die Abfrage verwendet die GROUP BY-Klausel, um Zeilen mit denselben Feldwerten zu gruppieren und berechnet dann die Größe der Gruppe.

Tag auswählen, Anzahl(*) aus Test GROUP BY Tag;  
+------------+----------+  
| Tag | Anzahl(*) |  
+------------+----------+  
| 08.10.2006 | 2 |  
| 09.10.2006 | 1 |  
+------------+----------+

Die Gruppengröße für doppelte Zeilen ist größer als 1. Wenn Sie nur doppelte Zeilen anzeigen möchten, müssen Sie die HAVING-Klausel verwenden, wie zum Beispiel

Wähle Tag, Anzahl(*) aus der Testgruppe nach Tag, wobei Anzahl(*) > 1 ist;  
+------------+----------+  
| Tag | Anzahl(*) |  
+------------+----------+  
| 08.10.2006 | 2 |  
+------------+----------+

Dies ist die grundlegende Technik: Gruppieren Sie nach einem Feld mit demselben Wert und zeigen Sie dann Gruppen mit einer Größe größer als 1 an.

Warum kann ich die WHERE-Klausel nicht verwenden? Weil die WHERE-Klausel die Zeilen vor der Gruppierung filtert und die HAVING-Klausel die Zeilen nach der Gruppierung filtert.

2. So löschen Sie doppelte Zeilen

Eine damit verbundene Frage ist, wie doppelte Zeilen entfernt werden. Eine häufige Aufgabe besteht darin, nur eine Zeile mit Duplikaten beizubehalten und die anderen zu löschen. Anschließend können Sie entsprechende Indizes erstellen, um zu verhindern, dass in Zukunft doppelte Zeilen in die Datenbank geschrieben werden.

Auch hier müssen Sie zunächst herausfinden, was eine doppelte Zeile ist. Welche Linie möchtest Du beibehalten? Die erste Zeile oder die Zeile mit dem Maximalwert für ein bestimmtes Feld? In diesem Artikel wird davon ausgegangen, dass Sie die erste Zeile behalten möchten – die Zeile mit dem kleinsten Wert im ID-Feld, was bedeutet, dass Sie die anderen Zeilen löschen möchten.

Der einfachste Weg, dies zu tun, ist wahrscheinlich die Verwendung einer temporären Tabelle. Insbesondere bei MYSQL gibt es einige Einschränkungen, z. B. können Sie eine Tabelle nicht update , während Sie in einer Abfrageanweisung eine select . Der Einfachheit halber wird hier nur die Methode der temporären Tabelle verwendet.

Unsere Aufgabe besteht darin, alle doppelten Zeilen außer der Zeile mit dem Mindestwert id Felds in der Gruppe zu entfernen. Daher müssen wir die Gruppen finden, deren Größe größer als 1 ist, und die Zeilen, die wir behalten möchten. Sie können die Funktion MIN() verwenden. Die Anweisungen hier erstellen eine temporäre Tabelle und finden die Zeilen, die mit DELETE gelöscht werden müssen.

Erstellen Sie eine temporäre Tabelle zum Löschen (Tagesdatum ungleich null, min_id int ungleich null).  

einfügen in to_delete(Tag, min_id)  
  Wählen Sie Tag, MIN(id) aus der Testgruppe nach Tag mit count(*) > 1;  

wähle * aus to_delete;  
+------------+--------+  
| Tag | min_id |  
+------------+--------+  
| 08.10.2006 | 1 |  
+------------+--------+

Wenn Sie diese Daten zur Hand haben, können Sie mit dem Löschen der „schmutzigen“ Zeilen beginnen. Es gibt mehrere Ansätze, jeder mit seinen eigenen Vor- und Nachteilen, aber ich werde sie hier nicht im Detail vergleichen, sondern nur den Standardansatz erläutern, der in relationalen Datenbanken verwendet wird, die Abfrageklauseln unterstützen.

aus Test löschen  
  wo existiert(  
     Wählen Sie * von to_delete  
     wobei to_delete.day = test.day und to_delete.min_id <> test.id  
  )

3. So finden Sie Duplikate in mehreren Spalten

Jemand hat kürzlich diese Frage gestellt: Ich habe in einer meiner Tabellen zwei Felder b und c, die jeweils mit den Feldern b und c zweier anderer Tabellen verknüpft sind. Ich möchte die Zeilen herausfinden, die entweder im Feld B oder im Feld C doppelte Werte haben.

Anfangs war es schwer zu verstehen, aber nach einem Gespräch habe ich es verstanden: Er wollte unique Indizes für b bzw. c erstellen. Wie oben erwähnt, ist das Auffinden von Zeilen mit doppelten Werten für ein Feld ganz einfach: Sie müssen sie lediglich mit group gruppieren und dann die Größe der Gruppe berechnen. Und es ist auch sehr einfach, Zeilen mit doppelten Feldern zu finden: Fügen Sie einfach alle Felder in die Gruppenklausel ein. Wenn das Problem jedoch darin besteht, zu bestimmen, ob Feld b oder Feld c wiederholt wird, ist das Problem viel schwieriger. Die vom Fragesteller hier verwendeten Beispieldaten

Tabelle a_b_c erstellen(  
  ein int ungleich null Primärschlüssel auto_increment,  
  b int,  
  c int  
);  

in a_b_c(b,c) Werte (1, 1) einfügen;  
in a_b_c(b,c) Werte (1, 2) einfügen;  
in a_b_c(b,c) Werte (1, 3) einfügen;  
in a_b_c(b,c) Werte (2, 1) einfügen;  
in a_b_c(b,c) Werte (2, 2) einfügen;  
in a_b_c(b,c) Werte (2, 3) einfügen;  
in a_b_c(b,c) Werte (3, 1) einfügen;  
in a_b_c(b,c) Werte (3, 2) einfügen;  
in a_b_c(b,c) Werte (3, 3) einfügen;

Jetzt können Sie leicht erkennen, dass die Tabelle einige doppelte Zeilen enthält, aber keine zwei Zeilen dasselbe Tupel {b, c} aufweisen. Deshalb wird das Problem schwierig.

4. Falsche Abfrageanweisungen

Wenn Sie beide Spalten gruppieren, erhalten Sie je nach Gruppierungsmethode und Berechnung der Größe unterschiedliche Ergebnisse. Genau hier bleibt der Fragesteller hängen. Manchmal findet eine Abfrage einige doppelte Zeilen, übersieht aber andere. Dies ist die Abfrage, die er verwendet hat

wähle b, c, anzahl(*) aus a_b_c  
Gruppieren nach b, c  
mit Anzahl (verschiedene b > 1)  
  oder Anzahl (unterschiedliche c > 1);

Das Ergebnis gibt alle Zeilen zurück, da CONT(*) immer 1 ist. Warum? Weil in COUNT() >1 geschrieben wird. Dieser Fehler wird leicht übersehen und ist in der Tat gleichbedeutend mit

wähle b, c, anzahl(*) aus a_b_c  
Gruppieren nach b, c  
mit Anzahl(1)  
  oder zähle(1);

Warum? Weil (b > 1) ein Boolescher Wert ist, was Sie überhaupt nicht wollen. Was Sie wollen, ist

wähle b, c, anzahl(*) aus a_b_c  
Gruppieren nach b, c  
mit count(distinct b) > 1  
  oder Anzahl(unterschiedliche c) > 1;

Gibt ein leeres Ergebnis zurück. Offensichtlich, da es keine Duplikate von {b,c} gibt. Die Person probierte viele andere Kombinationen von OR und AND aus, wobei sie ein Feld zum Gruppieren und ein anderes Feld zum Berechnen der Größe verwendete, wie hier

Wähle b, Anzahl(*) aus der Gruppe a_b_c nach b, wobei Anzahl(unterschiedliche c) > 1 ist;  
+------+----------+  
| b | Anzahl(*) |  
+------+----------+  
| 1 | 3 |  
| 2 | 3 |  
| 3 | 3 |  
+------+----------+

Keiner von ihnen kann alle doppelten Zeilen finden. Und das Frustrierendste ist, dass diese Aussage in manchen Situationen gültig ist, wenn Sie fälschlicherweise glauben, dass dies die richtige Schreibweise ist, in anderen Situationen jedoch möglicherweise das falsche Ergebnis erhalten.

Tatsächlich ist die einfache Verwendung GROUP BY nicht praktikabel. Warum? Denn wenn Sie group by auf ein bestimmtes Feld anwenden, werden die Werte eines anderen Felds auf verschiedene Gruppen verteilt. Beim Sortieren dieser Felder können diese Effekte ebenso auftreten wie beim Gruppieren. Sortieren Sie zuerst das B-Feld, um zu sehen, wie es gruppiert ist.

Wenn Sie das Feld b sortieren (gruppieren), wird der gleiche Wert von c in verschiedene Gruppen aufgeteilt. Daher können Sie用COUNT(DISTINCT c) . Interne Funktionen wie COUNT () funktionieren nur in derselben Gruppe und können für Zeilen in anderen Gruppen nichts tun. Wenn das Feld c sortiert ist, werden Felder b mit demselben Wert in verschiedene Gruppen aufgeteilt, womit wir unser Ziel aber ohnehin nicht erreichen.

5. Mehrere richtige Methoden

Die einfachste Möglichkeit besteht wahrscheinlich darin, für jedes Feld einzeln nach doppelten Zeilen zu suchen und diese dann mit UNION wie folgt zu kombinieren:

Wähle b als Wert, count(*) als cnt, 'b' als what_col  
von a_b_c gruppiert nach b mit count(*) > 1  
Union  
Wähle c als Wert, count(*) als cnt, 'c' als what_col  
von a_b_c-Gruppe nach c mit count(*) > 1;  
+-------+-----+----------+  
| Wert | Anzahl | welche_Spalte |  
+-------+-----+----------+  
| 1 | 3 | b |  
| 2 | 3 | b |  
| 3 | 3 | b |  
| 1 | 3 | c |  
| 2 | 3 | c |  
| 3 | 3 | c |  
+-------+-----+----------+

Das Feld what_col wird ausgegeben, um anzuzeigen, welches Feld wiederholt wird. Ein anderer Ansatz besteht darin, verschachtelte Abfragen zu verwenden:

wähle a, b, c aus a_b_c  
wobei b in (wähle b aus a_b_c-Gruppe nach b mit count(*) > 1)  
   oder c in (wähle c aus der a_b_c-Gruppe nach c mit count(*) > 1);  
+----+------+------+  
| ein | 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 |  
+----+------+------+

Dieser Ansatz ist viel weniger effizient als die Verwendung UNION und zeigt jede wiederholte Zeile anstelle der wiederholten Feldwerte an. Eine andere Methode besteht darin, die verschachtelten Abfrageergebnisse von sich selbst abzufragen und in einer gemeinsamen Tabelle zu gruppieren. Die Schreibmethode ist relativ kompliziert, aber bei komplexen Daten oder Situationen mit hohen Anforderungen an die Effizienz notwendig.

 wähle a, a_b_c.b, a_b_c.c  
von a_b_c  
  linker äußerer Join (  
     wähle b aus a_b_c-Gruppe nach b mit count(*) > 1  
  ) als b auf a_b_c.b = bb  
  linker äußerer Join (  
     wähle c aus a_b_c-Gruppe nach c mit count(*) > 1  
  ) als c auf a_b_c.c = cc  
wobei bb nicht null ist oder cc nicht null ist

Die oben genannten Methoden funktionieren, und ich bin sicher, dass es noch andere gibt. Wenn UNION verwendet werden kann, ist es meiner Meinung nach am einfachsten.

Dies ist das Ende dieses Artikels zum Suchen und Löschen doppelter Zeilen in MySQL. Weitere Informationen zum Suchen und Löschen doppelter Zeilen in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder durchsuchen Sie die folgenden verwandten Artikel weiter. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!