Faragiri

همهٔ زبان‌ها

آموزش MySQL موتورهای پایگاه داده

کولیشن پیش‌فرض بی‌توجه به بزرگی و کوچکی حروف مقایسه می‌کند. همان کوئری روی موتوری با پیش‌فرض حساس، سطرهای دیگری برمی‌گرداند.

۴بخش
۳دقیقه خواندن
۴عمق

روی همین نصب، با MySQL 8.4.0 در تاریخ ۲۰۲۶-۰۹-۰۳، مقایسه دو نام که فقط در بزرگی حروف فرق دارند نتیجه درست می‌دهد. کولیشن پیش‌فرض این سرور utf8mb3_general_ci است و بی‌توجه به بزرگی و کوچکی مقایسه می‌کند.

این یک انتخاب طراحی است و نه یک باگ، و پیامدش این است که همان کوئری، منتقل‌شده به موتوری که پیش‌فرض حساس دارد، سطرهای متفاوتی برمی‌گرداند. اگر کدی روی این رفتار تکیه کرده باشد، مهاجرت موتور یک تغییر منطقی است و نه یک تغییر زیرساخت.

و کولیشن در چهار سطح تنظیم می‌شود: سرور، پایگاه داده، جدول، و ستون. یعنی یک ستون می‌تواند رفتار متفاوتی از بقیه جدول داشته باشد و هیچ‌جا هم به چشم نیاید.

مقایسه سست، که هنوز هست

مقایسه یک عدد با رشته‌ای که با رقم شروع می‌شود، رشته را به عدد تبدیل می‌کند و بقیه‌اش را دور می‌ریزد. روی همین نصب، مقایسه عدد یک با رشته‌ای که با یک شروع می‌شود و حرف دارد، درست جواب می‌دهد.

دفاع در برابر تبدیل سست

حالت سخت‌گیر کجا جلوی خطا را می‌گیرد و کجا نه

مقایسه عدد با رشته‌ای که با رقم آغاز می‌شود بخش غیرعددی را دور می‌ریزد و در حالت سخت‌گیر هم فعال می‌ماند.

  1. حالت سخت‌گیر موتورتبدیل نادرست رشته به عدد هنگام نوشتن دادهتبدیل خودکار در شرط مقایسه هنوز عبور می‌کند
  2. تبدیل ضمنی پیش‌فرضهیچ کنترلی روی بخش متنی پس از رقم اول نداردرشته حرف‌دار با پیشوند رقم با شناسه عددی برابر می‌شود
  3. هم‌نوع کردن صریحتطبیق ناخواسته و بازگرداندن سطر اشتباهتنها مقایسه هم‌نوع و ایمن انجام می‌پذیرد
مقایسه شناسه عددی با ورودی متنی

اتکا به تبدیل خودکار می‌تواند با یک ورودی متنی سطری را برگرداند که نباید برگردد؛ پاسخ، هم‌نوع کردن است.

در حالت سخت‌گیر این تبدیل هنگام نوشتن خطا می‌دهد، ولی در مقایسه هنوز اتفاق می‌افتد. پیامد امنیتی‌اش جدی است: شرطی که یک شناسه عددی را با ورودی متنی مقایسه می‌کند می‌تواند سطری را برگرداند که نباید. جوابش هم‌نوع کردن است و نه اتکا به تبدیل خودکار.

موتور ذخیره‌سازی، و چیزی که باید باشد

موتور پیش‌فرض تراکنش و کلید خارجی و قفل سطری دارد. موتور قدیمی‌تری هم وجود دارد که هیچ‌کدام را ندارد و روی جدول قفل می‌گیرد، و در پایگاه داده‌های قدیمی هنوز دیده می‌شود.

مقایسه دو موتور ذخیره‌سازی

موتور قدیمی جدول‌محور در برابر پیش‌فرض امروزی

جدولی که هنوز روی موتور قدیمی‌تر مانده باشد، بی‌سروصدا هیچ‌کدام از قیدهای تراکنش و کلید خارجی را اجرا نمی‌کند.

موتور قدیمی‌ترهنوز در پایگاه داده‌های قدیمی دیده می‌شود
موتور پیش‌فرض امروزیچیزی که هر جدول باید رویش باشد
تراکنشپشتیبانی نداردپشتیبانی کامل
کلید خارجیقیدهای ارجاع وجود ندارنداعمال واقعی قیدها
سطح قفلقفل روی کل جدولقفل سطری دقیق
بررسی و اصلاحکشف با کوئری سیستمیتبدیل با یک دستور ساده

تا وقتی موتور جدول قدیمی را تغییر ندهید، قیدهایی که گمان می‌کردید دارید در عمل وجود ندارند.

