MySQL и казахский текст в 2012: коллации, полнотекстовый поиск и UTF-8

Казахский алфавит на кириллической основе содержит 9 специальных символов, которых нет в русском: ә, ғ, қ, ң, ө, ұ, ү, і, h. Все они входят в Unicode. Но поведение MySQL при работе с ними в 2012 году зависело от выбора collation - и большинство разработчиков выбирали неправильно.

---

Проблема коллации: utf8_general_ci против utf8_unicode_ci

utf8_general_ci - коллация по умолчанию в MySQL 5.1/5.5. Она быстрая, потому что «упрощённая»: многие символы с диакритикой приравниваются к базовым.

Конкретная проблема для казахского:

-- utf8_general_ci: ә и а считаются ОДИНАКОВЫМИ при поиске без учёта регистра
SELECT * FROM articles 
WHERE title = 'әке'  -- Найдёт и "әке" и "аке" - что неправильно
COLLATE utf8_general_ci;

-- utf8_unicode_ci: ә и а - разные символы (верно для казахского)
SELECT * FROM articles 
WHERE title = 'әке'
COLLATE utf8_unicode_ci;

Для казахского контента только utf8_unicode_ci обеспечивает корректное различение символов. utf8_general_ci приравнивает ə/ä/à/â к 'a', что ломает поиск и сортировку.

-- Проверка: как MySQL сортирует казахский алфавит
CREATE TABLE ky_sort_test (
    word VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

INSERT INTO ky_sort_test VALUES 
('Алматы'), ('Әлем'), ('Ғылым'), ('Қала'), ('Нұр-Сұлтан');

SELECT word FROM ky_sort_test ORDER BY word;
-- Ожидаемый порядок (по Unicode): Алматы, Ғылым, Қала, Нұр-Сұлтан, Әлем
-- utf8_unicode_ci даёт приемлемый результат
-- utf8_general_ci может перемешать Ә/А и Ғ/Г

---

Настройка сервера

# /etc/mysql/my.cnf - настройки на уровне сервера
[mysqld]
character-set-server  = utf8
collation-server      = utf8_unicode_ci
character_set_client  = utf8
init_connect          = 'SET NAMES utf8 COLLATE utf8_unicode_ci'

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

После изменения my.cnf - обязательно перезапустить MySQL и проверить:

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- Все значения должны быть utf8 / utf8_unicode_ci

---

Создание таблиц с явными настройками

Даже при правильных серверных настройках - явно указывать charset и collation для каждой таблицы. Это защищает от проблем при миграции на другой сервер:

CREATE TABLE products (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_kz      VARCHAR(500)  NOT NULL COMMENT 'Казахское название',
    name_ru      VARCHAR(500)  NOT NULL COMMENT 'Русское название',
    description  TEXT,
    slug         VARCHAR(200)  NOT NULL UNIQUE,
    price        DECIMAL(10,2) NOT NULL,
    created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    
    FULLTEXT KEY ft_name_kz (name_kz),
    FULLTEXT KEY ft_name_ru (name_ru)
    
) ENGINE=InnoDB 
  CHARACTER SET utf8 
  COLLATE utf8_unicode_ci;

---

Полнотекстовый поиск по казахскому тексту

MySQL FULLTEXT в 2012 году (MyISAM engine, InnoDB получил FULLTEXT только в 5.6) имел ряд ограничений для казахского:

Минимальная длина слова: по умолчанию ft_min_word_len = 4. Казахские частицы и короткие слова (3 символа и менее) не индексировались.

# my.cnf: снизить минимальную длину слова для поиска
[mysqld]
ft_min_word_len = 2
# После изменения: обязательно перестроить FULLTEXT индексы
# REPAIR TABLE products QUICK;

Стоп-слова: MySQL имел встроенный список стоп-слов на английском. Казахские и русские слова в список не входили - проблем нет. Но список стоп-слов включал короткие английские слова, которые совпадали с казахскими.

-- Отключить стандартный список стоп-слов (заменить пустым файлом)
[mysqld]
ft_stopword_file = /etc/mysql/stopwords_empty.txt
-- Создать пустой файл: touch /etc/mysql/stopwords_empty.txt

Практический поиск с учётом казахского:

function searchProducts(string $query, string $lang = 'kz'): array {
    $field = $lang === 'kz' ? 'name_kz' : 'name_ru';
    
    // Экранирование специальных символов FULLTEXT поиска
    $safeQuery = preg_replace('/[+\-><\(\)~*"@]+/', ' ', $query);
    $safeQuery = trim($safeQuery);
    
    if (mb_strlen($safeQuery, 'UTF-8') < 2) {
        return []; // Слишком короткий запрос
    }
    
    // Boolean mode: поддерживает +обязательное -исключённое *префикс
    $stmt = $pdo->prepare("
        SELECT id, name_kz, name_ru, price,
               MATCH({$field}) AGAINST (? IN BOOLEAN MODE) AS relevance
        FROM products
        WHERE MATCH({$field}) AGAINST (? IN BOOLEAN MODE)
        ORDER BY relevance DESC
        LIMIT 20
    ");
    
    $stmt->execute([$safeQuery, $safeQuery]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

---

Проблема с PHP и казахскими строками

PHP 5.x по умолчанию работал со строками побайтово. strlen('Алматы') возвращало 12 (6 символов × 2 байта в UTF-8), а не 6. Казахские строки с многобайтными символами ломались при обрезке через substr:

// НЕПРАВИЛЬНО: обрезает по байтам, может разорвать многобайтный символ
$short = substr('Нұр-Сұлтан', 0, 5); // Может получиться мусор

// ПРАВИЛЬНО: multibyte функции
$short = mb_substr('Нұр-Сұлтан', 0, 5, 'UTF-8'); // 'Нұр-С'
$len   = mb_strlen('Нұр-Сұлтан', 'UTF-8');        // 10

// Установить глобально для всего приложения
mb_internal_encoding('UTF-8');
mb_language('uni');

// После этого mb_strlen/mb_substr можно без явного указания кодировки

---

Итог

Корректная работа с казахским текстом в MySQL 2012 сводилась к трём правилам:

1. Collation: только utf8_unicode_ci - различает ә/а, ғ/г, қ/к
2. mb_ функции в PHP* для любых строковых операций
3. ft_min_word_len = 2 для индексации коротких казахских слов

Эти же принципы применимы к любому языку с нелатинским алфавитом - арабскому, кыргызскому, монгольскому. Разница только в конкретных специальных символах. Метод работы с ними - одинаков.