2 Reaktionen

Vorsicht bei MySQL 5.1+ UPDATE Statements mit JOIN

Geschätzte Lesedauer:

Kürzlich bin ich über ein Problem mit MySQL Statements gestolpert, das ziemlich unangenehm werden kann. Abfragen dieser Art werden nicht allzu häufig vorkommen, doch vielleicht stolpert der ein oder andere ja darüber.
Ich kann auch nicht sagen, ob dies bei allen MySQL Versionsupdates von 5.0 auf 5.1 passiert, bei mir war es unter Debian bei mehreren Tests so.

Nehmen wir folgende SQL-Abfrage:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 SET value = value + 0.05, value = IF(value > 1, 1, value) WHERE 1

[/pastacode]

Es werden also alle Werte in der Spalte um 0.05 erhöht, danach wird geprüft ob der Wert 1 überstiegen hat und in diesem Fall auf 1 gesetzt.

Wollen wir die gleiche Abfrage jetzt in Abhängigkeit einer zweiten Tabelle durchführen, könnten wir folgende Abfrage nutzen:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.05, t1.value = IF(t1.value > 1, 1, t1.value + 0.05) WHERE t2.column = 'search'

[/pastacode]

Das funktionierte bis zu einem MySQL Update von 5.0.x auf 5.1.x auch einwandfrei, doch danach tat sich ein Phänomen auf. Die Werte in der Spalte „value“ wurden bei Abfragen im Stil der 2. einfach nicht mehr höher. Abfrage 1 funktioniert weiterhin wunderbar. Ich kann nur vermuten, aber scheinbar wird in neueren Versionen bei UPDATE Anfragen, die ein JOIN verwenden der Inhalt der Spalten gecached, so dass bei der Ausführung des IF Teiles die vorhergegangene Erhöhung der Spalte nicht berücksichtigt wird, sondern der vorherige Inhalt der Spalte t1.value.

Die Abfrage muss also umgeschrieben werden:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = IF(t1.value + 0.05 > 1, 1, t1.value + 0.05) WHERE t2.column = 'search'

[/pastacode]

Bei dieser Abfrage geht das recht einfach und das Ergebnis ist wieder wie gewünscht.

Das gleiche Problem tritt auf, wenn man sich folgender Abfrage bedient:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.5, t1.value = t1.value + t2.addition WHERE t2.column = 'search'

[/pastacode]

Angenommen, dass der Wert von t1.value vor der Abfrage 0.2 war und in der Spalte t2.addition steht 0.3, so würde man erwarten, dass nach der Abfrage t1.value 1.0 enthält. Weit gefehlt, denn das Ergebnis ist 0.5. Nur die letzte Modifikation des Wertes wird berücksichtigt. Wir müssen also die Abfrage wieder umschreiben:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.5 + t2.addition WHERE t2.column = 'search'

[/pastacode]

So funktioniert auch dies wieder.

Dieser Artikel wurde von Marius Burkard verfasst.

Marius Burkard ist Diplom-Wirtschaftsinformatiker und arbeitet seit 2006 als selbstständiger Software-Entwickler und Linux-Server-Administrator mit der Firma pixcept KG. Er ist unter anderem mitverantwortlich für die Projekte Was-lese-ich.de und ISPProtect.

2 Kommentare - bis jetzt!

Eigenen Kommentar verfassen
  1. schrieb am :

    Da hat sich ein kleiner Schreibfehler zur Erklärung bei Query 4 eingeschlichen: Es wäre zu erwarten, dass nach der Abfrage t1.value 0.55 ist (nicht 1.0).

  2. schrieb am :

    Danke Techi 😉 In der SQL Abfrage sollte es eigentlich heißen + 0.5 und nicht + 0.05, daher die 1.0. Wird korrigiert 🙂

Und jetzt du! Deine Meinung?

Erforderliche Felder sind mit einem Asterisk (*) gekennzeichnet. Die E-Mail-Adresse wird nicht veröffentlicht.
  1. Nach Absenden des Kommentar-Formulars erfolgt eine Verarbeitung der von Ihnen eingegebenen personenbezogenen Daten durch den datenschutzrechtlich Verantwortlichen zum Zweck der Bearbeitung Ihrer Anfrage auf Grundlage Ihrer durch das Absenden des Formulars erteilten Einwilligung.
    Weitere Informationen