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 Procedures) භාවිතය

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

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

සන්නාම සහ Deadlocks

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

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

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

7. නිගමනය

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