Дневник megavtogal.com

МЕНЮ
  • Контакты
  • Статьи

Как узнать кодировку базы данных mysql через консоль

Обновлено: 14.01.2025

Сегодня речь пойдет о 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 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 соответственно (выше я про них упоминал).

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

  • Если к базе коннектится 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.

    Пускай это не самый "правильный" способ установки MySQL-сервера, зато быстрый и рабочий.

    Начало работы

    Итак, sockstat показала, что сервер работает, а установка говорит о том, что сервер абсолютно девственный. Чем это грозит? Кодировки по умолчанию выставлены англоязычные, а значит, будут проблемы при использовании кирилицы. Но как это распознать? Проверяем:

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

    Пока всё хорошо и радужно, никаких ошибок нет, пробуем сделать выборку:

    Как видно, запросы работают абсолютно корректно, так где же грабли. Оказывается мы на них уже стоим:

    Запрос на выборку с обратной сортировкой привёл к тому, что записи просто вывелись в обратном порядке, но не по алфавиту. До удара граблей остаются считанные секунды, но пока растянем удовольствие :) Сперва ответим на вопрос - почему поля не сортируются по алфавиту? У MySQL имеется мощный и богатый механизм для работы с интернациональными наборами символов, но.. но откуда MySQL узнает, что наши символы - есть русский алфавит, мы же качали английскую версию? Ничего не остаётся, как идти ковырять мануал на предмет кодировок.

    После того, как загрузился 16-метровый мануал, можно не полениться и прочитать первые пару-тройку страниц с оглавлением )), а можно просто сделать поиск на предмет charset или character set. Не суть важно, но через некоторое время можно найти раздел 9.1.2. Character Sets and Collations in MySQL, в котором написано много и интересно, а, главное, содержательно про то, каким образом можно и нужно работать с кодировками.

    Расставляя точки над и, Character Set - транслируется как "кодировка", а Collation - сравнение. В чём разница? Сравнение - это правила сравнения букв кодировки. Сравнения работают только в рамках кодировки, и нельзя сравнивать данные в латинице по правилам кирилицы. Поясню на примере: мы, как увидим позже, внесли данные в таблицу на латинице, а сортировать нужно на кирилице, для чего можно использовать ключевое слово collate :

    MySQL отказывается это делать. но почему? Потому, что latin1 не поддерживает сравнение в кирилице, а доступные "сравнения" можно увидеть так:

    Ни о какой кирилице не может идти и речи. Куда копать. В создание таблицы!

    Ага! По-умолчанию при создании таблицы была взята кодировка latin1, значит, если мы изменим таблицу и укажем ей, что надо использовать кирилистическую кодировку, то всё заработает. В мануале написан пример про изменение кодировки таблицы, используем его:

    Ок! Проверяем, что получилось.

    Хм.. опять та же ошибка, но откуда ей взяться.

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

    Ну вот. Злой кодировки latin1 нет и в помине, можно проверять наш роддом )))

    И вот тот страшный удар граблями, который так долго оттягивался! Внимательный читатель мог заметить, что когда была сделана попытка принудительно сменить кодировку столбца, содержащего данные в latin1, то на каждую запись, содержащую русские буквы, у MySQL был варнинг! Это был крик о том, что сервер не знает, каким образом можно перевести данные из latin1 в cp1251, ну и лучшего способа, чем заменить символы не latin1 вопросиками, он не нашёл :))). Роддом безвозвратно потерян потому, что теперь вместо кирилицы в базе содержатся вопросики..

    Вопросиков можно было избежать

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

    Именно эти переменные отвечают за дефолтные значения кодировок.

    • character_set_client - кодировка, в которой данные будут поступать от клиента
    • character_set_connection - кодировка по умолчанию для всего, что в рамках соединения не имеет кодировки
    • character_set_database - кодировка по умолчанию для баз
    • character_set_filesystem - кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT . INTO OUTFILE, и т.д.)
    • character_set_results - кодировка, в которой будет выбран результат
    • character_set_server - кодировка, в которой работает сервер
    • character_set_system - кодировка, в которой задаются идентификаторы MySQL, всегда UTF8
    • character_sets_dir - папка с кодировками

    ВАЖНО: Если character_sets_dir установлена неверно, то работа с кодировками будет под угрозой. Не пытайтесь менять её значение, если вы неуверены в своих силах. Если вы системный администратор, то перед установкой лучше ознакомиться с мануалом.

    Наиболее значимые для простых пользователей следующие переменные: character_set_client, character_set_results, character_set_connection. Поскольку именно они отвечают за внесение, извлечение информации и создание таблиц/баз соответственно. Какими они могут быть?

    Любую из этих кодировок можно пользовать на свой вкус. Обычно русскоязычные пользователи предпочитают cp1251 или utf8, но по сути, неважно, в какой кодировке хранятся данные, важно, чтобы она была изначально правильно указана и данные были корректно внесены.

    Настройка кодировок

    Мануал предлагает нам три варианта задания кодировок:

    ВНИМАНИЕ. Первые два варианта работают только в рамках текущего соединения. Это значит, что при следующем подключении все настройки вернутся в начальное состояние! Чтобы не выставлять кодировку каждый раз, нужно воспользоваться третьим вариантом.

    Вариант 1 - Через names

    Ну, тут всё ясно, три самые нужные кодировки в одном )))

    Вариант 2 - Через непосредственно переменные character_set_*

    Более детальная настройка, чем names.

    Вариант 3 - Через настройки самого сервера

    Тут можно пойти двумя путями - либо через конфиг файл:

    Ещё можно при конфигурировании задать кодировку по умолчанию

    Но лучше, когда кодировка настраивается прямо в соединении.

    Что делать, если данные внесены в неправильной кодировке

    Если база/таблица/данные были созданы/внесены в кодировке отличной от нужной, то необходимо сделать следующее:

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

    Правильный вариант работы с MySQL

    Ну и на посошок:

    Выбирать данные можно в любой кодировке, так же, как и вносить, главное - правильно сообщить об этом MySQL.

    © 2021 Антон Прибора. При копировании материалов с сайта, пожалуйста, указывайте ссылку на источник.

    От автора: вы зачем полное ведро баз на мусорку несете выбрасывать? Сервер китайский попался, и все строки иероглифами отображаются? Так кодировка базы данных MySQL не та, наверное. Кто ее закодирует, она же база? Понятно! Идите, гражданин дальше. Не обращаем на него внимания. Сегодня мы познакомимся с кодировками в MySQL.

    Зачем кодировать БД?

    Сразу хочу вас заверить, что у нас ни одной «пьющей» базы нет. Поэтому данная кодировка никак не связана с чрезмерным употреблением «горячительных» напитков. Наоборот, все БД кодируются для того, чтобы пользователи (то есть мы с вами) могли прочитать содержащуюся в них информацию. Понятно, что непонятно и без полулитра не разберешься? . Давайте обойдемся пока без этого крайнего средства. Сейчас все поясню!

    Код MySQL, как и любая другая информация в вычислительной технике, задается с помощью сочетания единицы и нуля, которые образуют биты. Вспоминаете? Это ведь основы информатики, которые все мы проходили в школе.

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



    Бесплатный курс по PHP программированию

    Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

    В курсе 39 уроков | 15 часов видео | исходники для каждого урока

    На данном этапе развития всемирной паутины самыми распространенными являются 3 типа кодировок:

    Мы не будет сильно «зарываться» в теоретическом ворохе. Для получения более детальной информации перейдите по расположенным выше ссылкам на соответствующие публикации в Википедии.

    Где ее искать?

    Немного пробежимся по интерфейсу 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. Например:


    ПОМНИТЕ! Всегда создавайте бекапы, они вам помогут вернуть все данные, если пойдет что-то не так.

    Установка стандартной кодировки для Mysql

    Например, я сейчас, установлю кодировку utf8 для mysql, для этого открываем файл:

    utf8_unicode и utf8_general не то же самое, но они очень похожи.

    Преобразование кодировки для пустой базы данных

    Чтобы преобразовать кодировку в пустой БД, выполните:

    Преобразование кодировки для базы данных с данными

    Необходимо установить кодировку по умолчанию для баз данных. Это не преобразует существующие таблицы, это только установит значение по умолчанию для вновь создаваемых таблиц:

    Преобразование кодировки для таблицы с данными

    Необходимо установить кодировку по умолчанию для всех существующих таблиц и их столбцов. Это предполагает, что текущие данные на самом деле используют кодировку и чтобы изменить кодировку с одной на другую, выполните:

    Преобразование кодировки с помошью vi/vim для базы данных содержащие таблицы

    Вот ряд действий, которые вы должны предпринять если хотите сменить кодировку:

    1. Конечно же, делаем бэкап (мало ли что пойдет не так):
    2. Скопирую созданный бекап для дальнейшей правки:
    3. Открываем файл для изменения кодировки:
    4. Вводим команды:
    • :%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
    • :%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
    • :wq

    Можно выполнить команду и сменить кодировку для таблиц с командной строки:

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

    Чтобы запустить, выполните:

    Скрипт будет просить некоторую информацию, такую как логин и пароль от mysql. Если нужно будет использовать другую кодировку ( у меня прописана UTF8), то измените ее в данном скрипте, если не знаете как, напишите мне и я помогу.

    Читайте также:

        
    • Как разархивировать файл bin база камер
      •   
    • Шкаф тип 2 для хранения транспортировки и зарядки ноутбуков мобильный schoollbox
      •   
    • Как называют компьютеры которые используют информацию в сети
      •   
    • Как отремонтировать наушники с микрофоном для компьютера
      •   
    • В каком году впервые был опубликован bartender s guide
  • Контакты
  • Политика конфиденциальности