MySQL INSERT vs UPDATE: Syntaxe, příklady, ON DUPLICATE KEY UPDATE a REPLACE

1. Úvod

MySQL je populární relační systém pro správu databází používaný v mnoha webových aplikacích a systémech správy databází. Mezi jeho funkce patří INSERT příkaz a UPDATE příkaz — používané k přidávání a úpravě dat — které hrají zásadní roli jako základní operace s daty. Správným pochopením a efektivním používáním se operace s databází stávají plynulejšími.

V tomto článku poskytujeme podrobný popis MySQL INSERT a UPDATE, od základního použití po pokročilé operace. Obsah je vhodný pro začátečníky i středně pokročilé uživatele, takže jej použijte jako referenci.

2. Základy INSERT

Základní syntaxe INSERT

Základní syntaxe příkazu INSERT je následující.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Jako příklad zvažte přidání nového uživatele do tabulky users.

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);

Tento SQL vloží hodnoty „Taro Yamada“, „taro@example.com“ a „30“ do sloupců name, email a age tabulky users v uvedeném pořadí.

Vkládání více řádků

V MySQL můžete také vložit více řádků dat najednou. V takovém případě syntaxe vypadá takto.

INSERT INTO users (name, email, age)
VALUES
('Hanako Sato', 'hanako@example.com', 25),
('Ichiro Suzuki', 'ichiro@example.com', 40);

Použití této metody snižuje počet přístupů k databázi a může zlepšit výkon.

Zpracování hodnot NULL

Při použití příkazu INSERT můžete potřebovat zpracovat hodnoty NULL. Například pokud není nastaveno age, zapíšete to takto.

INSERT INTO users (name, email, age)
VALUES ('Jiro Tanaka', 'jiro@example.com', NULL);

Všimněte si, že pokud má sloupec omezení NOT NULL, vložení hodnoty NULL způsobí chybu. V takovém případě musíte nastavit výchozí hodnotu nebo explicitně zadat hodnotu.

3. Základy UPDATE

Základní syntaxe UPDATE

Příkaz UPDATE se používá k úpravě dat v existujících záznamech. V této sekci vysvětlujeme základní syntaxi, jak aktualizovat s podmínkami a proč je klauzule WHERE důležitá.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

Jako příklad zvažte aktualizaci věku konkrétního uživatele v tabulce users.

UPDATE users
SET age = 35
WHERE name = 'Taro Yamada';

Tento SQL aktualizuje věk na 35 pro uživatele, jehož jméno je „Taro Yamada“ v tabulce users.

Proč je klauzule WHERE důležitá

Pokud v příkazu UPDATE vynecháte klauzuli WHERE, všechny řádky v tabulce budou aktualizovány. To může způsobit neúmyslnou ztrátu dat, proto vždy specifikujte podmínky.

-- When the WHERE clause is omitted
UPDATE users
SET age = 30;

Tento SQL nastaví věk všech uživatelů na 30.

Podmíněné aktualizace

Při zadávání více podmínek použijte AND nebo OR.

UPDATE users
SET age = 28
WHERE name = 'Hanako Sato' AND email = 'hanako@example.com';

Tímto způsobem můžete aktualizovat data s přesnějšími podmínkami.

4. Kombinování INSERT a UPDATE

V operacích s databází můžete narazit na situace, kdy někdy potřebujete přidat nová data a jindy aktualizovat existující data. V takových scénářích můžete efektivně zpracovat pomocí INSERT ... ON DUPLICATE KEY UPDATE nebo příkazu REPLACE. V této sekci vysvětlujeme, jak každou použít a na co si dát pozor.

Jak použít INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE je efektivní, když existují omezení primárního klíče nebo unikátního klíče. S touto syntaxí můžete provést „aktualizaci, pokud existuje, vložení, pokud neexistuje“ v jediném SQL příkazu.

Syntaxe

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

Příklad

Consider adding a new user to the users table. If the same email already exists, update that user’s name and age.

INSERT INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age);

This SQL statement does the following:

1. If no record exists with email = 'taro@example.com' , insert the data.
2. If an existing record does exist, update name and age .

Notes

• If there is an AUTO_INCREMENT column, the counter increases even when a duplicate key occurs. This can cause unintended behavior, so be careful.
• By using the VALUES() function, you can reuse the values you attempted to insert for the update.

How REPLACE Works and How It Differs

The REPLACE statement completely deletes existing data (when a duplicate key is found) and then inserts the new data. Unlike INSERT ... ON DUPLICATE KEY UPDATE, the original record is deleted.

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

Insert data into the users table, and if email is duplicated, delete the existing data and insert the new data.

REPLACE INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30);

This SQL statement does the following:

1. If a record exists with email = 'taro@example.com' , delete that record.
2. Insert the new data.

