База данных символов: принципы кодирования и хранения строк

Причина обычно лежит на стыке трёх уровней: свойств символов в Unicode, байтового представления строки и правил сравнения в СУБД.

Чтобы хранение символьных данных в СУБД оставалось предсказуемым, нужно разделять эти уровни. Unicode задаёт кодовые точки и свойства символов. UTF-8 преобразует их в байты. Набор символов и collation в базе определяют, что можно сохранить и как строки будут сравниваться и сортироваться.

Архитектура Unicode: от кодовой точки до байтов

Unicode адресует кодовые точки в диапазоне от U+0000 до U+10FFFF. Кодовая точка — идентификатор символа в стандарте. Она сама по себе не задаёт, сколько места займёт символ в памяти или в строке базы данных.

Свойства символов описывает Unicode Character Database, или UCD. Это набор данных, включающий, например, файлы UnicodeData.txt и PropList.txt. Спецификация UAX #44 определяет структуру и содержание этой базы. В ней описаны свойства кодовых точек, сведения для нормализации и сопоставления регистров.

Для разработчика UCD важна как источник правил обработки текста. Но СУБД не превращает кодовую точку в «символ длиной один байт». Она хранит последовательность байтов согласно выбранной кодировке. UTF-8 использует от одного до четырёх байтов на кодовую точку:

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

Отсюда возникает практическая ошибка в приложениях и схемах баз данных: число символов принимают за число байтов. Для строки переменной длины эти величины не совпадают. Ограничение поля в символах и расход физического места — разные характеристики.

Кодовая точка отвечает на вопрос, какой символ представлен. Кодировка отвечает на вопрос, какими байтами он записан.

Есть ещё один нюанс: пользовательское представление не всегда соответствует одной кодовой точке. Видимый знак может складываться из нескольких кодовых точек, например из базового символа и комбинируемого диакритического знака. Эмодзи тоже могут состоять из последовательностей code points, но последовательности формируются разными механизмами. Флаги обычно кодируются парой региональных индикаторов. Оттенок кожи задаётся модификатором, который следует за подходящим эмодзи. Некоторые составные эмодзи, в том числе изображения семей, объединяются с помощью zero-width joiner. Селекторы представления могут задавать текстовый или эмодзи-стиль символа, но не обозначают оттенок кожи.

Поэтому подсчёт байтов, кодовых точек и воспринимаемых пользователем знаков может дать три разных результата. При проектировании схемы нельзя подменять один вид длины другим. Это особенно заметно в ограничениях длины поля, проверке пользовательского ввода и логике обрезки текста. Если приложение считает «символы» иначе, чем база, слишком короткое поле может отклонить допустимую строку, а ограничение на длину в интерфейсе не гарантирует, что значение поместится в колонку.

Отдельно стоит упомянуть нормализацию. Unicode определяет формы NFC, NFD, NFKC и NFKD, которые приводят некоторые строки с одинаковым или совместимым видимым содержанием к согласованному представлению. Например, буква с диакритическим знаком может быть представлена одним кодовым знаком или последовательностью из буквы и комбинируемого знака. Если приложение не нормализует текст перед сравнением, визуально идентичные строки могут различаться на уровне байтов и не совпасть при поиске. СУБД обычно не нормализует все строки автоматически при записи. Поэтому нормализация становится ответственностью приложения либо явно выбранного механизма в схеме.

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

Ловушка utf8mb3 в MySQL

В MySQL название utf8 исторически вводит в заблуждение. Оно обозначает псевдоним utf8mb3, а этот набор символов ограничен тремя байтами на символ. Четырёхбайтовые символы, в том числе многие эмодзи и некоторые редкие иероглифы, в такую колонку записать нельзя.

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

Для полного диапазона UTF-8 в MySQL используют utf8mb4. В MySQL 8.0 этот набор символов установлен по умолчанию; стандартное правило сравнения в этой конфигурации — utf8mb4_0900_ai_ci. Для существующей системы одного изменения настройки клиента недостаточно: нужно проверить таблицы, колонки, соединения и миграции. Старые таблицы могли сохранить прежние параметры независимо от настроек сервера.

Перед миграцией полезно пройти по следующему порядку:

1. Зафиксировать фактические наборы символов и collations на уровне сервера, базы, таблиц и колонок.

2. Найти колонки с utf8 и выяснить, используют ли приложения символы за пределами BMP.

