utf8 срещу utf8mb4 в MySQL: Пълното техническо ръководство

MySQL's utf8 набор от символи е неправилно наименование — той не е истинска UTF-8 имплементация. Той кодира символи, използвайки само 1 до 3 байта, което означава, че мълчаливо изпуска или отхвърля всяка Unicode кодова точка над U+FFFF, включително всички емоджи и значителна част от допълнителните CJK символи. utf8mb4 е правилната, пълна UTF-8 имплементация на MySQL, поддържаща 1 до 4 байта на символ и пълния Unicode диапазон. За всяка производствена база данни, създадена след 2010 г., utf8mb4 е единственият разумен избор.

Това ръководство обяснява точно защо това разграничение е важно, къде оригиналният дизайн на utf8 е сгрешил, как да мигрирате безопасно и как да конфигурирате MySQL правилно на ниво сървър, база данни, таблица и връзка.

Основният проблем: Защо MySQL's utf8 е счупен по дизайн

Стандартът за кодиране UTF-8 (RFC 3629) дефинира схема с променлива ширина, която използва 1 до 4 байта за представяне на всяка валидна Unicode кодова точка — над 1,1 милиона възможни символа. Когато MySQL въведе своя `utf8` набор от символи във версия 4.1, имплементацията беше умишлено ограничена до 3 байта на символ. Това беше преднамерен инженерен компромис, а не пропуск.

По онова време форматът на редовете в InnoDB налагаше ограничение от 767 байта за префиксите на индексните ключове. Поддръжката на 4-байтови символи щеше да намали максималната дължина на индексирания префикс за `VARCHAR` колони, създавайки проблеми с индексната съвместимост. Ограничението от 3 байта беше прагматично заобикаляне, което се превърна в дългосрочен проблем.

Практическата последица: всяка Unicode кодова точка в Допълнителната многоезична равнина (SMP) — кодови точки U+10000 и по-горе — не може да бъде съхранена в `utf8` колона. Това включва:

• Всички стандартни емоджи (U+1F600 и по-нататък)
• Математически буквено-цифрови символи (U+1D400–U+1D7FF)
• Символи за музикална нотация
• Исторически писмености като Linear B, готическа и клинопис
• Допълнителни CJK унифицирани идеографи (U+20000–U+2A6DF)
• Определени символи на валути и технически оператори, добавени в последните версии на Unicode

Когато приложение се опита да вмъкне 4-байтов символ в `utf8` колона, MySQL или връща грешка `Incorrect string value`, или, ако `sql_mode` е разрешителен, мълчаливо съкращава данните. Мълчаливото съкращаване е може би по-опасният резултат — вашето приложение не получава грешка, но данните ви са повредени.

utf8mb4: Правилната имплементация

MySQL въведе utf8mb4 във версия 5.5.3 (издадена 2010 г.) специално за да отстрани този недостатък. Суфиксът `mb4` означава „многобайтов, максимум 4 байта.” Той е строго надмножество на `utf8` — всеки символ, представим в `utf8`, е идентично представим в `utf8mb4`. Няма загуба на данни при мигриране от `utf8` към `utf8mb4`.

utf8mb4 се съответства директно на стандарта RFC 3629 UTF-8. Той обработва пълното Unicode кодово пространство от U+0000 до U+10FFFF без ограничения.

utf8 срещу utf8mb4: Сравнение на функциите

Избор на съпоставяне в utf8mb4

Изборът на utf8mb4 като набор от символи е само половината от решението. Съпоставянето определя как се сравняват, сортират и индексират низовете. Грешното съпоставяне причинява фино, трудно за отстраняване поведение на заявките.

utf8mb4_unicode_ci

Базирано на алгоритъма за Unicode съпоставяне (UCA). Обработва правилно езиково-специфичните правила за сортиране. Малко по-бавно от `utf8mb4_general_ci` поради по-сложна логика за сравнение, но разликата в производителността е незначителна на съвременен хардуер.

utf8mb4_general_ci

Опростено съпоставяне, което не имплементира напълно UCA. По-бързо в тестовете от началото на 2010-те, но предимството в скоростта е без значение на съвременните CPU. Обработва някои гранични случаи неправилно — например, третира определени немски символи като еквивалентни, когато не трябва да бъдат. Избягвайте за нови проекти.

utf8mb4_0900_ai_ci

Налично в MySQL 8.0+. Базирано на Unicode 9.0 с нечувствително към ударения (`ai`) и нечувствително към регистъра (`ci`) сравнение. Това е препоръчителното по подразбиране за MySQL 8.0 и по-нови версии. То е по-бързо от `utf8mb4_unicode_ci` и по-точно.

utf8mb4_bin

Двоично сравнение — чувствително към регистъра, чувствително към ударенията, без специфични за локала правила. Използвайте, когато се нуждаете от точно съответствие на байтово ниво, например за хешове на пароли или идентификатори, чувствителни към регистъра.

