Antwort

SQL bietet verschiedene Möglichkeiten, Daten in einer Tabelle zu aktualisieren oder einzufügen:

UPDATE — ändert vorhandene Datensätze, die der Bedingung entsprechen.
MERGE (oder UPSERT) — kombiniert die Logik von Einfügen und Aktualisieren in einem einzigen Befehl: wenn die Zeile gefunden wird, wird sie aktualisiert; wenn nicht, wird sie hinzugefügt.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) oder INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — fügt eine neue Zeile ein und aktualisiert bei einem Schlüsselkonflikt die bestehende.

Wann und was verwenden:

Wenn sicher ist, dass es keine Datensätze gibt – verwenden Sie INSERT.
Zum Aktualisieren vorhandener Datensätze – nutzen Sie UPDATE mit Bedingungen.
Wenn nicht bekannt ist, ob der Datensatz existiert, ist es effizienter, MERGE oder UPSERT zu verwenden – dies spart Komplexität und verringert die Anzahl der SQL-Anfragen.
UPSERT-Operationen sind normalerweise schneller bei einer großen Anzahl von Datensätzen und minimieren Rennbedingungen.

Beispiel (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Einfluss auf Leistung und Integrität:

Mehrere separate UPDATE/INSERT können zu Rennbedingungen und Sperren führen.
MERGE/UPSERT ist effektiver bei massiven Migrationen und unterstützt die Atomizität.

Fangfrage

Was passiert, wenn Sie versuchen, ein UPSERT auf ein eindeutiges Feld auszuführen, aber gleichzeitig in derselben Transaktion dieselbe Zeile einfügen und aktualisieren?

Richtige Antwort: Es kann zu einem Sperr-/Konfliktfehler kommen, da die Transaktion versucht, dieselbe Zeile zweimal zu ändern. Die Operation wird entweder nicht abgeschlossen oder schlägt mit einem deadlock-Fehler je nach DBMS fehl.

Beispiel:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

Bei falscher Reihenfolge und Isolationsstufe kann es zu Konflikten bei den Änderungen kommen.

Geschichte Nr. 1

In einer Logik zur Datenmigration wurde eine Kombination aus SELECT und dann INSERT oder UPDATE verwendet, was manchmal dazu führte, dass eine andere Transaktion die Daten früher festlegte und ein Fehler „duplicate key“ auftrat. Dies führte zu häufigen Ausfällen beim nächtlichen Datenexport, sodass die Logik auf UPSERT umgestellt werden musste.

Geschichte Nr. 2

In einem Projekt mit MySQL wurde ON DUPLICATE KEY UPDATE fehlerhaft angewendet, was zusammengesetzte eindeutige Schlüssel nicht korrekt berücksichtigte. Infolgedessen wurden einige Daten nicht aktualisiert, und es traten Duplikate auf. Das Problem konnte nur in der Produktionsumgebung identifiziert werden.

Geschichte Nr. 3

In einem Projekt wurde UPDATE anstelle von UPSERT zur Synchronisation von Benutzerdaten verwendet. Bei Verbindungsfehlern ging ein Teil der Daten verloren, da Aktualisierungen für neue Benutzer übersprungen wurden. Nach der Analyse wurde MERGE/UPSERT vollständig implementiert.

Erklären Sie die Unterschiede zwischen den SQL-Befehlen UPDATE, MERGE und INSERT ... ON DUPLICATE KEY UPDATE. In welchen Fällen ist welcher Ansatz vorzuziehen, und wie wirken sich diese Werkzeuge auf die Leistung und Integrität der Daten aus?

Antwort

Fangfrage