Zur Startseite

Duplikate in MySQL finden und entfernen

Eine häufig gestellte Frage: Wie kann ich doppelte Datensätze in MySQL finden und entfernen? Die Antwort auf diese Frage ist abhängig von der verwendeten Version von MySQL.

[Mehr:]

Am elegantesten ist diese Aufgabe mit der Version ab 4.1 zu lösen, aber auch mit älteren Versionen ist eine Lösung möglich. Vor Version 4.0 muss dazu allerdings ein Skript bemüht werden.

Beispieldaten erzeugen

Zunächst soll eine Beispiel-Tabelle mit einigen Beispiel-Datensätzen erstellt werden. Dazu wird erst einmal folgende Adress-Tabelle erzeugt:

Advertisement/Werbung
Anmerkung: Die hier vorgestellten Tabellen-Definitionen und Abfragen sollen lediglich das Prinzip verdeutlichen. Bei einigen Einzelheiten würde man in einer endgültigen Anwendung möglicherweise anders vorgehen. Sie dienen hier lediglich als Beispiele, damit das Verfahren anschaulich erläutert werden kann. In meinen Online-Kursen wird selbstverständlich genauer auf Einzelheiten eingegangen.

<?php
mysql_connect("localhost", "root");
mysql_select_db("test");
 
$sql = "drop table if exists adressen";
mysql_query($sql);
echo mysql_error();
 
$sql = "create table adressen (
  id int primary key auto_increment,
  name varchar(255) not null,
  anschrift varchar(255) not null,
  plz char(5) not null,
  ort varchar(255) not null)";
mysql_query($sql);
echo mysql_error();
 
echo "Adresstabelle wurde angelegt<br>";
?>

Code herunterladen

Die Tabelle besteht aus einer eindeutigen ID, den Textfeldern Name, Anschrift, PLZ und Ort. Es sollen im nächsten Schritt auch gleich Daten eingefügt werden, die dann schon doppelte oder dreifache Datensätze enthalten. Bei diesen Daten sind natürlich nur die Anschriftsdaten doppelt, die eindeutige ID (die normalerweise automatisch erzeugt wird) ist immer unterschiedlich:

<?php
$sql = "insert into adressen (id, name, anschrift, plz, ort)
  VALUES
  (1, 'Heinz Meier', 'Hauptstr. 5', '12345', 'Testort1'),
  (2, 'Fritz Müller', 'Webergasse 3', '23456', 'Testort2'),
  (3, 'Bert Schmidt', 'Bahnhofstr. 4', '45678', 'Testort3'),
  (4, 'Heinz Meier', 'Hauptstr. 5', '12345', 'Testort1'),
  (5, 'Fritz Müller', 'Webergasse 3', '23456', 'Testort2'),
  (6, 'Heinz Meier', 'Hauptstr. 38', '12345', 'Testort1'),
  (7, 'Fritz Müller', 'Webergasse 3', '23456', 'Testort2')";
 
mysql_query($sql);
echo mysql_error();
 
echo "Adressen wurden eingefügt<br>";
?>

Code herunterladen

Seiten: 1 2 3 4

02.02.2006 11:26:00, von Marian , 190 Wörter, 68586 mal angeschaut   Kategorien: MySQL

15 Kommentare

Kommentare, Pingbacks:

Kommentar von: Matthias [Besucher] · http://www.scarus.de

Hallo Marian, eine gute Lösung. Aber häufig besteht das Problem von unterschiedlichen Schreibweise. Was mache ich mit 'Heinz Meier' und 'Heinz Mayer' selbst wenn die Adresse gleich ist? Hier hat Scarus ein Tool entwickelt, dass auch mit MySQL zusammenarbeitet. Infos gibt es unter www.intellicleaner.de Eine Demoversion bekommt man auf Anfrage.

Viele Grüße
Matthias

29.04.2006 @ 10:47

Kommentar von: Marian [Mitglied] · http://www.heddesheimer.de

Hallo Matthias,

kommerzielle Tools zur Doubletten-Entfernung gibt es sicher sehr viele. Sinn dieses Blogs ist es jedoch, dass die Leute selber programmieren lernen. Statt also Werbung für dein Produkt zu machen, hättest du vielleicht besser auf die Möglichkeiten aufmerksam machen sollen, dass man mit MySQL so etwas über den Fulltext-Search realisieren kann:

http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

Ich lasse trotzdem deinen Werbe-Link erstmal hier stehen, vielleicht findet sich ja jemand, der deine Software kaufen will.

Gruß

Marian

29.04.2006 @ 11:35