Notes

• Because it performs delete + insert, it may affect triggers and foreign key constraints.
• Be aware of side effects from deletion (such as losing related data).

Performance Considerations

INSERT ... ON DUPLICATE KEY UPDATE and REPLACE each have pros and cons. For large databases or high-frequency operations, performance differences matter, so consider the following points.

Choosing the Right Use Case

• When INSERT … ON DUPLICATE KEY UPDATE is suitable
• When foreign key constraints or triggers exist and you want to avoid deletes.
• When updates occur frequently.
• When REPLACE is suitable
• When you need a complete replacement of the data.
• For simple tables not affected by foreign key constraints or triggers.

5. Practical Examples

Here, we introduce real-world use cases for MySQL INSERT and UPDATE, as well as practical usage of “INSERT … ON DUPLICATE KEY UPDATE” and “REPLACE.” This will help you understand how to apply what you have learned in actual work.

Use Case 1: Inventory Management System

In an inventory management system, product registration and stock updates happen frequently. Use INSERT to add new products, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing products.

Inserting and Updating Product Data

For example, assume a product table products is structured as follows.

Registering a New Product

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50);

Updating Stock Quantity (Existing Product)

UPDATE products
SET stock = stock + 20
WHERE product_id = 1;

Insert New or Update Stock Quantity

If you want to either register a new product or update stock for an existing product, use “INSERT … ON DUPLICATE KEY UPDATE.”

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

This SQL accomplishes the following:

• If no data exists for product ID 1, insert it.
• If data exists for product ID 1, add 50 to the stock quantity.

Use Case 2: User Information Management

In web applications, user registration and updates are routine. Use INSERT to register new users, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing user information.

User Table Structure

Registering a New User

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW());

Aktualizace informací uživatele

Například, když uživatel změní svůj profil.

UPDATE users
SET name = 'Hanako Yamada', email = 'hanako@example.com'
WHERE user_id = 1;

Registrace nebo aktualizace informací

Když se uživatel přihlásí poprvé, zaregistrujte ho; pokud uživatel již existuje, aktualizujte čas posledního přihlášení.

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();

Případ použití 3: Periodické aktualizace dat

Při práci se senzorovými nebo logovacími daty mohou být nová data vkládána každou minutu nebo sekundu. V tomto případě buď vložte nová data pomocí INSERT, nebo aktualizujte existující data podmíněně.

Vkládání logovacích dat

Zde je příklad tabulky sensor_logs pro záznam senzorových dat.

Záznam nových senzorových dat

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW());

Aktualizace nebo vložení dat

Pokud ID senzoru již existuje, aktualizujte data; jinak je vložte.

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW())
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
last_updated = VALUES(last_updated);

Poznámky a nejlepší postupy

1. Zpracování chyb: Při používání ON DUPLICATE KEY UPDATE nebo REPLACE je důležité předem zkontrolovat vliv spouštěčů a omezení cizích klíčů.
2. Optimalizace výkonu: Pro velká data použijte návrh indexů a transakce pro efektivní provoz.
3. Integrita dat: Zejména u REPLACE dochází k delete + insert, takže potřebujete opatření k vyhnutí se riziku ztráty souvisejících dat.

6. Běžné chyby a jak je opravit

Při používání příkazů MySQL INSERT a UPDATE mohou nastat různé chyby. V této sekci vysvětlujeme běžné chyby, jejich příčiny a specifické způsoby jejich řešení.

Příklady běžných chyb

1. Chyba duplicitního záznamu

Chybová zpráva:

Error: Duplicate entry '1' for key 'PRIMARY'

Příčina:

• K této chybě dochází, když se pokusíte vložit hodnotu, která již existuje, do sloupce s primárním klíčem nebo unikátním omezením (UNIQUE).

Jak opravit:

• Použijte ON DUPLICATE KEY UPDATE: Proveďte aktualizaci, když existuje duplicitní záznam.

  INSERT INTO users (user_id, name, email)
  VALUES (1, 'Taro Yamada', 'taro@example.com')
  ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);
  

• Zkontrolujte existenci před vložením: Aby se zabránilo duplicitám, předem zkontrolujte, zda data již existují.

  SELECT COUNT(*) FROM users WHERE user_id = 1;
  

2. Chyba omezení cizího klíče

Chybová zpráva:

Error: Cannot add or update a child row: a foreign key constraint fails

Příčina:

• K této chybě dochází, když díky omezení cizího klíče (FOREIGN KEY) neexistují data v referenční rodičovské tabulce.

Jak opravit:

• Vložte související data do rodičovské tabulky.

  INSERT INTO parent_table (id, name) VALUES (1, 'Parent data');
  