3. Проверить настройки соединений и драйверов. Кодировка на клиенте должна согласовываться с кодировкой хранения.

4. Оценить ограничения индексов и размеры строк после перехода на utf8mb4, особенно если схема использует широкие индексируемые поля.

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

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

Для колонок фиксированной длины есть отдельный расчёт. При переходе CHAR(N) с однобайтового latin1 на utf8mb4 серверу может потребоваться резерв до N × 4 байт на строку. Это оценка худшего случая для размера, а не утверждение, что каждая строка фактически занимает вчетверо больше. Тем не менее широкие фиксированные поля способны повлиять на структуру таблиц и индексов, поэтому такой переход следует проверять на реальной схеме. В MySQL также есть предел размера строки в 65535 байт без учёта TEXT и BLOB. Миграция широких колонок на utf8mb4 может вытолкнуть схему за этот лимит.

UTF-8, длина строки и выбор типа поля

При выборе типа данных для строк обычно сравнивают CHAR, VARCHAR и текстовые типы. Решение зависит от характера значения и ограничений конкретной СУБД. Универсального ответа о том, в каких единицах задаётся N в VARCHAR(N), нет: семантика параметра различается между платформами, и это важный источник ошибок при переносе схем.

Из таблицы видно, что параметр N в VARCHAR(N) не везде означает одно и то же. В SQL Server предел задаётся в байтах, и это меняет расчёт. При строках в UTF-8 многобайтовые символы быстрее расходуют этот лимит, чем ASCII. При переходе схем между движками нельзя полагаться на привычное понимание «символьного» N: нужно смотреть документацию целевой платформы и фактический набор символов колонки.

В MySQL и PostgreSQL параметр VARCHAR(N) задаёт предел в символах. В SQL Server он ограничивает объём в байтах. При миграции схем это меняет максимальное содержимое колонки.

CHAR(N) подходит для значений с устойчивой фиксированной длиной, если поведение дополнения пробелами соответствует требованиям приложения. Для переменных строк обычно применяют VARCHAR. Для больших текстов выбирают типы с соответствующими лимитами и механизмами хранения, которые зависят от СУБД. Универсального правила вроде «все строки хранить в TEXT» нет: тип влияет на ограничения, индексы, обработку значений и работу запросов.

ASCII остаётся полезным для значений с ограниченным алфавитом: технических ключей, некоторых идентификаторов и протокольных маркеров. Переводить весь пользовательский текст в ASCII ради экономии нельзя: это либо исключит часть данных, либо потребует экранирования и усложнит обработку. Для многоязычного текста стандартный выбор обычно строят вокруг полного UTF-8 и проверенных ограничений схемы.

Отдельного внимания заслуживают Unicode-типы с фиксированным размером кодовой единицы. В SQL Server NCHAR и NVARCHAR хранят данные в UTF-16. Для символов дополнительных плоскостей UTF-16 использует суррогатные пары, то есть одной кодовой точке соответствуют две 16-битные кодовые единицы. Поэтому длина такой строки не всегда совпадает с числом кодовых точек или видимых знаков. Для пользовательского текста это редко проблема, пока приложение корректно работает с парами суррогатов. Но при подсчёте эмодзи или редких иероглифов результаты могут отличаться от ожиданий, основанных на BMP. Латиница в UTF-16 также занимает две байтовые кодовые единицы на символ, что создаёт дополнительный расход по сравнению с однобайтовым представлением ASCII.

Отдельный практический вопрос — индексы по многобайтовым колонкам. В MySQL для InnoDB максимальная длина ключа индекса зависит от версии и конфигурации; в распространённых конфигурациях с лимитом 3072 байта индексируемая колонка VARCHAR в utf8mb4 может упереться в него раньше, чем ожидает разработчик, исходящий из количества символов. Реальные ограничения нужно проверять по версии сервера, формату таблицы и составу индекса. Поэтому выбор N для индексируемых колонок связан не только с логическим контрактом данных, но и с физическим пределом ключа.

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

Кодировка и collation решают разные задачи

Набор символов описывает представление текста в байтах и допустимый репертуар символов. Collation задаёт правила сравнения и сортировки строк. Смешение этих понятий ведёт к ошибкам в поиске и ограничениях уникальности.

Суффиксы правил сопоставления обычно указывают, как обрабатываются регистр и диакритика:

• ci означает сравнение без учёта регистра;
• cs учитывает регистр;
• ai не учитывает диакритические знаки;
• as учитывает их.

Конкретный результат зависит от выбранного правила сопоставления и СУБД. Если колонка использует сравнение без учёта регистра, значения, различающиеся только регистром, могут считаться равными в операциях сравнения и при проверке уникальности. Нечувствительность к диакритике также меняет результаты поиска и сортировки.

Это влияет на схему, а не только на отображение списка. Например, уникальный индекс на имени пользователя должен соответствовать политике идентичности аккаунта. Если приложение считает User и user разными логинами, а collation базы — нет, вставка второго значения может конфликтовать с первым. Если база различает их, а приложение нет, дубли могут пройти проверку на одном уровне и быть отвергнуты на другом.

Collation нужно выбирать для конкретного поля и его семантики. Единая настройка для всех колонок упрощает администрирование, но не всегда совпадает с правилами предметной области. Технический идентификатор, отображаемое имя и поисковый текст могут требовать разных способов сравнения. В MySQL collation с суффиксом _bin использует побайтовое сравнение, что удобно для технических полей, где нежелательно объединять значения по правилам регистра или диакритики.

Нужно учитывать и то, что collation задаёт не просто удобный способ сортировки. Она влияет на результаты равенства и может менять поведение уникального индекса. Если правила сопоставления меняют или обновляют, перед изменением стоит проверить, не окажутся ли ранее различимые значения равными по новым правилам. Иначе при построении индекса могут обнаружиться дубликаты, которых прежняя конфигурация не считала дубликатами.

Настройка локализации в PostgreSQL

PostgreSQL позволяет создавать правила сортировки с разными провайдерами локализации: libc, icu или встроенным builtin. В качестве локалей могут использоваться, например, C, C.UTF-8 и PG_UNICODE_FAST. Выбор влияет на сортировку и сравнение, но не меняет базовый принцип, по которому строка представлена в кодировке.

Провайдеры решают практические задачи по-разному. libc использует системные таблицы локали и исторически совместим с поведением многих UNIX-систем. icu подключает библиотеку International Components for Unicode, которая предоставляет правила сортировки и сопоставления Unicode. builtin — компактный провайдер для базовых правил без зависимости от внешних библиотек.

При выборе ICU не стоит исходить из того, что его версия закреплена внутри PostgreSQL и никогда не меняется вместе с окружением. Версия ICU зависит от используемой библиотеки и способа сборки и установки PostgreSQL. В конкретной системе она может быть связана с библиотекой, установленной в окружении, и обновляться независимо от СУБД. После обновления могут измениться правила сортировки, поэтому стабильность поведения нужно проверять в фактической конфигурации развёртывания.

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

Отдельная эксплуатационная задача — воспроизводимость. Результат сортировки может зависеть от реализации провайдера и версии локализационных данных. Для сервисов, где порядок выдачи влияет на пагинацию, отчёты или синхронизацию, обновление окружения нужно тестировать на контрольном наборе строк. Иначе после деплоя запросы могут возвращать записи в другом порядке, хотя содержимое таблицы не менялось. Если сортировка используется в индексе, также нужно учесть влияние обновления правил на корректность его использования.

Проверка схемы перед деплоем

Ошибки с символами часто проходят тесты, потому что тестовые данные ограничены латиницей. В набор проверки следует включить кириллицу, символы с диакритикой, комбинируемые знаки, CJK и символы, занимающие четыре байта в UTF-8. Для эмодзи полезно отдельно проверить флаг, знак с модификатором оттенка кожи и последовательность с zero-width joiner: это разные случаи, и поддержка одного не гарантирует корректную обработку остальных.

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

Для аудита достаточно начать с нескольких конкретных вопросов:

• Совпадают ли кодировки клиента, соединения, базы и колонок?
• Поддерживает ли MySQL utf8mb4 там, где нужны символы за пределами BMP?
• Соответствуют ли ограничения длины реальным требованиям, если байтовая длина переменной строки зависит от кодировки и движка?
• Совпадает ли collation с правилами сравнения в приложении?
• Не используют ли уникальные индексы правила, которые объединяют значения, считающиеся разными на уровне продукта?
• Укладывается ли индексируемая колонка в физический лимит ключа при выбранной кодировке?
• Проверяется ли поведение после обновления СУБД, драйвера или провайдера локализации?

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

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