Practical MySQL & MariaDB

MySQL UPSERT පැහැදිලි කිරීම: INSERT … ON DUPLICATE KEY UPDATE උදාහරණ සමඟ මාර්ගෝපදේශය

SQL මූලික කරුණු (SQL Basics)
目次

1. UPSERT කියන්නේ මොනවාද?

සමාලෝචනය

“UPSERT” යනු “INSERT” සහ “UPDATE” මෙහෙයුම් එකට එකතු කරන දත්ත ගබඩා විශේෂාංගයක් වේ. වෙනත් වචන වලින් කියනවා නම්, දත්තය තවමත් නොමැති නම් එය ඇතුළත් කරයි; ඒම දත්තය දැනටමත් පවතිනවා නම් එය යාවත්කාලීන කරයි. මෙම විශේෂාංගය භාවිතා කිරීමෙන්, දත්ත සමගිත්වය රැකගෙන කාර්යක්ෂම මෙහෙයුම් සිදු කළ හැකිය.

MySQL හි, මෙම ක්‍රියාකාරිත්වය INSERT ... ON DUPLICATE KEY UPDATE වාක්‍ය රීතිය භාවිතා කර ක්‍රියාත්මක වේ. මෙම විශේෂාංගය ඔබට අනුපිටපත් යතුරු දෝෂ වලින් වැළැක්වීමට සහ අනුපිටපත් යතුරු ඇති වූ විටත් පවතින ලේඛන යාවත්කාලීන කිරීමට ඉඩ සලසයි.

භාවිතා නිරූපණයන්

  • පාරිභෝගික කළමනාකරණ පද්ධති : පාරිභෝගික දත්ත නොමැති නම් නව දත්ත එකතු කර, පාරිභෝගික තොරතුරු වෙනස් වූ විට පවතින දත්ත යාවත්කාලීන කරයි.
  • නිෂ්පාදන තොග කළමනාකරණය : නව නිෂ්පාදන එකතු කරන අතර පවතින නිෂ්පාදනවල තොග ප්‍රමාණය යාවත්කාලීන කරයි.

MySQL හි UPSERT හි වාසි

  • අනුපිටපත් යතුරු දෝෂ වලින් වැළැක්වීම
  • SQL විමසුම් සරල කිරීම
  • දත්ත අඛණ්ඩතාව රැකීම

2. MySQL හි UPSERT の මූලික භාවිතය

MySQL හි, UPSERT මෙහෙයුම් INSERT ... ON DUPLICATE KEY UPDATE වාක්‍ය රීතිය භාවිතා කර සිදු කරයි. මෙම රීතිය සමඟ, අනුපිටපත් යතුරක් සිදුවූ විට, නව දත්ත ඇතුළත් කිරීම වෙනුවට පවතින දත්ත කොටසක් හෝ සම්පූර්ණයෙන් යාවත්කාලීන කළ හැකිය.

මූලික වාක්‍ය රීතිය

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

විස්තරය:

  1. දත්ත ඇතුළත් කිරීම සඳහා INSERT INTO භාවිතා කරන්න.
  2. ඇතුළත් කරන දත්තය මේ දක්වා වගුවේ පවතිනවා නම්, ON DUPLICATE KEY UPDATE කොටස ක්‍රියාත්මක වෙයි, එවිට පවතින දත්තය යාවත්කාලීන වේ.

උදාහරණය:

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

ඉහත උදාහරණයේ, user_id 1 යන පරිශීලකයෙක් දැනට පවතිනවා නම්, name ක්ෂේත්‍රය ‘Taro Tanaka’ ලෙස යාවත්කාලීන වේ. පරිශීලකයා නොපවතිනවා නම්, නව ලේඛනයක් ඇතුළත් වේ.

3. UPSERT の විස්තරාත්මක SQL වාක්‍ය රීතිය සහ උදාහරණ

බහු තීරුවල යාවත්කාලීන කිරීම

UPSERT භාවිතා කරන විට, ඔබට විශේෂ තීරුවල පමණක් යාවත්කාලීන කිරීමට අවශ්‍ය විය හැක. එවැනි අවස්ථාවල, ON DUPLICATE KEY UPDATE කොටස තුළ අවශ්‍ය තීරුවල පමණක් සඳහන් කළ හැක.

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

මෙම උදාහරණයේ, product_id 100 යන නිෂ්පාදනය දැනට පවතිනවා නම්, price තීරුව පමණක් යාවත්කාලීන වේ, අනෙකුත් තීරුවල (උදාහරණයක් name) වෙනස් නොවේ.

