Practical MySQL & MariaDB

MySQL UPSERT erklärt: INSERT … ON DUPLICATE KEY UPDATE – Anleitung mit Beispielen

SQL-Grundlagen
目次

1. Was ist UPSERT?

Überblick

„UPSERT“ bezeichnet eine Datenbankfunktion, die die Operationen INSERT und UPDATE kombiniert. Mit anderen Worten: Wenn die Daten noch nicht existieren, werden sie eingefügt; existieren die gleichen Daten bereits, werden sie aktualisiert. Durch die Nutzung dieser Funktion können Sie effiziente Vorgänge ausführen und gleichzeitig die Datenkonsistenz wahren.

In MySQL wird diese Funktionalität mit der Syntax INSERT ... ON DUPLICATE KEY UPDATE implementiert. Diese Möglichkeit erlaubt es, Fehlermeldungen wegen doppelter Schlüssel zu vermeiden und vorhandene Datensätze zu aktualisieren, selbst wenn doppelte Schlüssel auftreten.

Anwendungsfälle

  • Kundenverwaltungssysteme : Neue Kundendaten hinzufügen, wenn sie noch nicht existieren, und vorhandene Kundeninformationen aktualisieren, wenn sie sich ändern.
  • Produktbestandsverwaltung : Neue Produkte hinzufügen, während die Lagermenge bereits vorhandener Produkte aktualisiert wird.

Vorteile von UPSERT in MySQL

  • Vermeidet Fehlermeldungen wegen doppelter Schlüssel
  • Vereinfacht SQL‑Abfragen
  • Gewährleistet Datenintegrität

2. Grundlegende Verwendung von UPSERT in MySQL

In MySQL werden UPSERT‑Operationen mit der Syntax INSERT ... ON DUPLICATE KEY UPDATE durchgeführt. Mit dieser Syntax können Sie, wenn ein doppelter Schlüssel auftritt, Teile oder die gesamten vorhandenen Daten aktualisieren, anstatt neue Daten einzufügen.

Grundsyntax

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Erklärung:

  1. Verwenden Sie INSERT INTO, um Daten einzufügen.
  2. Wenn die einzufügenden Daten bereits in der Tabelle existieren, wird die Klausel ON DUPLICATE KEY UPDATE ausgeführt und die vorhandenen Daten werden aktualisiert.

Beispiel:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

Im obigen Beispiel wird, falls ein Benutzer mit user_id = 1 bereits existiert, das Feld name auf „Taro Tanaka“ aktualisiert. Existiert der Benutzer nicht, wird ein neuer Datensatz eingefügt.

3. Detaillierte SQL‑Syntax und Beispiele für UPSERT

Aktualisierung mehrerer Spalten

Beim Einsatz von UPSERT gibt es Fälle, in denen nur bestimmte Spalten aktualisiert werden sollen. In solchen Situationen können Sie im ON DUPLICATE KEY UPDATE‑Teil nur die benötigten Spalten angeben.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

In diesem Beispiel wird, wenn ein Produkt mit product_id = 100 bereits existiert, ausschließlich die Spalte price aktualisiert, während andere Spalten (z. B. name) unverändert bleiben.

4. Unterschiede zu anderen Datenbanken

Auch Datenbanken außerhalb von MySQL bieten ähnliche Funktionalitäten. Beispielsweise verwenden PostgreSQL und SQLite die Anweisungen INSERT ... ON CONFLICT bzw. MERGE, um ein UPSERT‑ähnliches Verhalten zu erzielen.

PostgreSQL‑Beispiel

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

In PostgreSQL und SQLite wird die Klausel ON CONFLICT verwendet, um das Verhalten bei einem Fehler wegen doppelter Schlüssel zu steuern. Im Gegensatz dazu nutzt MySQL die Klausel ON DUPLICATE KEY UPDATE.

MySQL‑spezifische Eigenschaften

  • MySQL verwendet INSERT ... ON DUPLICATE KEY UPDATE; da die Syntax von anderen Datenbanken abweicht, ist bei Migrationen zwischen Systemen besondere Vorsicht geboten.

5. Fortgeschrittene UPSERT‑Techniken

Bulk‑UPSERT (Batch‑Verarbeitung mehrerer Datensätze)

UPSERT kann nicht nur für einen einzelnen Datensatz, sondern auch für mehrere Datensätze gleichzeitig ausgeführt werden. Das steigert die Effizienz von Datenbankoperationen erheblich.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

In diesem Beispiel werden mehrere Produktdatensätze auf einmal eingefügt. Existieren doppelte Schlüssel, wird nur das Feld price der jeweiligen betroffenen Datensätze aktualisiert.

Verwendung von Stored Procedures für UPSERT

.

Um die UPSERT‑Verarbeitung zu optimieren, können Sie auch Stored Procedures verwenden. Dadurch lässt sich wiederverwendbare Logik innerhalb der Datenbank erstellen, was sowohl die Lesbarkeit als auch die Wartbarkeit Ihres Codes verbessert.

6. Häufige Fallstricke und wichtige Überlegungen

Transaktionen und Deadlocks

Bei der Verwendung von UPSERT — insbesondere bei großen Datenmengen — können Deadlocks auftreten. Wenn das Transaktions‑Isolation‑Level von MySQL auf REPEATABLE READ gesetzt ist, sind Gap‑Locks wahrscheinlicher.

Vermeidung von Gap‑Locks

  • Sie können die Wahrscheinlichkeit von Deadlocks verringern, indem Sie das Transaktions‑Isolation‑Level auf READ COMMITTED ändern.
  • Falls nötig, sollten Sie einen großen UPSERT‑Vorgang in kleinere Batches aufteilen und mehrere Abfragen anstelle einer einzigen großen Anweisung ausführen.

7. Fazit

Die MySQL‑UPSERT‑Funktion ist äußerst nützlich, um das Einfügen und Aktualisieren von Daten zu vereinfachen und gleichzeitig Fehler durch doppelte Schlüssel zu vermeiden. Die Implementierung von UPSERT erfordert jedoch eine sorgfältige Berücksichtigung potenzieller Deadlocks und Transaktionseinstellungen. Bei richtiger Anwendung ermöglicht sie einfachere und effizientere Datenbankoperationen.