بررسی‌اش یک کوئری روی جدول‌های سیستمی است و اگر جدولی روی آن موتور قدیمی مانده، تغییرش یک دستور است. تا وقتی نکنید، آن جدول نه تراکنش دارد و نه کلید خارجی، و قید‌هایی که فکر می‌کردید دارید وجود ندارند: کلید اصلی، کلید خارجی، و قولی که می‌دهند.

جایی که کار گیر می‌کند، کولیشن قدیمی برای فارسی است

کولیشن پیش‌فرض بسیاری از نصب‌های قدیمی، مجموعه نویسه سه‌بایتی است و نه چهاربایتی. تفاوت عملی‌اش این است که نویسه‌های چهاربایتی، یعنی ایموجی و بخشی از نویسه‌های کمتررایج، در آن ذخیره نمی‌شوند و یا خطا می‌دهند یا بی‌صدا بریده می‌شوند. برای متن فارسی معمولی مسئله‌ای نیست و برای متنی که کاربر می‌نویسد هست، چون کاربر ایموجی می‌فرستد. دومین مسئله ترتیب است: کولیشن عمومی قدیمی ترتیب الفبای فارسی را نمی‌داند و مرتب‌سازی نام‌ها را جابه‌جا می‌دهد. جواب هر دو یکی است: ستون‌های متنی را روی مجموعه نویسه چهاربایتی و یک کولیشن یونیکد امروزی ببرید، و این تغییر روی جدول بزرگ همان ملاحظه‌های تغییر ساختاری که همین حالا داده دارد را دارد.

گره کولیشن فارسی

آنچه در ظاهر متن دیده می‌شود و آنچه زیر سطح می‌شکند

روی نصب‌های قدیمی متن فارسی معمولی ظاهرا درست ذخیره می‌شود، اما زیر سطح ایموجی و ترتیب الفبای فارسی هر دو می‌شکنند.

  • متن فارسی بدون ایموجیدر آزمایش‌های پایه خطایی دیده نمی‌شود
  • تنظیم عمومی قدیمیپیش‌فرض نصب‌های قدیمی بی‌صدا کار می‌کند
رفتار ظاهری در متن فارسی ساده
  • مجموعه نویسه سه‌بایتینویسه چهاربایتی و ایموجی کاربر خطا می‌دهند یا بریده می‌شوند
  • ترتیب الفبای فارسیکولیشن عمومی قدیمی ترتیب نام‌ها را جابه‌جا می‌کند
  • هزینه تغییر جدول بزرگانتقال به مجموعه چهاربایتی و یونیکد روی جدول پر از داده نیاز به دقت دارد

راه‌حل هر دو مشکل یکی است: بردن ستون‌های متنی روی مجموعه نویسه چهاربایتی و یک کولیشن یونیکد امروزی.

ابزار، و سالی که این حرف درست است

در سال ۲۰۲۶، MySQL 8.4 خط پایدار است و جای سری ۵ را گرفته که سال‌هاست پشتیبانی ندارد؛ متن‌باز و رایگان است. برای دیدن کولیشن هر ستون، کوئری روی جدول‌های سیستمی فهرست دقیق را می‌دهد و همان است که جای فرض کردن پیش‌فرض سرور را می‌گیرد. و برای پشتیبان‌گیری، ابزار خط فرمان همراه خود موتور می‌آید و باید یک بار بازیابی‌اش آزموده شود، وگرنه پشتیبان نیست.

ابزار و عمل در ۲۰۲۶

چهار گام نگهداری از MySQL 8.4 تا آزمون پشتیبان

در سال ۲۰۲۶ خط پایدار MySQL 8.4 جای سری ۵ پشتیبانی‌نشده را گرفته و ممیزی کولیشن و آزمون بازیابی ضروری است.

  1. ۰۱استقرار روی خط پایدار MySQL 8.4انتخاب متنباز و رایگان به‌جای سری ۵ که سال‌هاست پشتیبانی ندارد
  2. ۰۲بررسی کولیشن هر ستون در جدول سیستمیدیدن فهرست دقیق به‌جای فرض کردن پیش‌فرض سرور
  3. ۰۳پشتیبان‌گیری با ابزار خط فرمان رسمیاستفاده از ابزار همراه خود موتور
  4. ۰۴آزمون عملی بازیابی پشتیبانپشتیبانی که یک بار بازیابی‌اش آزموده نشده باشد، پشتیبان نیست

برای دیدن رفتار موتوری با پیش‌فرض‌های سخت‌گیرتر، مقایسه با رقیب سخت‌گیرتر تصویر را کامل می‌کند.

پرسش بعدی این است که موتوری که پیش‌فرض‌های سخت‌گیرتری دارد چه تفاوت‌هایی می‌سازد، که سخت‌گیرتر، غنی‌تر، و رفته‌رفته انتخاب اول است. و زبان کوئری که روی هر دو یکی است در زبانی که با پایگاه داده حرف می‌زند است.

بالای این