Practical MySQL & MariaDB

MySQL හි CSV ගොනුවක් අපනයනය කිරීම: SELECT INTO OUTFILE, අනුවාද වෙනස්කම්, දෝෂ සහ ආරක්ෂක හොඳ පුරුදු

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

1. හැඳින්වීම

CSV (Comma Separated Values) යනු දත්ත නිර්යාත, මාරු කිරීම සහ බැකප් සඳහා පුළුල් ලෙස භාවිතා වන ආකෘතියකි. MySQL CSV ආකෘතියෙන් දත්ත නිර්යාත කිරීමේ ක්‍රියාකාරිත්වය සපයයි, එය කාර්යක්ෂම දත්ත කළමනාකරණය සහ විශ්ලේෂණයට ප්‍රයෝජනවත් වේ. මෙම ලිපිය MySQL භාවිතා කර CSV ආකෘතියට දත්ත නිර්යාත කිරීමේ විස්තරාත්මක ක්‍රමය, අනුවාද අතර වෙනස්කම්, දෝෂ පණිවුඩ හසුරවීමේ ක්‍රම, සහ වැදගත් ආරක්ෂක සැලකිලි පිළිබඳව පැහැදිලි කරයි.

ක්‍රියාත්මක පරිසරය

මෙම ලිපිය MySQL 8.0 මත පදනම්ව ඇත, නමුත් MySQL 5.x භාවිතා කරන විට ඇති වෙනස්කම්ද ආවරණය කරයි. හැසිරීම සහ වින්‍යාසය අනුවාද අනුව වෙනස් විය හැකි බැවින්, ඔබ භාවිතා කරන අනුවාදයට අනුව සුදුසු ක්‍රියාමාර්ග අනුගමනය කිරීම අත්‍යවශ්‍යයි.

2. MySQL හි CSV නිර්යාත කිරීමේ මූලික පියවර

MySQL හි CSV ආකෘතියෙන් දත්ත නිර්යාත කිරීමට, SELECT INTO OUTFILE විධානය භාවිතා කරන්න. මෙම විධානය CSV ආකෘතියේ ගොනුවකට විමසුම් ප්‍රතිඵල සුරකින්නා වූ ප්‍රමිතියකි.

2.1 මූලික ව්‍යවස්ථාව

SELECT * FROM table_name INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

2.2 විධානයේ විස්තර

  • SELECT * : වගුවේ සියලු දත්ත තෝරයි. ඔබට විශේෂිත තීරුවල නිර්යාත කිරීමට අවශ්‍ය නම්, ඒ තීරුවේ නාමයන් සඳහන් කරන්න.
  • INTO OUTFILE : විමසුම් ප්‍රතිඵලය නියමිත මාර්ගයට ගොනුවක් ලෙස සුරකිනු ඇත. මාර්ගය සම්පූර්ණ (ඇබ්සලූට්) මාර්ගයක් විය යුතුය.
  • FIELDS TERMINATED BY ',' : තීරුවල අතර වෙන්කිරීම කොමා (,) ලෙස සකසයි.
  • ENCLOSED BY '"' : එක් එක් ක්ෂේත්‍රය දෙකකින් (“”) වටා ගනී. දත්ත තුළ කොමා හෝ පේළි බිඳුම් ඇතුළත් වුවත් නිවැරදිව සැකසීමට මෙය උපකාරී වේ.
  • LINES TERMINATED BY '\n' : එක් එක් පේළිය නව පේළි අක්ෂරයක් (\n) මගින් වෙන් කරයි. Windows පරිසරයන්හි '\r\n' භාවිතා කළ හැක.

3. අනුවාද-අනුසාර වෙනස්කම්

3.1 MySQL 5.x සහ 8.x අතර වෙනස්කම්

MySQL 5.x සහ 8.x අතර කිහිපයක් වැදගත් වෙනස්කම් ඇත. විශේෂයෙන්, අකේතනය සහ ගොනු නිර්යාත විශේෂතා සම්බන්ධයෙන් පහත කරුණු වෙත අවධානය යොමු කරන්න.

  • අකේතනයේ හසුරවීම :
  • MySQL 5.x මූලික අකේතනය ලෙස utf8 භාවිතා කරයි. එය 3-බයිට් අක්ෂරවලට පමණක් සහාය දක්වයි, එබැවින් එමෝජි හෝ විශේෂ අක්ෂරบางบาง නොහැකිය. ඒ නිසා utf8mb4 භාවිතා කිරීම නිර්දේශ කරයි, එය 4-බයිට් අක්ෂරවලට සහාය දක්වයි. නමුත් 5.x හි මෙයට සහාය සීමිතය.
  • MySQL 8.x මූලික අකේතනය ලෙස utf8mb4 භාවිතා කරයි, එමෝජි සහ සියලුම බහු-බයිට් අක්ෂර නිවැරදිව හසුරවයි.
  • secure_file_priv වැඩිදියුණුකම් :
  • MySQL 8.x හි ආරක්ෂාව ශක්තිමත් කර ඇත, ගොනු ලිවීම secure_file_priv මගින් දැඩිව පාලනය වේ. අවසර ලත් නාමාවලියෙන් පිටත ගොනුවක් ලිවීමට උත්සාහ කළහොත් දෝෂයක් සිදුවේ.
  • 5.x හිද සමාන සැකසුම් තිබේ, නමුත් වින්‍යාසය අනුව ඒවා අඩු දැඩි බවක් ඇති විය හැකි අතර, නිසි සැකසීම අවශ්‍ය විය හැක.

