Кодировка latin1 в старой базе: перенос без кракозябр

MAATRIX

Старую базу нужно перенести на новый сервер, и вроде бы задача рутинная — снять дамп, поднять новую БД, залить данные обратно. Но если исходная база объявлена в latin1 или вообще неизвестно в чём, после переноса кириллица превращается в вопросительные знаки или ряды непонятных символов вида Раздел. Это не баг инструмента переноса — это следствие того, что текст в базе физически хранится как байты, а не как «буквы», и без точного знания, в какой кодировке эти байты на самом деле записаны, любая автоматическая конвертация угадывает наугад. Ниже — как определить реальную кодировку, а не верить настройке из SHOW CREATE TABLE, и как перенести данные так, чтобы кириллица дошла целой.

Почему конвертация «в лоб» превращает текст в кракозябры

Компьютер не хранит буквы — он хранит числа. Кодировка — это таблица соответствия между числом (байтом или последовательностью байт) и символом; подробнее о том, как байт превращается в символ на каждом этапе своего пути, разобрано в статье «Откуда берутся кракозябры: путь одного байта». Буква «Р» в кодировке Windows-1251 (CP1251) — это байт 0xD0, в KOI8-R — байт 0xF0, а в UTF-8 та же буква — уже два байта, 0xD0 0xA0. Если байт 0xD0 прочитать как CP1251, получится «Р». Если тот же байт прочитать как первую половину UTF-8-последовательности без второй половины — получится ошибка или символ-заглушка. Если его прочитать как latin1 — получится буква «Ð» (латинская D с чертой), потому что в latin1 код 0xD0 закреплён именно за ней.

Отсюда и берутся кракозябры: это не испорченные данные, а корректно прочитанные байты, но через неправильную таблицу соответствия. Сам байт не несёт информации о том, в какой кодировке он был записан — это метаданные, которые либо хранятся рядом (в заголовке файла, в настройке колонки БД), либо теряются. И здесь главная ловушка переноса старой базы: настройка кодировки, формально объявленная в БД, часто не соответствует тому, что реально лежит в байтах.

Классический пример: колонка объявлена как latin1, но много лет в неё писали кириллицу в CP1251 или UTF-8, и всё работало без единой видимой ошибки. Это возможно потому, что MySQL конвертирует байты только когда кодировка клиентского соединения отличается от кодировки колонки. Если клиент тоже был подключен как latin1 (или SET NAMES latin1 стояло в коде приложения по умолчанию ещё с нулевых), MySQL прогонял байты насквозь без преобразования — отдавал обратно то же самое, что получил. Приложение читало те же байты через тот же неверный клиентский charset и видело правильный текст. Проблема была невидима внутри одной системы и стала видна только при переносе, где новая среда честно интерпретирует колонку как объявленный latin1.

Как определить реальную кодировку, а не полагаться на настройку базы

Первое и самое важное правило переноса: не доверяйте declared charset из SHOW CREATE TABLE, \d в psql или заголовка дамп-файла. Это то, что кто-то указал когда-то — не факт, что верно. Определять нужно от байтов.

Смотрим на сырые байты через hexdump, чтобы увидеть, что реально лежит на диске:

mysql -u root -p --default-character-set=binary \
  -e "SELECT name FROM users WHERE id=1001" legacy_db \
  | hexdump -C | head

Флаг --default-character-set=binary важен: он просит MySQL отдать байты как есть, без попытки их конвертировать. Дальше смотрите на паттерн:

• Байты вида D0 90-AF или D1 80-8F парами — это почти наверняка UTF-8-кириллица (диапазон кириллических кодовых точек в UTF-8 начинается с D0/D1).
• Одиночные байты в диапазоне C0-FF без второго байта из диапазона UTF-8-продолжения (80-BF) — вероятно однобайтовая кодировка: CP1251, KOI8-R или похожая.
• Байт 3F (?) вместо ожидаемой буквы — это уже необратимая потеря: где-то раньше произошла конвертация с заменой символа на заглушку, и восстановить оригинал из этого места нельзя.

Дальше стоит прогнать через утилиту определения кодировки, которая работает по статистике встречаемости байтовых последовательностей — uchardet (пакет uchardet в Ubuntu/Debian) или chardetect из пакета chardet для Python:

mysqldump --default-character-set=binary legacy_db users > sample_raw.sql
uchardet sample_raw.sql

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

