Löschen von doppelten Einträgen in einer MySQL Tabelle

Zur Protokollierung von Zugriffen auf mein Content Management System verwende ich eine SQL Tabelle. Hierbei habe ich folgendes Problem, dass manche Browser oder Downloadmanager Seiten unter Umständen doppelt aufrufen. Genauso werden auch Zugriffe gezählt, wenn die Benutzer einfach nur auf den “Reload-Button” ihres Browsers klicken. Dadurch wird das Log unnötig aufgebläht und ein falscher Eindruck von sehr vielen Zugriffen erweckt.

Es gäbe auch die Möglichkeit zur Lösung dieses Problems direkt bei der Erfassung der Logeinträge. Hierzu müsste ich bei Zugriff überprüfen, ob bereits ein Zugriff vom aktuellen User registriert wurde. Dazu müsste ich allerdings einmal die Datenbank abfragen. Im Interesse einer möglichst geringen Belastung durch das Logging habe ich diese Idee daher verworfen.

Eine zweite Lösung ist die nachträgliche Korrektur von fehlerhaften Einträgen in der MySQL Datenbank. Die Vorgehensweise ist dabei die Folgende:

  1. Eine temporäre Tabelle wird angelegt. Diese Tabelle hat die gleiche Struktur, wie die Tabelle, die von Doubletten beseitigt werden soll.
  2. Alle eindeutigen Datensätze werden dann mittels eines SELECT Befehls mit GROUP-Anweisung in die temporäre Tabelle kopiert.
  3. Die ursprüngliche Tabelle wird geleert.
  4. Alle Einträge aus der temporären Tabelle werden in die ursprüngliche Tabelle kopiert.
  5. Die temporäre Tabelle wird mit dem Ende der aktuellen Datenbanksession automatisch gelöscht.

Diese Lösung ist nicht sehr elegant. Mir ist aber leider keine andere praktikable Lösung bekannt, die schnell große Datenmengen in der MySQL Datenbank aussortieren könnte.

Anlegen der temporären Tabelle

CREATE TEMPORARY TABLE templogging (
 id bigint(20) unsigned NOT NULL,
 stamp timestamp(14) NOT NULL,
 type char(1) NOT NULL default '',
 id_target int(10) unsigned NOT NULL default '0',
 ip varchar(15) NOT NULL default '',
 url text,
 referer text,
 PRIMARY KEY (id),
 KEY type (type,id_target)
) TYPE=MyISAM;

Die Struktur der Tabelle ist recht simpel. Sie dient hier als Beispiel, um die Vorgehensweise zu erklären. Jeder Datensatz hat eine eindeutige, fortlaufende Nummer (id), einen Timestamp (stamp) mit den Zeitangaben des Zugriffs. In einem einstelligen Textfeld wird die Art des Dokumentes gespeichert, das geladen wurde (type). Für Webseiten ist dies zum Beispiel ein “d” für “document” oder ein “f” für “file”. Die eindeutige ID-Nummer der geladenen Datei wird als Integer-Wert gespeichert (id_target). Dazu kommen noch die IP-Adresse des Users (ip), per Reverse DNS-Lookup ermittelte URL seines Hosts (url) und der String des Referers (referer), den der Browser übermittelt hat.Durch den Parameter TEMPORARY wird der MySQL Datenbank gesagt, dass diese Tabelle nur so lange Gültigkeit haben soll, wie die aktuelle Session besteht. Also Vorsicht: Bei einer Beendigung der Datenbankverbindung oder einem Fehler sind alle Daten futsch, die sich in dieser Tabelle befunden haben. Für erste Versuche sollte man vielleicht das TEMPORARY erst einmal weglassen.

Der PHP-Code zur Speicherung des Zugriffs auf ein Dokument sähe dann zum Beispiel so aus:

$result=QueryDB(
 "INSERT DELAYED INTO logging
 SET stamp=NOW(),
 type='".$current_type."',
 id_target='".$current_id."',
 ip='".$_SERVER['REMOTE_ADDR']."',
 url='".mysql_real_escape_string(stripslashes(
 @gethostbyaddr($_SERVER['REMOTE_ADDR'])))."',
 referer='".mysql_real_escape_string(stripslashes(
 $_SERVER['HTTP_REFERER']))."'"
,$extranet);

Kopieren aller eindeutigen Datensätze in die temporäre Tabelle

INSERT INTO templogging SELECT * FROM logging AS A
GROUP BY TO_DAYS(A.stamp), A.type,
A.id_target, A.ip, A.referer;

Der dargestellte SQL-Befehl kopiert alle eindeutigen Datensätze in die temporäre Tabelle. Durch eine Gruppierung nach insgesamt fünf Kriterien wird sichergestellt, dass keine Doubletten mit kopiert werden.

Bei diesem Beispiel fliegen so alle doppelten Zugriffe raus, die am gleichen Tag (DO_DAYS(A.stamp)), auf den gleichen Dokumententyp (A.type), mit der gleichen ID-Nummer (A.id_target), der gleichen IP-Adresse (A.ip) und dem gleichen Referer (A.referer) stattgefunden haben.

Leeren der ursprünglichen Tabelle

TRUNCATE TABLE logging;

Mit dem Befehl TRUNCATE wird eine Tabelle gleichzeitig gelöscht und sofort mit der alten Struktur wieder neu angelegt.

Kopieren der Datensätze aus der temporären Tabelle

INSERT INTO logging SELECT * FROM templogging;

Zum Abschluss werden alle Daten aus der temporären Tabelle in die eigentliche Logging-Tabelle übertragen.