Die Welt von MySQL und MariaDB

MySQL UPSERT‑Leitfaden: Wie man INSERT ON DUPLICATE KEY UPDATE verwendet – mit Beispielen

SQL-Grundlagen
目次

1. Was ist UPSERT?

Übersicht

„UPSERT“ bezieht sich auf eine Datenbankoperation, die sowohl INSERT als auch UPDATE kombiniert. Mit anderen Worten: Wenn die Daten noch nicht existieren, werden sie eingefügt, und wenn sie existieren, werden sie aktualisiert. Diese Funktion ermöglicht effiziente Operationen bei gleichzeitiger Aufrechterhaltung der Datenkonsistenz.

In MySQL bietet die Syntax INSERT ... ON DUPLICATE KEY UPDATE diese Funktionalität. Damit können Sie Duplikat-Schlüssel-Fehler vermeiden und bestehende Datensätze aktualisieren, auch wenn Duplikat-Daten-Situationen auftreten.

Anwendungsfälle

  • Kundensysteme : Neue Kundendaten einfügen, wenn sie nicht existieren, oder bestehende Kundeninformationen aktualisieren, wenn sie sich ändern.
  • Lagerverwaltung : Neue Produkte hinzufügen, während die Bestandszahlen bestehender Produkte aktualisiert werden.

Vorteile von UPSERT in MySQL

  • Vermeidet Duplikat-Schlüssel-Fehler
  • Vereinfacht SQL-Abfragen
  • Erhält die Datenintegrität

2. Grundlegende Verwendung von UPSERT in MySQL

In MySQL werden UPSERT-Operationen mit der Syntax INSERT ... ON DUPLICATE KEY UPDATE implementiert. Dadurch wird, wenn ein Duplikat-Schlüssel gefunden wird, der bestehende Datensatz aktualisiert, anstatt einen neuen einzufügen.

Grundlegende Syntax

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

Erklärung:

  1. INSERT INTO versucht, Daten in die Tabelle einzufügen.
  2. Wenn die Daten bereits existieren, wird die Klausel ON DUPLICATE KEY UPDATE ausgeführt und aktualisiert den bestehenden Datensatz.

Beispiel:

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

In diesem Beispiel wird, wenn ein Benutzer mit user_id = 1 bereits existiert, sein name auf ‚Taro Tanaka‘ aktualisiert. Andernfalls wird ein neuer Datensatz eingefügt.

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

Aktualisieren mehrerer Spalten

Beim Verwenden von UPSERT können Sie wählen, nur bestimmte Spalten zu aktualisieren. In solchen Fällen geben Sie in der Klausel ON DUPLICATE KEY UPDATE nur die Zielspalten an.

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

Hier wird, wenn ein Produkt mit product_id = 100 bereits existiert, nur die Spalte price aktualisiert, während andere Spalten wie name unverändert bleiben.

4. Unterschiede zu anderen Datenbanken

Andere Datenbanken bieten ebenfalls UPSERT-ähnliche Funktionalitäten. Zum Beispiel verwenden PostgreSQL und SQLite INSERT ... ON CONFLICT oder MERGE als Äquivalente.

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 Duplikat-Schlüssel-Fehlern zu steuern. Im Gegensatz dazu verwendet MySQL ON DUPLICATE KEY UPDATE.

Einzigartigkeit von MySQL

  • MySQL verwendet INSERT ... ON DUPLICATE KEY UPDATE, was sich von anderen Datenbanken unterscheidet. Besondere Aufmerksamkeit ist bei der Datenbankmigration erforderlich.

5. Erweiterte UPSERT-Verwendung

Bulk-UPSERT (Mehrere Datensätze auf einmal)

UPSERT kann auch auf mehrere Datensätze auf einmal angewendet werden, was die Effizienz von Datenbankoperationen erheblich verbessert.

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

Hier werden mehrere Produkte auf einmal eingefügt, und wenn ein Duplikat-Schlüssel existiert, wird nur der price aktualisiert.

Verwendung von gespeicherten Prozeduren für UPSERT

Sie können auch gespeicherte Prozeduren verwenden, um UPSERT-Operationen zu optimieren. Dies macht den Code wiederverwendbar, lesbarer und leichter wartbar.

6. Fallstricke und Überlegungen für UPSERT

Transaktionen und Deadlocks

Beim Verwenden von UPSERT mit großen Datensätzen können Deadlocks auftreten. Wenn das Transaktionsisolationsniveau von MySQL auf REPEATABLE READ eingestellt ist, sind Gap-Locks wahrscheinlicher.

Vermeidung von Gap-Locks

  • Das Ändern der Transaktionsisolationsebene auf READ COMMITTED kann Deadlock-Risiken reduzieren.
  • Überlegen Sie, UPSERT-Operationen bei Bedarf in kleinere Abfragen aufzuteilen.

7. Schlussfolgerung

Die UPSERT-Funktionalität von MySQL ist ein mächtiges Werkzeug zum effizienten Einfügen und Aktualisieren von Daten, während Duplikat-Schlüssel-Fehler vermieden werden. Allerdings ist eine sorgfältige Berücksichtigung der Transaktionseinstellungen und potenzieller Deadlocks unerlässlich. Richtig verwendet kann UPSERT Datenbankoperationen einfacher und effektiver gestalten.