MySQL - Umgang mit Duplikaten

Im Allgemeinen enthalten Tabellen oder Ergebnismengen manchmal doppelte Datensätze. Meistens ist dies zulässig, manchmal ist es jedoch erforderlich, doppelte Datensätze zu stoppen. Es ist erforderlich, doppelte Datensätze zu identifizieren und aus der Tabelle zu entfernen. In diesem Kapitel wird beschrieben, wie Sie das Auftreten doppelter Datensätze in einer Tabelle verhindern und die bereits vorhandenen doppelten Datensätze entfernen.

Verhindern, dass Duplikate in einer Tabelle auftreten

Sie können eine verwenden PRIMARY KEY oder ein UNIQUE Indizieren Sie eine Tabelle mit den entsprechenden Feldern, um doppelte Datensätze zu stoppen.

Nehmen wir ein Beispiel: Die folgende Tabelle enthält keinen solchen Index oder Primärschlüssel, sodass doppelte Datensätze für möglich sind first_name und last_name.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Fügen Sie a hinzu, um zu verhindern, dass in dieser Tabelle mehrere Datensätze mit demselben Vor- und Nachnamen erstellt werden PRIMARY KEYzu seiner Definition. Wenn Sie dies tun, müssen Sie auch die indizierten Spalten als deklarierenNOT NULL, weil ein PRIMARY KEY erlaubt nicht NULL Werte -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Das Vorhandensein eines eindeutigen Index in einer Tabelle führt normalerweise zu einem Fehler, wenn Sie einen Datensatz in die Tabelle einfügen, der einen vorhandenen Datensatz in der Spalte oder den Spalten, die den Index definieren, dupliziert.

Verwenden Sie die INSERT IGNORE Befehl eher als die INSERTBefehl. Wenn ein Datensatz einen vorhandenen Datensatz nicht dupliziert, fügt MySQL ihn wie gewohnt ein. Wenn der Datensatz ein Duplikat ist, wird derIGNORE Das Schlüsselwort weist MySQL an, es stillschweigend zu verwerfen, ohne einen Fehler zu generieren.

Das folgende Beispiel ist fehlerfrei und fügt gleichzeitig keine doppelten Datensätze ein.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Verwenden Sie die REPLACEBefehl anstelle des Befehls INSERT. Wenn der Datensatz neu ist, wird er wie bei INSERT eingefügt. Wenn es sich um ein Duplikat handelt, ersetzt der neue Datensatz den alten.

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

Die Befehle INSERT IGNORE und REPLACE sollten gemäß dem Verhalten der doppelten Behandlung ausgewählt werden, das Sie ausführen möchten. Der Befehl INSERT IGNORE behält den ersten Satz der duplizierten Datensätze bei und verwirft die verbleibenden. Der Befehl REPLACE behält den letzten Satz von Duplikaten bei und löscht alle früheren.

Eine andere Möglichkeit, die Eindeutigkeit zu erzwingen, besteht darin, a hinzuzufügen UNIQUE Index statt eines primären Schlüssels für eine Tabelle.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Duplikate zählen und identifizieren

Es folgt die Abfrage zum Zählen doppelter Datensätze mit Vorname und Nachname in einer Tabelle.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

Diese Abfrage gibt eine Liste aller doppelten Datensätze in der Tabelle person_tbl zurück. Führen Sie im Allgemeinen die folgenden Schritte aus, um doppelte Wertesätze zu identifizieren.

Bestimmen Sie, welche Spalten die Werte enthalten, die möglicherweise dupliziert werden.
Listen Sie diese Spalten in der Spaltenauswahlliste zusammen mit dem auf COUNT(*).
Listen Sie die Spalten in der GROUP BY Klausel auch.
Füge hinzu ein HAVING Klausel, die die eindeutigen Werte beseitigt, indem die Gruppenanzahl größer als eins sein muss.

Entfernen von Duplikaten aus einem Abfrageergebnis

Du kannst den ... benutzen DISTINCT Befehl zusammen mit der SELECT-Anweisung, um eindeutige Datensätze zu ermitteln, die in einer Tabelle verfügbar sind.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Eine Alternative zum Befehl DISTINCT besteht darin, eine GROUP BY-Klausel hinzuzufügen, die die von Ihnen ausgewählten Spalten benennt. Dadurch werden Duplikate entfernt und nur die eindeutigen Wertekombinationen in den angegebenen Spalten ausgewählt.

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Entfernen von Duplikaten mithilfe der Tabellenersetzung

Wenn Sie doppelte Datensätze in einer Tabelle haben und alle doppelten Datensätze aus dieser Tabelle entfernen möchten, gehen Sie wie folgt vor.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Eine einfache Möglichkeit, doppelte Datensätze aus einer Tabelle zu entfernen, besteht darin, dieser Tabelle einen INDEX oder einen PRIMARY KEY hinzuzufügen. Selbst wenn diese Tabelle bereits verfügbar ist, können Sie diese Technik verwenden, um doppelte Datensätze zu entfernen, und Sie sind auch in Zukunft sicher.

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);