echo -n "d0 9f d0 b5 d1 82 d1 80" | xxd -r -p | iconv -f utf-8 -t utf-8

Если получили осмысленный текст на предполагаемой кодировке — гипотеза подтверждена. Если получили мусор — пробуйте следующую кодировку из списка кандидатов (iconv -l покажет все, что поддерживает система). Для баз, доставшихся от систем на базе 1С, dBase/FoxPro или старого «самописа» под Windows, частые кандидаты — CP1251 и CP866 (последняя — легаси DOS-кодировка, изредка встречается в очень старых выгрузках).

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

Однобайтовые кодировки вроде latin1 (ISO-8859-1) физически не содержат кириллических символов — в диапазоне, где в CP1251 живёт кириллица, у latin1 стоят западноевропейские буквы с диакритикой. Когда старая система хранит кириллицу «в latin1», происходит одна из трёх вещей:

Первые два случая обратимы — байты на диске физически целы, просто прочитаны не той таблицей. Третий — нет: если в байтах уже лежит 0x3F, это ASCII-вопросительный знак, а не кириллица под маской, и никакая перекодировка его не восстановит. Отличить их можно только глядя на сырые байты через hexdump.

Двойное перекодирование проверяется обратным проходом: сначала «раскодировать» текст обратно в те байты, которыми он ошибочно притворяется, затем прочитать эти байты в правильной кодировке:

echo -n "Привет" | iconv -f utf-8 -t latin1 | iconv -f utf-8 -t utf-8

Если строка после первой конвертации превращается в валидный UTF-8, значит перед вами именно двойное перекодирование, и его можно откатить программно для всего набора данных.

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

Гипотеза о реальной кодировке — это ещё не факт. Прежде чем запускать перенос всей базы (которая может весить десятки гигабайт и идти часами), проверьте гипотезу на небольшой выборке и посмотрите на результат глазами.

Выгрузите представительную выборку — не первые 100 строк подряд (там может не быть ни одной кириллической записи), а случайную с фильтром на непустое поле:

SELECT id, name, address FROM legacy_users
WHERE name REGEXP '[^\x00-\x7F]'
ORDER BY RAND()
LIMIT 200;

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

mysql -u root -p --default-character-set=binary legacy_db \
  -e "SELECT id, name, address FROM legacy_users ORDER BY RAND() LIMIT 200" \
  > sample.tsv

iconv -f cp1251 -t utf-8 sample.tsv > sample_converted.tsv

Дальше — обязательный шаг, который часто пропускают, торопясь: откройте sample_converted.tsv в терминале или редакторе с поддержкой UTF-8 и прочитайте текст глазами. Автоматическая проверка (что iconv не выдал ошибку и не осталось непреобразуемых байт) необходима, но недостаточна: iconv может успешно, без единой ошибки, перегнать данные в валидную последовательность байт UTF-8, при этом раскодировав их в совершенно неверную кодировку — почти любой набор однобайтовых значений технически укладывается в какую-то другую таблицу. Только человек, узнающий знакомые фамилии и названия городов, надёжно подтверждает, что конвертация прошла в правильную сторону, а не просто прошла без сбоя.

Если тестовая выборка выглядит корректно — расширьте её до нескольких тысяч записей и повторите проверку: сравните количество строк до и после, проверьте, что не появилось новых ? или символа-заглушки � (U+FFFD — верный признак, что байт не удалось раскодировать вообще). Только после этого имеет смысл переходить к переносу всей базы.

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

Когда исходная кодировка подтверждена на выборке, порядок действий для MySQL/MariaDB такой. Сначала снимите дамп, явно указав MySQL, что не нужно ничего конвертировать при выгрузке — отдать байты как есть:

mysqldump --default-character-set=binary \
  --single-transaction --routines --triggers \
  legacy_db > legacy_dump_raw.sql

Дальше перекодируйте сам файл дампа из подтверждённой реальной кодировки в UTF-8:

iconv -f cp1251 -t utf-8 legacy_dump_raw.sql > legacy_dump_utf8.sql

Если в дампе среди байт CP1251 попадаются и валидные UTF-8-фрагменты (не редкость для базы, живущей 10+ лет и хотя бы раз пережившей смену версий ПО), iconv упадёт на первом же байте, не вписывающемся в CP1251. В этом случае используйте флаг -c, который пропускает нераспознанные символы (годится только после того, как вы проверили выборку и убедились, что таких мест единицы, а не половина файла):