Препоръка: Използвайте `utf8mb4_0900_ai_ci` на MySQL 8.0+. Използвайте `utf8mb4_unicode_ci` на MySQL 5.7 и по-ранни версии.

Последици за съхранението и индексите

Честа загриженост при мигриране от utf8 към utf8mb4 е допълнителното съхранение. На практика въздействието е минимално:

• ASCII символите (U+0000–U+007F) все още заемат точно 1 байт и в двете кодирания.
• Повечето латински, гръцки, кирилски, арабски и еврейски символи заемат 2 байта и в двете кодирания.
• CJK символите в BMP заемат 3 байта и в двете кодирания.
• Само допълнителните символи (емоджи, допълнителни CJK) изискват 4 байта — и те просто не можеха да бъдат представени в utf8 преди.

Реалната загриженост за индексите е ограничението от 767 байта за InnoDB индексния префикс при по-стари конфигурации. С utf8mb4, при най-лошия случай от 4 байта на символ, 191-символен `VARCHAR` индексен префикс достига тавана от 767 байта. С `utf8`, същият таван позволяваше 255 символа. Ако имате `VARCHAR(255)` колони с пълни индекси на колони, може да срещнете грешки `Specified key was too long` по време на миграцията.

Решения:

• Активирайте `innodb_large_prefix = ON` (MySQL 5.6/5.7), за да повишите ограничението до 3072 байта.
• Използвайте `ROW_FORMAT=DYNAMIC` или `ROW_FORMAT=COMPRESSED` за засегнатите таблици.
• В MySQL 8.0, `innodb_large_prefix` е активиран по подразбиране и параметърът е премахнат.
• Съкратете индексните префикси: `INDEX (column(191))` вместо `INDEX (column(255))`.

Това е най-честата точка на неуспех при миграция и тази, която е най-слабо документирана в основните ръководства.

Как да мигрирате MySQL база данни от utf8 към utf8mb4

Миграцията е проста, но изисква прецизност. Пропускането на което и да е ниво — сървър, база данни, таблица или връзка — оставя приложението ви мълчаливо да се върне към старото кодиране.

Стъпка 1: Архивирайте базата данни

Никога не променяйте кодирането на символи в работеща база данни без верифициран архив.

“`bash

mysqldump -u username -p –single-transaction –routines –triggers

database_name > database_backup_$(date +%F).sql

“`

Флагът `–single-transaction` осигурява последователна снимка за InnoDB таблици без заключване. Съхранете архива на място, отделно от сървъра на базата данни, преди да продължите.

Стъпка 2: Актуализирайте конфигурацията на MySQL сървъра

Редактирайте `/etc/mysql/my.cnf` или `/etc/mysql/mysql.conf.d/mysqld.cnf` в зависимост от вашата дистрибуция:

“`ini

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

For MySQL 5.6/5.7 only — remove on MySQL 8.0

innodb_large_prefix = ON

innodb_file_format = Barracuda

innodb_file_per_table = ON

“`

Рестартирайте MySQL:

“`bash

sudo systemctl restart mysql

“`

Стъпка 3: Конвертирайте базата данни

“`sql

ALTER DATABASE database_name

CHARACTER SET = utf8mb4

COLLATE = utf8mb4_unicode_ci;

“`

Стъпка 4: Конвертирайте всички таблици

Генерирайте и изпълнете `ALTER TABLE` изрази за всяка таблица. Изпълнението им ръчно при големи схеми е склонно към грешки. Използвайте тази заявка за автоматично генериране на изразите:

“`sql

SELECT CONCAT(

'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'

)

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'database_name'

AND TABLE_TYPE = 'BASE TABLE';

“`

Изпълнете всеки генериран израз. Синтаксисът `CONVERT TO CHARACTER SET` променя едновременно стандарта на таблицата и всички съществуващи символни колони в една операция.

Стъпка 5: Поправете грешките в дължината на индекса

Ако срещнете `Specified key was too long; max key length is 767 bytes`, идентифицирайте проблемния индекс:

“`sql

— Change full-column index to prefix index

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name ADD INDEX index_name (column_name(191));

“`

За WordPress бази данни конкретно, колоната `option_name` на таблицата `wp_options` и колоната `meta_key` на `wp_postmeta` са чести източници на тази грешка.

Стъпка 6: Проверете конвертирането

“`sql

— Check server-level variables

SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%';

— Check a specific table

SHOW CREATE TABLE table_nameG

— Check all columns in a database

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'database_name'

AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');

“`

Всяка стойност `CHARACTER_SET_NAME` трябва да показва `utf8mb4`.

Стъпка 7: Актуализирайте низовете за връзка на приложението

