Umgang mit doppelten Daten in MySQL
Einige MySQL-Datentabellen können doppelte Datensätze enthalten. In manchen Fällen erlauben wir, dass doppelte Daten existieren, aber manchmal müssen wir diese doppelten Daten auch löschen.
In diesem Kapitel führen wir ein, wie man verhindert, dass doppelte Daten in Datentabellen auftreten, und wie man doppelte Daten aus Datentabellen löscht.
Verhindern doppelter Daten in Tabellen
Sie können festgelegte Felder in einer MySQL-Datentabelle alsPRIMARY KEYoderUNIQUE-Index festlegen, um die Eindeutigkeit der Daten sicherzustellen.Versuchen wir ein Beispiel: Die folgende Tabelle hat keinen Index und keinen Primärschlüssel, daher erlaubt die Tabelle mehrere doppelte Datensätze.
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
Wenn Sie die Felder first_name und last_name in der Tabelle so festlegen möchten, dass Daten nicht dupliziert werden können, können Sie den Dual-Primärschlüsselmodus verwenden, um die Eindeutigkeit der Daten festzulegen. Wenn Sie duale Primärschlüssel festlegen, darf der Standardwert dieses Schlüssels nicht NULL sein; er kann auf NOT NULL gesetzt werden. Wie unten gezeigt:
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) );
Wenn wir einen eindeutigen Index festlegen, schlägt die SQL-Anweisung beim Einfügen doppelter Daten fehl und löst einen Fehler aus.
Der Unterschied zwischen INSERT IGNORE INTO und INSERT INTO besteht darin, dass INSERT IGNORE INTO bereits in der Datenbank vorhandene Daten ignoriert. Wenn die Datenbank solche Daten nicht enthält, werden neue Daten eingefügt; wenn sie Daten enthält, wird dieser Datensatz übersprungen. Dadurch bleiben vorhandene Daten in der Datenbank erhalten und das Ziel erreicht, Daten in Lücken einzufügen.
Das folgende Beispiel verwendet INSERT IGNORE INTO. Nach der Ausführung tritt kein Fehler auf, und es werden keine doppelten Daten in die Datentabelle eingefügt:
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)
INSERT IGNORE INTO: Wenn beim Einfügen von Daten nach dem Festlegen der Datensatz-Eindeutigkeit doppelte Daten eingefügt werden, wird kein Fehler zurückgegeben; es wird nur eine Warnung zurückgegeben. REPLACE INTO: Wenn ein Datensatz mit demselben Primärschlüssel oder eindeutigen Schlüssel vorhanden ist, wird er zuerst gelöscht, dann wird der neue Datensatz eingefügt.
Eine weitere Möglichkeit, die Eindeutigkeit von Daten festzulegen, ist das Hinzufügen eines UNIQUE-Index, wie unten gezeigt:
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name) );
Doppelte Daten zählen
Im Folgenden zählen wir die Anzahl doppelter Datensätze von first_name und last_name in der Tabelle:
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
Die obige Abfrageanweisung gibt die Anzahl der doppelten Datensätze in der Tabelle person_tbl zurück. Im Allgemeinen geht man zum Abfragen doppelter Werte wie folgt vor:
- Bestimmen Sie, welche Spalte Werte enthält, die dupliziert sein können.
- Listen Sie diese Spalten mit COUNT(*) in der Spaltenauswahlliste auf.
- Listen Sie die Spalten in der GROUP BY-Klausel auf.
- Verwenden Sie die HAVING-Klausel, um die Anzahl der Duplikate auf größer als 1 zu setzen.
Doppelte Daten filtern
Wenn Sie nicht duplizierte Daten lesen möchten, können Sie das Schlüsselwort DISTINCT in der SELECT-Anweisung verwenden, um doppelte Daten herauszufiltern.
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
Sie können auch GROUP BY verwenden, um nicht duplizierte Daten aus der Datentabelle zu lesen:
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
Doppelte Daten löschen
Wenn Sie doppelte Daten aus der Datentabelle löschen möchten, können Sie die folgende SQL-Anweisung verwenden:
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
Natürlich können Sie auch die einfache Methode verwenden, der Datentabelle INDEX (Index) und PRIMARY KEY (Primärschlüssel) hinzuzufügen, um doppelte Datensätze in der Tabelle zu löschen. Die Methode ist wie folgt:
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
Andere Erweiterungen