MySQL: Doppelte Datensätze löschen

Hin und wieder kann es vorkommen, dass sich Datensätze ungewollt doppelt in der Datenbank wiederfinden.

Hier ein kleiner Spickzettel, wie sich die Doubletten wieder entfernen lassen.

Die Struktur der Testtabelle sieht folgendermaßen aus:

CREATE TABLE `sqltest`.`duplicates` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `a` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `b` INT(11) NOT NULL ,
  `c` DATETIME NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

Noch ein paar Daten rein und fertig ist die Spielwiese.

mysql> select * from duplicates;
+----+--------+-----+---------------------+
| id | a      | b   | c                   |
+----+--------+-----+---------------------+
|  1 | Apfel  | 123 | 2011-01-11 17:00:00 |
|  2 | Apfel  | 123 | 2011-01-11 17:00:00 |
|  3 | Banane | 234 | 2011-01-12 17:00:00 |
|  4 | Banane | 234 | 2011-01-12 17:00:00 |
|  5 | Birne  | 456 | 2011-01-15 17:00:00 |
|  6 | Birne  | 456 | 2011-01-15 17:00:00 |
+----+--------+-----+---------------------+
6 rows in set (0.00 sec)

Die erste Abfrage macht einen JOIN auf sich selbst, dafür die Aliasse d1 und d2 und dient zunächst der Prüfung, ob wir die gewünschten Datensätze geliefert bekommen.

SELECT d1.* FROM duplicates d1, duplicates d2
 WHERE d1.id != d2.id
   AND d1.a = d2.a
   AND d1.b = d2.b
   AND d1.c = d2.c
   AND d1.id < d2.id

Das Ergebnis:

+----+--------+-----+---------------------+
| id | a      | b   | c                   |
+----+--------+-----+---------------------+
|  1 | Apfel  | 123 | 2011-01-11 17:00:00 |
|  3 | Banane | 234 | 2011-01-12 17:00:00 |
|  5 | Birne  | 456 | 2011-01-15 17:00:00 |
+----+--------+-----+---------------------+
3 rows in set (0.00 sec)

MySQL kann offensichtlich keine Daten löschen, wenn die Löschaktion auf derselben Tabelle wie die Subquery gemacht wird. Darum schreiben wir die Ids in eine temporäre Tabelle.

CREATE TABLE duplicates_ids AS (
  SELECT d2.id FROM duplicates d1, duplicates d2
   WHERE d1.id != d2.id
     AND d1.a = d2.a
     AND d1.b = d2.b
     AND d1.c = d2.c
     AND d1.id < d2.id
)

Im letzten Schritt löschen wir die zuvor ausgewählten Datensätze und auch die temporäre Tabelle. Je nachdem was wir behalten wollen, noch ggf. ein “NOT” vor das “IN” schreiben.

DELETE FROM duplicates WHERE id [NOT] IN (SELECT * FROM duplicates_ids);
DROP TABLE duplicates_ids;

Achtung! Ich hafte nicht bei Datenverlust oder sonstigen Fehlern. Ein Backup sollte sicherheitshalber vorher gemacht werden.