MySQL: Doppelte Datensätze löschen

| 10 Kommentare

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.

10 Kommentare

  1. Gut, das haut aber nur nur dann hin, wenn es mindestens ein Unterscheidungsmerkmal zwischen den Duplikaten gibt (in deinem Fall die ID). Ich überlege grad, ob sowas (doppelte, identische Einträge) nicht direkt durch DB-interne Mechanismen im Design vermieden werden sollte (unique etc.).

  2. Im besten Fall würde man das schon während der Entwicklung berücksichtigen, aber manchmal treten gewisse Dinge auch erst später auf.

    Mindestens ein eindeutiges Merkmal muss schon sein, da solche Aktionen sonst noch unhandlicher würden. Aber auch dann lässt sich die Datenmenge natürlich reduzieren (kopieren mit DISTINCT z.B.).

  3. Eine weiter möglichkeit wäre auf die Spalte mit doppelten Einträgen nachträglich einen unique-Key zu setzen. Mit dem Schlüsselwort IGNORE löscht MySql automatisch doppelte Einträge.

    ALTER IGNORE TABLE duplicates ADD UNIQUE INDEX a

    Hier sollte es auch möglich sein mit ORDER BY (oder vielleicht eine andere Methode) die Reihenfolge festzulegen. Das hab ich jetzt aber nicht getestet.

  4. Das ist schön und gut, dass die Ids aber referentielle Abhängigkeiten mit anderen Tabellen haben könnten, wird hier gar nicht behandelt.

    Ich hatte letztens mal wieder einen schönen vermüllten Datensatz mit Nutzerregistrierungen und den unterschiedlichsten Merkmalen.
    Duplikate habe ich mit mehreren Methoden, von grob auf fein herausgesucht.. Das waren einfach mehrere Queries, die mit GROUP BY und COUNT() und HAVING COUNT() > 1 einzelne Duplikate gefunden haben. So funktioniert es auch über mehrere Tabellen hinweg mit einem JOIN.

    In einer Schleife wurden dann die tatsächlichen Duplikate mit allen IDs und weiteren Merkmalen herausgesucht.
    So konnte ich in späteren Datensätzen gegebenenfalls nicht mehr vorhandene Merkmale erhalten, mir die Reihenfolge aussuchen und mit einem Update auf die referentiellen Abhängigkeiten auf die neue ID hinweisen.

    Da das eine einmalige Aktion ist, ist die Rechenintensität zu verschmerzen..

  5. Pingback: doppelte Einträge in MySQL Datenbank finden « JimiHUY

  6. DELETE FROM a

    USING `db`.`tbl` a, `db`.`tbl` b
    WHERE b.Mail = a.Mail
    AND b.ID != a.ID

  7. Vielen, vielen Dank!

    Hat super geholfen;
    ganu die Idee wusste ich nicht umzusetzen.

  8. @bennson

    Sehr gefährlich was Du da vorschlägst!!!
    Da sieht man, dass Du nicht weiß wie ein DBMS funktioniert… sorry :-(

    MySQL kann einfach nicht mit einem Statement realisieren, dass doppelte Datensätze gelöscht werden und nur einer davon stehen bleibt… zumindest ist mir das nicht bekannt.

    Noch mal zum Delete-Statement:
    Damit löscht Du die doppelten Einträge, richtig!
    Allerdings alle!!! Es wird keiner mehr übrig bleiben…
    Warum? Na dafür musst Du verstehen wie ein DBMS arbeitet – bin ganz schlecht im erklären, daher lasse ich es direkt sein.
    Aber hier als Gedankenansatz:
    DELETE FROM a
    USING db.tpl a
    WHERE a.ID != (select min(c.id) from db.tpl c where c.mail=a.mail group by c.id)

    Tipp: Ganz trivial ausgedrückt: Dein Join ist vollkommen irrelevant, da es keine Eingrenzung der Daten ergibt, welcher Datensatz am Ende stehenbleiben muss, da sich das Join immer auf die ursprüngliche Datenmenge vor dem Löschen bezieht und daher immer ein Ergebnis mit einer ID geben wird. Ergo: es werden alle Sätze gelöscht… Bei meinem Gedankenanreiz wird der Satz durch die min(id) bestimmt..

    In anderen DBMS funktioniert sowas, da man dort bei einem gleichzeitigen Löschvorgang trotzdem noch Daten aus der Tabelle auslesen darf.
    Allerdings funktioniert sowas nicht in MySQL :-(

  9. Tausend Dank für die Anleitung. Ein Skript wurde mehrfach gestartet und hat mir zig tausend Duplicates in eine Tabelle gehauen. Dein Ansatz hat mir viel Zeit gespart und ich habe wieder einiges dazu gelernt. Vielen Dank nochmals.
    PS: Auf große Tabellen angewendet braucht das Löschquery aber seine Zeit (bei mir > 10 min) ;-)

  10. Hallo Greg! Freut mich, dass es dir geholfen hat. Das SQL-Statement hat ja Subqueries und die könnten langsam sein, wenn kein Index gesetzt ist. Du kannst das ja für deinen Fall mal mit “explain” testen, dann sollte die Bremse sichtbar werden.

Hinterlasse eine Antwort

Pflichtfelder sind mit * markiert.


Schlagwörter: A/B-Test, AbstractType, Adapter, AddOn, Administration, Ajax, Amazon, Animation, Annotations, Anonyme Klasse, Ant, Apache, API, Array, ArrayAccess, Attachment, Auftrag, Ausbildung, Auswertung, Authentifizierung, AutoLoader, AWS, Bedienung, Bedingung, Benchmark, Berechtigung, Berlin, Bildbearbeitung, Bildschirmfoto, Blog, Blogroll, BOM, Bootstrap, Bot, Browser, Bugtracker, Byte Order Mark, Bücher, Cache, CakePHP, Call-Center, Callback, CamelCase, Canvas, Captcha, CDN, Cheatsheet, CLI, Clickout, Closure, Cloud, CodeSniffer, Collection, Community, Comparator, Config, Contest, Controller, Converter, CouchDB, Countable, Cronjob, CRUD, CSS, CSV, CustomLibrary, Custom_Model, Daemon, Data Mapper, Datei, Datenbank, Datenstruktur, Datentypen, Dating, Datum, Debug, Decorator, Dekorierer, Design, Design Patterns, Doctrine, Dokumentation, Dump, Duplikat, each, EC2, Eclipse, Email, Entwicklung, Entwurfsmuster, Enum, Erweiterung, Event, Eventhandling, Exception-Handling, Extension, Facebook, Factory, Fallback, Fehler, Fehlermeldung, Filter, Firefox, Flash, flexigrid, Foreach, Formatierung, Formular, Framework, FTP, Funktion, Futon, ga:pi(), Getter, Google Analytics, Hash, Hash-Bang, Header, htaccess, HTML5, htpasswd, HTTP, HTTPS, IDE, If, Implementierung, InnoDB, Interceptor, Interface, Internet Explorer, isset, Iterator, Java, JavaScript, Job, jQuery, Kommentar, Konfiguration, Konsole, Kontrollstruktur, kopieren, kostenlos, Kundenbetreuung, Late Static Binding, Layout, Links, Linux, Listeners, Lizenz, Logging, Löschen, Magento, Magic Methods, Manual, ManyToMany, Marketing, Methode, Model, Monolog, MVC, MySQL, NetBeans, Network, Nirvanix, Objekt, Observable, Observer, OneToMany, Online Tool, OOP, Open Source, Operator, OR-Mapper, Order, ORM, O’Reilly, Parameter, Partnersuche, Passwort, Performance, PHP, php.ini, PHP hates me, phpMyAdmin, PHPUnit, Plugin, Popup, Proxy, Prüfsumme, Prüfung, QR-Code, Qualitätssicherung, Query, Queue, Redesign, Refactoring, Reflection, Request, Response, Responsive Design, Rest-API, Rockstar, Rollback, Routing, S3, Samba, Scheifen, Schleife, Schutz, Screenshot, Secure Shell, Selbstreferenz, Server, Setter, setTimeout, Shop, Sicherheit, Sicherung, Sichtbarkeit, Singleton Pattern, Skin, SOAP, Social Network, Software, Sortierung, Sourcecode, Spam, Speicherproblem, Spickzettel, SPL, Splittest, SSH, SSL, Stammtisch, Statement, static, Statistik, Status, Stellvertreter, Strategy Pattern, Stream, String, Stuttgart, Stylesheet, Subversion, Sun VirtualBox, Support, SVN, Switch, Symfony, Symfony2, Symfony Live, Tag, Template, Template Method, Ternär Operator, Testing, Theme, Thumbnail, Tool, Tour, Tracking, Twig, Twitter, Type-Cast, Ubuntu, Umwandlung, Underscore, unset, Update, Upload, Url, User Story, Validierung, Vererbung, Versionskontrolle, Versionsnummer, Verzweigung, Video, Videospiel, Virtualisierung, Visitor Pattern, Vorschaubild, walk, Warteschlange, Webserver, Webservice, Weiterleitung, Werkzeug, Windows, WindowsAzure, WordPress, Wrapper, Writer, XML, Youtube, Zeitschleife, Zeitsteuerung, Zend Framework, Zend_Application, Zend_Cloud, Zend_CodeGenerator, Zend_Http_Client, Zend_Reflection, Zend_Service, ZPress, Zugangskontrolle, Zugriffsmethode