Исправление и изменение кодировок MySQL
В связи с тем, что довольно много людей обращается с просьбой помочь исправить проблему с кодировками MySQL, решил написать статью с описанием, как «лечить» наиболее часто встречающиеся случаи.
В статье описывается не то, как первоначально правильно настроить кодировки MySQL (об этом уже довольно много написано), а о случаях, когда есть довольно большие таблицы с неправильными кодировками и нужно всё исправить.
Самое плохое в неправильно настроенных кодировках — то, что зачастую проблему сложно обнаружить, и с первого взгляда может казаться, что сайт работает правильно, и никаких проблем нет.
Небольшое отступление. Sypex Viewer
В какой-то момент надоело отправлять людей в громоздкий phpMyAdmin, и была написана крошечная утилитка Sypex Viewer. Она представляет собой один PHP-файл, использует современные Web 2.0 технологии AJAX, JSON и другие. Основные задачи, которые ставилась при создании — минимальный вес, и максимальное удобство и скорость работы. В дальнейшем в примерах будут скриншоты из неё, но все те же действия можно сделать и в phpMyAdmin.
Данные в cp1251 таблицы в latin1
Данные и таблицы в 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 кодировку.
Изменение кодировки
Кодировка соединения
Не забываем, что после исправлений кодировки, нужно убедиться, что ваши скрипты используют правильную кодировку соединения (в принципе, это будет сразу видно, они будут неправильно показывать русские символы без нужной кодировки соединения). У некоторых она выставляется в настройках, в некоторых придется добавить самостоятельно.
Для чего достаточно пройтись поиском по файлам, и найти где вызывается функция mysql_connect (или mysqli_connect). После этой строки нужно добавить строку которая укажет кодировку соединения.
Где вместо cp1251, указать нужную кодировку соединения.
Не забывайте перед преобразованиями кодировок делать бэкап, тут как с презервативами, лучше пусть он будет и не понадобится, чем когда понадобится — его не будет.
Как перейти с utf8 на utf8mb4 в MySQL
Содержание:
Переходим с utf8 на utf8mb4 в MySQL.
utf8 или utf8mb4
Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.
Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.
utf8mb4_general_ci или utf8mb4_unicode_ci
В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.
Настройка кодировки utf8mb4 для СУБД MySQL
Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.
Если у вас уже есть базы данных, то обязательно создайте резервные копии всех баз данных.
В конфигурационном файле MySQL ( my.ini (windows)/ my.cnf (Linux)) необходимо изменить кодировку на utf8mb4:
Проверяем корректность работы применимых настроек:
Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL
Для базы данных:
Для таблицы:
Для столбцов:
Восстановление и оптимизация всех таблиц
После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:
Или с использованием команды mysqlcheck :
Пример миграции для Yii2
В этом примере мы изменим кодировку для столбца content в таблице post :
Кодировка базы данных MySQL – выводим из запоя пьющие базы
Дата публикации: 2016-07-13
От автора: вы зачем полное ведро баз на мусорку несете выбрасывать? Сервер китайский попался, и все строки иероглифами отображаются? Так кодировка базы данных MySQL не та, наверное. Кто ее закодирует, она же база? Понятно! Идите, гражданин дальше. Не обращаем на него внимания. Сегодня мы познакомимся с кодировками в MySQL.
Зачем кодировать БД?
Код MySQL, как и любая другая информация в вычислительной технике, задается с помощью сочетания единицы и нуля, которые образуют биты. Вспоминаете? Это ведь основы информатики, которые все мы проходили в школе.
В мире существует множество текстовых кодировок (сочетаний 0 и 1), но в современности все они более-менее стандартизированы. Хотя и сейчас можно наткнуться на «абракадабру», если неправильно задать кодировку.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
На данном этапе развития всемирной паутины самыми распространенными являются 3 типа кодировок:
Мы не будет сильно «зарываться» в теоретическом ворохе. Для получения более детальной информации перейдите по расположенным выше ссылкам на соответствующие публикации в Википедии.
Для нас самой главной кодировкой базы данных MySQL является UTF-8. Это значит, что в ней используется 8-битный Юникод для представления всех символов. Благодаря широкой поддержке данной кодировки в Сети и на прикладном уровне вы можете быть уверены, что записи в базе будут сохранены в читаемом виде (без «абракадабр»).
Где ее искать?
Немного пробежимся по интерфейсу phpMyAdmin. Зайдя на первую страницы программы, обратите внимание на раздел «Общие настройки».
Параметр «Сопоставление кодировки соединения с MySQL» устанавливает, с какой кодировкой должен сравниваться формат полученных данных при подключении к серверу СУБД. В этой программной оболочке кодировку можно задать вручную.
В «родных» утилитах СУБД (mysqladmin, mysqlimport и других) кодировка таблиц MySQL берется из настроек операционной системы ПК. В случае их отсутствия устанавливается значение, заданное по умолчанию. Чаще всего, это кодировка latin1.
При соединении с СУБД клиентская сторона с помощью значений нескольких системных переменных говорит MySQL, какую кодировку использовать при обработке и отображении данных из БД.
Системные переменные и их значения
Архитектура MySQL построена на основе «клиент-сервер». Системные переменные (точнее их значения) используются при установке соединения клиента и сервера для корректного отображения всех данных, их обработки и поддержания сетевого сеанса.
Для ознакомления с установленными значениями переменных системы СУБД в phpMyAdmin нужно в последнем пункте верхнего меню «Еще» выбрать «Переменные».
Также список всех переменных (в том числе и тех, которые нужны для настройки кодировка MySQL) можно получить с помощью команды show variables.
Вернемся к полученному списку переменных, и остановимся на тех, которые начинаются с charset. Краткое описание тех, которые могут пригодиться:
character_set_client – указана кодировка, в которой будут поступать данные с клиентской стороны.
character_set_connection – устанавливает кодировку соединения.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
character_set_database –набор символов, используемы в БД по умолчанию.
character_set_results – кодировка, в которой сервер отправит данные клиенту.
character_set_server –используемая по умолчанию на сервере.
Вот тут перед описанием следующей группы переменных, необходимых для того, как узнать кодировку базы MySQL, следует покопаться в теории.
Под кодировкой мы подразумеваем набор символов, применяемых для определенной структуры данных (базы, таблицы, поля). Но есть еще такое понятие как «представление» (collation), которая соответствует конкретному языку и используется при сравнении и упорядочивания записей.
Например, есть кодировка UTF-8. Существует огромное количество представлений в рамках одной кодировки. Нельзя сравнивать данные, «представленных» в разных наборах символов. Это можно осуществлять только в рамках одной кодировки. Последние 3 переменные как раз и показывают установленные представления.
Разрешенные сравнения для конкретной кодировки на текущем экземпляре сервера, можно узнать с помощью команды show collation. Например:
Sergey Danielyan
Корректная настройка MySQL для работы с UTF8
Основная цель данного поста — выяснить, какие параметры и с какими значениями следует прописать в конфигурационный файл my.cnf (my.ini) для дальнейшей беспроблемной работы с Юникодом.
Рабочее окружение
UTF8 на данный момент у меня успешно работает в Мастер-Слейв конфигурации:
Любой внешний клиент в состоянии корректно работать с UTF8 базой (проверено на EMS Manager for MySQL c Windows 8 x64).
Все опции и настройки я привожу для версии сервера 5.1.x, однако с минимальными (а то и вовсе без оных) изменениями все это будет работать и на версиях 5.5.x и 5.6.x.
Параметры кодировок MySQL
Довольно часто приходится видеть в ответах на вопросы о настройке UTF8 следующее:
Предполагается, что после вставки всего этого добра (тут кстати есть противоречащие друг другу опции) в конфигурационный файл my.cnf (my.ini) магический Юникод начнет работать.
Но давайте забудем о списке и попытаемся разбираться со всеми опциями сами и начнем с самого начала. То есть с документации. Потому как все это прекрасно описано в документации MySQL на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.
Символьная кодировка может быть задана для:
Сделано это для гибкой настройки баз данных и доступа клиентов с разными кодировками. Однако, последнее не входит в область рассмотрения данного поста, поэтому будем рассматривать вариант с кодировкой UTF8 настроенной для всего по-умолчанию.
Все параметры могут быть переданы серверу тремя разными способами:
Второй и третий варианты рассматриваться не будут. Тут уместно будет просто прочитать официальные доки — в каждом разделе приведены примеры конфигурации с использованием всех трех способов. Я же буду использовать первый вариант.
Кодировка (character set) и представление (collation) сервера
Тут есть несколько фундаментальных вещей которые надо понимать.
Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:
SHOW COLLATION LIKE ‘your_character_set_name’;
Поле Default дает ответ о представлении выбранной кодировки.
В нашем случае, при настройке дефолтной кодировки в UTF8, параметры должны быть определены, так как могут быть использованы при определении кодировки или представления базы данных:
Наши команды:
my.cnf (my.ini)
[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
Кодировка (character set) и представление (collation) базы данных
Тут есть два варианта определения кодировки и представления:
явно — при выполнении запроса на создание базы данных:
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Вообще при работе с базой данных огромную роль помимо серверных настроек играют настройки клиент-серверного соединения (connection). На этом этапе вступают в игру следующие специфичные для соединения параметры:
Есть еще представление кодировки соединения ( colation_connection ). Для чего нужен этот параметр думаю пояснять не надо.
Озадачиваться проблемой инициализации всех этих переменных не стоит (хотя в нашем случае присвоить им значения необходимо). Есть способ проще: существует два типа запросов (statements) которые задают настройки соединения клиента с сервером группой:
Запрос SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]
Параметр определяет в какой кодировке теперь будут приходить сообщения для сервера от клиента. Прелесть в том, что запрос SET NAMES x эквивалентен следующей группе:
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
Для определении представления кодировки соединения ( colation_connection ) отличного от дефолтного, следует дополнить запрос:
SET NAMES x COLLATE y
SET NAMES utf8 COLLATE utf8_unicode_ci
Таким образом, используя только этот запрос, можно добиться корректной UTF8 инициализации соединения.
Однако, тут есть один нюанс:
init_connect=‘SET collation_connection = utf8_unicode_ci’
Запрос SET CHARACTER SET charset_name
Запрос групповой и он также эквивалентен следующей группе:
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;
Согласно документации, разница между двумя запросами в том, что параметры character_set_connection и collation_connection будут установлены на @@character_set_database и @@collation_database соответственно (выше я про них упоминал).
Наши команды:
my.cnf (my.ini)
[client]
default_character_set = utf8
[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
Кодировка (character set) и представление (collation) таблиц
Тут все довольно просто. Задать кодировку и ее представление можно через команды:
CREATE TABLE t1 ( … )
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Тут главное иметь в виду, что если эти настройки не заданы, то берутся настройки базы данных (см. пред. раздел). Нам эти настройки не интересны.
Кодировка (character set) и представление (collation) колонок в таблице
Тут по аналогии с пред. секцией. Если параметры кодировок не указаны, берутся те, что указывались для таблицы.
Прежде чем перейти к след. разделу, должен сказать, что все команды и запросы относятся к указанной версии MySQL и в случае возникновения каких-либо проблем советую обратиться к соответствующей версии документации.
skip-character-set-client-handshake
Верификация настроек
Итак, вот финальный snapshot наших изменений в файле my.cnf (my.ini):
[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci
[client]
default-character-set = utf8
После применения всех опций и рестарта сервера mysql для проверки настроек можно воспользоваться командами SHOW VARIABLES LIKE ‘char%’ и SHOW VARIABLES LIKE ‘collation%’ ;
Состояние среды до изменений:
Состояние среды после изменений (в случае, если вы приконнектились не SUPER пользователем):
Для примера, вот отличие при соединении через mysql.exe пользователем с и без привилегии SUPER:
с привилегией и выполненной вручную командой ‘SET collation_connection = utf8_unicode_ci’:
Поздравляю, теперь ваши база, таблицы и все в таблицах по-умолчанию в кодировке UTF8.
Работа с кодировками в MySQL 4.1.11 и выше
Тестовая машина
Устанавливаем MySQL 5.1
Пускай это не самый «правильный» способ установки MySQL-сервера, зато быстрый и рабочий.
Начало работы
Итак, sockstat показала, что сервер работает, а установка говорит о том, что сервер абсолютно девственный. Чем это грозит? Кодировки по умолчанию выставлены англоязычные, а значит, будут проблемы при использовании кирилицы. Но как это распознать? Проверяем:
Первым делом используем тестовую базу, которая уже есть на сервере, затем создаём в ней таблицу и вставляем в неё три слова на русском, про кодировки мы пока ничего не знаем и знать не хотим ))).
Пока всё хорошо и радужно, никаких ошибок нет, пробуем сделать выборку:
Как видно, запросы работают абсолютно корректно, так где же грабли. Оказывается мы на них уже стоим:
После того, как загрузился 16-метровый мануал, можно не полениться и прочитать первые пару-тройку страниц с оглавлением )), а можно просто сделать поиск на предмет charset или character set. Не суть важно, но через некоторое время можно найти раздел 9.1.2. Character Sets and Collations in MySQL, в котором написано много и интересно, а, главное, содержательно про то, каким образом можно и нужно работать с кодировками.
MySQL отказывается это делать. но почему? Потому, что latin1 не поддерживает сравнение в кирилице, а доступные «сравнения» можно увидеть так:
Ни о какой кирилице не может идти и речи. Куда копать. В создание таблицы!
Ага! По-умолчанию при создании таблицы была взята кодировка latin1, значит, если мы изменим таблицу и укажем ей, что надо использовать кирилистическую кодировку, то всё заработает. В мануале написан пример про изменение кодировки таблицы, используем его:
Ок! Проверяем, что получилось.
Хм.. опять та же ошибка, но откуда ей взяться.
Ого, структура таблицы резко изменилась, теперь у неё задана одна кодировка, а у поля совсем другая.. :(( Порыв ещё мануал, можно изменить и кодировку столбца:
Ну вот. Злой кодировки latin1 нет и в помине, можно проверять наш роддом )))
Вопросиков можно было избежать
На самом деле, ситуация, когда изначально выставлена неправильная кодировка, встречается сплошь и рядом. Симптомы можно выявить следующим образом:
Именно эти переменные отвечают за дефолтные значения кодировок.
ВАЖНО: Если character_sets_dir установлена неверно, то работа с кодировками будет под угрозой. Не пытайтесь менять её значение, если вы неуверены в своих силах. Если вы системный администратор, то перед установкой лучше ознакомиться с мануалом.
Наиболее значимые для простых пользователей следующие переменные: character_set_client, character_set_results, character_set_connection. Поскольку именно они отвечают за внесение, извлечение информации и создание таблиц/баз соответственно. Какими они могут быть?
Любую из этих кодировок можно пользовать на свой вкус. Обычно русскоязычные пользователи предпочитают cp1251 или utf8, но по сути, неважно, в какой кодировке хранятся данные, важно, чтобы она была изначально правильно указана и данные были корректно внесены.
Настройка кодировок
Мануал предлагает нам три варианта задания кодировок:
ВНИМАНИЕ. Первые два варианта работают только в рамках текущего соединения. Это значит, что при следующем подключении все настройки вернутся в начальное состояние! Чтобы не выставлять кодировку каждый раз, нужно воспользоваться третьим вариантом.
Ну, тут всё ясно, три самые нужные кодировки в одном )))
Более детальная настройка, чем names.
Ещё можно при конфигурировании задать кодировку по умолчанию
Но лучше, когда кодировка настраивается прямо в соединении.
Что делать, если данные внесены в неправильной кодировке
Если база/таблица/данные были созданы/внесены в кодировке отличной от нужной, то необходимо сделать следующее:
Правильный вариант работы с MySQL
Таким образом, клиент работает в KOI8-R, но данные хранятся в cp1251, MySQL знает об этом и делает перекодировку на лету.
© 2021 Антон Прибора. При копировании материалов с сайта, пожалуйста, указывайте ссылку на источник.