• Dočasně zakažte omezení cizích klíčů (nedoporučeno).

  SET FOREIGN_KEY_CHECKS = 0;
  -- Data operations
  SET FOREIGN_KEY_CHECKS = 1;
  

3. Chyba související s NULL

Chybová zpráva:

Error: Column 'name' cannot be null

Příčina:

• K této chybě dochází, když se pokusíte vložit hodnotu NULL, přestože sloupec má omezení NOT NULL.

Jak opravit:

• Nastavte výchozí hodnotu.

  ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL DEFAULT 'Unspecified';
  

• Vložte vhodnou hodnotu pomocí INSERT.

  INSERT INTO users (name, email, age)
  VALUES ('Taro Yamada', 'taro@example.com', NULL);
  

4. Chyba typu dat

Chybová zpráva:

Error: Data truncated for column 'age' at row 1

Příčina:

• Toto nastane, když se pokusíte vložit nebo aktualizovat hodnotu, která neodpovídá datovému typu sloupce.

Jak opravit:

• Zkontrolujte datový typ a použijte vhodnou hodnotu.

  INSERT INTO users (age) VALUES (30); -- For an INT column
  

• Změňte datový typ sloupce (pokud je to potřeba).

  ALTER TABLE users MODIFY age VARCHAR(10);
  

5. Chyba související se zamčením tabulky

Chybová zpráva:

Error: Lock wait timeout exceeded; try restarting transaction

Příčina:

• Toto nastane, když jiná transakce zamkne tabulku a zámek není uvolněn během určitého času.

Jak opravit:

• Aby se předešlo soutěži o transakce, zvažte následující kroky:
• Rozdělit dotazy, aby se snížilo zamykání tabulky.
• Vytvořit vhodné indexy pro zrychlení provádění dotazů.

Nejlepší postupy pro výkon a prevenci chyb

1. Používejte správu transakcí

• Při provádění více INSERT a UPDATE operací používejte transakce pro spolehlivé řízení operací.

  START TRANSACTION;
  INSERT INTO orders (order_id, user_id) VALUES (1, 1);
  UPDATE users SET last_order = NOW() WHERE user_id = 1;
  COMMIT;
  

2. Optimalizujte indexy

• Nastavení vhodných indexů na primárních a cizích klíčích snižuje riziko chyb a zlepšuje výkon.

  ALTER TABLE users ADD INDEX (email);
  

3. Rollback při chybách

• Když nastane chyba, proveďte rollback pro zachování integrity dat.

  START TRANSACTION;
  -- Some operations
  ROLLBACK; -- On error
  

7. Často kladené otázky

Při používání MySQL příkazů INSERT a UPDATE má mnoho lidí podobné otázky. V této sekci prohloubíme porozumění pomocí běžných otázek a odpovědí.

Q1: Co mám použít, INSERT nebo UPDATE?

Odpověď:

Použijte INSERT pro přidání nových dat a UPDATE pro úpravu existujících dat. Pokud jsou však přidávání nových dat a aktualizace existujících dat smíchány, nejlepší volbou je použít „INSERT … ON DUPLICATE KEY UPDATE“.

Příklad:

