January 5, 2013
Posted by:
gaHcep
Category:
MySQL, Unicode
Сегодня речь пойдет о 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 следующее:
[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
Предполагается, что после вставки всего этого добра (тут кстати есть противоречащие друг другу опции) в конфигурационный файл my.cnf (my.ini) магический Юникод начнет работать.
Но давайте забудем о списке и попытаемся разбираться со всеми опциями сами и начнем с самого начала. То есть с документации. Потому как все это прекрасно описано в документации MySQL на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.
Главный раздел по описанию кодировок (character sets) и их представлений (collations — используется например при сортировке) в контексте сервера, базы, таблиц — это секция 10.1.3. Specifying Character Sets and Collations.
Символьная кодировка может быть задана для:
- сервера,
- базы данных,
- таблицы и
- колонок в таблице.
Сделано это для гибкой настройки баз данных и доступа клиентов с разными кодировками. Однако, последнее не входит в область рассмотрения данного поста, поэтому будем рассматривать вариант с кодировкой UTF8 настроенной для всего по-умолчанию.
Все параметры могут быть переданы серверу тремя разными способами:
- через командную строку mysqld
- через конфигурационный файл my.cnf (my.ini)
- через опции компиляции.
Второй и третий варианты рассматриваться не будут. Тут уместно будет просто прочитать официальные доки — в каждом разделе приведены примеры конфигурации с использованием всех трех способов. Я же буду использовать первый вариант.
Кодировка (character set) и представление (collation) сервера
Секция 10.1.3.1. Server Character Set and Collation
Кодировка (characher set) — набор используемых символов.
Представление (collation) — набор правил для сравнения символов в наборе.
Тут есть несколько фундаментальных вещей которые надо понимать.
Основные параметры используемые в контексте сервера — это character_set_server и collation_server. Оба параметра влияют на определение кодировки и отображения сервера MySQL.
Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:
-
Не заданы — используются значения по умолчанию (дефолтные),
-
Заданы оба — используются указанные кодировка и ее представление,
-
Задана только кодировка — ее представление выставляется по умолчанию для данного типа кодировки. Что это значит? Для каждого типа кодировки есть ее дефолтное представление, например, дефолтная кодировка сервера —
latin1, а дефолтное отображение для нее —latin1_swedish_ci.
Посмотреть соответствие кодировки и ее дефолтного представления можно используя команду:SHOW COLLATION LIKE ‘your_character_set_name’;
Пример:
mysql> SHOW COLLATION LIKE ‘latin1%’;
+-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | Yes | 1 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | Yes | 1 | | latin1_general_cs | latin1 | 49 | | Yes | 1 | | latin1_spanish_ci | latin1 | 94 | | Yes | 1 | +-------------------+---------+----+---------+----------+---------+
Поле 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) базы данных
Секция 10.1.3.2. Database Character Set and Collation
Секция 10.1.4. Connection Character Sets and Collations
Тут есть два варианта определения кодировки и представления:
-
явно — при выполнении запроса на создание базы данных:
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'
- срабатывает опция в конфигурационном файле
- Если к базе коннектится mysql.exe клиент с пользователем без привилегии SUPER:
- срабатывает опция в конфигурационном файле
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) таблиц
Секция 10.1.3.3. Table Character Set and Collation
Тут все довольно просто. Задать кодировку и ее представление можно через команды:
CREATE TABLE t1 ( … )
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Тут главное иметь в виду, что если эти настройки не заданы, то берутся настройки базы данных (см. пред. раздел). Нам эти настройки не интересны.
Кодировка (character set) и представление (collation) колонок в таблице
Секция 10.1.3.4. Column Character Set and 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%';
Состояние среды до изменений:
mysql> SHOW VARIABLES LIKE'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
Состояние среды после изменений (в случае, если вы приконнектились не SUPER пользователем):
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
Для примера, вот отличие при соединении через mysql.exe пользователем с и без привилегии SUPER:
с привилегией:
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | **utf8_general_ci** |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
с привилегией и выполненной вручную командой ‘SET collation_connection = utf8_unicode_ci’:
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
без привилегии:
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
Поздравляю, теперь ваши база, таблицы и все в таблицах по-умолчанию в кодировке UTF8.
Ссылки
- Официальное руководство MySQL версии 5.1
- Отличие utf8_unicode_ci от utf8_general_ci
- “MySQL Character Set Support” на informit.com позволит вам больше узнать о том что есть characher set и collation.
« Previous Blog Post | Back to top | Next Blog Post »
comments powered by Disqus
Переходим с 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:
[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE init_connect ='SET collation_connection = utf8mb4_unicode_ci' init_connect ='SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
Проверяем корректность работы применимых настроек:
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
Результат:
+--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+ 10 rows in set, 1 warning (0.00 sec)
Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL
Запросы для измениния кодировки и сравнения для базы данных, таблиц и столбцов на utf8mb4.
Для базы данных:
ALTER DATABASE [db_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Для таблицы:
ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Для столбцов:
ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Восстановление и оптимизация всех таблиц
После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:
REPAIR TABLE [table_name]; OPTIMIZE TABLE [table_name];
Или с использованием команды mysqlcheck:
$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
Пример миграции для Yii2
В этом примере мы изменим кодировку для столбца content в таблице post:
/**
* @return void
* @throws yiidbException
*/
public function safeUp()
{
$sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
Yii::$app->db->createCommand($sql)->execute();
}
/**
* @return void
* @throws yiidbException
*/
public function safeDown()
{
$sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
Yii::$app->db->createCommand($sql)->execute();
}
Mysql поддерживает много кодировок и это нередко является головной болью для программистов. Самая частая проблема — кракозяблы вместо русского текста. Это происходит из за того, что текст либо лежит на сервере, либо отдается клиенту в неверной кодировке. Последнее(а иногда и первое) решается проще всего. Устанавливаем кодировку соединения (в utf8 в примере) сразу после установления соединения
|
mysql_set_charset(‘utf8’); // или mysql_query(‘SET NAMES «utf8″‘); |
Хуже, когда скрипт отдает в базу, данные в верной кодировке, а в ответ получаем кракозяблы или вопросики. Или когда часть таблиц в верной кодировке, часть нет.. В таких случаях придется разбираться детально.
|
mysql_query(«SHOW VARIABLES LIKE ‘char%'» ); /* character_set_client: latin1 character_set_connection: latin1 character_set_database: utf8 character_set_filesystem: binary character_set_results: latin1 character_set_server: cp1251 character_set_system: utf8 character_sets_dir: usrlocalmysql-5.1sharecharsets */ |
Этот запрос обязательно проверять в самом скрипте, а не в phpmyadmin, где могут быть установлены другие параметры
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, всегда UTF8character_sets_dir— папка с кодировками
По умолчанию после установки mysql сервер, который устанавливается ленивым хостеромадмином имеет кодировку latin1. Соответственно указанные выше глобальные переменные будут в latin1. Базы соответственно по умолчанию и таблицы так же. И именно на это стоит обратить в самом начале обратить внимание, чтобы проблемы не всплывали позднее.
В идеальном варианте, нам следуетпривести все отмеченные цветом кодировки к единому значению. Тогда мы просто будем избавлены от мелких ошибок с кодировкой. Фактически, если мы работаем с хостингом, то на (3) и (6) мы повлиять не сможем. Но и это не страшно если настроены остальные три параметра. Mysql умет перекодировать на лету если правильно настроена кодировка соединения.
Ну и наконец, основной вопрос, что делать если одна из mysql таблиц(или несколько) в неверной кодировке и на сайте видны кракозяблывопросики?
1. Выяснить кодировку таблицы.
|
mysql > SHOW CREATE TABLE `files` ————————————————————————— CREATE TABLE `files` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `iNode` int(10) unsigned NOT NULL, `pid` int(10) unsigned NOT NULL, `sName` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `sTitle` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`), KEY `iNode` (`iNode`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
В этой таблице поле sName в кодировке latin1, если у нас соединение в другой кодировке, то мы увидим кракозябры.
2. Поэтому дальше проверим кодировку соединения, sql запросом SHOW VARIABLES LIKE ‘character_set_client’. Замечу, что php функция mysqli_client_encoding(), нам не подойдет, так как она отображает кодировку только на момент соединения.
3. Если кодировка соединения не совпала с кодировкой одного из полей таблицы, то 2 очевидных варианта.
Если у нас все таблицы в одной кодировке, то проще поменять кодировку соединения .
А как исправить неверную кодировку поля таблицы?
Для этого выполним 2 запроса
|
ALTER TABLE files CHANGE sName sName BLOB; ALTER TABLE files CHANGE sName sName VARCHAR(128) CHARACTER SET utf8; |
Нельзя обойтись только вторым запросом. Важно выполнить оба. Первый преобразовывает данные в двоичные, второй запрос, преобразовывает данные в строковые сменив кодировку.. Т.е. по сути мы не измениили двоичные данные, мы изменили правило формирования символов. Если бы мы попробовали обойтись только вторым запросом, то получили бы ошибочный набор.
-
Доступные статьи
-
MySQL
-
Кодировки в MySQL
Работа с кодировками в MySQL 4.1.11 и выше
- Тестовая машина
- Устанавливаем MySQL
- Начало работы
- Разумные выводы
- Настройка кодировок
- Через names
- Через системные переменные
- Через настройки сервера
- Что делать, если данные внесены в неправильной кодировке
- Правильный вариант работы с MySQL
Полезность первоисточника информации трудно переоценить, поэтому не поленитесь и скачайте полный мануал от разработчиков MySQL — http://dev.mysql.com/doc/
Тестовая машина
test# uname -a FreeBSD test.dm 7.0-RELEASE FreeBSD 7.0-RELEASE #1: Fri May 9 15:40:21 YEKST 2008 zg@test.dm:/usr/obj/usr/src/sys/GATE i386 test#
Устанавливаем MySQL 5.1
test# pkg_add -r mysql51-server Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/ packages-7.0-release/Latest/mysql51-server.tbz... Done. Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/ packages-7.0-release/All/mysql-client-5.1.22.tbz... Done. Added group "mysql". Added user "mysql". ************************************************************************ Remember to run mysql_upgrade (with the optional --datadir=<dbdir> flag) the first time you start the MySQL server after an upgrade from an earlier version. ************************************************************************ test# echo mysql_enable="YES" >> /etc/rc.conf test# cp /usr/local/share/mysql/my-large.cnf /etc/my.cnf test# /usr/local/etc/rc.d/mysql-server start Starting mysql. test# sockstat | grep mysql mysql mysqld 1154 13 tcp4 *:3306 *:* mysql mysqld 1154 14 stream /tmp/mysql.sock test#
Пускай это не самый «правильный» способ установки MySQL-сервера, зато быстрый и рабочий.
Начало работы
Итак, sockstat показала, что сервер работает, а установка говорит о том, что сервер абсолютно девственный. Чем это грозит? Кодировки по умолчанию выставлены англоязычные, а значит, будут проблемы при использовании кирилицы. Но как это распознать? Проверяем:
test# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.1.22-rc-log FreeBSD port: mysql-server-5.1.22 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use test; Database changed mysql> create table `test` (`field` VARCHAR(60)); Query OK, 0 rows affected (0.01 sec) mysql> insert into `test` values ('иван'), ('родил'), ('девчёнку'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
Первым делом используем тестовую базу, которая уже есть на сервере, затем создаём в ней таблицу и вставляем в неё три слова на русском, про кодировки мы пока ничего не знаем и знать не хотим ))).
Пока всё хорошо и радужно, никаких ошибок нет, пробуем сделать выборку:
mysql> select * from `test`; +----------+ | field | +----------+ | иван | | родил | | девчёнку | +----------+ 3 rows in set (0.01 sec) mysql> select * from `test` where `field` like "иван"; +-------+ | field | +-------+ | иван | +-------+ 1 row in set (0.00 sec)
Как видно, запросы работают абсолютно корректно, так где же грабли?… Оказывается мы на них уже стоим:
mysql> select * from `test` order by `field` DESC; +----------+ | field | +----------+ | девчёнку | | родил | | иван | +----------+ 3 rows in set (0.01 sec)
Запрос на выборку с обратной сортировкой привёл к тому, что записи просто вывелись в обратном порядке, но не по алфавиту… До удара граблей остаются считанные секунды, но пока растянем удовольствие 
Сперва ответим на вопрос — почему поля не сортируются по алфавиту? У MySQL имеется мощный и богатый механизм для работы с интернациональными наборами символов, но.. но откуда MySQL узнает, что наши символы — есть русский алфавит, мы же качали английскую версию? Ничего не остаётся, как идти ковырять мануал на предмет кодировок…
После того, как загрузился 16-метровый мануал, можно не полениться и прочитать первые пару-тройку страниц с оглавлением )), а можно просто сделать поиск на предмет charset или character set. Не суть важно, но через некоторое время можно найти раздел 9.1.2. Character Sets and Collations in MySQL, в котором написано много и интересно, а, главное, содержательно про то, каким образом можно и нужно работать с кодировками.
Расставляя точки над и, Character Set — транслируется как «кодировка», а Collation — сравнение. В чём разница? Сравнение — это правила сравнения букв кодировки. Сравнения работают только в рамках кодировки, и нельзя сравнивать данные в латинице по правилам кирилицы. Поясню на примере: мы, как увидим позже, внесли данные в таблицу на латинице, а сортировать нужно на кирилице, для чего можно использовать ключевое слово collate:
mysql> select * from `test` order by `field` collate cp1251_general_ci DESC; ERROR 1253 (42000): COLLATION 'cp1251_general_ci' is not valid for CHARACTER SET 'latin1' mysql>
MySQL отказывается это делать… но почему? Потому, что latin1 не поддерживает сравнение в кирилице, а доступные «сравнения» можно увидеть так:
mysql> show collation like 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | Yes | 1 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | Yes | 1 | | latin1_general_cs | latin1 | 49 | | Yes | 1 | | latin1_spanish_ci | latin1 | 94 | | Yes | 1 | +-------------------+---------+----+---------+----------+---------+ 8 rows in set (0.00 sec) mysql>
Ни о какой кирилице не может идти и речи… Куда копать?.. В создание таблицы!
mysql> show create table `test`; +-------+-------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------+ | test | CREATE TABLE `test` ( `field` varchar(60) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------+ 1 row in set (0.00 sec)
Ага! По-умолчанию при создании таблицы была взята кодировка latin1, значит, если мы изменим таблицу и укажем ей, что надо использовать кирилистическую кодировку, то всё заработает?… В мануале написан пример про изменение кодировки таблицы, используем его:
mysql> alter table `test` charset "cp1251"; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0
Ок! Проверяем, что получилось…
mysql> select * from `test` order by `field` collate cp1251_general_ci DESC; ERROR 1253 (42000): COLLATION 'cp1251_general_ci' is not valid for CHARACTER SET 'latin1'
Хм.. опять та же ошибка, но откуда ей взяться?!..
mysql> show create table `test`; +-------+-------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------+ | test | CREATE TABLE `test` ( `field` varchar(60) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 | +-------+-------------------------------------------------+ 1 row in set (0.00 sec)
Ого, структура таблицы резко изменилась, теперь у неё задана одна кодировка, а у поля совсем другая.. :(( Порыв ещё мануал, можно изменить и кодировку столбца:
mysql> alter table `test` modify `field` varchar(60) charset "cp1251"; Query OK, 3 rows affected, 3 warnings (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table `test`; +-------+------------------------------------------+ | Table | Create Table | +-------+------------------------------------------+ | test | CREATE TABLE `test` ( `field` varchar(60) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 | +-------+------------------------------------------+ 1 row in set (0.01 sec)
Ну вот!!! Злой кодировки latin1 нет и в помине, можно проверять наш роддом )))
mysql> select * from `test` order by `field` collate cp1251_general_ci DESC; +----------+ | field | +----------+ | ???????? | | ????? | | ???? | +----------+ 3 rows in set (0.00 sec)
И вот тот страшный удар граблями, который так долго оттягивался! Внимательный читатель мог заметить, что когда была сделана попытка принудительно сменить кодировку столбца, содержащего данные в latin1, то на каждую запись, содержащую русские буквы, у MySQL был варнинг! Это был крик о том, что сервер не знает, каким образом можно перевести данные из latin1 в cp1251, ну и лучшего способа, чем заменить символы не latin1 вопросиками, он не нашёл :))). Роддом безвозвратно потерян потому, что теперь вместо кирилицы в базе содержатся вопросики..
Вопросиков можно было избежать
На самом деле, ситуация, когда изначально выставлена неправильная кодировка, встречается сплошь и рядом. Симптомы можно выявить следующим образом:
mysql> show variables like "char%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.02 sec)
Именно эти переменные отвечают за дефолтные значения кодировок.
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, всегда UTF8character_sets_dir— папка с кодировками
ВАЖНО: Если character_sets_dir установлена неверно, то работа с кодировками будет под угрозой. Не пытайтесь менять её значение, если вы неуверены в своих силах. Если вы системный администратор, то перед установкой лучше ознакомиться с мануалом.
Наиболее значимые для простых пользователей следующие переменные: character_set_client, character_set_results, character_set_connection. Поскольку именно они отвечают за внесение, извлечение информации и создание таблиц/баз соответственно. Какими они могут быть?
mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | +----------+-----------------------------+---------------------+--------+ 26 rows in set (0.00 sec)
Любую из этих кодировок можно пользовать на свой вкус. Обычно русскоязычные пользователи предпочитают cp1251 или utf8, но по сути, неважно, в какой кодировке хранятся данные, важно, чтобы она была изначально правильно указана и данные были корректно внесены.
Настройка кодировок
Мануал предлагает нам три варианта задания кодировок:
- Через names
- Через непосредственно переменные character_set_*
- Через настройки самого сервера
ВНИМАНИЕ!!! Первые два варианта работают только в рамках текущего соединения. Это значит, что при следующем подключении все настройки вернутся в начальное состояние! Чтобы не выставлять кодировку каждый раз, нужно воспользоваться третьим вариантом.
Вариант 1 — Через names
mysql> set names 'cp1251'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | cp1251 | | character_set_connection | cp1251 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp1251 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.02 sec)
Ну, тут всё ясно, три самые нужные кодировки в одном )))
Вариант 2 — Через непосредственно переменные character_set_*
mysql> set @@character_set_client='cp1251'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | cp1251 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.01 sec)
Более детальная настройка, чем names.
Вариант 3 — Через настройки самого сервера
Тут можно пойти двумя путями — либо через конфиг файл:
---- Файл my.cnf [client] # Для местного клиента default-character-set=cp1251 .... [mysqld] # Для всего сервера default-character-set=cp1251 ....
либо
shell> mysqld --character-set-server=cp1251
Ещё можно при конфигурировании задать кодировку по умолчанию
shell> ./configure --with-charset=latin1
Но лучше, когда кодировка настраивается прямо в соединении.
Что делать, если данные внесены в неправильной кодировке
Если база/таблица/данные были созданы/внесены в кодировке отличной от нужной, то необходимо сделать следующее:
- Создать бэкап базы данных
- Создать текстовый дамп базы в SQL-запросах (mysqldump или PhpMyAdmin)
- С помощью текстового редактора исправить вхождения неверной кодировки на нужную (а лучше попросту удалить всю информацию о кодировках и сравнениях)
- Удалить базу/таблицу
- Выставить нужную кодирвку на клиента/соединение
- Импортировать данные исправленного SQL-дампа
Этот вариант подходит почти для всех случаев, за исключением некоторых особых ситуаций, например, когда сравнение, выставленное по-умолчанию, не уместно для некоторых полей. Пример — поле для хранения пароля, необходимо сравнивать его с учётом регистра, тогда как по-умолчанию выставляется сравнение без учёта регистра.
mysql> show variables like 'char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.02 sec) ## Кодировки выставлены неверно, нужно их настроить mysql> set names 'koi8r'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | koi8r | | character_set_connection | koi8r | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | koi8r | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.02 sec) ## Я работаю через koi8r, поэтому и выставляю её, ## но данные в таблице буду хранить в cp1251 mysql> create table `test2` (`field` varchar(60)) charset cp1251; Query OK, 0 rows affected (0.01 sec) ## Проверяем, всё ли в порядке mysql> show create table `test2`; +-------+--------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------+ | test2 | CREATE TABLE `test2` ( `field` varchar(60) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 +-------+--------------------------------------------+ 1 row in set (0.01 sec) ## Вносим данные mysql> insert into `test2` values ('и раз'), ('Два'),('три'), ('И ять'), ('шесть'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 ## Проверяем сортировки ## В обычном сравнении "И" и "и" одинаковы, поэтому ## сравнение идёт до первого отличного символа mysql> select * from `test2` order by `field` collate cp1251_general_ci ASC; +-------+ | field | +-------+ | Два | | и раз | | И ять | | три | | шесть | +-------+ 5 rows in set (0.01 sec) ## В бинарном сравнении "И" меньше чем "и", поскольку у неё код меньше mysql> select * from `test2` order by `field` collate cp1251_bin ASC; +-------+ | field | +-------+ | Два | | И ять | | и раз | | три | | шесть | +-------+ 5 rows in set (0.00 sec)
Таким образом, клиент работает в KOI8-R, но данные хранятся в cp1251, MySQL знает об этом и делает перекодировку на лету.
Ну и на посошок:
mysql> set character_set_results='cp1251'; Query OK, 0 rows affected (0.00 sec) mysql> select * from `test2`; +-------+ | field | +-------+ | Х ПЮГ | | дБЮ | | РПХ | | х ЪРЭ | | ЬЕЯРЭ | +-------+ 5 rows in set (0.00 sec)
Выбирать данные можно в любой кодировке, так же, как и вносить, главное — правильно сообщить об этом MySQL.
I found out in my MySQL I have different global and database «character_set_client» variable. show variables shows
character_set_client | utf8
while show global variables shows
character_set_client | latin1
I believe the first one is database setting. How can I change it? When I do
set character_set_client='latin1'
it is changed only for session. When I disconnect and connect again it is set back to ‘utf8’. How can I change it so that it stays at ‘latin1’?
asked Dec 13, 2013 at 15:35
The idea is to force the character set on the server side and tell it to skip negotiation regarding character set.
See MySQL manual(V5.7 at time this was written).
Three parameters are vital for correct operation between client and server regarding that matter. They are set in the [mysqld] section of the .cnf file:
[mysqld]
#...
#UTF8 stuff
skip-character-set-client-handshake
collation-server=latin1_general_ci
character-set-server=latin1
Side note, on this day and age one should always use UTF8, which would require setting the following lines:
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
Side-side note, since the release of MySQL 8.0 MySQL manual, the correct values would be:
collation-server=utf8mb4_0900_ai_ci
character-set-server=utf8mb4
answered Nov 13, 2017 at 17:18
2
If you want to change the default for the database (and thus for all users of the database) and you are the administrator of the database, you can either compile it into the mysqld build:
configure --with-charset=latin1
or include it on the command line whenever your server starts/restarts:
mysqld --character-set-server=latin1
See this reference.
If you don’t want to change the defaults for the server but you do want to change your default when connecting, then you can use the client command line parameter --default-character-set as described here.
They may also be a way to apply that setting in your .my.cnf file, but I don’t know it offhand.
answered Dec 13, 2013 at 17:50
dg99dg99
5,2842 gold badges36 silver badges49 bronze badges
I found out in my MySQL I have different global and database «character_set_client» variable. show variables shows
character_set_client | utf8
while show global variables shows
character_set_client | latin1
I believe the first one is database setting. How can I change it? When I do
set character_set_client='latin1'
it is changed only for session. When I disconnect and connect again it is set back to ‘utf8’. How can I change it so that it stays at ‘latin1’?
asked Dec 13, 2013 at 15:35
The idea is to force the character set on the server side and tell it to skip negotiation regarding character set.
See MySQL manual(V5.7 at time this was written).
Three parameters are vital for correct operation between client and server regarding that matter. They are set in the [mysqld] section of the .cnf file:
[mysqld]
#...
#UTF8 stuff
skip-character-set-client-handshake
collation-server=latin1_general_ci
character-set-server=latin1
Side note, on this day and age one should always use UTF8, which would require setting the following lines:
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
Side-side note, since the release of MySQL 8.0 MySQL manual, the correct values would be:
collation-server=utf8mb4_0900_ai_ci
character-set-server=utf8mb4
answered Nov 13, 2017 at 17:18
2
If you want to change the default for the database (and thus for all users of the database) and you are the administrator of the database, you can either compile it into the mysqld build:
configure --with-charset=latin1
or include it on the command line whenever your server starts/restarts:
mysqld --character-set-server=latin1
See this reference.
If you don’t want to change the defaults for the server but you do want to change your default when connecting, then you can use the client command line parameter --default-character-set as described here.
They may also be a way to apply that setting in your .my.cnf file, but I don’t know it offhand.
answered Dec 13, 2013 at 17:50
dg99dg99
5,2842 gold badges36 silver badges49 bronze badges
This question already has a lot of answers, but Mathias Bynens mentioned that ‘utf8mb4’ should be used instead of ‘utf8’ in order to have better UTF-8 support (‘utf8’ does not support 4 byte characters, fields are truncated on insert). I consider this to be an important difference. So here is yet another answer on how to set the default character set and collation. One that’ll allow you to insert a pile of poo (💩).
This works on MySQL 5.5.35.
Note, that some of the settings may be optional. As I’m not entirely sure that I haven’t forgotten anything, I’ll make this answer a community wiki.
Old Settings
mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
Config
# 💩 𝌆
# UTF-8 should be used instead of Latin1. Obviously.
# NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4
New Settings
mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
character_set_system is always utf8.
This won’t affect existing tables, it’s just the default setting (used for new tables).
The following ALTER code can be used to convert an existing table (without the dump-restore workaround):
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Edit:
On a MySQL 5.0 server: character_set_client, character_set_connection, character_set_results, collation_connection remain at latin1. Issuing SET NAMES utf8 (utf8mb4 not available in that version) sets those to utf8 as well.
Caveat:
If you had a utf8 table with an index column of type VARCHAR(255), it can’t be converted in some cases, because the maximum key length is exceeded (Specified key was too long; max key length is 767 bytes.). If possible, reduce the column size from 255 to 191 (because 191 * 4 = 764 < 767 < 192 * 4 = 768). After that, the table can be converted.