Кириллица в MySQL
или о настройках кодировкок

После чтения форумов по MySQL у меня сложилось впечатление, что всё прогрессивное человечество бьётся над вопросами настройки не-английских алфавитов в MySQL. Наверное, потому, что это тот редкий случай, когда не очень помогает документация по MySQL. Нужная информация, конечно, в ней содержится (иначе откуда бы я узнал то, что излагаю здесь?), но сильно разбросана по разным параграфам. Надеюсь, данная статья восполнит этот досадный пробел — пожалуй, единственное препятствие на пути использования этого замечательного SQL-сервера.

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

Кодировка, она же charset, она же character set

Это таблица, которая каждому символу ставит в соответствие число (одно- или двухбайтное).

Именно эти числа хранятся в текстовых полях MySQL. Кстати, эти поля бывают сортируемые(char и varchar) и несортируемые (text), причём у сортируемых нужно указывать максимальную длину. Чтоб лучше в этом разобраться, перейдёт к следующему вопросу.

Collations, или как нам отсортировать строки

Для простоты понимания представим, что у нас всего две буквы: а и б. Как мы будем сортировать строки, составленные из них?

Первое, что мы должны будем сделать — задать порядок символов, т.е. «кто за кем стоит». Сделать это просто: букве а поставим в соответсвие 1, букве б — 2

Обратите внимание: эти числа не есть кодировка (charset). Эти числа задают порядок символов при сортировке, или, говоря терминами MySQL, collation.

Как мы отсортируем строки равной длины, очевидно:

ааа 111
ааб 112
абб 122
Если строки неравной длины, мы решим проблему, доставив нули справа:

ааа 111
аб 120
б 200
Алгоритм очевиден: нули добавляются справа до тех пор, пока длина строки не станет равной максимальной. Теперь Вам понятно, зачем для полей char и varchar необходимо указывать максимальный размер строки.

И ещё Вам понятно, что чем больше максимальный размер строки, тем страшнее число, в которое преобразуется строка для сортировки. Тем более, что в реальности число, сопоставляемое символу согласно collation, вовсе не однозначно, как в нашем примере. Приблизим пример к реальности, и увидим, какими числами придётся оперировать при мксимальном числе символов 3 (три):

ааа A0A0A016 10 526 880
аб A0A10016 10 526 976
б A1000016 10 551 296
Вывод очевиден: нужно экономно задавать макимальный размер строки для поля, чтобы не тормозить без надобности сортировку и поиск по нему.

Вам теперь также понятно, почему длинные тексты не сортируются.

Чуть сложнее понять, почему для одной кодировки (charset) возможно существование нескольких collation. По двум причинам:

Одна кодировка может «обслуживать» несколько языков.
Кроме символов национальных алфавитов, в кодировках присутствуют цифры и прочие символы.

Какие кодировки поддерживает Ваша версия MySQL?

Задайте этот вопрос своему серверу:

SHOW CHARACTER SET;

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

Кириллических кодировок версия 5.0.67 предлагает четыре:

+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
Я считаю, что предпочтительней всего использовать cp1251.

* * *

Итак, мы уже знаем, что такое charset и collation. Осталось лишь рассмотреть, как и для каких объектов они устанавливаются в MySQL.

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

Установка кодировки по умолчанию на сервере

Что значит кодировка по умолчанию?

Дело в том, что мы можем явно установить кодировку для следующих объектов:

таблиц (опять же по умолчанию – об этом далее);
полей таблиц текстового типа ;
Переменных текстового типа в функциях и сохранённых процедурах;
А если кодировка для какого-то из этих объектов явно не указана (так поступают почти все сочинители сайтов, рассчитывая на админов хостинга)? В этом случае действует систем умолчаний, для которой корневой является вот эта самая кодировка сервера по умолчанию. Т.е. если не указано вообще никаких кодировок для объектов -- будет установлена кодировка по умолчанию на сервере

Кодировка по умолчанию на сервере MySQL задаётся тремя параметрами:

default-character-set – кодировка (charset) по умолчанию. Она дует установлена для всех объектов (таблиц, полей и др.), где кодировка явно не задана
default-collation
character-sets-dir – папка с файлами кодировок. По умолчанию это подпапка /share/charsets/
Есть три способа установить эти параметры для сервера:

Задать их в командной строке mysqld (или mysqld-nt)
Создание файла параметров (option file) my.cnf или my.ini
(Unix / Linux) перекомиляция
Рассмотрим первые два способа более подробно.

Командная строка mysqld (или mysqld-nt)

Командная строка может иметь следующий вид:

mysqld --default-character-set=кодировка --default-collation=порядок сортировки --character-sets-dir=папка с кодировками

Идентичный формат строки и для mysqld-nt:

