Как узнать кодировку mysql

Корректная настройка MySQL для работы с UTF8

Сегодня речь пойдет о MySQL и о настройке UTF8 кодировки по-умолчанию. Тема заезжена, но как я убедился за прошедшую неделю, мало кто в состоянии нормально пояснить какие параметры и куда надо прописать для полноценной работы с UTF8 в MySQL. К сожалению, ситуация на тематических блогах оставляет желать лучшего. Основной тип ответа — приведение соедржимого конфигурационного файла с комментарием типа “попробуй, у меня это работает”.

Основная цель данного поста — выяснить, какие параметры и с какими значениями следует прописать в конфигурационный файл my.cnf (my.ini) для дальнейшей беспроблемной работы с Юникодом.

Рабочее окружение

UTF8 на данный момент у меня успешно работает в Мастер-Слейв конфигурации:

• MySQL версии 5.1.66
• Два сервера CentOS версии 6.3
• Репликация между серверами Master-Slave на базе SSL

Любой внешний клиент в состоянии корректно работать с 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 на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.

Главный раздел по описанию кодировок (character sets) и их представлений (collations — используется например при сортировке) в контексте сервера, базы, таблиц — это секция 10.1.3. Specifying Character Sets and Collations.

Символьная кодировка может быть задана для:

1. сервера,
2. базы данных,
3. таблицы и
4. колонок в таблице.

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

Все параметры могут быть переданы серверу тремя разными способами:

1. через командную строку mysqld
2. через конфигурационный файл my.cnf (my.ini)
3. через опции компиляции.

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

Кодировка (character set) и представление (collation) сервера

Кодировка (characher set) — набор используемых символов.
Представление (collation) — набор правил для сравнения символов в наборе.

Тут есть несколько фундаментальных вещей которые надо понимать.

Основные параметры используемые в контексте сервера — это character_set_server и collation_server . Оба параметра влияют на определение кодировки и отображения сервера MySQL.

Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:

Не заданы — используются значения по умолчанию (дефолтные),

Заданы оба — используются указанные кодировка и ее представление,

Задана только кодировка — ее представление выставляется по умолчанию для данного типа кодировки. Что это значит? Для каждого типа кодировки есть ее дефолтное представление, например, дефолтная кодировка сервера — latin1 , а дефолтное отображение для нее — latin1_swedish_ci . Посмотреть соответствие кодировки и ее дефолтного представления можно используя команду:

Поле Default дает ответ о представлении выбранной кодировки.

В нашем случае, при настройке дефолтной кодировки в UTF8, параметры должны быть определены, так как могут быть использованы при определении кодировки или представления базы данных:

Наши команды:
my.cnf (my.ini)

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci

Дефолтное представление для utf8 — utf8_general_ci , так что если бы мы его использовали вместо utf8_unicode_ci , то параметр collation_server можно было бы вообще опустить.

Кодировка (character set) и представление (collation) базы данных

Тут есть два варианта определения кодировки и представления:

явно — при выполнении запроса на создание базы данных:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

неявно через переменные character_set_database и collation_database . Однако, эти переменные нельзя задать явно ни в командной строке ни в конфигурационном файле. Как они инициализируются — чуть ниже.

Вообще при работе с базой данных огромную роль помимо серверных настроек играют настройки клиент-серверного соединения (connection). На этом этапе вступают в игру следующие специфичные для соединения параметры:

• character_set_client — кодировка в которой посылается запрос от клиента
• character_set_connection — кодировка используемая для конвертации пришедшего запроса (statement’а)
• character_set_results — кодировку, в которую сервер должен перевести результат перед его отправкой клиенту

