Работа MySQL со строками

Начиная с версии 4.0, сервер MySQL поддерживает преобразование кодировок символов. Эта статья рассказывает о том, что такое кодировки, сопоставления и о том, как работать с ними применительно к серверу MySQL.

Русскоязычные кодировки

Традиционно в России использовались четыре кодировки для представления русских символов:

• CP866 (DOS)
• KOI8-R
• MacCyrillic
• CP1251 (Windows)

Эти четыре кодировки имеют как общие черты, так и различия. Во-первых, все они используют один байт информации для хранения одного символа. Во-вторых, они совместимы с кодировкой cp1251, т.е. у них совпадают первые 128 байт таблицы кодировки. Различаются же значения символов с установленным верхним битом (т.е. со значениями 128-255).

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

Так была создана кодировка Unicode (UCS-2). Разумеется, невозможно разместить очень большое количество символов в 8 битах, поэтому в этой кодировке используются 2 байта (т.е. 16 бит) на каждый символ. Строки в этой кодировке, безусловно, занимают больше места, чем в старых кодировках, но зато приложения, использующие такие строки, очень легко переносятся на другие языки.

Не смотря на введение UCS-2, некоторые проблемы все-же остались. Во-первых, это уже указанное увеличение длины строки. Люди, использующие только латинские символы, не могли для себя понять, почему они должны использовать 16 бит для обозначения символов, которые уместятся в 7 бит. Во-вторых, 65536 символов все равно не хватает для обозначения всех используемых символов на планете.

Для преодоления этих трудностей, была создана кодировка с переменной длиной символа UTF-8. В этой кодировке используется 1 байт для обозначения первых 128 символов (т.е. символов, входящих в latin1). Если установлен верхний бит первого байта, то используется второй байт (т.е. русские буквы в этой кодировке занимают 2 байта). Если установлен верхний бит второго байта, то используется третий байт (и туда входят разнообразные иероглифы и другие восточные символы) и так далее.

Такая кодировка во-первых, может разместить в себе произвольное количество символов (т.к. она позволяет увеличивать количество байт динамическим образом). Во-вторых, для английского языка используется 1 байт, и это очень приятно для англоговорящих стран. Восточные страны не очень любят эту кодировку, т.к. им приходится использовать 3 байта для обозначения своих символов (в UCS-2 они используют только два).

Сопоставления символов

Некоторые приложения (включая и MySQL, о котором речь пойдет далее) используют не только кодировки, но и сопоставления символов. Дело в том, что некоторые символы некоторые языки считают одинаковыми (а иногда считаются одинаковыми символ или последовательность символов). Например, «ü» в немецком языке может быть записан как «ue». С другой стороны, в шведском языке тот же символ может быть записан как «uy».

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

Сопоставление влияет именно на то, как приложение работает с совокупностью символов, оно влияет на порядок сортировки и на сравнение символов. Разумеется, сопоставление зависит от кодировки символов.

Кодировки символов в MySQL

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

CREATE TABLE enctest (
    str1 CHAR(10) CHARSET koi8r,
    str2 CHAR(15) COLLATE utf8_general_ci
);

В данном примере создается таблица с двумя полями, одно из которых будет храниться в кодировке KOI8-R (и с сопоставлением по-умолчанию). Второе поле будет иметь сопоставление utf8_general_ci и кодировку UTF-8 (кодировка определяется по сопоставлению, т.к. сопоставление зависит от нее).

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

SHOW CHARACTER SET;
SHOW COLLATION;

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

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

CREATE TABLE enctest2 (
    str1 CHAR(10),
    str2 CHAR(15)
) DEFAULT CHARSET cp1251;

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

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

ALTER TABLE enctest2 MODIFY str1 CHAR(10) CHARSET utf8;

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

Сопоставления в MySQL

В MySQL для каждой кодировки есть по крайней мере одно сопоставление (а обычно, несколько). Для того, чтобы понять, чем они отличаются, они все имеют очень подробные названия.

Название каждого сопоставления начинается с названия соответствующей кодировки (например, utf8_). Далее идет спецификация сопоставления (чаще всего, идет простое сопоставление, general, не идентифицирующее буквы) и указание на чувствительность к регистру (cs — case sensitive — чувствительно к регистру, ci — case insensitive — не чувствительно).

Отдельно стоит отметить бинарные сопоставления (binary). Если строки сопоставляются бинарным образом, то между ними не делается никаких сопоставлений и преобразований. Такие строки не будут преобразованы командой SET NAMES (см. далее). Бинарные сопоставления особенно удобно использовать при переходе с MySQL 3.23, который не поддерживал кодировки и который указывает, что все таблицы находятся в кодировке latin1 (не смотря на то, что таблицы могут содержать данные, скажем, в KOI8-R).

Клиентские кодировки MySQL

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

SHOW VARIABLES LIKE 'char%';

Вы можете указывать или переменные по-одиночке, или изменять их сразу большим набором (как требуется в большинстве случаев):

SET NAMES koi8r;

После того, как сервер получит такую команду, он будет ожидать, что Вы будете передавать ему все строки в кодировке KOI8-R и будет автоматически переводить выводимые им строки в эту кодировку.

Это удобно тогда, когда сервер по-умолчанию работает не в той кодировке, которую Вы ожидаете (например, в KOI8-R, а Вы хотите выводить информацию на сайте в CP1251).

Хранение информации в MySQL

MySQL хранит информацию в соответствии с тем, какую кодировку вы указали. Так сервер выделяет по 1 байту на каждый символ для однобайтовых кодировок (при этом CHAR(10) занимает всегда 10 байт, VARCHAR(10) занимает от 1 до 11 байт в зависимости от длины строки, 1 лишний байт тратится на хранение длины строки).

Для кодировки UCS-2 сервер выделяет по 2 байта на каждый символ. Для кодировки UTF-8 сервер выделяет разное количество байт для разных символов (в соответствии с кодировкой) в случае VARCHAR и 3 байта на каждый символ в случае CHAR. Таким образом, в UTF-8 строка CHAR(10) всегда занимает 30 байт, а VARCHAR(10) — от 1 до 31 байта.При этом ни в каком случае при применении UTF-8 сервер не может хранить символы длиной от 4 байт (впрочем, это не накладывает особых ограничений).