mysqld-nt --default-character-set=кодировка --default-collation=порядок сортировки --character-sets-dir=папка с кодировками

Пример (для Windows):

mysqld --default-character-set=cp1251 --default-collation= cp1251_general_ci --character-sets-dir=c:\mysql\share\charsets

Файл параметров (option file) my.cnf или my.ini

Файл my.cnf согласно документации должен работать и в Linux, и в Windows, но как ни вертел я его в Windows, он у меня так и не заработал (не знаю, чья это недоработка. Возможно, я что-то не так делал). Так что я советую в Windows использовать my.ini. Он должен находиться в корневой папке MySQL (если Вы при инстялляции не меняли папку для установки, то это что-то вроде C:\program files\MySQL\MySQL 5.0 Server ).

Приведу пример конфигурационного файла мy.ini, позволяющий подерживать кириллицу. В этом файле позволяются комментарии, начинающиеся с # — чем я и воспользуюсь для отметки строк, отвечающих за кириллицу.

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

Итак, конфигурационный файл мy.ini.

[client]
port = 3306

#Конфигурационные параметры для сервера MySQL
[mysqld]
port = 3306



socket = /tmp/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
# Установка кириллицы на сервере
default-character-set=cp1251 #Указание кодировки

#Указание пути к папке кодировок (скорректируйте для своего сервера!)
character-sets-dir=g:/mysql/share/charsets

server-id = 1

# Конфигурационные параметры для программы резервного копирования
[mysqldump]
quick
max_allowed_packet = 16M
# Установка кириллицы на сервере
default-character-set=cp1251 #Указание кодировки

#Указание пути к папке кодировок (скорректируйте для своего сервера!)
character-sets-dir=g:/mysql/share/charsets

# Конфигурационные параметры для программы-клиента mysql.exe
[mysql]
no-auto-rehash
# Установка кириллицы на сервере
default-character-set=cp1251 #Указание кодировки

#Указание пути к папке кодировок (скорректируйте для своего сервера!)
character-sets-dir=g:/mysql/share/charsets

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

(Unix / Linux) перекомиляция

Я этот способ не осуществлял, поэтому отсылаю читателя к документации:

9.1.3.1. Server Character Set and Collation

Установка кодировки по умолчанию на клиенте

Под клиентом в данном случае понимается программа, направляющая MySQL запросы и получающая ответы.

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

Пример 1. php-скрипт

Если в таблицах есть текстовые поля в кодировке cp1251, то для корректной обработки данных их них нужно для MySQL-сессии установить кодировку:

mysql_query("set names cp1251",$conn);

где $conn — указатель на сессию MySQL (MySQL link identifier).

Пример 2. Программа mysql

В состав MySQL входит программа mysql для работы с сервером через командную строку. Хоть она и сходит в поставку сервера MySQL — это тоже клиент: она направляет серверу запросы и получает от него ответы.

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

mysql> set names cp1251;
Query OK, 0 rows affected (0.05 sec)

Установка кодировки для таблиц и полей таблиц

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

mysql> create table test
-> (
-> id int primary key,
-> txt1 varchar(10), /* кодировка этого поля будет cp1251 */
-> txt2 varchar(10) character set latin1 /* кодировка этого поля будет latin1 */
-> ) default charset=cp1251;

Query OK, 0 rows affected (0.03 sec)

mysql>
В этом примере мы установили для таблицы test кодировку по умолчанию cp1251, и поскольку для поля txt1 кодировка явно не біла установлена, для него будет установлена кодировка cp1251. Заметьте — вовсе не установленная на сервере по умолчанию: действует более «близкое» умолчание — для таблицы.

Этот пример также иллюстрирует, как можно явно прописать кодировку для поля таблицы (в нашем примере для txt2).

Если для таблицы не устновить default charset — он будет установлен сервером с использовании кодировки по умолчанию на сервере.

А не установить ли везде utf-8?

Хороший вопрос. Я его для себя ещё не решил.

С одной стороны, для того и придуман Unicode — чтоб была одна кодировка на все случаи жизни.

С другой стороны, меня смущает вопрос сортировки. То, что она будет медленней для utf-8 — и к бабке не ходи. Раз символы кодируются двумя байтами, а не одним — из материала параграфа Collations, или как нам отсортировать строки Вам станет ясно, что разрядость числа, в которое преобразуется строка, возрастает колоссально.

Да и есть ли сортировка, скажем, кириллицы? Ответ MySQL 5.0.67 меня не обнадёжил:


mysql> SHOW COLLATION like 'utf%';
+--------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
+--------------------+---------+-----+---------+----------+---------+
Как-то не бросается в глаза русская или украинская сортировка. Так что я повременю пока с переходом на Юникод...