Есть еще представление кодировки соединения ( 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

А так как у нас utf8 и ее дефолтное представление utf8_general_ci , то нам нужно выпонить полный запрос:

SET NAMES utf8 COLLATE utf8_unicode_ci

Таким образом, используя только этот запрос, можно добиться корректной UTF8 инициализации соединения.

Однако, тут есть один нюанс:

SET NAMES x , как понятно из определения, определяет настройку клиента при коннекте к серверу. Но что делать, если клиент — сам mysql.exe и нам хочется установить collation_connection по-умолчанию, не выполняя каждый раз SET NAMES x при коннекте?
Для этих целей, существует еще один параметр — default_character_set . Он эквивалентен запросу SET NAMES utf8 . В случае его использования задать collation_connection отличный от дефолтного уже не получится, поэтому придется заюзать еще одну команду init_connect (так как напрямую collation_connection нельзя прописать в конфигурационном файле):

init_connect=‘SET collation_connection = utf8_unicode_ci’

Но и тут есть еще одно но: init_connect команда не выполняется для SUPER пользователей — пользователей, обладающих привилегией SUPER. root входит в этот перечень, поэтому при коннекте root’ом команду 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 соответственно (выше я про них упоминал).

За более детальной информацией отсылаю по двум источникам — собственно к официальной документации и прекрасно оформленному ответу на stackoverflow.com. Для нашей задачи вполне хватает первого параметра вместе с дополнительной командой.

Подытожим: различные сценарии и что юзается на каждом из них — относительно к настройкам соединения:

• Если к базе коннектится mysql.exe клиент с пользователем с привилегией SUPER:
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • надо выполнить вручную команду init_connect=’SET collation_connection = utf8_unicode_ci’
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • срабатывает команда в конфигурационном файле init_connect=’SET collation_connection = utf8_unicode_ci’
  • надо выполнить вручную команду SET NAMES utf8 COLLATE utf8_unicode_ci

  Наши команды:
  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

  Помимо освещенных параметров, есть еще один довольно часто фигурирующий в разного рода источниках — 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.

  Snip Code

  Задача: одном запросом узнать кодировку базы данных MySQL.

  Решение

  Автор: SnipCode.ru

  Рейтинг:

  Если вы знаете более оригинальное, красивое, ЛУЧШЕЕ решение этой задачи, у вас есть шанс заработать 100 рублей. Если ваше решение будет признано лучшим, деньги ваши! Мы гарантируем выплату!

  phpguru

  Функция отличная только надо бы добавить для utf8 кодировку обработать строку, а то не все понимают как сделать подобное!
  $str = iconv_strlen($str,’UTF-8′);
  Я так считаю!
  13-12-2013 в 12:59:38 ID# 454 посмотреть.

  SnipCode.ru

  Возможно Вы правы, непонятно только зачем тут iconv_strlen (((
  13-12-2013 в 13:02:55 ID# 455 посмотреть.

  Сергей

  поторопился, так надо в конце, пардон.
  return $v[‘pass’];
  >
  return FALSE;
  >

  02-12-2013 в 23:33:41 ID# 377 посмотреть.

  Сергей

  Думаю все же логичней использовать foreach, т.к. можно промахнуться с ключами, а если массив ассоциативный (я раньше делал ключ = дата и время регистрации), то вообще работать не будет.

  function search($array,$login)
  <
  foreach ($array as $k=>$v)
  if($v[‘login’] == $login) <
  return $v[‘pass’];
  >
  >
  return FALSE;
  Так, на минутку.
  Время исполнения смысла не имеет, авторизация используется 1 раз, если юзер не параноик)))

  02-12-2013 в 23:33:41 ID# 376 посмотреть.

  Пабло

  Как сделана ваша система рейтинга,с учетом количеством людей,проголосовавших?
  11-11-2013 в 17:04:39 ID# 207 посмотреть.

  SnipCode.ru

  ну. вобщем то все просто: берем общую оценку, делим на кол-во проголосовавших, получаем рейтинг
  11-11-2013 в 17:07:06 ID# 208 посмотреть.

  Пабло

  Да,просто у меня возникли проблемы,при сохранении данных,в бд и отображение количество проголосовавших людей,у вас где нибудь на сайте описано как это сделано или будет?

  Мне нужна система рейтинга точно такая же как у вас=)
  11-11-2013 в 17:10:56 ID# 209 посмотреть.

  KorniloFF

  Приведенный код обрезает до первой точки в строке, в случае, если в ней будет их несколько, что не соответствует теме.
  Вот код, обрезающий до последней точки:

  Виктор

  Все отлично работает! Извините , у меня есть задача отрезать после второй и до второй запятой , что нужно поправить в первом примере?
  Спасибо!
  30-09-2013 в 20:34:07 ID# 204 посмотреть.

  mysqli_get_charset

  Возвращает объект, содержащий свойства текущей кодировки.

  Список параметров

  Только для процедурного стиля: объект mysqli , полученный с помощью mysqli_connect() или mysqli_init() .

  Возвращаемые значения

  Функция возвращает объект с следующими свойствами: charset

  Директория, из которой получено описание кодировки. (?) или "" для встроенных наборов

  Минимальная длина символа в байтах

  Максимальная длина символа в байтах

  Внутренний номер кодировки

  Состояние набора символов (?)

  Примеры

  Пример #1 Пример использования mysqli::get_charset()

  Как посмотреть с какой кодировкой подключился клиент MySQL?

  К серверу MySQL подключен клиент, висит постоянно, можно спокойно узнать о нём всё, что нужно.

  Но не знаю, собственно, как это сделать.

  Клиентом является бинарник, который что-то внутри у себя не так переварил и стал вместо UTF8 отправлять строки в Latin-1.

  Нужно убедиться в том, что действительно кодировка на стороне клиента некорректна, пользуясь, скажем, учётной записью root.

  Как это сделать?

  To see the values of the character set and collation system variables that apply to your connection, use these statements:
  SHOW VARIABLES LIKE ‘character_set%’;
  SHOW VARIABLES LIKE ‘collation%’;

  Так ты увидишь переменные _своего_ соединения. А ТСу надо посмотреть переменные чужого.

  и стал вместо UTF8 отправлять строки в Latin-1.

  Большинство mysql-клиентов, ЕМНИП, по умолчанию настроены на latin1. Просто измени значение —default-character-set, или после каждого соединения устанавливай принудительно через SET.

  Нужно убедиться в том, что действительно кодировка на стороне клиента некорректна,

  Дак ты уверен или нет? Ты же только что писал:

  и стал вместо UTF8 отправлять строки в Latin-1.

  Даже не представляю что именно тебе нужно? Сделать детект кодировки данных от клиента? По мне дак никак, ну если только не брать большой кусок данных и не делать частотный анализ 🙂 Шучу, конечно.
  А если серьезно, то ответил в первом абзаце.

  

  Так ты увидишь переменные _своего_ соединения. А ТСу надо посмотреть переменные чужого.

  +100500 Именно так!

  

  Ну как бы есть тот же MySQL Workbench, в нём много всякого полезного можно узнать о текущих соединениях с сервером: какие запросы выполняются, топ самых длительных запросов от того или иного клиента и т.д.

  Внутри себя MySQL-сервер точно знает, в какой кодировке ему отдаёт данные клиент — как минимум он должен «прозрачно» преобразовать из кодировки клиента в кодировку сервера, если первая и вторая не совпадают.

  Вот как «вытащить» из сервера эту полезную информацию?

  Т.е. ты не клиент и не сервер. Тебе нужна информация о _чужом_ клиент-серверном соединении, причем _чужие_ в твоем случае и клиент, и сервер, я правильно понял?

  

  Сервер — мой! Кто сказал, что сервер-то чужой?

  Он мало того, что мой, так ещё и вполне себе MySQL Enterprise.

  

  Кстати, не знаете, как применить опцию в my.cnf: skip-character-set-client-handshake — без перезапуска mysqld? У меня mysqld при останове только дропать соединения будет минут 15-ть.

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

  Сервер — мой! Кто сказал, что сервер-то чужой?

  Ну дак если сервер ваш, в чем проблема стартануть с опцией —default-character-set или установить через SET после соединения клиента, не понимаю?

  

  Как это всё поможет мне выяснить, корректно ли работает клиент и не пытается ли использовать latin1?

  Положим, я понимаю, как создать трешовую ситуацию с клиентом, отправляющим данные в однобайтовой кодировке и сервером, игнорирующим этот факт и упорно пихающим однобайтовые строки в utf-ные поля. Собственно, потому и спросил об установке skip-character-set-client-handshake в онлайн: так можно считать всех клиентов utf-ными, даже если это совсем не так.

  Но я бы всё же хотел отдебажить ситуацию и понять, что делает клиент какую кодировку выставляет. Может, вовсе и не в latin1 дело, вполне вероятно, кстати. Этот же клиент раньше всё делал правильно, ему башню снесло вообще непонятно в какой момент.

  Как это всё поможет мне выяснить, корректно ли работает клиент и не пытается ли использовать latin1?

  Еще раз – если ты установишь опцию – ничего выяснять не нужно будет – клиент будет работать _корректно_. Тебе шашечки или ехать?

  Но я бы всё же хотел отдебажить ситуацию и понять, что делает клиент какую кодировку выставляет.

  Ну блин, откуда этой информации взяться на сервере-то? Я ж говорю, если только брать достаточно большой кусок данных от клиента и проводить частотный анализ.

  

  Ну блин, откуда этой информации взяться на сервере-то?

  А слово «handshake» в названии приведённой выше опции ни о чём не говорит?

  Да, MySQL-сервер знает, в какой кодировке ему шлёт данные клиент и в какой кодировке ему нужно отправлять данные клиенту.

  Если же MySQL-сервер игнорирует попытки клиента установить кодировку, а она несовместима с UTF-8 (например, тот же cp1251), то клиент будет слать бинарные данные в однобайтовой кодировке, а сервер будет считать, что принял UTF-8 — со всеми вытекающими отсюда последствиями в виде кракозяблов и прочих прелестей.

  Ну и ещё хреновее, если MySQL-сервер преобразует данные, отправляемые SELECT’ом, в кодировку клиента (кириллица в UTF -> latin1 = "?" вместо русских буковок), а клиент потом эти данные возвращает серверу — например, при UPDATE’е. Но только принудительной установкой «всё в UTF8» такие траблы не решаются по-моему.

  На тебе аццкий hack:

  в конфиге mysql можно прописать команды которые будут выполняться при коннекте.

  Проблема в том, что клиент может игнорировать это всё.

  Поэтому надо выдрать variables. Например прописав в init_connect=’show variables . ‘

  Но show variables не подразумевает способа сохранить как-то результат. Однако, позволяет использовать WHERE, а в WHERE можно expr, в том числе и присваивание.

  Поэтому нужен финт ушами:

  Почему-то нужно 2 раза show variables, чтобы все переменные попали в @uservars. Первый раз только одна последняя переменная попадает. И \n нифига не работает, но тем не менее, в файле /tmp/uservars окажутся переменные и значения юзверя из-под которого это запустили.

  А запустить из-под юзера можно посредством init_connect

  Ну и вот эту всю бодягу оформить в виде процедуры, или прямо запускать из init_connect. Логично ещё имя файла согласно юзерскому имени давать. Через CURRENT_USER() и prepared statement, т.е. как обычно через жопу.

  

  Проблема в том, что клиент может игнорировать это всё.

  Я бы сказал даже по-другому: клиент может не то, чтобы игнорировать, клиент (код клиента) может вообще не учитывать тот факт, что сервер его к чему-то форсит. В реальности я не видел ни одного клиентского куска кода под мускуль, который бы проверял, что устанавливаемая им кодировка действительно «установилась», а не была зафорсена сервером.

  Если же MySQL-сервер игнорирует попытки клиента установить кодировку, а она несовместима с UTF-8 (например, тот же cp1251), то клиент будет слать бинарные данные в однобайтовой кодировке, а сервер будет считать, что принял UTF-8

  Да, MySQL-сервер знает, в какой кодировке ему шлёт данные клиент

  Ты вроде запутался, не?

  Но только принудительной установкой «всё в UTF8» такие траблы не решаются по-моему.

  Именно так и решается.

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

  Как ты себе это представляешь? Он _не может_, и _не должен_ этого проверять. Завтра отвечу более детально, к сожалению сейчас нужно бежать.

  Ну ты дальше-то прочёл? Там про черезжопный способ увидеть-таки variables юзера.

  

  Эх. ещё бы IP клиента в имя файла добавлять. Нельзя ли так, случаем?

  CURRENT_USER() тебе вернёт ‘user@hostname’. Так что и так всё есть.

  

  Просто клиентов-то много, и они по злому недоразумению коннектятся под одним пользователем. Хотя можно в общем и самостоятельного юзера завести с такими же правами.

  Как отфильтровать вывод «Финта» по пользователю или IP-шнику чтобы файл с переменными писать только для нужного клиента? 🙂

  

  CURRENT_USER() всё выдаст. Можно даже извратиться чтобы для каждого user@hostname свой файл был. Но есть ещё другая засада — mysql не умеет удалять или перезаписывать файлы. Т.е. файл должен отсутствовать перед SELECT . INTO OUTFILE.

  

  «Знать» и «принимать во внимание» — 2 сильно разные вещи, не?

  Да щаз. Это ж SQL.

  ЗЫ: СРАНЫЕ, СЦУКО, КАВЫЧКО-НА-ЭТИ-ВАШИ-ЭМОДЗИ-ЗАМЕНЯТЕЛИ. Хотя бы в [ pre ] можно же было не заниматься этим дебилизмом? Приходится [ code ] дурацкий пользовать

  Отвечаю как и обещал.

  Но только принудительной установкой «всё в UTF8» такие траблы не решаются по-моему.

  Если ты хочешь, чтобы _клиенты использовали установленную тобой_ кодировку через —default-charset-set, стартуй сразу сервер с опцией —skip-character-set-client-handshake. После этого серверу станет откровенно пофиг на информацию о кодировках, посылаемую клиентом. Он эти данные будет просто _игнорировать_. Т.е. соединение _гарантированно_ будет установлено в той кодировке, которая тебе необходима.

  Далее, что касается описанной тобой ситуации:

  Если же MySQL-сервер игнорирует попытки клиента установить кодировку, а она несовместима с UTF-8 (например, тот же cp1251), то клиент будет слать бинарные данные в однобайтовой кодировке, а сервер будет считать, что принял UTF-8

  Для этого и существует SET NAMES, который укажет серверу, в какой кодировке данные будет слать _клиент_. Естественно, что это будет работать только на current-connection. И это означает, что данные от клиента могут идти в одной кодировке, сервер отдавать их клиенту может в другой, а хранить в базе вообще в третьей. Чтобы избежать этого зоопарка – переходи уже на одну 🙂

  Читать:

  Как вышить фотографию крестом программа