Aktualisieren Sie den Datensatz, falls er existiert, oder fügen Sie ihn in die MySQL-Tabelle ein

Die Datenbankmanager müssen möglicherweise gelegentlich einen Datensatz zur MySQL-Tabelle hinzufügen oder einen aktualisieren, wenn er regelmäßig vorhanden ist. MySQL sendet eine Warnung, wenn ein Benutzer versucht, einen Datensatz einzufügen, der bereits vorhanden ist oder einen doppelten PRIMARY KEY-Wert hat.

Dieser Artikel beschreibt die Methoden, die verwendet werden können, um diesen Fehler zu korrigieren und ein besseres Ergebnis zu erzielen.

Aktualisieren Sie den Datensatz, falls er existiert, oder fügen Sie ihn in die MySQL-Tabelle ein

Wenn Zeilen eingefügt werden müssen, nachdem festgestellt wurde, ob sie tatsächlich neu sind oder bereits existieren, bietet MySQL mehrere hilfreiche Anweisungen.

Verwenden Sie REPLACE INTO, um den Datensatz zu aktualisieren, falls er existiert, andernfalls fügen Sie ihn in die MySQL-Tabelle ein

Ein Ansatz besteht darin, die Anweisung REPLACE zu verwenden, wenn Sie wirklich Zeilen ersetzen möchten, wenn die Befehle INSERT aufgrund doppelter UNIQUE- oder PRIMARY KEY-Werte wie zuvor beschrieben fehlschlagen würden.

Es gibt zwei mögliche Konsequenzen für jeden ausgegebenen Befehl, wenn eine REPLACE INTO-Anweisung verwendet wird:

1. Eine typische INSERT-Anweisung wird ausgeführt, weil keine Datenzeile mit den gewünschten Werten gefunden werden konnte.
2. Wenn eine übereinstimmende Datenzeile entdeckt wird, wird die vorhandene Zeile mit der üblichen DELETE-Anweisung gelöscht, und dann wird das Standard-INSERT ausgeführt.

Nehmen wir zum Beispiel an, wir haben eine Tabelle Mitarbeiter mit Spalten für id, Abteilung, Name des Mitarbeiters und Jahr des Eintritts. Wir haben die Entscheidung getroffen, unseren department und joining_year von id = 101 auf den ursprünglichen Operations-Datensatz zurückzusetzen.

-- Before update
SELECT * from employees where id = 101;
REPLACE INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (101, 'Operations', 'John Doe', 2013);
-- After update
SELECT * from employees where id = 101;

Führen Sie die obige Codezeile in jedem mit MySQL kompatiblen Browser aus. Es wird das folgende Ergebnis angezeigt:

Ausgang:

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 2 rows affected (0.01 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+

Obwohl wir in diesem Beispiel nur ein Element geändert haben, zeigt das Ergebnis, dass zwei Zeilen betroffen waren, weil wir den vorherigen Datensatz GELÖSCHT und dann die neue Zeile eingefügt haben, um ihren Platz einzunehmen.

Verwenden Sie INSERT ... ON DUPLICATE KEY UPDATE, um den Datensatz zu aktualisieren, falls er existiert, andernfalls fügen Sie ihn in die MySQL-Tabelle ein

Wir können die Anweisung und Klausel INSERT ... ON DUPLICATE KEY UPDATE als alternativen Ansatz zum Einfügen in Zeilen verwenden, die möglicherweise doppelte UNIQUE- oder PRIMARY KEY-Werte haben.

Indem nur INSERT- oder UPDATE-Anweisungen und niemals DELETE ausgegeben werden, ist die Verwendung von INSERT... ON DUPLICATE KEY UPDATE nicht destruktiv, im Gegensatz zu REPLACE, das aufgrund des DELETE von Natur aus schädlich ist. Befehle, die es bei Bedarf ausführt.

Nehmen wir zum Beispiel an, wir haben eine Tabelle Mitarbeiter mit Spalten für id, Abteilung, Name des Mitarbeiters und Jahr des Eintritts. Wir haben die Entscheidung getroffen, unseren department und joining_year von id = 101 auf den ursprünglichen Operations-Datensatz zurückzusetzen.

Die neue Klausel ON DUPLICATE KEY UPDATE kann jetzt zu unserer ursprünglichen INSERT-Anweisung hinzugefügt werden:

-- Before update
SELECT * from employees where id = 101;
SET @id = 101,
    @department = 'Operations',
    @employee_name = 'John Doe',
    @joining_year = 2013;
INSERT INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (@id, @department, @employee_name, @joining_year)
ON DUPLICATE KEY UPDATE
    department = @department,
    employee_name = @employee_name,
    joining_year = @joining_year;
-- After update
SELECT * from employees where id = 101;

Im obigen Beispiel haben wir uns entschieden, Benutzervariablen zu verwenden, um zu vermeiden, dass die tatsächlichen Werte, die wir INSERT oder UPDATE möchten, mehr als einmal deklariert werden, obwohl dies nicht erforderlich ist, damit die Methode ON DUPLICATE KEY UPDATE funktioniert wie beabsichtigt.

Führen Sie die obige Codezeile in jedem mit MySQL kompatiblen Browser aus. Es wird das folgende Ergebnis angezeigt:

Ausgang:

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 1 rows affected (0.00 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+