Mysql utf 8 windows 1251

Время на прочтение
3 мин

Количество просмотров 23K

В связи с тем, что довольно много людей обращается с просьбой помочь исправить проблему с кодировками MySQL, решил написать статью с описанием, как «лечить» наиболее часто встречающиеся случаи.

В статье описывается не то, как первоначально правильно настроить кодировки MySQL (об этом уже довольно много написано), а о случаях, когда есть довольно большие таблицы с неправильными кодировками и нужно всё исправить.

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

Небольшое отступление. Sypex Viewer

В какой-то момент надоело отправлять людей в громоздкий phpMyAdmin, и была написана крошечная утилитка Sypex Viewer. Она представляет собой один PHP-файл, использует современные Web 2.0 технологии AJAX, JSON и другие. Основные задачи, которые ставилась при создании — минимальный вес, и максимальное удобство и скорость работы. В дальнейшем в примерах будут скриншоты из неё, но все те же действия можно сделать и в phpMyAdmin.

Данные в cp1251 таблицы в latin1

Наверное, самая популярная проблема. Когда данные в кодировке cp1251 (Windows-1251), а у таблиц указана кодировка по умолчанию latin1. Такие ситуации возникают в следующих случаях:

• при неграмотном обновлении с версии MySQL меньше 4.1 на более новые;
• очень часто возникает в «буржуйских» скриптах, которых вполне устраивает кодировка по умолчанию, и они «забывают», что неплохо бы указывать кодировку, как таблиц, так и соединения;
• также бывают случаи, когда переезжают с одного сервера (у которого установлена дефолтная кодировка cp1251, в частности, так сделано в Денвере) на другой (у которого стоит стандартная кодировка latin1).

В результате на сайте вроде как всё нормально, но если посмотреть в Sypex Viewer, то русские символы будут выглядеть как «кракозябры» (как их обычно называют пользователи).

В статье я рассмотрю один из вариантов преобразование кодировок с помощью бесплатного php-скрипта Sypex Dumper, в качестве готового решения.

1. На вкладке «Экспорт» выбираем нужные таблицы.
2. Кодировка должна быть auto (остальные параметры неважны, можно комментарий добавить, например, «Дамп перед исправлением кодировки»).
3. Нажимаем «Выполнить». Теперь у нас есть бэкап (его в любом случае желательно делать при любых преобразованиях базы данных).
4. Переходим на вкладку «Импорт»
5. Выбираем только что сделанный файл бэкапа.
6. Выбираем кодировку cp1251 и помечаем опцию «Коррекция кодировки».
7. Нажимаем «Выполнить».
8. Вот и всё заходим в Sypex Viewer, чтобы убедиться, что русские символы выводятся корректно.

Данные и таблицы в utf8, но кодировка соединения latin1

Теперь рассмотрим более запущенный случай. Набирающая популярность в последнее время проблема, в связи с повальным увлечением UTF-8. Создатели софта стали переводить свои детища на UTF-8, но и тут не всё так гладко, как хотелось бы.

Возникает проблема в основном в случае, когда у таблиц указана кодировка UTF-8, данные в UTF-8, но кодировка соединения установлена по умолчанию latin1 (типичный пример, vBulletin 4, хоть там и есть в конфигах настройка кодировки соединения, но она закомментирована по умолчанию).

В результате в MySQL присылаются данные в UTF-8, но поскольку указана кодировка соединения latin1, то MySQL пытается преобразовать данные из latin1 в UTF-8. В итоге русские символы выглядят так:

Ситуация более запущенная, но исправляется проблема почти также, как в первом случае, только в пункте 2 нужно выбрать кодировку latin1, а в пункте 6 нужно выбрать utf8 кодировку.

Изменение кодировки

Также часто встречающаяся проблема преобразования кодировки из cp1251 в UTF-8. До выполнения этого шага обязательно убедитесь, что русские символы у вас правильно показываются в Sypex Viewer или phpMyAdmin, если это не так, то предварительно исправьте кодировку.
Итак, опять заходим в Sypex Dumper.