Kommentar von: Michael Stember [Besucher]

Hallo Marian,

danke für diesen hervorragenden Blog!
Ich konnte mit deiner Hilfe eine MAC Address Datenbank von 700.000 auf 20.000 Einträge zusammenschrumpfen.

THX,

Michael

18.07.2006 @ 10:14

Kommentar von: Marian [Mitglied] · http://www.heddesheimer.de

Hallo Michael,

das klingt doch wie ein guter Erfolg ;-)
Obwohl 700.000 Einträge in mySQL nicht wirklich viel ist, aber wenn die überwiegend aus Duplikaten bestehen, ist das schon einigermaßen lästig.

Gruß
Marian

18.07.2006 @ 10:43

Kommentar von: Martin Goldmann [Besucher] · http://www.goldmann.de/blog

Für die Meyers und Meiers dieser Welt müsste sich auch die Soundex-Funktion in MySQL ganz gut eignen.

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#id2775139

Ciao
Martin

19.07.2006 @ 13:47

Kommentar von: Hirbod [Besucher] · http://www.hirbod.de

Hallo Leute,

ich bin am verzweifeln.

Ich habe ein Gästebuch programmiert, und dazu eine Funktion eingebaut, die automatisch die E-Mail Adresse des einzutragenden in meine Newsletterveteilerliste einträgt.

(Natürlich mit Erlaubnis, dazu ist ein Haken den man ak /- deaktivieren kann.)

Mein Newsletterscript ist ne Open Source Lösung, die sehr gut funktioniert, nur da ich die Eintragung verändert habe, wird nicht mehr überprüft, ob ne Mail bereits in der Datenbank ist, oder nicht.

Da viele oftmals mit der selben E-Mail eintragen, habe ich oftmals 4-5 mal die selbe Mailadresse drinne.

Das Prob: Die Leute bekommen dadurch auch 4-5 mal den Newsletter.
Es ist sehr mühsam das ständig manuell zu säubern, und bei über hunderten von Mailadressen, verliert man so den Überblick.

Wie kann ich aus der Tablle "email" bei mir alle doppelten Einträge löschen?

Kann mir da jmd. helfen?

Vielen Dank!

20.08.2006 @ 07:34

Kommentar von: Karsten [Besucher] · http://www.holiday4you.de

Hallo Marian,

deine 4.1er MYSQL Version funzt wirklich super. Vielen Dank dafür.

schneller als in 20 Sekunden deine Abfrage anzupassen hätt ich es nicht hingekriegt...jetzt kann ich in Ruhe auf die Suche nach der Ursache für die Dublettenerzeugung gehen ;o)

Grüße
Karsten

27.02.2007 @ 20:01

Kommentar von: PeJ [Besucher]

einfacher:
die original-tabelle als *.sql exportieren, dann dieses kleine php-script laufen lassen:


sql-tabelle aufräumen






...fertig...

29.12.2007 @ 18:31

Kommentar von: Kalle [Besucher]

Hi,

die Anweisung:

delete strassen from strassen,
(select count(*) as counter,
min(id) as id,
plz, ort, strasse
from strassen
group by plz, ort, strasse
having counter > 1) as doppel
where strassen.plz = doppel.plz and
strassen.ort = doppel.ort and
strassen.strasse = doppel.strasse and
strassen.id != doppel.id

funktioniert natürlich, allerdings dauerte sie bei mir (1.250.000 Datensätze / 1.300 Duplikate) etwa eine zwei Stunden.

Sinnvoller ist es also wirklich, die Daten bei der Eingabe zu überprüfen, sofern möglich.

Dieser Beitrag sei an all die gerichtet, die aus Faulheit auf eine Überprüfungsroutine verzichten, so wie ich das damals gemacht habe. :)

Gruß
Kalle

12.03.2008 @ 14:44

Kommentar von: Jan [Besucher] · http://www.jan-kuepper.de

Hallo,

Vielen Dank für die Tipps, das hat mir sehr geholfen auf die doch sehr einfach Lösung meiner Probleme mit doppelten EInträgen zu kommen.

Anbei meine überarbeitete Version, mit Hilfe eines INNER JOINS.
Meines Erachtens noch einfach als das hier gezeigt Beispiel :)

DELETE adressen
FROM adressen
INNER JOIN (select count(*),
min(id) as id,
name,
ort,
plz
FROM adressen
GROUP BY name,
ort,
plz
HAVING count(*) > 1)
AS doppel
ON adressen.id = doppel.id

Gruß
Jan

19.03.2008 @ 16:34

