Frage SQL-Standard-UPSERT-Aufruf


Ich suche nach einem Standard SQL "UPSERT"Anweisung. Ein Aufruf zum Einfügen und Aktualisieren, falls vorhanden.

Ich bin auf der Suche nach einem funktionierenden, effizienten und plattformübergreifenden Anruf.

Ich habe gesehen MERGE, UPSERT, REPLACE, INSERT .. ON DUPLICATE UPDATE aber keine Aussage erfüllt die Bedürfnisse.

BTW verwende ich MYSQL und HSQLDB für unitests. Ich verstehe, dass HSQLDB begrenzt ist und möglicherweise nicht deckt, was ich brauche, aber ich konnte einen Standardweg auch ohne es nicht finden. Eine Aussage, dass nur MYSQL und HSQLDB auch für jetzt genug sind.

Ich habe mich eine Weile umgesehen und konnte keine Antwort bekommen.

Mein Tisch:

CREATE TABLE MY_TABLE (
  MY_KEY varchar(50) NOT NULL ,
  MY_VALUE varchar(50) DEFAULT NULL,
  TIME_STAMP bigint NOT NULL,
  PRIMARY KEY (MY_KEY)
);

Irgendeine Idee?

Danke;)


5
2018-03-06 16:03


Ursprung


Antworten:


Die einzige Lösung, die sowohl von MySQL als auch von HSQLDB unterstützt wird, besteht darin, die Zeilen abzufragen, die Sie ersetzen möchten, und bedingt entweder INSERT oder UPDATE. Dies bedeutet, dass Sie mehr Anwendungscode schreiben müssen, um die Unterschiede zwischen RDBMS-Implementierungen zu kompensieren.

  1. START TRANSACTION.
  2. WÄHLEN SIE ... FÜR UPDATE.
  3. Wenn das SELECT Zeilen findet, UPDATE.
  4. Sonst, EINFÜGEN.
  5. VERPFLICHTEN.

MySQL unterstützt die ANSI SQL MERGE-Anweisung nicht. Es unterstützt REPLACE und INSERT ... ON DUPLICATE KEY UPDATE. Siehe meine Antwort auf "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" mehr dazu.


Zu den Kommentaren: Ja, ein anderer Ansatz besteht darin, einfach das INSERT auszuprobieren und zu prüfen, ob es erfolgreich ist. Ansonsten führe ein UPDATE durch. Wenn Sie INSERT versuchen und es einen doppelten Schlüssel trifft, wird ein Fehler generiert, der bei einigen Client-Schnittstellen zu einer Ausnahme wird. Der Nachteil dabei ist, dass MySQL eine neue Auto-Inkrement-ID erzeugt, selbst wenn das INSERT fehlschlägt. Sie haben also Lücken. Ich weiß, dass Lücken in der Auto-Increment-Sequenz normalerweise keine Sorge sind, aber ich half einem Kunden im letzten Jahr, der aufgrund dieses Effekts Lücken zwischen 1000-1500 zwischen erfolgreichen Inserts hatte, und das Ergebnis war, dass sie die Reichweite eines INT in ihrem Primärschlüssel.

Wie @Baraky sagt, könnte man stattdessen zuerst das UPDATE versuchen, und wenn sich das auf null Zeilen auswirkt, dann führe das INSERT aus. Mein Kommentar zu dieser Strategie ist, dass UPDATE Null Zeilen keine Ausnahme ist - Sie müssen nach "UPDATE" nach "Anzahl der betroffenen Zeilen" suchen, um zu wissen, ob es erfolgreich war oder nicht.

Wenn Sie jedoch die Anzahl der betroffenen Zeilen abfragen, kehren Sie zum ursprünglichen Problem zurück: Sie müssen verschiedene Abfragen in MySQL und HSQLDB verwenden.

HSQLDB:

CALL DIAGNOSTICS(ROW_COUNT);

MySQL:

SELECT ROW_COUNT();

4
2018-03-06 16:28



Die Syntax für das Ausführen eines Upsert in einem einzelnen Befehl variiert je nach RDBMS.

In MySQL ist es INSERT...ON DUPLICATE KEY UPDATE

In HSQLDB ist es MERGE

Wenn Sie eine plattformübergreifende Lösung benötigen, müssen Sie mehrere Befehle verwenden. Überprüfen Sie zuerst die vorhandene Zeile, und fügen Sie sie dann entsprechend ein oder aktualisieren Sie sie.


3
2018-03-06 16:10