Practical MySQL & MariaDB

MySQL UPSERT Vysvětleno: INSERT … ON DUPLICATE KEY UPDATE – průvodce s příklady

Chyby a řešení problémů
目次

1. Co je UPSERT?

Přehled

„UPSERT“ označuje funkci databáze, která kombinuje operace „INSERT“ a „UPDATE“. Jinými slovy, pokud data ještě neexistují, jsou vložena; pokud stejná data již existují, jsou aktualizována. Použitím této funkce můžete provádět efektivní operace při zachování konzistence dat.

V MySQL je tato funkčnost implementována pomocí syntaxe INSERT ... ON DUPLICATE KEY UPDATE. Tato funkce vám umožní vyhnout se chybám duplicitních klíčů a aktualizovat existující záznamy i v případě, že nastanou duplicitní klíče.

Příklady použití

  • Systémy správy zákazníků: Přidejte nová data zákazníka, pokud neexistují, a aktualizujte existující informace o zákazníkovi, když se změní.
  • Správa inventáře produktů: Přidejte nové produkty a zároveň aktualizujte množství zásob existujících produktů.

Výhody UPSERT v MySQL

  • Zabraňuje chybám duplicitních klíčů
  • Zjednodušuje SQL dotazy
  • Udržuje integritu dat

2. Základní použití UPSERT v MySQL

V MySQL jsou operace UPSERT prováděny pomocí syntaxe INSERT ... ON DUPLICATE KEY UPDATE. S touto syntaxí můžete při výskytu duplicitního klíče aktualizovat část nebo všechna existující data místo vložení nových dat.

Základní syntaxe

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Vysvětlení:

  1. Použijte INSERT INTO k vložení dat.
  2. Pokud data, která se vkládají, již v tabulce existují, provede se klauzule ON DUPLICATE KEY UPDATE a existující data se aktualizují.

Příklad:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

V uvedeném příkladu, pokud uživatel s user_id = 1 již existuje, pole name se aktualizuje na „Taro Tanaka“. Pokud uživatel neexistuje, vloží se nový záznam.

3. Podrobná SQL syntaxe a příklady UPSERT

Aktualizace více sloupců

Při použití UPSERT se mohou vyskytnout situace, kdy chcete aktualizovat jen konkrétní sloupce. V takových případech můžete v klauzuli ON DUPLICATE KEY UPDATE uvést pouze potřebné sloupce.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

V tomto příkladu, pokud produkt s product_id = 100 již existuje, aktualizuje se pouze sloupec price, zatímco ostatní sloupce (např. name) zůstávají beze změny.

4. Rozdíly oproti jiným databázím

Databáze jiné než MySQL také poskytují podobnou funkčnost. Například PostgreSQL a SQLite používají příkazy INSERT ... ON CONFLICT nebo MERGE k dosažení chování podobného UPSERT.

Příklad pro PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

V PostgreSQL a SQLite se používá klauzule ON CONFLICT k řízení chování při výskytu chyby duplicitního klíče. Naopak MySQL používá klauzuli ON DUPLICATE KEY UPDATE.

Specifické vlastnosti MySQL

  • MySQL používá INSERT ... ON DUPLICATE KEY UPDATE a protože syntaxe se liší od ostatních databází, je při migraci mezi systémy nutná zvláštní opatrnost.

5. Pokročilé techniky UPSERT

Hromadný UPSERT (Dávkové zpracování více záznamů)

UPSERT lze provádět nejen pro jeden záznam, ale i pro více záznamů najednou. To výrazně zvyšuje efektivitu operací s databází.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

V tomto příkladu se najednou vloží více záznamů o produktech. Pokud existují duplicitní klíče, aktualizuje se pouze pole price u odpovídajících záznamů.

Použití uložených procedur pro UPSERT

Pro optimalizaci zpracování UPSERT můžete také použít uložené procedury. To vám umožní vytvořit znovupoužitelnou logiku přímo v databázi, což zlepšuje čitelnost i udržovatelnost vašeho kódu.

6. Časté úskalí a důležité úvahy

Transakce a deadlocky

Při používání UPSERT—zejména při velkém objemu dat—mohou nastat deadlocky. Pokud je úroveň izolace transakcí v MySQL nastavena na REPEATABLE READ, jsou pravděpodobnější gap locky.

Vyhýbání se gap lockům

  • Pravděpodobnost deadlocků můžete snížit změnou úrovně izolace transakcí na READ COMMITTED .
  • V případě potřeby zvažte rozdělení velké UPSERT operace na menší dávky a provádění více dotazů místo jednoho velkého příkazu.

7. Závěr

Funkce MySQL UPSERT je mimořádně užitečná pro zjednodušení vkládání a aktualizaci dat při vyhýbání se chybám duplicitních klíčů. Nicméně implementace UPSERT vyžaduje pečlivé zvážení možných deadlocků a nastavení transakcí. Při správném použití umožňuje jednodušší a efektivnější operace s databází.