Jak ZAKTUALIZOWAĆ wiersz w tabeli lub WSTAWIĆ go, jeśli nie istnieje?

Mam następującą tabelę liczników:

CREATE TABLE cache (
    key text PRIMARY KEY,
    generation int
);

Chciałbym zwiększyć jeden z liczników lub ustawić go na zero, jeśli odpowiedni wiersz jeszcze nie istnieje. Czy można to zrobić bez problemów ze współbieżnością w standardowym języku SQL? Operacja jest czasami częścią transakcji, czasami oddzielną.

Jeśli to możliwe, SQL musi być niezmodyfikowany na SQLite, PostgreSQL i MySQL.

Wyszukiwanie przyniosło kilka pomysłów, które albo cierpią na problemy ze współbieżnością, albo są specyficzne dla bazy danych:

• Spróbuj przejść INSERTdo nowego wiersza, a UPDATEjeśli wystąpił błąd. Niestety błąd przy INSERTprzerywaniu bieżącej transakcji.

• UPDATEwiersz, a jeśli żadne wiersze nie zostały zmodyfikowane, INSERTnowy wiersz.

• MySQL zawiera ON DUPLICATE KEY UPDATEklauzulę.

EDYCJA: Dzięki za wszystkie świetne odpowiedzi. Wygląda na to, że Paul ma rację i nie ma jednego, przenośnego sposobu na zrobienie tego. To dla mnie dość zaskakujące, ponieważ brzmi to jak bardzo podstawowa operacja.

MySQL (a następnie SQLite) obsługują również składnię REPLACE INTO:

REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');

To automatycznie identyfikuje klucz podstawowy i znajduje pasujący wiersz do zaktualizowania, wstawiając nowy, jeśli nie zostanie znaleziony.

SQLite obsługuje zastępowanie wiersza, jeśli już istnieje:

INSERT OR REPLACE INTO [...blah...]

Możesz to skrócić do

REPLACE INTO [...blah...]

Ten skrót został dodany, aby był zgodny z REPLACE INTOwyrażeniem MySQL .

Zrobiłbym coś takiego:

INSERT INTO cache VALUES (key, generation)
ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);

Ustawienie wartości generacji na 0 w kodzie lub w sql, ale użycie ON DUP ... w celu zwiększenia wartości. Myślę, że w każdym razie taka jest składnia.

klauzula ON DUPLICATE KEY UPDATE jest najlepszym rozwiązaniem, ponieważ: REPLACE wykonuje polecenie DELETE, po którym następuje INSERT, więc przez bardzo krótki okres rekord jest usuwany, tworząc bardzo małą możliwość, że zapytanie może powrócić po pominięciu tego, gdyby strona została wyświetlane podczas zapytania REPLACE.

Wolę INSERT ... ON DUPLICATE UPDATE ... z tego powodu.

Rozwiązanie jmoz jest najlepsze: chociaż wolę składnię SET od nawiasów

INSERT INTO cache 
SET key = 'key', generation = 'generation'
ON DUPLICATE KEY 
UPDATE key = 'key', generation = (generation + 1)
;

Nie wiem, czy znajdziesz rozwiązanie neutralne dla platformy.

Nazywa się to powszechnie „UPSERT”.

Zobacz kilka powiązanych dyskusji:

• Rozwiązania do WSTAWIANIA LUB AKTUALIZACJI na serwerze SQL
• Wdrożenie MySQL w SQL Server 2005 WYMIENIĆ NA?

W PostgreSQL nie ma polecenia merge, a napisanie go nie jest trywialne - w rzeczywistości istnieją dziwne przypadki skrajne, które sprawiają, że zadanie jest „interesujące”.

Najlepszym (jak w przypadku pracy w jak najbardziej możliwych warunkach) podejściem jest użycie funkcji - takiej jak pokazana w instrukcji (merge_db).

Jeśli nie chcesz korzystać z funkcji, zwykle możesz uciec z:

updated = db.execute(UPDATE ... RETURNING 1)
if (!updated)
  db.execute(INSERT...)

Wystarczy pamiętać, że to nie jest odporne na dowód i to będzie nie ostatecznie.

Standardowy SQL udostępnia instrukcję MERGE dla tego zadania. Nie wszystkie DBMS obsługują instrukcję MERGE.

Jeśli nie masz wspólnego sposobu niepodzielnej aktualizacji lub wstawiania (np. Za pośrednictwem transakcji), możesz wrócić do innego schematu blokowania. Plik o rozmiarze 0 bajtów, muteks systemu, nazwany potok itp.

Czy możesz użyć wyzwalacza wstawiania? Jeśli to się nie powiedzie, przeprowadź aktualizację.

Jeśli nie masz nic przeciwko korzystaniu z biblioteki, która pisze dla Ciebie SQL, możesz użyć Upsert (obecnie tylko Ruby i Python):

Pet.upsert({:name => 'Jerry'}, :breed => 'beagle')
Pet.upsert({:name => 'Jerry'}, :color => 'brown')

Działa to w MySQL, Postgres i SQLite3.

Zapisuje procedurę składowaną lub funkcję zdefiniowaną przez użytkownika (UDF) w MySQL i Postgres. Używa INSERT OR REPLACEw SQLite3.