Как исправить кодировку sql

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

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

В связи с тем, что довольно много людей обращается с просьбой помочь исправить проблему с кодировками 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. Спасибо Шортикам за веселый контент для примеров.

Есть SQL запрос, который выполняется из скрипта python.

Проблема в том, что поле, которого нет в базе, выдает символы не utf8. Как это можно адекватно перекодировать в кириллицу? В самом питоновском скрипте или на стороне базы именно для этой переменной? Когда запрос выполняется на чистом sql, в базе все норм, отображается с кириллицей.

На питоне идет подключение к mssql через pymssql.

Вот запрос:

SELECT  
CASE WHEN c_object_mane = '1' or c_object_mane = '2'
THEN 'Санкт-Петербург' 
WHEN c_object_mane = '3' 
THEN 'Москва'
END AS c_object_adress, an.[c_object_mane], an.[c_house_name], an.[c_type_room], an.[c_room], an.[c_status_flat], an.[lead_bujet], an.[c_an], an.[ddu_date]
FROM mssql_db as an where an.ddu_date >= '2021-01-01' 

При выводе по логике должны нормально писать города Москва, Санкт-Петербург, но выходит только это:

Òþìåíü
Òþìåíü
Òþìåíü
Ìîñêâà
Òþìåíü
Òþìåíü
Òþìåíü
Òþìåíü
Òþìåíü

Mysql поддерживает много кодировок и это нередко является головной болью для программистов. Самая частая проблема — кракозяблы вместо русского текста. Это происходит из за того, что текст либо лежит на сервере, либо отдается клиенту в неверной кодировке. Последнее(а иногда и первое) решается проще всего. Устанавливаем кодировку соединения (в utf8 в примере) сразу после установления соединения

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

Этот запрос обязательно проверять в самом скрипте, а не в phpmyadmin, где могут быть установлены другие параметры

1. character_set_client — кодировка, в которой данные будут поступать от клиента
2. character_set_connection — по умолчанию для всего, что в рамках соединения не имеет кодировки
3. character_set_database — кодировка по умолчанию для баз
4. character_set_filesystem — кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT … INTO OUTFILE, и т.д.)
5. character_set_results — кодировка, в которой будет выбран результат
6. character_set_server — кодировка, в которой работает сервер
7. character_set_system — идентификаторы MySQL, всегда UTF8
8. character_sets_dir — папка с кодировками

По умолчанию после установки mysql сервер, который устанавливается ленивым хостеромадмином имеет кодировку latin1. Соответственно указанные выше глобальные переменные будут в latin1. Базы соответственно по умолчанию и таблицы так же. И именно на это стоит обратить в самом начале обратить внимание, чтобы проблемы не всплывали позднее.

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

Ну и наконец, основной вопрос, что делать если одна из mysql таблиц(или несколько) в неверной кодировке и на сайте видны кракозяблывопросики?

1. Выяснить кодировку таблицы.

В этой таблице поле sName в кодировке latin1, если у нас соединение в другой кодировке, то мы увидим кракозябры.

2. Поэтому дальше проверим кодировку соединения, sql запросом SHOW VARIABLES LIKE ‘character_set_client’. Замечу, что php функция mysqli_client_encoding(), нам не подойдет, так как она отображает кодировку только на момент соединения.

3. Если кодировка соединения не совпала с кодировкой одного из полей таблицы, то 2 очевидных варианта.
Если у нас все таблицы в одной кодировке, то проще поменять кодировку соединения .
А как исправить неверную кодировку поля таблицы?
Для этого выполним 2 запроса

Нельзя обойтись только вторым запросом. Важно выполнить оба. Первый преобразовывает данные в двоичные, второй запрос, преобразовывает данные в строковые сменив кодировку.. Т.е. по сути мы не измениили двоичные данные, мы изменили правило формирования символов.  Если бы мы попробовали обойтись только вторым запросом, то получили бы ошибочный набор.