1. Во вкладке «Экспорт» выбираем нужные таблицы.
2. Устанавливаем кодировку, в которую хотите преобразовать таблицы, в данном случае utf8.
3. Нажимаем «Выполнить».
4. После чего заходим в «Импорт» и выбираем нужный файл.
5. Выставляем кодировку utf8 и опцию «Коррекция кодировки».
6. Нажимаем «Выполнить».
7. Вот и всё таблицы в UTF-8. Не забываем, что нужно еще установить кодировку соединения, сконвертировать ваши скрипты и шаблоны в UTF-8, выставить правильную кодировку в заголовках.

Кодировка соединения

Не забываем, что после исправлений кодировки, нужно убедиться, что ваши скрипты используют правильную кодировку соединения (в принципе, это будет сразу видно, они будут неправильно показывать русские символы без нужной кодировки соединения). У некоторых она выставляется в настройках, в некоторых придется добавить самостоятельно.

Для чего достаточно пройтись поиском по файлам, и найти где вызывается функция mysql_connect (или mysqli_connect). После этой строки нужно добавить строку которая укажет кодировку соединения.

mysql_query("SET NAMES 'cp1251'");

Где вместо cp1251, указать нужную кодировку соединения.

Не забывайте перед преобразованиями кодировок делать бэкап, тут как с презервативами, лучше пусть он будет и не понадобится, чем когда понадобится — его не будет.

P.S. Спасибо Шортикам за веселый контент для примеров.

Попросили тут перенести сайт на другой хостинг, а он, как оказалось, до сих пор жил в кодировке cp1251. Вроде ничего страшного — работает, но уж больно по колхозному это в наше время и давно пора перейти на UTF-8.

Перед любыми действиями с базой данных ОБЯЗАТЕЛЬНО СОЗДАЕМ РЕЗЕРВНУЮ КОПИЮ!

Самый быстрый и простой способ — пересохранить копию базу данных в любом текстовом редакторе (sublime text, notepad++) в нужной кодировке и импортировать на место старой. Можно проделать данную операцию используя SQL-запросы в phpMyAdmin.

ALTER DATABASE имя_вашей_базы_данных charset=utf8;

Данный запрос конвертирует базу в указанную кодировку. Кроме этого потребуется конвертировать и сами таблицы. Запрос для конвертации таблицы базы данных:

ALTER TABLE `db_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

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

SELECT CONCAT(  'ALTER TABLE `', t.`TABLE_SCHEMA` ,  '`.`', t.`TABLE_NAME` ,  '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) AS sqlcode
FROM  `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` =  'My_DB_for_convert'
ORDER BY 1
LIMIT 0 , 90

Остается только скопировать результаты и вставить их в форму нового SQL запроса.

Часто случается, что необходимо выполнить конвертацию существующей базы из одной кодировки в другую. Нам чаще всего приходится конвертировать из Win-1251 в UTF-8. Это можно произвести разными способами, но самый быстрый и простой — использование SQL-запроса.

Запрос конвертирования кодировки:

ALTER TABLE `db_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

При помощи этого запроса, можно конвертировать таблицу базы данных в любую, доступную в MySQL кодировку. Но что делать, если таблиц 100, 200 или больше, и все таблицы необходимо перекодировать в UTF8 из Win-1251? Для решения этой проблемы, можно отправить в MySQL запрос, который сгенерирует необходимые SQL-запросы для всех таблиц БД.
При использовании, например, PHPMyAdmin останется только скопировать результаты и запустить их как SQL-запрос:

SELECT CONCAT(  'ALTER TABLE `', t.`TABLE_SCHEMA` ,  '`.`', t.`TABLE_NAME` ,  '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) AS sqlcode
FROM  `information_schema`.`TABLES` t
WHERE 1 
AND t.`TABLE_SCHEMA` =  'My_DB_for_convert'
ORDER BY 1 
LIMIT 0 , 90

Этот запрос будет работать в MySQL версии 5 и выше. My_DB_for_convert — следует заменить на имя БД, таблицы в которой необходимо конвертировать в UTF-8.

utf8_general_ci или utf8_unicode_ci

utf8_general_ci и utf8_unicode_ci отличаются только скоростью работы и порядком сортировки. Поскольку utf8_general_ci работает быстрее — это и есть предпочтительный выбор. В случаее необходимости, способом описанным выше, можно будет изменить подкодировку UTF8 c utf8_general_ci на utf8_unicode_ci.