rss
twitter
  •  

SQL – Duplikate finden und löschen

| Posted in Technik |

22

Hin und wieder ist man mit doppelten Einträgen in einer Datenbank und dem Problem der Beseitigung konfrontiert. Nachfolgend zeige ich einen Weg, wie sich per SQL Duplikate effektiv finden und entfernen lassen.

Meine Test-Tabelle ist eine einfache Adresstabelle mit etwas über 400.000 Datensätzen und etlichen Duplikaten.

Der übliche Tipp zum Auffinden von Duplikaten mit Hilfe von GROUP BY und HAVING COUNT(*)>1 ist aber in meinen Augen sehr unpraktisch, wie die folgende Abbildung verdeutlicht.

Duplikate finden - Variante 1

Nicht wirklich praktikabel…

Viel anzufangen ist mit diesem Ergebnis nicht. Wir wissen jetzt zwar, daß wir doppelte Einträge in der Tabelle haben, aber das wußten wir vorher ja auch schon. Ein weiterer Nachteil dieser Abfrage ist die schlechte Performance im Vergleich zur nachfolgend vorgestellten Lösung.

Die wesentlich elegantere Variante ist die Verwendung einer sogenannten Correlated Subquery. Hinter dieser Art der Abfrage verbirgt sich ein indirekter Self-Join, da ein und dieselbe Tabelle in zwei miteinander in Wechselwirkung stehenden Abfragen gemeinsam verwendet wird. Über eine Correlated Subquery kann ich nun also alle doppelten (oder auch mehrfach vorkommenden) Datensätze mit jeweils einer einzigen SQL-Anweisung anzeigen bzw. löschen.

Mit der folgenden Abfrage lasse ich mir alle Datensätze anzeigen, in denen Vorwahl und Telefonnummer doppelt vorkommen.

SELECT Id, Vorname, Nachname, Strasse, PLZ, Ort, Vorwahl, Telefon
FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id <> Dup.Id)
ORDER BY Nachname;

Wie die nächste Abbildung zeigt, sind die gelieferten Informationen hier wesentlich ausführlicher.

aa

…schon besser!

Auf gleiche Weise kann ich nun auch die vorhandenen Duplikate aus der Tabelle löschen:

DELETE FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id < Dup.Id);

Damit werden nun alle Duplikate mit der kleineren Id gelöscht.

Datenbank sichern mit mysqldump

| Posted in Linux, Technik |

0

MySQL Datenbanken die bei einem Webhoster liegen sichert man bequem mit phpMyAdmin. In manchen Fällen ist phpMyAdmin aber nicht unbedingt das Tool der Wahl. Größere Datenbanken lassen sich meist schlecht sichern, da die meisten Webhoster der Ausführungszeit von PHP-Scripten beschränken. Auch der eigene Internetzugang kann einem schon einen Strich durch die Rechnung machen. Als Ergebnis gibts dann entweder einen Server-Fehler oder einen Timeout.

Hat man in seinem Hostingpaket aber Shellzugriff mit drin, so läßt sich dieses Problem bei den meisten Hostern umgehen. Man loggt sich per SSH auf dem Webserver ein und sichert seine Datenbank(en) mit dem Kommandozeilentool mysqldump.

Die allgemeine Sytax zum Sichern:

mysqldump [OPTIONS] database [tables]

Das folgende Beispiel sichert die Datenbank Datenbank in die Datei backup.sql:

mysqldump --opt datenbank > backup-file.sql

Bei Strato z. Bsp. würde der Aufruf folgendermaßen aussehen:

mysqldump --opt -h rdbms --user=username --password=passwort ↵
 DB123 > backup-file.sql

Mit „–opt“ werden einige Standareinstellungen des MySQL-Server aktiviert, „-h rdbms“ gibt den Host an, auf welchem die Datenbanken liegen (bei vielen Hostern liegen Webserver und Datenbanken auf unterschiedlichen Maschinen). Bei „–username“ und „–password“ natürlich die eigenen Benutzerangaben eintragen. Damit wird die Datenbank DB123 in die Datei backup-file.sql gesichert.

Sollte die Sicherung mit einem Fehler abbrechen, kann man noch die Option „–force“ verwenden, dann dumpt mysqldump auch bei Fehlern weiter.

Das Rücksichern des Backups übenimmt das Tool mysql. Der Aufruf ist ähnlich wie bei mysqldump:

mysql datenbank < backup-file

In meinem Beispiel also:

mysql -h rdbms --user=username --password=passwort DB123 < backup-file.sql

Somit hat man seine Datenbank auf einfache und schnelle Weise gesichert. Die Sicherungen kann man dann entweder auf dem Server archivieren, oder per FTP herunterladen. Da die Backupdatei eine reine Textdatei ist, läßt sich diese auch wunderbar komprimieren, damit sie für den Download etwas handlicher ist.

Das Ganze verpacke ich dann noch in ein Shellscript, so ist meine Datenbank mit einem Aufruf gesichert, komprimiert und bereit zur Archivierung:

#!/bin/sh
#Sicherung Datenbank

echo "Datenbank wird jetzt gesichert..."

DATESTRING=`date +"%Y-%m-%d"`
NEWNAME=db123_$DATESTRING

mysqldump -h euerhost --user=euername --password=euerpasswort ↵
DB123 > $NEWNAME.sql
gzip -9 -S .zip $NEWNAME.sql

echo "Die Datenbank nach $NEWNAME gesichert"

Dieses Script einfach in eine Textdatei kopieren und z. Bsp. dasi.sh benennen und dann per FTP auf den Server kopieren und per SSH auf dem Server einloggen. Bei vielen Hostern muss dann noch das Recht zum Ausführen gesetzt werden:

chmod u+x dasi.sh

und das Script dann mit

./dasi.sh

aufrufen. Fertig.