phpBuddy

Datensätze tabellenübergreifend löschen mit Multi-Delete

Betreiber von Webseiten mit selbst programmierten News Systemen oder Blogs mit Kommentarfunktionen stehen in den meisten Fällen früher oder später vor dem Problem, wie man tabellenübergreifend Datensätze löschen kann.

Stellen wir uns mal vor das wir ein News System programmiert haben. Unser News System wird von verschiedenen Autoren mit Artikel befüllt und jeder Artikel kann eine unbestimmte Anzahl von Kommentaren haben. Üblicherweise, oder besser gesagt korrekterweise, teilt man diese Daten in der Datenbank auf mehrere Tabellen auf. Man hat eine Tabelle, in der die Autoren Info steht und jeder Autor wird über eine eindeutige ID identifiziert. Die News stehen ebenfalls in einer eigenen Tabelle und damit man weiß wer die News geschrieben hat, fügt man eine Spalte mit der Autoren ID ein, die eine News mit dem Autor verknüpft. Da die News auch von Sitebesucher kommentiert werden können, speichern wir die Kommentare ebenfalls in einer eigenen Tabelle und verknüpfen diese über die News ID, ähnlich der Vorgehensweise, wie wir News mit einem Autor verknüpft haben.

Soweit alles kein Problem. Jetzt stellen wir uns aber mal vor, wir müssen aus irgend einem Grund einen Autor verbannen und möchten auch seine Artikel komplett entfernen. Löschen wir den Autor, wird auch die News gegenstandslos. Löschen wir die News, sind auch die zugehörigen Kommentare Datenleichen, die nur Speicherplatz belegen. Jetzt stellt sich die Frage, wie man auf möglichst elegante Art alle 3 Fliegen (Autor löschen, News löschen, Kommentare löschen) mit einer Klatsche erschlagen kann?!

Die meisten Programmierer, besonders die weniger erfahrenen, würden das in gefühlten 99% der Fälle so angehen:
1) Autor ID ermitteln und anschließend den Datensatz löschen
2) Alle News IDs ermitteln, die als Autor die Autor ID hat
3) Alle News mit dieser Autor ID löschen
4) Alle Kommentare, passend zur jeweiligen News ID, suchen und löschen

Zwischen den einzelnen Schritten liegen häufig auch eine oder mehrere Datenbankabfragen um die verknüpfenden IDs zu ermitteln. Das geht doch bestimmt aber auch einfacher, oder?! ;-)

Was bei anderen RDBMS über sogenannte Foreign Keys, zu Deutsch Fremdschlüssel, gelöst wird, ist bei MySQL etwas schwieriger zu bewerkstelligen. Um etwas präziser zu sein ist es bei MySQL auch möglich, solange man als Engine InnoDB verwendet. Allerdings wird in den meisten Fällen die deutlich schnellere Engine MyISAM gewählt, die leider keine nativen Foreign Keys kennt.
Klären wir noch kurz, was genau diese Foreign Keys sind. Über diese Fremdschlüssel wird eine Zusammengehörigkeit von Datensätzen aus verschiedenen Tabellen realisiert. In unserem Beispiel oben fügen wir in die News Tabelle ein Feld autor_id ein, in dem die ID eines Datensatz aus der Tabelle Autor eingetragen wird. Dadurch lässt sich ein eindeutiger Autor zu einer News zuordnen. Das Gleiche gilt auch für die Kommentare, die ein Feld news_id haben. In dieses Feld wird die News ID eingefügt, wodurch eine Relation entsteht.

Der elegenate Weg
Was bei lesenden Anweisungen (SELECT) praktisch zum kleinen Einmaleins eines Programmierers gehört, ist beim Löschen eher selten anzutreffen. Die Rede ist natürlich von JOIN-Verknüpfungen für Tabellen über einen Fremdschlüssel. Schauen wir uns kurz eine absichtlich sehr stark vereinfachte Tabellenstruktur für unser Beispiel an, damit wir die folgenden MySQL Anweisungen besser nachvollziehen können.

Tabelle News:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| autor_id | int(9)       | NO   |     | NULL    |                |
| titel    | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Tabelle News Autor:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

Tabelle News Kommentare

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| news_id   | int(11)      | NO   |     | NULL    |                |
| kommentar | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Wir sehen hier also, was ich weiter oben angesprochen habe. Das Feld news_id in der Tabelle News Kommentare korrespondiert mit dem Feld id der Tabelle News. Das Feld autor_id in der Tabelle News stellt eine Verbindung zur id der Tabelle News Autor dar.
Um Datensätze, die via Fremdschlüssel in Relation stehen, tabellenübergreifend löschen zu können, gibt es verschiedene Wege.

Eine News und zugehörigen Kommentare löschen mit INNER JOIN

DELETE `news`.*, `news_kommentare`.*
FROM `news`, `news_kommentare`
WHERE `news`.`id` = `news_kommentare`.`news_id` AND `news`.`id` = 5

Mit dem Stern-Selektor wählen wir alle Datensätze der Tabelle News und News Kommentare aus, die die Kriterien der WHERE-Klausel erfüllen. Die Kriterien sind, dass die id der News Tabelle identisch sein muss mit der news_id der News Kommentar Tabelle. Desweiteren schränken wir die Löschung auf die News ein, die als ID den Wert 5 hat.

Einen Autor und dessen News mit allen Kommentaren löschen mit LEFT JOIN

DELETE `news`.*, `news_autor`.*, `news_kommentare`.*
FROM `news`
LEFT JOIN `news_autor`
ON `news`.`autor_id` = `news_autor`.`id`
LEFT JOIN `news_kommentare`
ON `news_kommentare`.`news_id` = `news`.`id`
WHERE `news_autor`.`id` = 2

Das hier ist schon eine Spur heftiger, weil wir in diesem Beispiel über 3 Tabellen löschen!
Wir verknüpfen die Tabelle News Autor (id) mit der Tabelle News (autor_id), sowie die Tabelle News Kommentare (news_id) mit Tabelle News (id). In der WHERE-Klausel legen wir fest, dass wir den Autor, und alle Daten die mit ihm in Verbindung stehen, mit ID 2 löschen möchten.

Auch wenn speziell das zweite Beispiel auf den ersten Blick recht kompliziert aussieht, ist es aber durchaus sehr logisch, wenn man einen Moment darüber nachdenkt und sich die Beziehung der Fremdschlüssel zueinander bewusst macht.
Damit man die gezeigten Beispiele besser nachvollziehen und selbst etwas herumtesten kann, habe ich die oben beschriebenen Tabellen, mit einigen Beispieldaten und den MySQL Anweisungen in eine Zip Datei gepackt. Da kein PHP Code enthalten ist empfiehlt es sich, direkt über phpMyAdmin oder einem ähnlichen Datenbank Tool zu testen.

Viel Spaß beim einfachen löschen,
phpBuddy :-)

MySQL Multi-Delete (1 KB)