Ang Mundo ng MySQL at MariaDB

Gabay sa MySQL UPSERT: Paano Gamitin ang INSERT ON DUPLICATE KEY UPDATE na may mga Halimbawa

Mga pundasyon ng SQL
目次

1. Ano ang UPSERT?

Pangkalahatang-ideya

Ang “UPSERT” ay tumutukoy sa isang operasyon ng database na pinagsasama ang parehong INSERT at UPDATE. Sa madaling sabi, kung ang data ay hindi pa umiiral, ito ay i-insert, at kung ito ay umiiral na, ito ay i-update. Ang tampok na ito ay nagbibigay-daan sa mahusay na operasyon habang pinapanatili ang pagkakapare-pareho ng data.

Sa MySQL, ang syntax na INSERT ... ON DUPLICATE KEY UPDATE ay nagbibigay ng functionality na ito. Sa tulong nito, maaari mong maiwasan ang mga error sa duplicate key at i-update ang umiiral na mga record kahit na mangyari ang mga sitwasyon ng duplicate data.

Mga Use Case

  • Mga System ng Pamamahala ng Customer : Mag-insert ng bagong data ng customer kung hindi ito umiiral, o i-update ang umiiral na impormasyon ng customer kapag nagbago ito.
  • Pamamahala ng Inventory : Magdagdag ng mga bagong produkto habang ini-update ang mga bilang ng stock ng umiiral na mga ito.

Mga Benepisyo ng UPSERT sa MySQL

  • Naiiwasan ang mga error sa duplicate key
  • Pinapadali ang mga SQL query
  • Pinapanatili ang integridad ng data

2. Basic Usage ng UPSERT sa MySQL

Sa MySQL, ang mga operasyon ng UPSERT ay ipinatutupad gamit ang syntax na INSERT ... ON DUPLICATE KEY UPDATE. Sa tulong nito, kung matatagpuan ang duplicate key, ang umiiral na record ay i-update sa halip na mag-insert ng bago.

Basic Syntax

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

Paliwanag:

  1. Ang INSERT INTO ay nagsisikap na mag-insert ng data sa table.
  2. Kung ang data ay umiiral na, ang clause na ON DUPLICATE KEY UPDATE ay ipinapatupad at ini-update ang umiiral na record.

Halimbawa:

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

Sa halimbawang ito, kung may umiiral nang user na may user_id = 1, ang kanilang name ay i-update sa ‘Taro Tanaka’. Kung hindi, isang bagong record ang i-insert.

3. Detalyadong SQL Syntax at Mga Halimbawa para sa UPSERT

Pag-update ng Maraming Column

Kapag gumagamit ng UPSERT, maaari mong piliin na i-update lamang ang ilang column. Sa mga ganitong kaso, tukuyin lamang ang mga target column sa clause na ON DUPLICATE KEY UPDATE.

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

Dito, kung may umiiral nang produkto na may product_id = 100, lamang ang column na price ang i-update, na nag-iiwan ng iba pang mga column tulad ng name na hindi binago.

4. Mga Pagkakaiba sa Iba Pang Database

Ang iba pang mga database ay nagbibigay din ng functionality na katulad ng UPSERT. Halimbawa, ang PostgreSQL at SQLite ay gumagamit ng INSERT ... ON CONFLICT o MERGE bilang kanilang katumbas.

Halimbawa sa PostgreSQL

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

Sa PostgreSQL at SQLite, ang clause na ON CONFLICT ay ginagamit upang kontrolin ang pag-uugali kapag nangyari ang mga error sa duplicate key. Sa paghahambing, ang MySQL ay gumagamit ng ON DUPLICATE KEY UPDATE.

Ang Pagkakakilanlan ng MySQL

  • Ang MySQL ay gumagamit ng INSERT ... ON DUPLICATE KEY UPDATE, na naiiba sa iba pang mga database. Kailangan ng espesyal na pansin sa panahon ng migration ng database.

5. Advanced Usage ng UPSERT

Bulk UPSERT (Maraming Record nang Sabay-Sabay)

Ang UPSERT ay maaari ring ilapat sa maraming record nang sabay-sabay, na nakakapagpapabuti nang malaki sa efficiency ng mga operasyon ng database.

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

Dito, maraming produkto ang ini-insert nang sabay-sabay, at kung may umiiral nang duplicate key, lamang ang price ang i-update.

Paggamit ng Stored Procedures para sa UPSERT

Maaari mo ring gumamit ng stored procedures upang gawing mas madali ang mga operasyon ng UPSERT. Ito ay nagdudulot ng reusability ng code, mas madaling basahin, at mas madaling mapanatili.

6. Mga Pitfall at Mga Isinasaalang-alang para sa UPSERT

Mga Transaksyon at Deadlocks

Kapag gumagamit ng UPSERT sa malalaking dataset, maaaring mangyari ang mga deadlock. Kung ang transaction isolation level ng MySQL ay nakatakda sa REPEATABLE READ, mas malamang na mangyari ang mga gap locks.

Pag-iwas sa Gap Locks

  • Ang pagbabago ng antas ng pag-iisa ng transaksyon sa READ COMMITTED ay maaaring magpababa ng panganib ng deadlock.
  • Isaalang-alang ang paghahati ng mga operasyon ng UPSERT sa mas maliliit na kung kinakailangan.

7. Konklusyon

Ang UPSERT functionality ng MySQL ay isang makapangyarihang kasangkapan para sa pagpasok at pag-update ng data nang epektibo habang iniiwasan ang mga duplicate key error. Gayunpaman, mahalagang isaalang-alang ang mga setting ng transaksyon at posibleng mga deadlock. Kapag ginamit nang tama, maaaring gawing mas simple at mas epektibo ang mga operasyon sa database ang UPSERT.