4. අනෙකුත් දත්ත ගබඩා සමඟ වෙනස්කම්

MySQL හැර වෙනත් දත්ත ගබඩා පද්ධතිද සමාන ක්‍රියාකාරිත්වයක් ලබා දෙයි. උදාහරණයක් ලෙස, PostgreSQL සහ SQLite INSERT ... ON CONFLICT හෝ MERGE ප්‍රකාශන භාවිතා කර UPSERT‑සම ව්‍යාපාරයක් සිදු කරයි.

PostgreSQL උදාහරණය

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

PostgreSQL සහ SQLite හි, අනුපිටපත් යතුරු දෝෂයක් සිදුවූ විට හැසිරීම පාලනය කිරීම සඳහා ON CONFLICT කොටස භාවිතා කරයි. එයට විරුද්ධව, MySQL ON DUPLICATE KEY UPDATE කොටස භාවිතා කරයි.

MySQL‑විශේෂ ලක්ෂණ

  • MySQL INSERT ... ON DUPLICATE KEY UPDATE භාවිතා කරයි, එමනිසා රීතිය අනෙකුත් දත්ත ගබඩා වලින් වෙනස් වන බැවින්, පද්ධති අතර මාරු කිරීමේදී විශේෂ අවධානයක් අවශ්‍ය වේ.

5. උසස් UPSERT තාක්ෂණයන්

බහු UPSERT (බහු ලේඛන එකවර සැකසීම)

UPSERT එකක් පමණක් නොව, එකවර බහු ලේඛන සඳහාද ක්‍රියාත්මක කළ හැක. මෙය දත්ත ගබඩා මෙහෙයුම් කාර්යක්ෂමතාවය ගණනාවට වැඩි කරයි.

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

මෙම උදාහරණයේ, බහු නිෂ්පාදන ලේඛන එකවර ඇතුළත් කරයි. අනුපිටපත් යතුරු පවතින විට, අදාළ ලේඛනවල price ක්ෂේත්‍රය පමණක් යාවත්කාලීන වේ.

UPSERT සඳහා ගබඩා කරන ක්‍රමලේඛ (Stored Procedure) භාවිතය

UPSERT සැකසුම් උපරිම කිරීම සඳහා, ඔබට stored procedures ද භාවිතා කළ හැක. මෙය ඔබට දත්ත සමුදාය තුළ නැවත භාවිතා කළ හැකි ලොජික් නිර්මාණය කිරීමට ඉඩ සලසන අතර, ඔබේ කේතයේ කියවන ලෙසත් පවත්වාගෙන යාමේ හැකියාවත් වැඩි දියුණු කරයි.

6. සුලබ දෝෂ සහ වැදගත් සලකා බැලීම්

සන්නාම සහ Deadlocks

UPSERT භාවිතා කිරීමේදී—විශේෂයෙන්ම විශාල දත්ත ප්‍රමාණයක් සමඟ—deadlocks සිදු විය හැක. MySQL හි සන්නාම වෙන්කිරීමේ මට්ටම REPEATABLE READ ලෙස සකස් කර ඇත්නම්, gap locks සිදු වීමේ ඉඩකඩ වැඩි වේ.

Gap Locks වළක්වා ගැනීම

  • සන්නාම වෙන්කිරීමේ මට්ටම READ COMMITTED ලෙස වෙනස් කිරීමෙන් deadlocks සිදු වීමේ ඉඩකඩ අඩු කළ හැක.
  • අවශ්‍ය නම්, විශාල UPSERT මෙහෙයුමක් කුඩා බැච්වලට බෙදා, එක විශාල ප්‍රකාශනයකට වෙනුවට බහු ප්‍රශ්න කිහිපයක් ක්‍රියාත්මක කිරීම සලකා බලන්න.

7. නිගමනය

MySQL UPSERT විශේෂාංගය දත්ත ඇතුළත් කිරීම සහ යාවත්කාලීන කිරීම සරල කිරීමට ඉතා වැදගත් වන අතර, අනුපුගී ප්‍රධාන දෝෂ වළක්වයි. කෙසේ වෙතත්, UPSERT ක්‍රියාත්මක කිරීම සඳහා deadlocks සහ සන්නාම සැකසුම් පිළිබඳ සැලකිලිමත් බව අවශ්‍ය වේ. නිවැරදි ලෙස භාවිතා කළ විට, එය සරල සහ කාර්යක්ෂම දත්ත සමුදාය මෙහෙයුම් සඳහා හැකියාවක් සපයයි.