فصل ۱: شروع درست — نصب، کلاینت‌ها و تنظیمات فارسی

charset و collation برای فارسی: دام utf8 در برابر utf8mb4

معروف‌ترین دام MySQL برای متن فارسی

Character set تعیین می‌کند هر کاراکتر با چه بایت‌هایی ذخیره شود و Collation تعیین می‌کند مقایسه و مرتب‌سازی چطور انجام شود (بزرگ و کوچکی حروف، اعراب، ترتیب الفبا). دام تاریخی این است: در MySQL نام utf8 در واقع یعنی utf8mb3، یعنی حداکثر ۳ بایت برای هر کاراکتر. متن فارسی معمولی در ۲ بایت جا می‌شود، پس سال‌ها همه‌چیز درست کار می‌کرد — تا روزی که کاربری در نظرات ایموجی گذاشت یا نامی با یک کاراکتر نادر وارد کرد و خطای زیر آمد:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8C\xB9' for column 'comment' at row 1

utf8mb4 یونیکد کامل (تا ۴ بایت) است. در MySQL 8 پیش‌فرض سرور همین است، اما دیتابیس‌ها و جدول‌هایی که از 5.7 مهاجرت کرده‌اند هنوز ممکن است utf8mb3 یا حتی latin1 باشند. utf8mb3 منسوخ شده و در نسخه‌های آینده حذف می‌شود.

چهار سطح، و سطح پنجم که همه فراموش می‌کنند

charset در سطح سرور، دیتابیس، جدول و ستون تعریف می‌شود و هر سطح پیش‌فرض سطح پایین‌تر است. اما سطح پنجم اتصال است: کلاینت باید بگوید بایت‌هایی که می‌فرستد با چه charset‌ای است. اگر اتصال latin1 باشد و ستون utf8mb4، فارسی‌ها «؟؟؟؟» یا «Ø³Ù„ام» ذخیره می‌شوند.

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- تنظیم charset اتصال

-- کدام ستون‌ها هنوز utf8mb4 نیستند؟
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'carpet_shop'
  AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

-- تبدیل یک جدول (کل جدول بازسازی می‌شود؛ روی جدول بزرگ در ساعت کم‌بار)
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

کدام collation؟

Collationرفتارکاربرد
utf8mb4_0900_ai_ciUCA 9.0، بی‌حساس به اعراب و بزرگی حروف، NO PAD، سریعپیش‌فرض MySQL 8؛ انتخاب عمومی خوب
utf8mb4_0900_as_csحساس به اعراب و بزرگی حروفکد محصول، نام کاربری حساس
utf8mb4_persian_ciUCA قدیمی‌تر با تنظیم مخصوص الفبای فارسی، PAD SPACEوقتی ترتیب الفبایی دقیق فارسی مهم است
utf8mb4_unicode_ciUCA 4.0، قدیمیسازگاری با MariaDB و پروژه‌های قدیمی
utf8mb4_binمقایسه‌ی بایت‌به‌بایتتوکن، هش، شناسه‌های دقیق

قبل از انتخاب نهایی، روی داده‌ی واقعی خودتان مرتب‌سازی را ببینید:

SELECT name FROM (
  SELECT 'گلیم' AS name UNION ALL SELECT 'پشتی' UNION ALL SELECT 'ژاکت'
  UNION ALL SELECT 'کناره' UNION ALL SELECT 'چله' UNION ALL SELECT 'بافت'
) t ORDER BY name COLLATE utf8mb4_persian_ci;

نکته‌هایی که کمتر کسی می‌داند

  • collationهای 0900 از نوع NO PAD هستند: 'فرش' = 'فرش ' (با فاصله‌ی انتهایی) در آن‌ها نادرست است، اما در utf8mb4_unicode_ci درست. مهاجرت collation می‌تواند رفتار UNIQUE را عوض کند.
  • در collationهای UCA نیم‌فاصله کاراکتری «قابل چشم‌پوشی» است؛ بنابراین معمولاً 'می‌شود' = 'میشود' درست است. برای جست‌وجو خوب است، برای UNIQUE ممکن است غافلگیرتان کند.
  • JOIN بین دو ستون با collation متفاوت یا خطای Illegal mix of collations می‌دهد یا ایندکس را بی‌استفاده می‌کند؛ کل دیتابیس را یکدست نگه دارید.
  • ستون VARCHAR(255) در utf8mb4 تا 1020 بایت جا می‌گیرد؛ محدودیت قدیمی 767 بایت ایندکس در 5.6 دلیل آن VARCHAR(191)های معروف لاراول بود که در MySQL 8 دیگر لازم نیست.
  • برای یک ستون خاص می‌توانید collation جدا بگذارید: sku VARCHAR(30) COLLATE utf8mb4_bin؛ بقیه‌ی جدول دست نمی‌خورد.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.