Кодирането на сървъра и схемата не означава нищо, ако приложението ви се свързва с грешния набор от символи. Кодирането на ниво връзка замества стандарта на сървъра.

PHP (PDO):

“`php

$dsn = 'mysql:host=localhost;dbname=database_name;charset=utf8mb4';

$pdo = new PDO($dsn, $user, $pass, [

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"

]);

“`

PHP (MySQLi):

“`php

$mysqli = new mysqli('localhost', $user, $pass, $db);

$mysqli->set_charset('utf8mb4');

“`

Python (mysql-connector-python):

“`python

cnx = mysql.connector.connect(

host='localhost', user=user, password=pass,

database=db, charset='utf8mb4', collation='utf8mb4_unicode_ci'

)

“`

Node.js (mysql2):

“`javascript

const pool = mysql2.createPool({

host: 'localhost', user: user, password: pass,

database: db, charset: 'utf8mb4'

});

“`

Неуспехът да се зададе наборът от символи на връзката е единствената най-честа причина емоджи да продължават да не се вмъкват след привидно завършена миграция.

Специфични съображения за WordPress

WordPress използва utf8mb4 като набор от символи по подразбиране от версия 4.2 (април 2015 г.). Ако използвате WordPress инсталация на по-стара база данни, която никога не е мигрирана, файлът `wp-config.php` може все още да съдържа:

“`php

define('DB_CHARSET', 'utf8');

“`

Променете това на:

“`php

define('DB_CHARSET', 'utf8mb4');

define('DB_COLLATE', 'utf8mb4_unicode_ci');

“`

WordPress също включва вградена рутина за надграждане (`maybe_convert_table_to_utf8mb4()`), която се изпълнява по време на актуализации на ядрото. Въпреки това, тази рутина не винаги обхваща всяка таблица, особено тези, създадени от плъгини. Ръчният подход с `ALTER TABLE`, описан по-горе, е по-надежден.

В среда за VPS Хостинг с root достъп можете да автоматизирате целия този процес с shell скрипт и да го планирате като еднократна cron задача, давайки ви пълен контрол върху времето и регистрирането.

Съображения за производителността

Въздействието върху производителността на utf8mb4 спрямо utf8 е незначително за по-голямата част от натоварванията:

• Заявки за четене: Няма измерима разлика за BMP символи. Допълнителните символи изискват един допълнителен байт I/O, който се поглъща от кешираното в буферния пул.
• Заявки за запис: Идентични за ASCII и BMP съдържание. Незначително по-високи за допълнителни символи.
• Операции с индекси: Намалената максимална дължина на префикса (191 срещу 255 символа за индекси с пълна ширина) може да засегне плановете на заявките, ако имате индекси на пълни колони върху дълги `VARCHAR` колони. Одитирайте индексите си преди и след миграцията.
• Памет: MySQL разпределя буфери с фиксирана ширина за операции с низове въз основа на максималните байтове на символ. Преминаването от utf8 (максимум 3 байта) към utf8mb4 (максимум 4 байта) увеличава паметта, разпределена за буфери за сортиране в паметта и временни таблици с приблизително 33% за операции с много низове. На Dedicated Server с достатъчно RAM, това е без значение. В среда с ограничена памет, наблюдавайте `sort_buffer_size` и `tmp_table_size` след миграцията.

Кога utf8 все още е приемливо

Съществува тесен набор от легитимни причини да запазите `utf8`:

• Строга съвместимост с наследени системи: Приложение, използващо неподдържан ORM или драйвер за база данни, който не може да обработва 4-байтови символи. Това е проблем с техническия дълг, а не причина да запазите utf8 за неопределено време.
• Бази данни само за четене за архивиране: Ако базата данни никога няма да получава нови записи и съществуващите данни не съдържат допълнителни символи, миграцията добавя риск без полза.
• Строги ограничения за съхранение: В крайни гранични случаи — вградени системи или среди с тежко ограничен капацитет — пределната разлика в съхранението може да има значение. Това не се отнася за никакъв стандартен сценарий за уеб хостинг.

Във всеки друг случай utf8mb4 е правилният избор. Аргументът, че utf8 спестява пространство за съхранение, е технически верен само за допълнителни символи, които така или иначе не можеха да бъдат представени в utf8. Не спестявате пространство за данни, които не можехте да съхраните.

Избор на правилната хостинг среда за MySQL utf8mb4

Правилната конфигурация на utf8mb4 изисква достъп до конфигурационния файл на MySQL сървъра (`my.cnf`). Това изключва повечето среди за споделен хостинг, където не можете да променяте променливи на ниво сървър.

За пълен контрол върху кодирането на символи в MySQL, съпоставянето, настройките на InnoDB и параметрите на връзката, ви е необходим или план за VPS Хостинг с root достъп, или Dedicated Server. И двете ви дават директен достъп до `/etc/mysql/my.cnf`, възможността да рестартирате MySQL услугата и свободата да конфигурирате `innodb_large_prefix`, `ROW_FORMAT` и други параметри, които влияят на успеха на миграцията към utf8mb4.

