Die mySQL NULL Falle

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.

SELECT-INSERT-UPDATE - Kombinationen sind unzuverlässig, die Gefahr eines doppelten Datensatzes der eigentlich einmalig sein sollte, ist zu groß. Die Alternativen funktionieren aber auch nicht immer, denn mySQL verhält sich inkonsistent.

Als Perl-Entwickler ist "undef" ein vollkommen normaler Zustand. Eine Variable ist entweder nicht definiert (eben undef) oder enthält einen leeren String - das ist ein Unterschied mit dem man problemlos arbeiten kann, denn dieser zusätzliche "leere" Zustand verhält sich genau wie man es erwarten würde: Genau wie ein beliebiger Wert.

SQL kennt an dieser Stelle NULL, ein Begriff für den es viele, zum Teil interessante, Aussprachevarianten gibt, da SQL-Nutzer immer zwischen "0" und "NULL" unterscheiden müssen. Das eine ist eine Zahl, das andere das "undef"-Äquivalent.

Das typische Konstrukt von SQL-Neulingen sieht in etwa so aus:

SELECT id FROM TABLE WHERE Bedingung

if ($id) { UPDATE TABLE ... WHERE id=$id } else { INSERT INTO TABLE ... }

Dieser Ablauf ist durchaus funktionsfähig, hat allerdings einen entscheidenen Nachteil: Er ist unzuverlässig! Wenn zwei Prozesse gleichzeitig ihren SELECT absetzen, finden beide keine id und beide führen danach den INSERT aus - und an Stelle eines Eintrages existieren auf einmal zwei.

Die richtige Alternative ist ein

INSERT INTO TABLE ... ON DUPLICATE KEY UPDATE ...

bei dem der Server sicherstellt, dass immer nur ein Eintrag existiert. Dieser wird entwerde erstellt oder geändert. Ob man diese Aufteilung von Schreibvorgängen in zwei Befehle als vorteilhaft oder unnötig ansieht, ist jedem freigestellt.

Allerdings hat die Sache einen Haken: Der INSERT INTO ON DUPLICATE KEY UPDATE funktioniert nur wenn auch ein passender Unique Index vorhanden ist, andernfalls kommt es nie zu einem "harten" Duplicate und es wird immer ein INSERT ausgeführt.

An dieser Stelle hilft ein kleiner Trick: Der ->execute von DBI hat einen sehr interessanten Rückgabewert, der gleichzeitig den Status des SQL-Befehls (erfolgreich/fehlgeschlagen) und die Anzahl der geschrieben Zeilen enthält. Damit ergibt sich folgende Möglichkeit (zumindest in Perl):

$sth = UPDATE TABLE ... WHERE Bedingung

if ($sth->execute == 0) { INSERT INTO TABLE ... }

Zunächst wird der UPDATE ausgeführt und versucht den Datensatz zu aktualisieren. Wenn die WHERE-Bedingung (die ursprünglich im SELECT stand) keinen Datensatz findet, ist execute zwar wahr (erfolgreich), aber die Anzahl der geänderten Zeilen ist 0, in diesem Fall kann der INSERT ausgeführt werden.

Diese Lösung ist natürlich nicht perfekt, tatsächlich ist sie nur ein wenig besser als der vorherige SELECT, allerdings wird eine SQL-Abfrage eingespart (wenn der Eintrag schon existiert) und in einem per Definition inkonsistenten mySQL-Cluster wird die Anzahl der mehrfachen Zeilen drastisch reduziert, meiner Erfahrung nach meist auf 0.

Diese Kombination - um wieder zur NULL-Falle zurück zu kommen - hat bisher immer prima funktioniert, bis heute. Meine heutige Tabelle hat zwei Spalten die vollkommen absichtlich den Wert NULL (bzw. undef) annehmen können und natürlich habe ich vertrauensvoll die UPDATE-INSERT - Methode angewendet.

Nach etwa einer halben Stunde Verwunderung und Experimentieren - denn eine bestehende Zeile wurde niemals aktualisiert, es entstanden immer neue - fand ich diese kleine Gemeinheit in der mySQL-Dokumentation:

In [my]SQL ergibt ein Vergleich zwischen NULL und irgendeinem anderen Wert nie einen Treffer, selbst wenn auch der andere Wert NULL ist.

Damit hat sich die schöne, clustersichere Variante in diesem Fall erledigt, denn "leer" und "undef" (bzw. NULL) sind in meinem aktuellen Fall tatsächlich unterschiedliche Werte.

Sybase SQL (die Mutter von Microsoft SQL) verhält sich übrigens genau so wie erwartet: SELECT * FROM TABLE WHERE spalte=NULL ergibt alle Zeilen die in "spalte" keinen Wert haben.

PS: Ja, ich kenne "IS NULL" in mySQL, allerdings müsste ich dazu 4 verschiedene Queries preparen und absetzen, je nachdem ob ein Wert gerade NULL ist oder nicht. Schöne Datenbank die solche Umstände verursacht.