INSERT INTO users (user_id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

Tato syntaxe dokáže vložit nová data a aktualizovat existující data v jediném dotazu.

Q2: Můžu použít ON DUPLICATE KEY UPDATE pro každý případ použití?

Odpověď:

Ne. ON DUPLICATE KEY UPDATE má následující omezení:

1. Funguje pouze, pokud je definován primární klíč nebo unikátní klíč. Pokud není žádný definován, nedojde k chybě, ale nebude se chovat podle očekávání.
2. U rozsáhlých aktualizací může výkon klesat. V takovém případě zvažte použití transakcí nebo rozdělení dat.

Q3: Jaký je rozdíl mezi REPLACE a ON DUPLICATE KEY UPDATE?

Odpověď:

Jsou si podobné, ale jejich chování se výrazně liší.

Obecně používejte ON DUPLICATE KEY UPDATE, když chcete aktualizovat bez mazání dat, a REPLACE, když chcete kompletní nahrazení.

Q4: Co se stane, když zapomenu na klauzuli WHERE?

Odpověď:

Pokud spustíte UPDATE příkaz bez specifikace klauzule WHERE, budou aktualizovány všechny záznamy v tabulce. To je extrémně nebezpečné a může způsobit nechtěné změny dat.

Příklad:

-- The age of all records is updated to 30
UPDATE users
SET age = 30;

Prevence:

• Vždy specifikujte klauzuli WHERE, aby byly aktualizovány jen data splňující konkrétní podmínky.
• Jako nejlepší postup nejprve spusťte SELECT příkaz, abyste potvrdili cílová data před provedením UPDATE.

  SELECT * FROM users WHERE name = 'Taro Yamada';
  UPDATE users SET age = 35 WHERE name = 'Taro Yamada';
  

Q5: Existuje způsob, jak urychlit INSERT a UPDATE?

Odpověď:

Výkon můžete zlepšit pomocí následujících metod.

1. Optimalizujte indexy: Nastavte vhodné indexy na požadované sloupce pro zrychlení vyhledávání a aktualizačních operací.

   CREATE INDEX idx_email ON users(email);
   

2. Dávkové operace: Je efektivnější vložit nebo aktualizovat více řádků najednou než zpracovávat jeden řádek po jednom.

   INSERT INTO users (name, email, age)
   VALUES
   ('Hanako Sato', 'hanako@example.com', 25),
   ('Ichiro Suzuki', 'ichiro@example.com', 40);
   

3. Používejte transakce: Zpracování více operací v jedné transakci snižuje soutěž o zámky.

   START TRANSACTION;
   INSERT INTO orders (order_id, user_id) VALUES (1, 1);
   UPDATE users SET last_order = NOW() WHERE user_id = 1;
   COMMIT;
   

4. Vyhněte se zbytečným operacím: Zkontrolujte data předem, abyste se vyhnuli zbytečným aktualizacím nebo vložením.

   SELECT COUNT(*) FROM users WHERE user_id = 1;
   -- Avoid unnecessary inserts/updates
   

Q6: Jak mohu zabránit chybám při INSERT nebo UPDATE?

Odpověď:

Aby se předešlo chybám, použijte následující přístupy.

• Ověřte datové typy: Ujistěte se, že data, která vkládáte/aktualizujete, odpovídají datovému typu sloupce.
• Správně nastavte omezení: Správně nakonfigurujte primární klíče, unikátní klíče a cizí klíče, aby byla zachována integrita dat.
• Implementujte zpracování chyb: Přidejte logiku do svého programu, která bude zpracovávat chyby, když nastanou.

  -- Roll back on error
  START TRANSACTION;
  INSERT INTO users (user_id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com');
  ROLLBACK; -- As needed
  

8. Shrnutí

V tomto článku jsme pokryli širokou škálu témat: základy i pokročilé použití příkazů MySQL INSERT a UPDATE, praktické případy použití, zpracování chyb a odpovědi na časté otázky. Níže je shrnutí hlavních bodů.

Klíčové poznatky

1. Základy INSERT

• Použijte INSERT k vložení nových dat do tabulky.
• Můžete vložit více řádků, což umožňuje efektivní operace.
• Buďte opatrní s hodnotami NULL a omezeními NOT NULL.

2. Základy UPDATE

• Použijte UPDATE k úpravě existujících dat na základě podmínek.
• Vždy specifikujte klauzuli WHERE, abyste se vyhnuli neúmyslnému aktualizování všech řádků.

3. Kombinování INSERT a UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE umožňuje vložit nová data a aktualizovat existující data v jedné operaci.
• REPLACE smaže data a vloží je znovu, proto buďte opatrní na spouštěče (triggery) a cizí klíče.

4. Praktické příklady

• Naučili jste se, jak používat INSERT a UPDATE v případech jako správa zásob a správa uživatelských informací.
• Také jsme představili osvědčené postupy pro efektivní zpracování více operací.

5. Chyby a opravy

• Vysvětlili jsme příčiny a řešení běžných problémů, jako jsou duplicitní záznamy, omezení cizích klíčů a chyby při vkládání NULL.
• Používání transakcí a návrh indexů jsou důležité.

6. Často kladené otázky

• Odpověděli jsme na časté otázky ohledně výběru mezi INSERT a UPDATE, rozsahu ON DUPLICATE KEY UPDATE a optimalizace výkonu.

Další kroky

Příkazy MySQL INSERT a UPDATE jsou základní pro databázové operace a nezbytné dovednosti pro vývoj aplikací. Na základě toho, co jste se v tomto článku naučili, zvažte následující další kroky.

1. Naučte se správu transakcí: Pro provádění pokročilejších databázových operací prohlubte své pochopení používání transakcí.
2. Optimalizujte návrh indexů: Naučte se navrhovat indexy, aby se udržoval výkon dotazů s rostoucím objemem dat.
3. Zlepšete logování pro odstraňování problémů: Zaveďte zaznamenávání a analýzu logů, abyste mohli rychle identifikovat příčiny, když nastanou chyby.
4. Používejte oficiální dokumentaci MySQL: Pro více detailů a nejnovější funkce se odkažte na oficiální dokumentaci MySQL .

Závěrečná poznámka

Doufáme, že vám tento článek pomůže pochopit INSERT a UPDATE a používat je efektivně. Ovládnutí základních operací s daty zlepší vaše dovednosti v správě databází a umožní vám zvládat pokročilejší vývoj aplikací.

Pokračujte v prohlubování svých znalostí MySQL!