3.2 CSV නිර්යාත කාර්යක්ෂමතාව

MySQL 8.x කාර්යක්ෂමතා වැඩිදියුණුකම් අඩංගු කර ඇත, විශේෂයෙන් විශාල දත්ත කට්ටල CSV වෙත නිර්යාත කිරීමේදී එය පැහැදිලි වේ. CSV නිර්යාතය 5.x හිද සම්භවයක් වුවත්, 8.x හි අනුකූලතා වේගවත් හා වැඩි කාර්යක්ෂම දත්ත නිර්යාතය ලබා දේ.

4. CSV නිර්යාත කිරීමේ වැදගත් සටහන්

4.1 ගොනු ලිවීමේ අවසර සහ secure_file_priv

secure_file_priv යනු MySQL ගොනු මෙහෙයුම් සඳහා ප්‍රවේශ විය හැකි නාමාවලිය සීමා කරන සැකසුමකි. මෙය සකසා ඇත්නම්, නියමිත නාමාවලියෙන් පිටත ගොනු ලිවීමට අවසර නොදේ. මෙම සැකසුම පරීක්ෂා කිරීමට, පහත විධානය භාවිතා කරන්න:

SHOW VARIABLES LIKE 'secure_file_priv';

මෙම සැකසුම ගොනු ආරක්ෂිතව ලිවිය හැකි නාමාවලිය සීමා කරයි. ඔබ අවසර ලත් නාමාවලියක් සඳහන් නොකළහොත්, පහත පරිදි දෝෂ පණිවුඩයක් ලැබේ.

4.2 අකේතන ගැටළු

බහු-බයිට් හෝ විශේෂ අක්ෂර (උදාහරණයක් ලෙස ජපන් පදනම් හෝ එමෝජි) ඇතුළත් දත්ත නිර්යාත කිරීමේදී අකේතන සැකසුම් අත්‍යවශ්‍ය වේ. utf8mb4 භාවිතා කිරීමෙන් සියලු අක්ෂර නිවැරදිව නිර්යාත කළ හැක. MySQL 5.x සාමාන්‍යයෙන් utf8 භාවිතා කරන නමුත්, 8.x වෙත යාවත්කාලීන කිරීමෙන් අකේතන-සම්බන්ධ ගැටළු වලින් වළක්වීමට පහසු වේ.

5. දෝෂ පණිවුඩ සහ විසඳුම්

Various errors can occur during CSV export. Below are common error messages and their solutions.

5.1 secure_file_priv Error

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

මෙම දෝෂය secure_file_priv සැකසුමෙන් ඉඩ නොදෙන නාමාවලියකට ගොනුවක් ලිවීමට උත්සාහ කරන විට සිදුවේ. ඔබට ගොනුව ඉඩ ඇති නාමාවලියකට අපනයනය කළ යුතුය හෝ සැකසුම වෙනස් කළ යුතුය.

5.2 Write Permission Error

ERROR 13 (HY000): Can't get stat of '/path/to/file.csv' (Errcode: 13 - Permission denied)

ලියන අවසර ප්‍රමාණය ප්‍රමාණවත් නොවීමේදී මෙම දෝෂය සිදුවේ. සුදුසු අවසර සකස් කිරීමට, පහත කමාන්ඩ් එක භාවිතා කරන්න:

sudo chmod 755 /path/to/directory

ආරක්ෂා සටහන: chmod 777 භාවිතා කිරීමෙන් වැළකින්න. සියලු පරිශීලකයන්ට ලිවීමේ අවසර ලබා දීම ආරක්ෂා අවදානමක් වේ. අවශ්‍ය අවම අවසර පමණක් යෙදීම සුදුසුය.

6. Additional Security Considerations

6.1 Managing File Permissions

