مسئله‌ی ی و ک عربی، ارقام فارسی و فایل my.cnf

«علی» که پیدا نمی‌شود

کاربر در جست‌وجو «کاشانی» تایپ می‌کند و نتیجه‌ای نمی‌آید، در حالی که رکورد وجود دارد. علت: داده با کیبورد قدیمی ویندوز، از فایل اکسل یا از یک نرم‌افزار حسابداری قدیمی وارد شده و به‌جای «ی» فارسی (U+06CC) و «ک» فارسی (U+06A9)، «ي» عربی (U+064A) و «ك» عربی (U+0643) دارد. این دو از نظر ظاهری تقریباً یکسان‌اند اما کاراکترهای متفاوتی هستند و هیچ collationی را نمی‌توان با اطمینان کامل مسئول یکسان دیدنشان دانست.

SELECT HEX('ی'), HEX('ي'), HEX('ک'), HEX('ك');
-- DB8C       D98A       DAA9       D983

-- پیدا کردن رکوردهای آلوده (مقایسه‌ی دودویی تا collation دخالت نکند)
SELECT id, full_name
FROM customers
WHERE full_name COLLATE utf8mb4_bin LIKE '%ي%'
   OR full_name COLLATE utf8mb4_bin LIKE '%ك%';

درمان: یکسان‌سازی در ورودی، نه در جست‌وجو

راه درست این است که داده همیشه با حروف فارسی ذخیره شود. یک بار داده‌ی موجود را اصلاح کنید و بعد جلوی ورود دوباره را بگیرید؛ بهترین جا لایه‌ی برنامه است (مثلاً متد clean در فرم جنگو)، و به‌عنوان تور ایمنی یک Trigger در خود دیتابیس:

UPDATE customers
SET full_name = REPLACE(REPLACE(full_name, 'ي', 'ی'), 'ك', 'ک')
WHERE full_name COLLATE utf8mb4_bin LIKE '%ي%'
   OR full_name COLLATE utf8mb4_bin LIKE '%ك%';

CREATE TRIGGER customers_fa_bi BEFORE INSERT ON customers
FOR EACH ROW
  SET NEW.full_name = REPLACE(REPLACE(NEW.full_name, 'ي', 'ی'), 'ك', 'ک');

ارقام هم همین داستان را دارند: «۰۹۱۲» فارسی، «٠٩١٢» عربی و «0912» لاتین سه رشته‌ی متفاوت‌اند. شماره‌ی موبایل، کد ملی و کد پستی را همیشه با ارقام لاتین ذخیره کنید و نمایش فارسی را به لایه‌ی نمایش بسپارید.

فایل تنظیمات: my.cnf و my.ini

[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_0900_ai_ci
default-time-zone    = '+03:30'
sql_mode             = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
max_allowed_packet   = 64M

[client]
default-character-set = utf8mb4

[mysql]
safe-updates
prompt = "\u@\h [\d]> "

بخش [mysqld] برای سرور، [client] برای همه‌ی ابزارهای کلاینت (mysql، mysqldump) و [mysql] فقط برای کلاینت تعاملی است. پس از تغییر، سرویس را ری‌استارت کنید.

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

• mysqld --verbose --help | grep -A1 "Default options" ترتیب دقیق فایل‌هایی را که سرور می‌خواند نشان می‌دهد؛ اگر تنظیمی اعمال نمی‌شود، احتمالاً فایل دیگری بعد از فایل شما آن را بازنویسی کرده است.
• SET PERSIST max_connections = 300; تنظیم را بدون ری‌استارت اعمال و در mysqld-auto.cnf داخل پوشه‌ی داده ذخیره می‌کند؛ این فایل بعد از my.cnf خوانده می‌شود و بر آن غلبه دارد — منشأ رایج «تنظیم را در my.cnf عوض کردم ولی اثر ندارد».
• تابع REPLACE() در MySQL همیشه حساس به حروف و دودویی تطبیق می‌دهد، مستقل از collation ستون؛ برای همین برای اصلاح ی/ک قابل اعتماد است.
• مقدار ثابت +03:30 برای ایران از زمان حذف ساعت تابستانی (۱۴۰۱) کافی است؛ برای داده‌ی تاریخی قبل از آن، نام منطقه‌ی Asia/Tehran و جدول‌های timezone لازم است.
• در ویندوز، Notepad ممکن است my.ini را با BOM ذخیره کند و سرور خط اول را نفهمد؛ با VS Code و کدگذاری UTF-8 بدون BOM ذخیره کنید.