Работа с кодировками в MySQL 4.1.11 и выше

Пускай это не самый "правильный" способ установки MySQL-сервера, зато быстрый и рабочий.

Начало работы

Итак, sockstat показала, что сервер работает, а установка говорит о том, что сервер абсолютно девственный. Чем это грозит? Кодировки по умолчанию выставлены англоязычные, а значит, будут проблемы при использовании кирилицы. Но как это распознать? Проверяем:

Первым делом используем тестовую базу, которая уже есть на сервере, затем создаём в ней таблицу и вставляем в неё три слова на русском, про кодировки мы пока ничего не знаем и знать не хотим ))).

Пока всё хорошо и радужно, никаких ошибок нет, пробуем сделать выборку:

Как видно, запросы работают абсолютно корректно, так где же грабли. Оказывается мы на них уже стоим:

Запрос на выборку с обратной сортировкой привёл к тому, что записи просто вывелись в обратном порядке, но не по алфавиту. До удара граблей остаются считанные секунды, но пока растянем удовольствие :) Сперва ответим на вопрос - почему поля не сортируются по алфавиту? У MySQL имеется мощный и богатый механизм для работы с интернациональными наборами символов, но.. но откуда MySQL узнает, что наши символы - есть русский алфавит, мы же качали английскую версию? Ничего не остаётся, как идти ковырять мануал на предмет кодировок.

После того, как загрузился 16-метровый мануал, можно не полениться и прочитать первые пару-тройку страниц с оглавлением )), а можно просто сделать поиск на предмет charset или character set. Не суть важно, но через некоторое время можно найти раздел 9.1.2. Character Sets and Collations in MySQL, в котором написано много и интересно, а, главное, содержательно про то, каким образом можно и нужно работать с кодировками.

Расставляя точки над и, Character Set - транслируется как "кодировка", а Collation - сравнение. В чём разница? Сравнение - это правила сравнения букв кодировки. Сравнения работают только в рамках кодировки, и нельзя сравнивать данные в латинице по правилам кирилицы. Поясню на примере: мы, как увидим позже, внесли данные в таблицу на латинице, а сортировать нужно на кирилице, для чего можно использовать ключевое слово collate :

MySQL отказывается это делать. но почему? Потому, что latin1 не поддерживает сравнение в кирилице, а доступные "сравнения" можно увидеть так:

Ни о какой кирилице не может идти и речи. Куда копать. В создание таблицы!

Ага! По-умолчанию при создании таблицы была взята кодировка latin1, значит, если мы изменим таблицу и укажем ей, что надо использовать кирилистическую кодировку, то всё заработает. В мануале написан пример про изменение кодировки таблицы, используем его:

Ок! Проверяем, что получилось.

Хм.. опять та же ошибка, но откуда ей взяться.

Ого, структура таблицы резко изменилась, теперь у неё задана одна кодировка, а у поля совсем другая.. :(( Порыв ещё мануал, можно изменить и кодировку столбца:

Ну вот. Злой кодировки latin1 нет и в помине, можно проверять наш роддом )))

И вот тот страшный удар граблями, который так долго оттягивался! Внимательный читатель мог заметить, что когда была сделана попытка принудительно сменить кодировку столбца, содержащего данные в latin1, то на каждую запись, содержащую русские буквы, у MySQL был варнинг! Это был крик о том, что сервер не знает, каким образом можно перевести данные из latin1 в cp1251, ну и лучшего способа, чем заменить символы не latin1 вопросиками, он не нашёл :))). Роддом безвозвратно потерян потому, что теперь вместо кирилицы в базе содержатся вопросики..

Вопросиков можно было избежать

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

Именно эти переменные отвечают за дефолтные значения кодировок.

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_sets_dir установлена неверно, то работа с кодировками будет под угрозой. Не пытайтесь менять её значение, если вы неуверены в своих силах. Если вы системный администратор, то перед установкой лучше ознакомиться с мануалом.

Наиболее значимые для простых пользователей следующие переменные: character_set_client, character_set_results, character_set_connection. Поскольку именно они отвечают за внесение, извлечение информации и создание таблиц/баз соответственно. Какими они могут быть?

Любую из этих кодировок можно пользовать на свой вкус. Обычно русскоязычные пользователи предпочитают cp1251 или utf8, но по сути, неважно, в какой кодировке хранятся данные, важно, чтобы она была изначально правильно указана и данные были корректно внесены.

Настройка кодировок

Мануал предлагает нам три варианта задания кодировок:

ВНИМАНИЕ. Первые два варианта работают только в рамках текущего соединения. Это значит, что при следующем подключении все настройки вернутся в начальное состояние! Чтобы не выставлять кодировку каждый раз, нужно воспользоваться третьим вариантом.

Вариант 1 - Через names

Ну, тут всё ясно, три самые нужные кодировки в одном )))

Вариант 2 - Через непосредственно переменные character_set_*

Более детальная настройка, чем names.

Вариант 3 - Через настройки самого сервера

Тут можно пойти двумя путями - либо через конфиг файл:

Ещё можно при конфигурировании задать кодировку по умолчанию

Но лучше, когда кодировка настраивается прямо в соединении.

Что делать, если данные внесены в неправильной кодировке

Если база/таблица/данные были созданы/внесены в кодировке отличной от нужной, то необходимо сделать следующее:

Создать бэкап базы данных
Создать текстовый дамп базы в SQL-запросах (mysqldump или PhpMyAdmin)
С помощью текстового редактора исправить вхождения неверной кодировки на нужную (а лучше попросту удалить всю информацию о кодировках и сравнениях)
Удалить базу/таблицу
Выставить нужную кодирвку на клиента/соединение
Импортировать данные исправленного SQL-дампа

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

Правильный вариант работы с MySQL

Таким образом, клиент работает в KOI8-R, но данные хранятся в cp1251, MySQL знает об этом и делает перекодировку на лету.

Ну и на посошок:

Выбирать данные можно в любой кодировке, так же, как и вносить, главное - правильно сообщить об этом MySQL.

📎📎📎📎📎📎📎📎📎📎