Ако управлявате множество бази данни или клиентски сайтове, VPS с cPanel предоставя графичен интерфейс за управление на бази данни, като същевременно запазва необходимия достъп до основния сървър за конфигурация на набора от символи. За екипи, предпочитащи гъвкавостта на командния ред с лек панел, VPS контролни панели предлагат няколко алтернативи, подходящи за различни оперативни работни процеси.

За проекти, изискващи също сигурно предаване на данни, съчетаването на миграцията на базата данни с правилно конфигуриран SSL сертификат гарантира, че данните, кодирани с utf8mb4, са защитени при пренос, а не само в покой.

Контролен списък за технически решения

Използвайте този контролен списък преди и след всяка миграция от utf8 към utf8mb4:

Преди миграцията:

• [ ] Пълен `mysqldump` архив, верифициран и възстановим
• [ ] Версията на MySQL потвърдена (изисква се 5.5.3+ за utf8mb4)
• [ ] Статусът на `innodb_large_prefix` проверен (активирайте при MySQL 5.6/5.7)
• [ ] Всички `VARCHAR(255)` колони с индекси на пълни колони идентифицирани
• [ ] Кодът за набора от символи на връзката на приложението прегледан и актуализиран
• [ ] Планиран прозорец за поддръжка за производствени бази данни

След миграцията:

• [ ] `SHOW VARIABLES LIKE 'character_set%'` показва `utf8mb4` на ниво сървър
• [ ] `SHOW CREATE TABLE` потвърждава `utf8mb4` за всички конвертирани таблици
• [ ] Заявката `information_schema.COLUMNS` потвърждава липсата на оставащи `utf8` колони
• [ ] `SET NAMES utf8mb4` на ниво приложение или еквивалент потвърден в кода за връзка
• [ ] Тестът за вмъкване на емоджи преминат успешно на представителна таблица
• [ ] Базовата линия на производителността на заявките сравнена с метриките преди миграцията
• [ ] Дължините на индексите верифицирани — без мълчаливо съкращаване на дълги индексирани стойности

ЧЗВ

Причинява ли мигрирането от utf8 към utf8mb4 загуба на данни?

Не. utf8mb4 е строго надмножество на MySQL's utf8. Всеки символ, съхранен в utf8 колона, е идентично представим в utf8mb4. Миграцията е неразрушителна за съществуващите данни. Единственият риск са грешки в дължината на индекса при `VARCHAR(255)` колони с индекси на пълни колони, които трябва да бъдат разрешени чрез съкращаване на индексния префикс.

Защо емоджи все още не се вмъкват след като конвертирах таблиците си към utf8mb4?

Най-честата причина е наборът от символи на връзката на приложението. Ако вашият PHP, Python или Node.js код се свързва без изрично указване на `utf8mb4`, MySQL използва стандарта `character_set_client` на сървъра за тази сесия. Добавете `SET NAMES utf8mb4` или еквивалентния параметър за набора от символи към конфигурацията на вашата връзка.

Каква е разликата между utf8mb4_unicode_ci и utf8mb4_0900_ai_ci?

`utf8mb4_unicode_ci` е базирано на правилата за съпоставяне на Unicode 4.0 и е стандартният избор за MySQL 5.7. `utf8mb4_0900_ai_ci` е базирано на Unicode 9.0, е стандартното в MySQL 8.0 и е едновременно по-бързо и по-езиково точно. Използвайте `utf8mb4_0900_ai_ci` на MySQL 8.0+ за нови проекти.

Ще увеличи ли преминаването към utf8mb4 значително размера на съхранението на базата данни ми?

На практика, не. ASCII и повечето BMP символи използват същия брой байтове и в двете кодирания. Само допълнителните символи (емоджи, допълнителни CJK) използват 4 байта — и те не можеха да бъдат представени в utf8 преди. Допълнителното натоварване на паметта за буфери за сортиране се увеличава с приблизително 33% за операции с много низове, но това е незначително на всеки съвременен сървър.

Мога ли да конфигурирам utf8mb4 на споделен хостинг?

Частично. Можете да зададете набора от символи на ниво база данни и таблица, използвайки SQL `ALTER` изрази, и можете да укажете набора от символи в низа за връзка на вашето приложение. Въпреки това, не можете да промените `my.cnf` или да рестартирате MySQL на споделен хостинг. Стандартите на ниво сървър ще останат непроменени, което означава, че новите бази данни, създадени чрез панела за хостинг, може да използват utf8 по подразбиране. Пълната конфигурация на utf8mb4 изисква VPS или dedicated сървър с root достъп.