Кодировки в MySQL, или почему не работает поиск

Тема, которую я сегодня хочу рассмотреть это, как понятно из топика, вопрос работы с кодировками в MySQL.

Сразу оговорюсь, я не стану особо глубоко теоретизировать на тему (все вполне понятно описано в мануале), а приведу лишь вполне конкретные рекомендации, связанные с реальной ситуацией.

Итак, ситуация: На сайте данные из базы данных отображаются нормально, а поиск выдает "фантастические" результаты. К примеру недавно на одном из сайтов, которые пришлось дорабатывать, по запросу "москва" выдалась кроме всего прочего книжка: "Программирование на С++", при том что поиск работал по наименованию. Вот и пришлось ломать голову как синтаксически связано слово "москва" и "программирование на С++".

Не-то MySQL оказался настолько умным и понял что в москве много хороших С++ программистов и им нужно обязательно предложить эту книгу, то ли "почувствовал", что у книги издательство московское, но факт остается фактом, это был результат поиска, который не определялся вхождением искомой фразы в значение поля, по которому он производился.

А теперь собственно, к тому что же на самом деле произошло. Конечно никакого семантического прогнозирования MySQL не делает (не для этого он предназначен :) проблема оказалась именно в кодировках.

Немного теории: Скажем так, MySQL может работать на разных этапах обработки данных, в разных кодировках, при этом преобразования он выполняет "на лету". В том числе эти "этапы" и соответствующие им кодировки характеризуются следующими переменными MySQL:

  • character_set_client - кодировка в которой данные будут поступать от клиента
  • character_set_connection - кодировка по умолчанию для всего, что в рамках соединения не имеет кодировки
  • character_set_database - кодировка по умолчанию для баз
  • character_set_filesystem - кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT ... INTO OUTFILE, и т.д.)
  • character_set_results - кодировка, в которой будет выбран результат
  • character_set_server - кодировка, в которой работает сервер
  • character_set_system - кодировка, в которой задаются идентификаторы MySQL, всегда UTF8
  • character_sets_dir - папка с кодировками
Наиболее значимые для нас следующие переменные: character_set_client, character_set_results, character_set_connection именно они отвечают за внесение , извлечение информации и создание таблиц / баз соответственно. Все эти переменные определяют дефолтные значения, т.е. если в дампе не указаны кодировки, то используются эти. Есть 3 способа изменить дефолтные значения:
  1. Используя SET names
  2. Непосредственно меняя каждую из этих переменных через SET character_set_*
  3. Через настройки самого сервера

Следует обратить внимание что первые два варианта работают только в рамках текущего соединения. Это значит, что при следующем подключении все настройки вернуться в начальное состояние.

Просмотреть возможные значения этих переменных можно так SHOW CHARACTER SET

Для русскоязычных сайтов наиболее приемлимы варианты: cp1251 и utf8

Для реализации третьего варианта установок нужно: а) править файл my.cnf (конфиг. MySQL):

[client]
# Для местного клиента
default-character-set=cp1251
....

[mysqld]
# Для всего сервера
default-character-set=cp1251
....

б) через командную строку: shell> mysqld --character-set-server=cp1251 в) задать при конфигурировании из командной строки shell> ./configure --with-charset=latin1 Теперь о том что же на самом деле случилось и как это "лечить".

А произошло я полагаю следующее: по дефолту на сервере выставлена кодировка latin1, данные когда заливали дамп фактически были в windows-1251, MySQL заливая данные перекодировал их на лету, в результате получилась "каша". Как это лечить:

  1. Создать бекап базы данных.
  2. Создать текстовый дамп базы данных в SQL запросах с помощью mysqldump, PHPMyAdmin, Sypex Dumper или другими средствами
  3. С помощью текстового редактора удалить всю информацию о кодировке в запросах на создание таблиц, и кодировку полей.
  4. При необходимости, если кириллические символы в значениях полей в дампе нечитабильны (это мой вариант), раскодировать файл с помощью сторонних программ, т.е. привести в читабильный вид кириллические символы.
  5. Удалить таблицу / базу
  6. Выставить нужную кодировку character_set_client,character_set_connection
  7. Импортировать исправленный SQL-дамп базы.
Вобщем после вышеприведенных манипуляций, все стало на место и поиск стал выдавать правильные результаты. P.S. А книжка по С++, кстати хорошая была :)))