So finden und löschen Sie doppelte Datensätze in MySQL

Hallo zusammen, ich bin Tony, ein Lehrer, der nur über Techniken spricht und keine Haare schneidet. Aus historischen Gründen oder aufgrund einer Fehlbedienung können in der Datentabelle doppelte Datensätze vorhanden sein. Heute sprechen wir darüber, wie doppelte Daten in MySQL-Tabellen gefunden und wie diese doppelten Datensätze gelöscht werden.

Erstellen einer Beispieltabelle

Erstellen Sie zunächst eine Beispieltabelle „Personen“ und generieren Sie einige Daten:

Tabelle löschen, wenn Personen vorhanden sind;
Tabelle Personen erstellen (
 id int auto_increment Primärschlüssel,
 Name varchar(50) nicht null,
 E-Mail varchar(100) nicht null
);

in Personen einfügen (Name, E-Mail)
Werte ('张三', '[email protected]'),
  ('Li Si', '[email protected]'),
  ('Wang Wu', '[email protected]'),
  ('lisi', '[email protected]'),
  ('Wang Wu', '[email protected]'),
  ('Wang Wu', '[email protected]');

wählen Sie * aus Personen aus;
ID|Name |E-Mail |
--|------|-----------------|
 1|Zhang San|[email protected]|
 2|Li Si|[email protected] |
 3|Wang Wu|[email protected] |
 4|Lisi|[email protected] |
 5|Wang Wu|[email protected] |
 6|Wang Wu|[email protected] |

Darunter enthalten die E-Mail-Felder 2 und 4 doppelte Daten; die Namens- und E-Mail-Felder 3, 5 und 6 enthalten doppelte Daten.

Wenn wir an diesem Punkt versuchen, eine eindeutige Einschränkung für E-Mails zu erstellen, wird ein Fehler zurückgegeben:

Tabelle „Personen“ ändern, Einschränkung „uk_people_email“ hinzufügen, eindeutiger Schlüssel (E-Mail);
FEHLER 1062 (23000): Doppelter Eintrag „[email protected]“ für Schlüssel „people.uk_people_email“

Offensichtlich müssen wir doppelte Datensätze im E-Mail-Feld finden und entfernen, um eine eindeutige Einschränkung zu erstellen.

Suchen nach doppelten Daten in einem einzelnen Feld

Wenn Sie doppelte E-Mail-Daten finden möchten, können Sie basierend auf diesem Feld gruppieren und zählen und die Gruppen mit mehr als einer Zeile zurückgeben:

E-Mail auswählen, Anzahl (E-Mail)
von Leuten
nach E-Mail gruppieren
mit Anzahl (E-Mails) > 1;
E-Mail |Anzahl(E-Mail)|
---------------|------------|
[email protected] | 2|
[email protected]| 3|

Die Abfrageergebnisse zeigen, dass zwei doppelte E-Mail-Adressen vorhanden sind. Wenn Sie die vollständigen doppelten Daten anzeigen möchten, können Sie eine Unterabfrage oder eine Join-Abfrage verwenden:

wählen *
von Leuten
wo E-Mail in (
  E-Mail auswählen
  von Leuten
  nach E-Mail gruppieren
  mit Anzahl (E-Mail) > 1)
Bestellung per E-Mail;
ID|Name |E-Mail |
--|------|--------------|
 2|Li Si|[email protected] |
 4|Lisi|[email protected] |
 3|Wang Wu|[email protected]|
 5|Wang Wu|[email protected]|
 6|Wang Wu|[email protected]|

wähle p.*
von Leuten
verbinden (
 E-Mail auswählen
 von Leuten
 nach E-Mail gruppieren
 mit count(email) > 1
) d auf p.email = d.email
Bestellung per E-Mail;
ID|Name |E-Mail |
--|------|--------------|
 2|Li Si|[email protected] |
 4|Lisi|[email protected] |
 3|Wang Wu|[email protected]|
 5|Wang Wu|[email protected]|
 6|Wang Wu|[email protected]|

Eine weitere Möglichkeit zum Auffinden doppelter Datensätze besteht in der direkten Verwendung einer Self-Join-Abfrage und des Distinct-Operators. Beispiel:

wähle ein bestimmtes p.*
von Leuten
schließe dich Leuten an d auf p.email = d.email
wobei p.id <> d.id
Bestellung per E-Mail;
ID|Name |E-Mail |
--|------|--------------|
 4|Lisi|[email protected] |
 2|Li Si|[email protected] |
 6|Wang Wu|[email protected]|
 5|Wang Wu|[email protected]|
 3|Wang Wu|[email protected]|

Beachten Sie, dass „distinct“ nicht weggelassen werden kann, da sonst einige Daten (3, 5, 6) mehrfach zurückgegeben werden.

Suchen nach doppelten Daten in mehreren Feldern

Wenn wir Daten mit doppelten Namens- und E-Mail-Feldern finden möchten, ist die Implementierung ähnlich:

wählen *
von Leuten
wobei (Name, E-Mail) in (
  Wählen Sie Name, E-Mail
  von Leuten
  Gruppieren nach Name, E-Mail
  mit count(1) > 1)
Bestellung per E-Mail;
ID|Name |E-Mail |
--|------|--------------|
 3|Wang Wu|[email protected]|
 5|Wang Wu|[email protected]|
 6|Wang Wu|[email protected]|

wähle ein bestimmtes p.*
von Leuten
schließe dich den Leuten an, die p.name = d.name und p.email = d.email verwenden
wobei p.id <> d.id
Bestellung per E-Mail;
ID|Name |E-Mail |
--|------|--------------|
 6|Wang Wu|[email protected]|
 5|Wang Wu|[email protected]|
 3|Wang Wu|[email protected]|

