Gibt es einen Nachteil bei der blinden Verwendung von INSERT in MySQL?

Question

Oft möchte ich einen Wert zu einer Tabelle hinzufügen oder den Wert aktualisieren, wenn der Schlüssel bereits existiert. Dazu gibt es mehrere Möglichkeiten, vorausgesetzt, in den Spalten "user_id" und "pref_key" im Beispiel ist ein Primärschlüssel oder eindeutiger Schlüssel festgelegt:

1. Blindes Einfügen, Aktualisieren, wenn ein Fehler mit doppelten Schlüsseln auftritt:

// Try to insert as a new value
INSERT INTO my_prefs 
(user_id, pref_key, pref_value)
VALUES (1234, 'show_help', 'true');

// If a duplicate-key error occurs run an update query
UPDATE my_prefs 
SET pref_value = 'true'
WHERE user_id=1234 AND pref_key='show_help';

2. Prüfen Sie, ob es vorhanden ist, und wählen Sie dann oder aktualisieren Sie:

// Check for existence
SELECT COUNT(*) 
FROM my_prefs
WHERE user_id=1234 AND pref_key='show_help';

// If count is zero, insert
INSERT INTO my_prefs 
(user_id, pref_key, pref_value) 
VALUES (1234, 'show_help', 'true');

// If count is one, update
UPDATE my_prefs 
SET pref_value = 'true' 
WHERE user_id=1234 AND pref_key='show_help';

Die erste Methode scheint vorzuziehen zu sein, da sie nur eine Abfrage für neue Einfügungen und zwei für eine Aktualisierung erfordert, während die zweite Methode immer zwei Abfragen erfordert. Gibt es irgendetwas, das ich übersehe, das es zu einer schlechten Idee machen würde, blind einzufügen?

Answer 1

Wird es gleichzeitige INSERTs auf diese Zeilen geben? DELETEs?

"ON DUPLICATE" klingt großartig (das Verhalten ist genau das, was Sie wollen), vorausgesetzt, Sie machen sich keine Sorgen um die Übertragbarkeit auf Nicht-MySQL-Datenbanken.

Das "blinde Einfügen" scheint vernünftig und robust zu sein, vorausgesetzt, es werden niemals Zeilen gelöscht. (Wenn das INSERT fehlschlägt, weil die Zeile existiert, sollte das UPDATE danach erfolgreich sein, weil die Zeile noch existiert. Diese Annahme ist jedoch falsch, wenn Zeilen gelöscht werden - dann bräuchte man eine Wiederholungslogik.) Bei anderen Datenbanken ohne "ON DUPLICATE" könnten Sie eine Optimierung in Erwägung ziehen, wenn Sie die Latenzzeit als schlecht empfinden: Sie könnten einen Datenbank-Roundtrip im Fall von bereits vorhandenen Zeilen vermeiden, indem Sie diese Logik in eine gespeicherte Prozedur aufnehmen.

Die "Existenzprüfung" ist schwierig zu bewerkstelligen, wenn es gleichzeitige INSERTs gibt. Zwischen Ihrem SELECT und Ihrem UPDATE könnten Zeilen hinzugefügt werden. Transaktionen werden nicht einmal wirklich helfen - ich denke, selbst auf der Isolationsebene "serializable" werden Sie gelegentlich die Fehlermeldung "could not serialize access due to concurrent update" sehen (oder was auch immer die entsprechende Fehlermeldung bei MySQL ist). Sie benötigen eine Wiederholungslogik, daher würde ich sagen, dass die Person oben, die vorschlägt, diese Methode zu verwenden, um "ausnahmebasierte Programmierung" zu vermeiden, falsch liegt, ebenso wie die Person, die vorschlägt, aus demselben Grund zuerst das UPDATE durchzuführen.

Answer 2

Soweit ich weiß, ist der erste Weg der bevorzugte Weg.

Answer 3

In Ihrem DAO-Modell könnten Sie ein id-Feld haben.

• Wenn sie auf null / -1 / was auch immer gesetzt ist, wurden die Daten nicht persistiert.

• Wenn Sie sie aufrechterhalten (oder aus der Datenbank abrufen), setzen Sie sie auf den id-Wert in der Datenbank.

• Ihre persist-Methode kann die ID überprüfen und sie an die update()- oder add()-Implementierung weitergeben.

• Schwächen: Synchronisierung mit der Datenbank, usw. Ich bin sicher, es gibt noch mehr, aber ich sollte wirklich etwas arbeiten...

Answer 4

Solange Sie die MySQL können Sie die ON DUPLICATE Schlüsselwort. Zum Beispiel:

INSERT INTO my_prefs (user_id, pref_key, pref_value) VALUES (1234, 'show_help', 'true') 
ON DUPLICATE KEY UPDATE (pref_key, pref_value) VALUES ('show_help', 'true');