MySQL utf8mb4 и правила сравнения: utf8 против utf8mb4, конвертация

В MySQL utf8 - это не UTF-8. Это псевдоним для utf8mb3, трёхбайтового подмножества, которое не может хранить ни одного символа за пределами базовой многоязычной плоскости (BMP) - а это все эмодзи, многие иероглифы CJK и ряд математических и исторических письменностей. utf8mb4 - настоящий UTF-8, и начиная с MySQL 8.0 это кодировка по умолчанию, с utf8mb4_0900_ai_ci в качестве правила сравнения по умолчанию. Если вы создаёте базу данных сегодня, используйте utf8mb4 везде: в базе данных, таблицах, столбцах и соединении. Если вам досталась база на utf8 или latin1, её можно конвертировать, но способ зависит от того, правильно ли в ней хранятся данные, и ошибка здесь - именно то, из-за чего текст превращается в é.

Это руководство объясняет разницу, как выбрать правило сравнения (collation), какие настройки соединения должны совпадать и как безопасно конвертировать существующую базу данных на MySQL 8.0 или 8.4 LTS.

utf8, utf8mb3 и utf8mb4#

UTF-8 кодирует каждый символ одним-четырьмя байтами. ASCII занимает один, большинство европейских и ближневосточных букв (включая кириллицу) - два, большая часть китайского, японского и корейского - три, а всё выше U+FFFF - эмодзи, редкие иероглифы CJK, музыкальные символы - четыре. Когда MySQL в 2002 году добавил поддержку Unicode, он ограничил свой utf8 тремя байтами на символ ради экономии места, и это решение с тех пор постоянно подводит людей.

Начиная с MySQL 8.0.30 сервер показывает utf8mb3, а не utf8, в SHOW CREATE TABLE и information_schema, так что ситуация наконец стала видна. В планах - сделать utf8 псевдонимом utf8mb4 в одном из будущих релизов; пока этого не произошло, никогда не пишите utf8 в схеме, файле конфигурации или строке подключения. Пишите utf8mb4.

Попытка сохранить эмодзи в столбце utf8mb3 при строгом режиме SQL по умолчанию громко проваливается:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1

\xF0 - первый байт четырёхбайтовой последовательности. Без строгого режима MySQL обрезает символ или заменяет его на ? и продолжает работу, что хуже: данные молча портятся. Если вы видите вопросительные знаки там, где пользователи вводили эмодзи, значит, либо столбец, либо соединение не utf8mb4.

Правила сравнения: что означают имена#

Кодировка говорит, как символы хранятся. Правило сравнения говорит, как они сравниваются и сортируются: равно ли a и A, равно ли e и é и куда идёт ß. У каждого символьного столбца есть и то и другое. Имя правила сравнения описывает его поведение:

Правила 0900 основаны на версии 9.0.0 Unicode Collation Algorithm и одновременно более правильные и, в MySQL 8, более быстрые, чем старые unicode_ci. От старых правил они отличаются двумя важными вещами:

• Символы за пределами BMP сортируются и сравниваются правильно. В utf8mb4_unicode_ci и utf8mb4_general_ci все четырёхбайтовые символы равны друг другу - столбец с уникальным индексом не может хранить одновременно эмодзи суши и эмодзи пива, потому что для правила сравнения это один и тот же символ. Правила 0900 считают их разными.
• Конечные пробелы учитываются. Правила 0900 относятся к типу NO PAD: 'abc' и 'abc ' различаются. Старые - PAD SPACE и считают их равными. Это меняет результаты сравнений и проверок уникальности на данных со случайными конечными пробелами.

Как выбрать

Для большинства приложений оставьте utf8mb4_0900_ai_ci по умолчанию. Сопоставление без учёта регистра и диакритики - это то, чего люди ждут от поля поиска и формы входа: Ana@Example.com находит ana@example.com, cafe находит café.

