MySQL: Temporäre Tabellen

Manchmal kommt man um temporäre Tabellen einfach nicht herum. So kann es geschehen, dass man ein und die selbe Tabelle in einem Update und einer Unterabfrage  benötigt. Beispiel:

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10
)

Zugegebenermaßen hinkt die Sinnhaftikeit dieser Abfrage, was jedoch nicht unser Thema sein soll. Natürlich fällt eine Unterabfrage i.d.R. etwas komplexer aus. Beim Ausführen des obigen Queries erhält man folgende Fehlermeldung:

Error Code : 1093
You can’t specify target table ‚user‘ for update in FROM clause

Mit einer temporären Tabelle lässt sich das Problem schnell lösen. Man schreibt zunächst alle relevanten Daten in eine temporäre Tabelle, führt das Update aus und löscht die Temp-Tabelle wieder. Das Ganze könnte dann etwasso aussehen:

CREATE TEMPORARY TABLE tmp ENGINE=INNODB
SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10;

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `tmp`
);
DROP TABLE `tmp`;

MySQL bietet hierfür eine besondere Syntax „CREATE TEMPORARY“. Die Tabelle ist dadurch nur für die aktuelle Verbindung sichtbar und wird automatisch wieder entfernt, wenn man die Verbindung schließt.

Vorsicht beim DROP TABLE!

Nicht schlecht soweit. Eine Besonderheit ist mir allerdings im Beisein von Transaktionen noch aufgefallen. Lässt man alle Statements innerhalb einer Transaktion ausführen und macht am Ende ein Rollback, so hat letzterer bei obigem Code keine Wirkung mehr.

START TRANSACTION;
...
ROLLBACK;

Warum? Werfen wir einen Blick in MySQL-Dokumentation zu dem Stickwort „DROP TABLE“: http://dev.mysql.com/doc/refman/5.1/de/drop-table.html

Denn auch bei DROP TABLE gibt es das zusätzliche Schlüsselwort „TEMPORARY“. Dieses bewirkt unter anderem, dass keine laufende Transaktion wie etwa beim normalen „DROP TABLE“ beendet wird. Also updaten wir nochmal unser Skript:

START TRANSACTION;
CREATE TEMPORARY TABLE tmp ENGINE=INNODB
SELECT `id` FROM `user` WHERE `id` > 5 AND `id` < 10;

UPDATE `user`
SET `firstname` = 'Vorname', `lastname` = 'Nachname'
WHERE `id` IN (
  SELECT `id` FROM `tmp`
);
DROP TEMPORARY TABLE `tmp`;
ROLLBACK;

Die Datensätze in der Tabelle „user“ dürfen durch die oberen Statements nun nicht mehr beeinflusst worden sein.

Fazit:

Wer temporäre Tabellen nutzt und diese im gleichen Atemzug wieder löscht, sollte innerhalb von Transaktionen peinlichst genau auf das Schlüsselwort „TEMPORARY“ achten – sonst kann das ganz schön in die Hose gehen 😉

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *