Вопросы кодировки на разных версиях MySQL

Сейчас появляется все больше предложений хостинга с самыми последними версиями MySQL-серверов, хостеры обновляют у себя версии софта "до последних имеющихся".

Часто это приносит проблемы пользователям, в частности большое количество жалоб связано с нарушением работы "Joomla! LE". Вернее так: сам движок встает и работает без вопросов, а любой вновь устанавливаемый компонент, модуль или другая "крутая прибабаха" после установки упорно выдает вместо кириллицы в контенте или знаки вопросиков "???? ??????? ?????? ?????????????? ???????" или вообще что-то непотребное "Сейчас появляется РІСЃРµ больС?Рµ предложе".

Как в таком случае исправить ситуацию и запустить необходимый компонент на "LE"?

Для начала давайте рассмотрим отличия в структуре БД и таблиц разных версий MySQL-серверов.

Я делю версии MySQL-серверов на:

• нижние версии - все версии, до версии 4.1
• высшие версии - все версии, начиная с 4.1 включительно и выше

Они имеют следующие отличия:

• нижние версии - выдают результат в той кодировке, на какую настроен сам сервер. Если он настроен на работу с кириллицей, то никаких проблем обычно не возникает, это в 99.9 % случаев так. Поставил - забыл.
• высшие версии - имеют внутренне представление идентификаторов всегда в utf8-кодировке, т.е. независимо от того, в какой кодировке выдаются и обрабатываются данные, внутренне их представление всегда в utf8. Кроме того, у сервера имеется несколько настраиваемых параметров, с помощью которых он может проводить промежуточные перекодировки в зависимости от требований пользователя, причем они еще делятся на две группы - GLOBAL и SESSION.

• GLOBAL - Глобальные, относятся к самому серверу. т.е. настройки "по умолчанию". Если в SQL-запросе не передаются какие-то требуемые параметры, то применяются параметры "по умолчанию", которые в свою очередь берутся именно отсюда. Могут устанавливаться в файле конфигурации (my.cnf , my.ini), передаваться как параметры командной строки при запуске сервера, динамически меняться в зависимости от поступивших команд для динамической перенастройки сервера и его параметров "на лету". В последнем случае требуется наличие таковых прав у пользователя, который посылает серверу такую команду, как правило - это привилегия пользователя ROOT.
• SESSION - Настройки текущего соединения в БД- текущей сессии. Как только соединение с БД разорвано, сессия считается завершенной и настройки "пропадают". Они более безопасны (для хостера :)), т.к. нигде не сохраняются и и не могут повлиять на работу и настройки самого сервера. Выдача этих команд и их результаты являются заботой самого пользователя.

Все эти параметры (а мы сейчас рассматриваем только те, которые влияют на работу с кириллицей) существуют в системе MySQL-сервера в виде переменных, соответственно - глобальных (global) и локальных (session) и для перенастройки параметров MySQL-сервера достаточно их изменить следующими SQL-операторами:

SET GLOBAL variable_name = new_valie
SET SESSION variable_name = new_valie

Для просмотра текущих значений нужной переменной используйте следующие операторы:

select @@global.variable_name
select @@session.variable_name

Пример:

set session collation_server = cp1251_general_ci;
select @@session.collation_server;

... вот 1251

set session collation_server=latin1_swedish_ci;
select @@session.collation_server;

... а здесь уже latin1

Посмотреть их можно и иным способом - через phpMyAdmin:

... вот они:

Можно посмотреть стразу все переменные сервера:

SHOW VARIABLES

Давайте рассмотрим эти переменные:

Теперь давайте рассмотрим случай "ненормального" MySQL-сервера, который установлен хостером с параметрами "по умолчанию и хай стоить...". Дело в том, что "по умолчанию MySQL-сервер устанавливается в кодировке latin1 , что нас ну никак не устраивает, поскольку именно эта настройка и является основным нашим врагом и "производителем" знаков вопросиков "?????? ???????? ??????????".

И так мы имеем установленные MySQL-сервер с параметрами кодировки по умолчанию latin1.

При подаче последовательности SQL-команд на создание новой БД и таблицы в ней:

# создание БД
CREATE DATABASE `TEST` ; 

# создание таблицы с одним текстовым полем
CREATE TABLE `TEST` (
`title` TEXT 
) ENGINE = MYISAM ;

... мы получим:

• БД с кодировкой latin1
• Таблицу "TEST" с кодировкой latin1

• Текстовое поле 'title' с кодировкой latin1

Для нормальной работы с кириллицей создание БД, таблицы и полей на сервере с latin1 должно выглядеть так:

# создание БД
CREATE DATABASE `TEST` COLLATE cp1251_general_ci; 

# создание таблицы с одним текстовым полем
CREATE TABLE `TEST` (
`title` TEXT CHARACTER SET cp1251 COLLATE cp1251_general_ci
) ENGINE = MYISAM COLLATE cp1251_general_ci;

... только тогда мы получим:

• БД с кодировкой 1251
• Таблицу "TEST" с кодировкой 1251

• Текстовое поле 'title' с кодировкой 1251

Но:

• во первых, как правило у пользователя нет прав на создание БД собственным SQL-скриптом, БД создается или панелью управления хостинга, которая оставляет кодировку по умолчанию, что и приводит к дальнейшему распространению действия latin1 на вложенные объекты - таблицы и поля;
• во вторых, в Joomla! при установке компонентов, как правило, в 99.9 % случаев, SQL-команды создания объектов и работы с ними, не содержат этик команд тоже.

Выход?

• первое: переписывать SQL-скрипты (на что не каждый пользователь способен, да и "на фиг надо");
• второе: перевести все вложенные объекты БД в кодировку 1251

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

Для начала требуется составить список БД, таблиц и полей в таблицах. которые имеют кодировку, вызывающую потерю кириллицы, в нашем случае это любая кодировка, отличная от cp1251_general_ci , после чего потребуется составить SQL-скрипт и выполнить его на MySQL-сервере. Вот параметры этого скрипта:

Последнюю команду рассмотрим поподробнее:

Кодировку отдельных объектов можно сменить прямо в phpMyAdmin:

После смены кодировки полей, даже старые компоненты, которые были написаны еще до выхода этих версий MySQL, начинают работать нормально, только данные, которые уже находятся в БД "вопросиками", можно считать потерянными, их придется вводить заново уже после того, как БД, таблицы и поля таблиц будут переведены в кодировку 1251.

Для хостеров: (и не говорите, что не читали...)

Для перенастройках самого MySQД-сервера на кириллицу 1251 следует внести изменения в MY.CNF (желательно в каталоге /etc для *nix) или MY.INI:

character-sets-dir=/путь_к_папке_с_чарсетами # если запросит...
default-character-set=cp1251

Выглядеть это должно так:

[mysql]
default-character-set=cp1251

[mysqld]
default-character-set=cp1251

... после чего перезагрузить MySQL-сервер. В результате должно получиться:

... особенно важно Глобальное значение, т.к. от него "пляшут" все по умолчанию.

Как правило, этого вполне достаточно для перевода всей системы MySQL-сервера в 1251 кодировку, т.к. все остальные объекты MySQL-сервера будут воспринимать установку по умолчанию. БД установится в 1251 потому, что сервер в 1251, таблицы будут создаваться в 1251, потому что БД и 1251. Поля будут в 1251, потому что таблица в 1251 и т.п.

Если установки этих параметров не хватит, вводите в конфиг-файл соответствующие переменные, пока после очередной перезагрузки не установятся нужные параметры. Общее правило таково:

• переменная пишется с разделением слов знаком подчеркивания: default_character_set
• написание параметра в конфиг-файле вместо знака подчеркивания знак "тире": default-character-set
• параметром командной строки при запуске сервера знаком подчеркивания:

shell>  mysqld --variable1_name=Variable1Value 
--variable2_name=Variable2Value 
--variable3_name=Variable3Value

Для установки глобальных параметров MySQL-сервера (равнозначные команды):

mysql> SET GLOBAL collation_server=cp1251_general_ci;
mysql> SET @@global.collation_server=cp1251_general_ci;

Полный список переменных смотри:

SHOW VARIABLES

1.

97K

16 июня 2016 года

Anuta Goodluck

0 / / 16.06.2016

16 июня 2016, 21:49:09

Ваша статья очень помогла, избавилась от вопросительных знаков в полях таблицы))) спасибо

2.

55K

14 ноября 2009 года

Сметанин

0 / / 14.11.2009

+2 / -0

14 ноября 2009, 17:21:52

Хорошая статья.

Но у меня такая проблема. global.Character_set_results=cp1251,
а session.Character_set_results=latin1!!!
Запись в My.cnf
[mysql]
default-character-set=cp1251
влияет только на global.
Можно ли как-то решить проблему?