Doppelte Daten liegen nur vor, wenn Name und E-Mail-Adresse identisch sind. Daher sind 2 und 4 keine doppelten Datensätze.

Deduplizierung

Nachdem wir die doppelten Daten gefunden haben, müssen wir das Problem lösen, wie wir sie löschen können. Normalerweise müssen wir einen der Datensätze behalten.

Löschen doppelter Daten mit DELETE FROM

Wenn wir doppelte E-Mail-Datensätze löschen und nur einen behalten möchten, können wir dazu die Anweisung DELETE FROM verwenden:

p löschen
von Leuten
schließe dich den Leuten d an unter p.email = d.email und p.id < d.id;

Die Löschanweisung verwendet eine Verbindung, um die zu löschenden Datensätze zu finden. Im obigen Beispiel bleibt die Datenzeile erhalten, die der größten ID in den doppelten Daten entspricht. Fragen Sie die Personentabelle erneut ab:

wählen Sie * aus Personen aus;
ID|Name |E-Mail |
--|------|-----------------|
 1|Zhang San|[email protected]|
 4|Lisi|[email protected] |
 6|Wang Wu|[email protected] |

Überlegen Sie: Wie können Sie die Daten mit der kleinsten ID unter den doppelten Daten behalten?

Löschen doppelter Daten mithilfe von Unterabfragen

Sie können Unterabfragen verwenden, um die Daten zu finden, die Sie behalten müssen, und dann den Rest löschen:

löschen
von Leuten
wo id nicht in (
  wähle max(id)
  von Leuten
  nach E-Mail gruppieren
  );

Denken Sie vor der Ausführung der obigen Anweisung daran, die Personentabelle neu zu erstellen und Testdaten zu generieren.

Löschen doppelter Daten über Zwischentabellen

Das Löschen doppelter Datensätze kann auch durch die Verwendung einer Zwischentabelle erreicht werden, zum Beispiel:

- Erstellen Sie eine Zwischentabelle, erstellen Sie die Tabelle people_temp wie people;

-- Kopieren Sie die Datenzeilen, die beibehalten werden müssen, und fügen Sie sie in people_temp ein (ID, Name, E-Mail).
Wählen Sie ID, Name, E-Mail
von Leuten
wobei id in (
  wähle max(id)
  von Leuten
  nach E-Mail gruppieren
  );

--Löschen Sie die ursprüngliche Tabelle, Drop-Tabelle Personen;

-- Benennen Sie die Zwischentabelle in die Originaltabelle um. „Alter Table people_temp“ umbenennen in „people“.

Denken Sie vor der Ausführung der obigen Anweisung daran, die Personentabelle neu zu erstellen und Testdaten zu generieren.

Bei dieser Methode müssen Sie darauf achten, dass die Anweisung „create table ... like“ die Fremdschlüsseleinschränkungen der Originaltabelle nicht kopiert und manuell hinzugefügt werden muss.

Verwenden von Fensterfunktionen zum Entfernen doppelter Daten

ROW_NUMBER() ist eine neue Fensterfunktion in MySQL 8.0, die zum Gruppieren von Daten und anschließenden Zuweisen einer eindeutigen Nummer zu jedem Datenelement verwendet werden kann. Zum Beispiel:

Wählen Sie ID, Name, E-Mail, 
  row_number() über (Partition nach E-Mail, sortiert nach ID) als row_num 
von Leuten;
ID|Name |E-Mail |Zeilennummer|
--|------|-----------------|-------|
 2|Li Si|[email protected] | 1|
 4|Lisi|[email protected] | 2|
 3|Wang Wu|[email protected] | 1|
 5|Wang Wu|[email protected] | 2|
 6|Wang Wu|[email protected] | 3|
 1|Zhangsan|[email protected]| 1|

Die obige Anweisung gruppiert die Daten nach E-Mail (partition by email), sortiert sie nach ID (order by id) und weist dann den Daten in jeder Gruppe eine Nummer zu; ist die Zahl größer als 1, bedeutet dies, dass die Daten doppelt vorhanden sind.

📝Zusätzlich zu den Funktionen ROW_NUMBER() können auch die Funktionen RANK() oder DENSE_RANK() die oben genannten Funktionen erreichen. Eine Einführung und Anwendungsfälle von Fensterfunktionen finden Sie in diesem Artikel.

Basierend auf den Abfrageergebnissen können doppelte Datensätze gelöscht werden:

löschen
von Leuten
wobei id in (
 ID auswählen
 aus (
  ID auswählen,
    row_number() über (Partition nach E-Mail-Reihenfolge nach ID desc) als row_num 
  von Menschen)
 wobei row_num > 1);

Denken Sie vor der Ausführung der obigen Anweisung daran, die Personentabelle neu zu erstellen und Testdaten zu generieren.

Die Methode zum Entfernen von Duplikaten auf der Grundlage mehrerer Felder ist der Methode für ein einzelnes Feld sehr ähnlich. Sie können es selbst ausprobieren und gerne eine Nachricht zur Diskussion hinterlassen!

Zusammenfassen

In diesem Artikel wird erläutert, wie Sie doppelte Datensätze in MySQL suchen und löschen. Dabei wird unter anderem die Verwendung von GROUP BY, Unterabfragen oder Join-Abfragen zum Suchen doppelter Daten in einem oder mehreren Feldern sowie die Verwendung von DELETE FROM-Anweisungen, Unterabfragen, Zwischentabellen und Fensterfunktionen zum Löschen doppelter Daten beschrieben. Weitere Informationen zum Suchen und Löschen doppelter Datensätze in MySQL finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!