MySQL හි CSV ගොනු අපනයනය කරන විට, ලිවීමේ අවසරයන්ට අවධානය යොමු කර සුදුසු අවසර සැකසීම අත්‍යවශ්‍ය වේ. විශේෂයෙන් පොදු සේවාදායකවල, අධික අවසර ලබා දීම ආරක්ෂා අවදානම් ඇති කරයි. chmod 755 වැනි අවම අවසර යෙදීම සහ ගොනු ප්‍රවේශය පාලනය කිරීම (පරිපාලකයන් හෝ විශේෂිත පරිශීලකයන් පමණක්) සුදුසුය.

6.2 Using secure_file_priv

secure_file_priv MySQLට ගොනු කියවීමට සහ ලිවීමට හැකි නාමාවලියන් සීමා කරයි, එය දත්ත රහස්‍යතා හා අනුමත නොකළ ප්‍රවේශය වැළැක්වීමට අත්‍යවශ්‍ය වේ. මෙම සැකසුම MySQL වින්‍යාස ගොනුව (my.cnf හෝ my.ini) තුළ කළමනාකරණය කරයි. ඉඩ ඇති නාමාවලිය පැහැදිලිව නිරූපණය කිරීම ආරක්ෂා අවදානම් අඩු කිරීමට උපකාරී වේ.

7. Summary

MySQL හි CSV ගොනු අපනයනය කිරීම දත්ත මාරු කිරීම සහ බැකප් කිරීම සඳහා ඉතා ප්‍රයෝජනවත් වේ, නමුත් ලක්ෂණ සහ කාර්ය සාධනය අනුවාදය අනුව වෙනස් වේ. විශේෂයෙන් MySQL 8.x වැඩිදියුණු කාර්ය සාධන සුදුසුකම් සහ වර්ධිත ආරක්ෂාව ලබා දේ. CSV අපනයනය කිරීමේදී අක්ෂර සංකේතනය සහ නාමාවලි සීමා කළමනාකරණයද වැඩිදියුණු කර ඇත.

අනෙක් පැත්තට, MySQL 5.x අක්ෂර සංකේතන වින්‍යාසය සහ secure_file_priv කළමනාකරණය තුළ සුළු වෙනස්කම් ඇත, එබැවින් මෙම වෙනස්කම් අවබෝධ කර ගනිමින් සුදුසු ප්‍රතිචාර ලබා දීම අවශ්‍ය වේ. දත්ත අපනයනය කිරීමේදී utf8mb4 භාවිතා කිරීම සහ ආරක්ෂා සැකසුම් කාරුණිකව කළමනාකරණය කිරීම සුදුසුය.

එමෙන්ම, ගොනු අවසර නිවැරදිව සැකසීම සහ secure_file_priv භාවිතා කර ගොනු ප්‍රවේශය සීමා කිරීමෙන් දත්ත රහස්‍යතා හා අනුමත නොකළ ප්‍රවේශ අවදානම් අඩු කළ හැක. විශේෂයෙන් පොදු සේවාදායකවල වැඩ කරන විට, අවම අවසර සැකසුම් (උදාහරණයක් ලෙස chmod 755) යෙදීම සහ පරිපාලකයන් හෝ අවශ්‍ය පරිශීලකයන් පමණක් ප්‍රවේශය ලබා දීම වැදගත් වේ.

7.1 Key Practical Points

  • අනුවාද වෙනස්කම් අවබෝධ කරගන්න : MySQL 5.x සහ 8.x අතර වෙනස්කම්, විශේෂයෙන් අක්ෂර සංකේතනය සහ ගොනු ප්‍රතිදානයේ හැසිරීම, හඳුනා ගන්න.
  • සුදුසු අවසර සැකසීම : අධික අවසර වලින් වැළකින්න. ගොනු අවසර අවම අවශ්‍ය මට්ටමට සකසන්න. විශේෂයෙන් chmod 777 භාවිතා කිරීමෙන් වැළකින්න සහ chmod 755 වැනි සීමා භාවිතා කරන්න.
  • secure_file_priv භාවිතා කරන්න : MySQLට ප්‍රවේශය ඇති නාමාවලි නිවැරදිව සීමා කිරීම සහ ආරක්ෂා අවදානම් අඩු කිරීම සඳහා secure_file_priv සැකසන්න.
  • අක්ෂර සංකේතනය තහවුරු කරන්න : බහු-බයිට් අක්ෂර හෝ ඉමෝජි අඩංගු CSV ගොනු අපනයනය කරන විට utf8mb4 භාවිතා කිරීම සුදුසුය.

මෙම කරුණු මතක තබා ගතහොත්, MySQL හි CSV අපනයන කාර්ය සාධනය ආරක්ෂිතව සහ කාර්යක්ෂමව භාවිතා කළ හැක.