Цена нечувствительности проявляется в уникальных индексах. С ai_ci слова resume и résumé равны, поэтому уникальный индекс по столбцу с именем пользователя отклонит второе. Для имён пользователей это обычно желательно - не даёт выдавать себя за другого с помощью диакритики, - а для других данных иногда оказывается сюрпризом. Для столбцов, которым нужно точное совпадение (токены, хэши, коды с учётом регистра), используйте utf8mb4_0900_bin или utf8mb4_0900_as_cs только для этого столбца:

CREATE TABLE api_tokens (  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  token VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,  name VARCHAR(100) NOT NULL,  UNIQUE KEY uq_token (token)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Токены и хэши - чистый ASCII, поэтому ascii с ascii_bin хранит их по байту на символ и сравнивает точно. Языковые правила сравнения оправданы, когда порядок сортировки важен для пользователей одного языка: турецкие i с точкой и без, шведские å ä ö после z, испанская ñ.

Кодировки на каждом уровне#

Кодировку и правило сравнения можно задать на пяти уровнях, и каждый наследует от вышестоящего, если значение не указано:

1. Сервер: character_set_server и collation_server, значения по умолчанию для новых баз данных.
2. База данных: CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, значения по умолчанию для новых таблиц.
3. Таблица: DEFAULT CHARSET=..., значение по умолчанию для новых столбцов.
4. Столбец: то, что реально используется для хранения.
5. Соединение: как текст передаётся между клиентом и сервером.

Что хранится, решает уровень столбца. Изменение значения по умолчанию для базы данных через ALTER DATABASE ничего не меняет в существующих таблицах; оно влияет только на таблицы, созданные после. На этом попадаются те, кто «сконвертировал» базу и всё равно получает ошибки Incorrect string value.

-- Every text column in a database that is not utf8mb4SELECT table_name, column_name, character_set_name, collation_nameFROM information_schema.columnsWHERE table_schema = 'appdb'  AND character_set_name IS NOT NULL  AND character_set_name <> 'utf8mb4'ORDER BY table_name, ordinal_position;-- Tables whose default differsSELECT table_name, table_collationFROM information_schema.tablesWHERE table_schema = 'appdb' AND table_collation NOT LIKE 'utf8mb4%';

Кодировка соединения#

Сервер хранит байты в кодировке столбца, но ему нужно знать, в какой кодировке присылает данные клиент. Это описывают три сессионные переменные: character_set_client (что отправляет клиент), character_set_connection (в какой кодировке интерпретируются операторы) и character_set_results (в какой кодировке отправляются результаты). SET NAMES utf8mb4 задаёт все три, а у каждого драйвера есть опция, которая делает это при подключении:

SHOW SESSION VARIABLES LIKE 'character_set_%';SHOW SESSION VARIABLES LIKE 'collation_connection';

Соединение, объявляющее latin1, когда приложение на самом деле отправляет UTF-8, - первопричина большинства кракозябр: MySQL добросовестно конвертирует каждый байт UTF-8-текста так, будто это символ Latin-1, и é (два байта в UTF-8) приходит как é. Задавайте кодировку в строке подключения, а не запросом SET NAMES после подключения, чтобы пул при переподключении её не потерял. Полную настройку подключения для каждой среды показывают статьи PHP PDO и MySQL и удалённые подключения к MySQL.

Размер индекса и фольклор про VARCHAR(191)#

При расчёте длины ключа индекса utf8mb4 резервирует четыре байта на символ. Максимальный ключ индекса InnoDB - 3072 байта при формате строк DYNAMIC, используемом по умолчанию с 5.7, что позволяет полный индекс по VARCHAR(768). Старые форматы COMPACT и REDUNDANT, а также MySQL 5.6 по умолчанию ограничивали ключи 767 байтами - 191 символом utf8mb4, - и поэтому так много фреймворков и руководств до сих пор используют VARCHAR(191) для индексируемых столбцов.

В MySQL 8 с таблицами DYNAMIC 191 не нужен. Используйте ту длину, которая нужна вашим данным. Если старая таблица отказывается создавать индекс с ошибкой Specified key was too long; max key length is 767 bytes, проверьте её формат строк:

SELECT table_name, row_format FROM information_schema.tablesWHERE table_schema = 'appdb' AND row_format IN ('Compact', 'Redundant');ALTER TABLE legacy_table ROW_FORMAT=DYNAMIC;

Конвертация существующей базы данных#

Сначала выясните, что на самом деле лежит в столбцах, потому что ситуации бывают две, и очень разные:

• Правильно хранимые данные в неправильной кодировке. Столбец latin1 с текстом в Latin-1 или столбец utf8mb3 с корректным текстом. MySQL знает, что это за символы, и может их сконвертировать. Используйте CONVERT TO.
• Данные с неверной меткой. Столбец latin1, который на самом деле содержит байты UTF-8, потому что приложение годами писало UTF-8 через соединение latin1. В приложении всё выглядит нормально (оно читает данные обратно тем же неправильным способом), а в phpMyAdmin или дампе - неправильно. CONVERT TO закодировал бы их дважды. Здесь нужен описанный ниже обход через двоичный тип.

Чтобы понять, какой у вас случай, посмотрите на известное значение с диакритикой через HEX():

SELECT name, HEX(name) FROM customers WHERE id = 42;-- 'José' correctly stored in latin1:          4A6F73E9-- 'José' as UTF-8 bytes in a latin1 column:   4A6F73C3A9

E9 - это é в Latin-1. C3A9 - это é в UTF-8, лежащий в столбце, который считает, что хранит два символа Latin-1.

Правильно хранимые данные: CONVERT TO

ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;ALTER TABLE customers  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CONVERT TO меняет значение по умолчанию таблицы и каждый текстовый столбец, конвертируя данные. Он перестраивает таблицу алгоритмом копирования, поэтому на всё это время запись блокируется, а на диске нужно свободное место под полную копию. Конвертация из latin1 может также повысить столбцы TEXT до MEDIUMTEXT, чтобы они вмещали то же количество символов; проверьте схему после, если вашей ORM это важно. Сгенерируйте операторы для каждой таблицы:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 ',              'COLLATE utf8mb4_0900_ai_ci;') AS stmtFROM information_schema.tablesWHERE table_schema = 'appdb' AND table_type = 'BASE TABLE';

Неверная метка: обход через двоичный тип

Чтобы MySQL переинтерпретировал байты, не конвертируя их, нужно пройти через двоичный тип, у которого нет кодировки:

ALTER TABLE customers MODIFY name VARBINARY(255);ALTER TABLE customers MODIFY name VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

VARCHAR проходит через VARBINARY, TEXT - через BLOB, MEDIUMTEXT - через MEDIUMBLOB. На обратном пути указывайте полное определение столбца, включая NOT NULL и значения по умолчанию, потому что MODIFY его заменяет. Индексы по столбцу сохраняются. Затем исправьте кодировку соединения приложения, прежде чем оно что-либо запишет, иначе новые строки будут неправильными уже в обратную сторону.

План конвертации для работающего приложения#

Операторы ALTER - самая простая часть. Сохранить работу живого сайта помогает порядок действий вокруг них.

1. Инвентаризация. Выполните запросы к information_schema выше и составьте список всех таблиц и столбцов не в utf8mb4, с размерами. Маленькие таблицы конвертируются за секунды; таблица на несколько гигабайт может занять столько времени, что понадобится окно обслуживания, потому что CONVERT TO блокирует запись, пока копирует.
2. Диагностика. Для каждой таблицы с не-ASCII текстом проверьте несколько известных значений через HEX() и решите, хранятся ли они правильно или с неверной меткой. В одной базе данных может встретиться и то и другое, если настройки соединения приложения когда-то менялись.
3. Сначала исправьте соединение, где это безопасно. Если данные хранятся правильно, переключение соединения приложения на utf8mb4 до конвертации таблиц безвредно: MySQL сам конвертирует между соединением и столбцом. Если метка неверна, смена соединения и обход через двоичный тип должны происходить вместе, с остановленной между ними записью.
4. Отрепетируйте на копии. Восстановите вчерашний дамп во временную базу данных, проведите полную конвертацию и проверьте те же известные значения, порядок сортировки и поиск. Засеките время - это и есть ваше окно.
5. Проведите её по-настоящему, таблица за таблицей, самые большие в конце, со свежим бэкапом, снятым непосредственно перед этим.
6. Задайте значения по умолчанию на уровне базы данных, чтобы новые таблицы сразу рождались правильными, и закрепите кодировку и правило сравнения в миграциях, чтобы фреймворк не создал втихую следующую таблицу с чем-то другим.
7. Поищите остатки: хранимые процедуры и представления несут кодировку, которая действовала при их создании, и SHOW CREATE PROCEDURE её показывает. Пересоздайте их после конвертации.

Фреймворки заслуживают отдельного взгляда. config/database.php в Laravel задаёт charset и collation для соединения и для таблиц, которые создают его миграции; старые проекты часто закрепляют utf8mb4_unicode_ci, что нормально, но должно совпадать со всеми остальными таблицами, чтобы избежать ошибки смешения правил сравнения, описанной ниже. MySQL-бэкенд Django использует ключ charset в OPTIONS. WordPress задаёт DB_CHARSET и DB_COLLATE в wp-config.php и использует utf8mb4 начиная с версии 4.2; у старого сайта, существовавшего до этого обновления, могут остаться таблицы в utf8mb3, которые процедура обновления пропустила из-за ограничений длины индекса. В любом случае настройка фреймворка и реальные определения таблиц должны совпадать - настройка применяется только к тому, что фреймворк создаст в следующий раз.

Ошибки правил сравнения и как их исправить#

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and(utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

Два столбца с разными правилами сравнения сравниваются в соединении или WHERE. Обычно так бывает после частичной миграции: старые таблицы в utf8mb4_unicode_ci, новые - в значении по умолчанию 8.0. Настоящее решение - привести оставшиеся таблицы к одному правилу сравнения; быстрое - COLLATE utf8mb4_0900_ai_ci на одной стороне сравнения, но оно не даёт использовать индекс на этой стороне.

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Дамп MySQL 8 загружается в MySQL 5.7 или сервер другого семейства. Правила 0900 существуют только в MySQL 8. По возможности восстанавливайте в MySQL 8; иначе замените имя правила сравнения по всему дампу. О несовпадении версий в целом - статья перенос MySQL на новый хост.

FAQ#

Что использовать: utf8mb4_unicode_ci или utf8mb4_0900_ai_ci?

На MySQL 8 - utf8mb4_0900_ai_ci. Оно следует более новой версии правил Unicode, различает эмодзи и другие четырёхбайтовые символы и быстрее в реализации MySQL 8. Оставляйте unicode_ci только там, где базу данных нужно загружать ещё и в MySQL 5.7 или в сервер другого семейства, где нет правил 0900.

Занимает ли utf8mb4 больше места, чем utf8?

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

Почему я вижу вопросительные знаки вместо эмодзи?

Текст прошёл через что-то, что не является utf8mb4, - столбец, таблицу или чаще всего соединение, - а MySQL был не в строгом режиме и поэтому заменил символы, вместо того чтобы отклонить их. Проверьте столбец через SHOW CREATE TABLE, соединение через SHOW SESSION VARIABLES LIKE 'character_set_%' и задайте charset=utf8mb4 в драйвере.

Можно ли сделать один запрос чувствительным к регистру, не меняя столбец?

Да: WHERE code = 'AbC' COLLATE utf8mb4_0900_as_cs. Это работает, но не может использовать индекс, построенный с правилом сравнения самого столбца, поэтому выполняется сканирование. Для столбца, который всегда сравнивается с учётом регистра, лучше смените его правило сравнения.

Конвертирует ли ALTER DATABASE мои таблицы?

Нет. Он меняет только значение по умолчанию для таблиц, созданных после. Существующие таблицы и их столбцы сохраняют свою кодировку, пока вы не выполните ALTER TABLE ... CONVERT TO для каждой.