Изменение кодировки таблиц в MySQL

Начиная с версии 4.1 ,СУБД MySQL умеет работать с кодировками. Для многих работа с кодировками в MySQL — это большая проблема. Проблемы, связанные с кодировками неплохо освещены в Википедии на сайте http://phpclub.ru. Тем, кто плавает в вопросах кодировки я рекомендую посетить ресурс и читать статью до полного понимания. В основном в статье освещаются проблемы, связанные с настройкой связки PHP vs MySQL. Главное, что нужно запомнить, что кодировка содержимого БД и кодировка соединения должны совпадать. Ну, мы немного отклонились от темы.

Итак, все началось с того, что вы установили MySQL, создали таблицы, наполнили их данными, а потом обнаружили, что запросы к таблицам, в которых есть поиск или сортировка по полям типа CHAR, VARCHAR, TEXT выдают непредсказуемые результаты. Если это происходит, то, скорее всего, у вас некорректно указаны кодировки для данных. Попытаюсь объяснить, в чем проблема. Надеюсь, вы понимаете, что такое кодировка. Если нет, то прогугилите вопрос и получите хотя бы первичные знания.

Итак. Проследим, как получилось так, что у вас появились проблемы с кодировкой. MySQL при установке по умолчанию указывает кодировку для БД latin1. Большинство клиентов, при подключении, настроены на кодировку latin1. Вы, пользуясь клиентами, вносите данные, просматриваете результаты и т.д. При этом везде используете кодировку latin1. Эта кодировка позволяет корректно отображать кириллицу. На этом корректность в работе с кириллице в кодировке latin1 заканчивается. Cимволы вы видите правильные, но коды в таблице символов в данной кодировке не соответствуют реальным кириллическим символам. И получается, что поиск и сортировка выдают непредсказуемые результаты, потому что работа происходит не с символами, которые вы видите, а с кодами. Может, запутано объяснил, тогда ставлю перед фактом: кодировка latin1 — это некорректная кодировка для полноценной работы с криллицей. Для корректно работы с кириллицей нужно выбирать кодировки, которые поддерживают кириллицу. Для того, что бы узнать полный список этих кодировок RTFM по MySQL. Я же остановлюсь на двух распространённых из них: utf-8 и cp1251. Начну с cp1251, потому что это родная кодировка для ОС Windows.

Итак, перед нами стоит задача: конвертировать данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. Идем в документацию по языку:

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; (1)

Не спешим выполнять предложенное решение, а читаем дальше:

Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like cp1251). In this case, you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB; (2)
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET cp1251; (3)

The reason this works is that there is no conversion when you convert to or from BLOB columns.

Простое конвертирование данных (1) нам не подходит, потому что у нас некорректные коды для символов, а так как конвертирование происходит согласно таблице кодов, то мы получим некорректный результат. Выход в данной ситуации — это избавиться от привязки символов к кодировке. Это делается путем преобразования данных и символьного типа в двоичные данные (2). Представьте, что кодировка — это маска, которая накладывается на двоичные данные. Т.е. сам порядок байтов не изменяется, а изменяется только маска, по которой выбираются коды для визуального формирования символов. Итак, запросом (2) вы избавились от маски, а запросом (3) вы применили новую маску. Повторюсь еще раз (грубо говоря): физически мы не изменили ни одного байта данных, мы изменили правило формирования символов! В итоге вы получаете следующее: сами данные как были корректными для кодировки cp1251, так они и остались, но теперь мы правильно указали кодировку cp1251.

После таких манипуляций жизнь вашей БД и выборок должна наладиться, потому что теперь все сходится и все на своих местах. Теперь, если вы захотите изменить кодировку таблицы и полей, то можете смело пользоваться запросом (1). Кстати, я сейчас думаю переходить с cp1251 на utf-8, но пока что не могу чётко озвучить, зачем мне это надо: кое-какие предпосылки есть, но ясного понимания пока что нет.

Замечания:

1. Если вы конвертируете данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. Нельзя просто взять и изменить кодировку для столбца, т.е. не выполнять запрос (2), а сразу выполнить запрос (3). Потому что выполнив сразу запрос (3) мы физически не перестроим маску, а просто изменим текущее значение маски. Если у вас нет проблем с несоответствием кодировок, и вам нужно просто поменять кодировку таблицы, то вам нужно всего-навсего выполнить запрос (1).
2. Если вы конвертируете данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. При этом вы изменяете кодировку у поля, у которого есть индексы, то перестройте индексы (удалите и создайте заново).
3. Если вы решите переходить на кодировку utf-8 — проверьте, что бы визуальные клиенты поддерживали unicode. Например, так горячо мною любимый SQLyog 5.2 курит бамбук при отображении содержимого таблиц, хранящих данные в utf-8.
4. MySQL начал поддерживать unicode только с версии 4.1.x, т.е. до этого поддержки юникода не было. Это значит, если вы захотите перенсти таблицы с юникодом на версию младше 4.1.x, то у вас ничего не получится. Выходов два: или конвертировать данные из юникода во что-то менее многобайтовое, например, cp1251; или обновлять СУБД до версии не младше 4.1.x. Обновление СУБД предпочитетльней, потому что у MySQL уже 3ка по-моему не поддерживается и 4ка тоже скоро перестанет поддерживаться, при этом активно 5ка в двух ветках разрабатывается. Да и весь мир в юникоде активно в сети работает.