UPSERT wielu wierszy w MySQL

Dzisiaj zacznę odwrotnie niż zwykle czyli od prezentacji gotowego rozwiązania:

INSERT INTO user_counters (user_id, counter_type, counter_value, updated_at)
VALUES
    (:user_id1, :counter_type1, :counter_value1, :updated_at1),
    (:user_id2, :counter_type2, :counter_value2, :updated_at2),
    # ...
    (:user_idN, :counter_typeN, :counter_valueN, :updated_atN)
ON DUPLICATE KEY UPDATE
    counter_value = counter_value + VALUES(counter_value),
    updated_at = VALUES(updated_at);

To tak zwany UPSERT czyli zapytanie, które wstawia rekord(y) do bazy, a jeśli taki już istnieje (tzn. istnieje już taki klucz główny lub unikalny) to go zaktualizuje (wartość w kolumnie counter_value zostanie powiększona o nową wartość, a w kolumnie updated_at zastąpiona). To zapytanie jest jednak trochę lepsze od tych, które zazwyczaj podaje się jako przykład UPSERTA, ponieważ pozwala zapisać wiele (dziesięć, sto, tysiąc) wierszy za jednym zamachem. Sprawia to użyta w części UPDATE funkcja VALUES(), która pobiera wartości ze wskazanej kolumny w części INSERT.

Po co się w ogóle tak babrać w SQL-u jak zwierzę skoro Doctrine mógłby to zrobić za nas? Akurat tego nie zrobi. Przy użyciu Doctrine należałoby:

1. Pobrać wszystkie pasujące rekordy (jeden SELECT).
2. Przeiterować przez nie i:
   • zaktualizować wartości,
   • zidentyfikować brakujące rekordy.
3. Utworzyć encje brakujących rekordów.
4. Opróżnić bufor managera encji (flush, który zapewne wykona jeden INSERT i jeden UPDATE).

Jak widać zarówno po stronie aplikacji (PHP) jak i bazy to wyraźnie więcej pracy, która i tak pewnie poszłaby na marne, bo przy kilku pracujących jednocześnie instancjach aplikacji jest spora szansa na wyjątek UniqueConstraintViolationException, który zamknie managera encji.

UPSERT z wieloma wartościami rozwiązuje problem wydajności i problem naruszania ograniczeń, ale tworzy klikna innych:

• Przy jednoczesnych instancjach aplikacji łatwo o deadlocki na bazie. Trzeba zastosować jakiś mechanizm blokowania.
• Jeśli w ten sposób zapisujemy dane z encji Doctrine’a to ich stan może być nieaktualny.

INSERT … ON DUPLICATE KEY UPDATE nie jest częścią standardu SQL, to rozszerzenie dostępne w MySQL. W PostgrSQL jest podobny mechanizm, ale jego działanie jest nieco inne, więc może opiszę je przy innej okazji.