iconv -f cp1251 -t utf-8 -c legacy_dump_raw.sql > legacy_dump_utf8.sql
grep -c $'\xEF\xBF\xBD' legacy_dump_utf8.sql  # сколько строк iconv не смог раскодировать

Дальше в самом дампе нужно поправить объявление кодировки, которое mysqldump мог прописать по умолчанию (SET NAMES latin1 в начале файла), заменив на utf8mb4, и создать целевую базу с явной кодировкой и коллацией:

CREATE DATABASE new_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Загружайте дамп, подключаясь клиентом именно с utf8mb4, чтобы MySQL не попытался переконвертировать уже правильные UTF-8-байты ещё раз:

mysql --default-character-set=utf8mb4 new_db < legacy_dump_utf8.sql

Для PostgreSQL логика та же, но инструменты другие: pg_dump уважает client_encoding, поэтому его стоит явно выставить на реальную кодировку исходных данных перед выгрузкой. Если данные пришли не из PostgreSQL, а, например, из выгрузки старого 1С или dBase-подобной системы, проще сконвертировать исходный текстовый/CSV-экспорт через iconv, а затем импортировать его в уже созданную с UTF8-encoding базу через COPY — так вы полностью контролируете момент конвертации, а не полагаетесь на то, что драйвер СУБД сам правильно угадает. Общий процесс переноса между серверами, включая проверку целостности дампа, подробнее разобран в статье про миграцию базы данных между серверами.

Проверка результата и типичные грабли

После загрузки в новую базу проверка обязательна — не полагайтесь на отсутствие ошибок при импорте, потому что и MySQL, и PostgreSQL по умолчанию (без строгого режима) могут молча заменить нераспознанный символ на ? вместо того, чтобы прервать вставку с ошибкой.

Сверьте количество непустых строк с не-ASCII символами до и после переноса — оно должно совпасть:

-- на новой базе
SELECT COUNT(*) FROM users WHERE name REGEXP '[^\x00-\x7F]';

Сравните с тем же запросом на старой базе (выполненным через --default-character-set=binary, чтобы регулярное выражение сработало по сырым байтам, а не по искажённой интерпретации). Если числа разошлись — где-то в процессе часть записей превратилась в пустую строку, NULL или чистый ASCII-мусор.

Точечно сверьте десяток конкретных ID, которые вы запомнили ещё на этапе тестовой конвертации — тех же «знакомых» фамилий и городов. Это быстрее, чем гонять диффы по всей таблице, и ловит именно смысловые ошибки, а не только технические сбои конвертации.

Частые грабли, на которые стоит обратить внимание отдельно:

• Смешанные кодировки внутри одной колонки. Если приложение меняло настройки подключения к БД в разные годы, старые строки могут быть в одной кодировке, а новые — уже в другой. Один общий iconv -f X -t utf-8 в этом случае корректно обработает только часть таблицы — нужен скрипт, который определяет кодировку по дате записи (created_at до и после известной даты смены настроек) и конвертирует диапазоны раздельно.
• Бинарные/BLOB-поля, ошибочно объявленные текстовыми. Изображения, файлы, сериализованные данные в колонке TEXT при конвертации через iconv будут испорчены безвозвратно — такие поля нужно исключать из текстовой конвертации заранее.
• Коллация вместо кодировки. utf8mb4_general_ci и utf8mb4_unicode_ci — это про сортировку и сравнение строк, а не про то, как хранятся байты; спутать их с charset — источник других проблем (неверная сортировка кириллицы, неточный LIKE).
• Длина полей в новой схеме. VARCHAR(255) считает символы, а не байты, но если где-то в коде приложения лимит был завязан на байтовую длину — в UTF-8 кириллица занимает 2 байта на символ, и старая проверка «максимум 255 байт» после переноса начнёт резать текст на середине буквы.

Если в процессе всплывает подозрение, что часть данных уже утрачена ещё до переноса (замена на ? произошла годами раньше, как в истории про колонку, испортившую 300 тысяч имён) — это не проблема переноса, и никакая конвертация её не решит. Стоит заранее проверить, нет ли независимого источника тех же данных, прежде чем считать перенос завершённым.

Частые вопросы