Kommentar von: Stefan [Besucher]

Hallo zusammen,
bin im Zuge meiner Recherche auch auf eine Lösung gestoßen, die bei mir (MySql 5.0.51b) gut und v.a. performant funktioniert:

ALTER IGNORE TABLE table ADD UNIQUE INDEX(column1, column2, column3);

Die Ignore Anweisung veranlasst MySql, alle außer der ersten Zeile zu löschen, die identisch in den Einträgen column 1-3 sind.

Viele Grüße,
Stefan

05.08.2008 @ 09:50

Kommentar von: Benjamin [Besucher] · http://www.geoflags.de

Zur Duplikat-Vermeidung:

Wenn man z.B. eine E-Mail-Adresse in eine Tabelle einfügen möchte, die nur einmal vorkommen darf, dann sollte man diese Spalte mit einem UNIQUE-Constraint belegen. Dann kann man mit MySQL auch REPLACE oder INSERT IGNORE benutzen.

REPLACE ersetzt den Konflikt-Datensatz mit dem neuen Datensatz. INSERT IGNORE hingegen fügt nur dann einen neuen Datensatz ein, wenn kein Konflikt besteht, ansonsten wird der Fehler ignoriert. Das hat den Vorteil, dass auch bei einem Duplikat keine MySQL-Fehlermeldung geschmissen wird.

03.09.2008 @ 11:42

Kommentar von: Watz Mann [Besucher]

Leider haut die im Blog vorgeschlagene Methode nicht bei “echten” Adresstabellen hin. Zumindest nicht bei meiner:


DESCRIBE dubletten_bereinigt;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Anrede | varchar(255) | NO | MUL | NULL | |
| Titel | varchar(255) | NO | | NULL | |
| Vorname | varchar(255) | NO | | NULL | |
| Name | varchar(255) | NO | | NULL | |
| Zusatz1 | varchar(255) | NO | | NULL | |
| Zusatz2 | varchar(255) | NO | | NULL | |
| Strasse | varchar(255) | NO | | NULL | |
| PLZ | int(5) | NO | MUL | 0 | |
| Ort | varchar(255) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+


Wenn ich hier mit der vorgeschlagenen Methode arbeite:


SELECT COUNT(*) AS anzahl,
MIN(id) AS id,
Vorname, Name, Strasse, PLZ, Ort
FROM dubletten_bereinigt
GROUP BY Name, Ort
HAVING anzahl > 1;


kriege ich jede Menge falsche Dubletten zurück. Falsch im Sinne von: gleicher Nachname in gleicher Stadt ist noch keine Dublette. Erst die Kombination von Vorname, Nachname und Ort ist wirklich verdächtig.

Nach längerer Recherche bin ich auf folgende, viel einfachere Methode gekommen:


INSERT INTO dubletten_bereinigt (Anrede, Titel, Vorname, Name, Zusatz1, Zusatz2, Strasse, PLZ, Ort)
SELECT DISTINCT Anrede, Titel, Vorname, Name, Zusatz1, Zusatz2, Strasse, PLZ, Ort FROM dubletten_test;


Auf einen Schlag wurden so aus 143.214 136.460 Adressen.

Schönen Gruß!

21.10.2008 @ 17:40

Kommentar von: Roland [Besucher] · http://www.mediaventa.de

Die erläuterten Beispiele funktionieren gut.
Ich möchte aber nicht direkt löschen sondern erst einmal anzeigen und zwar alle Duplikate die mit einem bestimmten Buchstaben beginnen. Mein Ziel ist es dabei Duplikate nur EINMAL anzuzeigen auch wenn der counter größer als 2 ist.
Ich habe versucht mit LIMIT oder DISTINCT zu arbeiten, ich habe es aber nicht hinbekommen.
Meine Abfrage sieht wie folgt aus:

SELECT adressen.vorname,adressen.name,adressen.email,counter FROM adressen,
(SELECT count(*) AS counter,
min(nr) AS nr,
name, vorname FROM adressen
WHERE name regexp '^[A]'
GROUP BY name, vorname
HAVING counter > 1) AS doppel
WHERE adressen.name = doppel.name AND
adressen.vorname = doppel.vorname AND
adressen.nr != doppel.nr

Hat einer eine Idee?
Grüße
Roland

11.11.2008 @ 07:55

Kommentar von: Benny [Besucher] · http://modechecker.de/

@Stefan: Danke für das ALTER IGNORE TABLE table ADD UNIQUE INDEX ... Statement! Das hat mir gerade eine Menge Arbeit erspart.

08.05.2009 @ 14:01