Kõike MySQL-ist ja MariaDB-st

MySQL UPSERT: Andmete sisestamine ja uuendamine ühe käsuga

SQL-i algteadmised
目次

1. Mis on UPSERT?

Ülevaade

„UPSERT“ viitab andmebaasis funktsioonile, mis ühendab „INSERT“ ja „UPDATE“. See tähendab, et kui uut andmeid ei eksisteeri, lisatakse see, ning kui sama andmed on juba olemas, uuendatakse neid. Selle funktsiooni abil saab säilitada andmete järjepidevust ja teha operatsioone tõhusamalt.

MySQL-is vastab sellele süntaks INSERT ... ON DUPLICATE KEY UPDATE. See funktsioon võimaldab vältida duplikaatvõtme vigu ja uuendada olemasolevaid andmeid olukorras, kus võib tekkida andmete kattumine.

Kasutusjuhtumid

  • Kliendihaldussüsteem: kui uut kliendiandmeid pole, lisatakse need; kui olemasoleva kliendi info muutub, siis uuendatakse seda.
  • Tootevarude haldamine: lisatakse uued tooted ja uuendatakse olemasolevate toodete laoseisu.

UPSERT-i eelised MySQL-is

  • Välditakse duplikaatvõtme vigu
  • SQL-päringud muutuvad lihtsamaks
  • Säilitatakse andmete järjepidevus

2. UPSERT-i põhikasutus MySQL-is

MySQL-is teostatakse UPSERT INSERT ... ON DUPLICATE KEY UPDATE süntaksiga. Selle abil saab duplikaatvõtme korral uute andmete sisestamise asemel uuendada olemasolevaid ridu osaliselt või täielikult.

Põhisüntaks

INSERT INTO tabeli_nimi (veerg1, veerg2)
VALUES (väärtus1, väärtus2)
ON DUPLICATE KEY UPDATE
veerg1 = väärtus1, veerg2 = väärtus2;

Selgitus:

  1. INSERT INTO abil sisestatakse andmed.
  2. Kui sisestatavad andmed on juba tabelis olemas, käivitatakse ON DUPLICATE KEY UPDATE ja olemasolevad andmed uuendatakse.

Näide:

INSERT INTO users (user_id, name)
VALUES (1, '田中太郎')
ON DUPLICATE KEY UPDATE
name = '田中太郎';

Selles näites, kui user_id väärtusega 1 kasutaja on juba olemas, uuendatakse tema name väljaks ‘田中太郎’. Kui kasutajat ei ole, lisatakse uus kirje.

3. UPSERT-i SQL-süntaksi detailid ja näited

Mitme veeru uuendamine

UPSERT-i kasutades saab vajadusel uuendada ainult osa veerge. Sel juhul määratakse ON DUPLICATE KEY UPDATE klauslis vaid need veerud, mida on vaja uuendada.

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

Selles näites, kui product_id väärtusega 100 toode on juba olemas, uuendatakse ainult price, kuid teisi veerge (nt name) ei muudeta.

4. Erinevused teiste andmebaasidega

Ka teistes andmebaasides on olemas sarnased toimingud. Näiteks PostgreSQL ja SQLite kasutavad UPSERT-i jaoks INSERT ... ON CONFLICT või MERGE süntaksit.

PostgreSQL näide

INSERT INTO users (user_id, name)
VALUES (1, '田中太郎')
ON CONFLICT (user_id) DO UPDATE SET
name = '田中太郎';

PostgreSQL-is ja SQLite-is kasutatakse ON CONFLICT klauslit, et määrata, mida teha duplikaatvõtme vea korral. MySQL-is kasutatakse selle asemel ON DUPLICATE KEY UPDATE.

MySQL-i eripära

  • MySQL-is kasutatakse INSERT ... ON DUPLICATE KEY UPDATE, kuid teiste andmebaaside süntaks on erinev, mistõttu migreerimisel tuleb olla ettevaatlik.

5. Täpsem UPSERT-i kasutamine

Mass-UPSERT (mitme rea korraga töötlemine)

UPSERT-i saab kasutada ka mitme rea korraga töötlemiseks. See parandab märgatavalt andmebaasi töö efektiivsust.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Sülearvuti', 50000),
(101, 'Nutitelefon', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Selles näites sisestatakse mitu toodet korraga. Kui esineb duplikaatvõti, uuendatakse ainult hinna väärtus.

UPSERT salvestatud protseduuriga

UPSERT-i saab optimeerida ka salvestatud protseduuride abil. See võimaldab luua korduvkasutatavat koodi andmebaasis ning parandada loetavust ja hooldatavust.

6. UPSERT-i lõksud ja ettevaatusabinõud

Tehingud ja ummikseisud

UPSERT-i kasutades, eriti suurte andmemahtude korral, võib tekkida ummikseise (deadlock). Kui MySQL-i tehingu isolatsioonitase on seadistatud väärtusele REPEATABLE READ, on suurem oht nn gap lock’ide tekkeks.

Gap lock’i vältimine

  • Muuda tehingu isolatsioonitasemeks READ COMMITTED, et vähendada ummikseisu riski.
  • Vajadusel jaga UPSERT-operatsioonid väiksemateks osadeks ja käivita need mitmes päringus.

7. Kokkuvõte

MySQL-i UPSERT-funktsioon on väga kasulik, et lihtsustada andmete sisestamist ja uuendamist ning vältida duplikaatvõtme vigu. Samas tuleb arvestada võimalike deadlock’ide ja tehingu seadistustega. Kui seda õigesti kasutada, muudab UPSERT andmebaasi töö lihtsamaks ja tõhusamaks.