1. Co je UPSERT?
Přehled
„UPSERT“ označuje databázovou operaci, která kombinuje INSERT a UPDATE. Jinými slovy, pokud data ještě neexistují, budou vložena, a pokud existují, budou aktualizována. Tato funkce umožňuje efektivní operace při zachování konzistence dat.
V MySQL syntaxe INSERT ... ON DUPLICATE KEY UPDATE poskytuje tuto funkcionalitu. S ní můžete vyhnout chybám duplicitních klíčů a aktualizovat existující záznamy i při výskytu duplicitních dat.
Případů použití
- Systémy pro správu zákazníků : Vložit nová zákaznická data, pokud neexistují, nebo aktualizovat existující informace o zákaznících, když se změní.
- Správa skladu : Přidat nové produkty při aktualizaci počtů skladových zásob existujících.
Výhody UPSERT v MySQL
- Vyhnout se chybám duplicitních klíčů
- Zjednodušit SQL dotazy
- Zachovat integritu dat
2. Základní použití UPSERT v MySQL
V MySQL se operace UPSERT implementují pomocí syntaxe INSERT ... ON DUPLICATE KEY UPDATE. S touto syntaxí, pokud je nalezen duplicitní klíč, existující záznam je aktualizován místo vložení nového.
Základní syntaxe
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;
Vysvětlení:
INSERT INTOse pokusí vložit data do tabulky.- Pokud data již existují, klauzule
ON DUPLICATE KEY UPDATEse provede a aktualizuje existující záznam.
Příklad:
INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';
V tomto příkladu, pokud uživatel s user_id = 1 již existuje, jeho name bude aktualizován na ‚Taro Tanaka‘. Jinak bude vložen nový záznam.
3. Podrobná SQL syntaxe a příklady pro UPSERT
Aktualizace více sloupců
Při používání UPSERT můžete zvolit aktualizaci pouze určitých sloupců. V takových případech specifikujte pouze cílové sloupce v klauzuli ON DUPLICATE KEY UPDATE.
INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
Zde, pokud produkt s product_id = 100 již existuje, bude aktualizován pouze sloupec price, zatímco jiné sloupce jako name zůstanou nezměněny.
4. Rozdíly s jinými databázemi
Další databáze také poskytují funkcionalitu podobnou UPSERT. Například PostgreSQL a SQLite používají INSERT ... ON CONFLICT nebo MERGE jako ekvivalenty.
Příklad v 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 klauzule ON CONFLICT používá k ovládání chování při chybách duplicitních klíčů. Na rozdíl od toho MySQL používá ON DUPLICATE KEY UPDATE.
Jedinečnost MySQL
- MySQL používá
INSERT ... ON DUPLICATE KEY UPDATE, což se liší od jiných databází. Během migrace databáze je potřeba zvláštní pozornost.
5. Pokročilé použití UPSERT
Hromadný UPSERT (více záznamů najednou)
UPSERT lze také aplikovat na více záznamů najednou, což výrazně zlepšuje efektivitu databázových operací.
INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
Zde jsou vloženy více produktů najednou a pokud existuje duplicitní klíč, je aktualizován pouze price.
Použití uložených procedur pro UPSERT
Můžete také použít uložené procedury k zjednodušení operací UPSERT. To činí kód znovupoužitelným, čitelnějším a snadněji udržovatelným.
6. Pasti a úvahy pro UPSERT
Transakce a deadlocks
Při používání UPSERT s velkými datovými sadami mohou nastat deadlocks. Pokud je úroveň izolace transakcí v MySQL nastavena na REPEATABLE READ, jsou gap locks pravděpodobnější.
Vyhnutí se gap locks
- Změna úrovně izolace transakce na
READ COMMITTEDmůže snížit rizika deadlocků. - Zvažte rozdělení operací UPSERT do menších dotazů, pokud je to nutné.
7. Závěr
Funkčnost UPSERT v MySQL je výkonným nástrojem pro efektivní vkládání a aktualizaci dat při vyhýbání se chybám duplicitních klíčů. Nicméně je nezbytné pečlivě zvážit nastavení transakcí a potenciální deadlocks. Pokud se používá správně, UPSERT může databázové operace zjednodušit a učinit je efektivnějšími.
