utf8 vs utf8mb4 w MySQL: Kompletny Przewodnik Techniczny

Zestaw znaków utf8 MySQL to błędna nazwa — nie jest to prawdziwa implementacja UTF-8. Koduje znaki używając tylko 1 do 3 bajtów, co oznacza, że po cichu pomija lub odrzuca każdy punkt kodowy Unicode powyżej U+FFFF, w tym wszystkie emoji i znaczną część uzupełniających znaków CJK. utf8mb4 to poprawna, pełna implementacja UTF-8 MySQL, obsługująca 1 do 4 bajtów na znak i pełny zakres Unicode. W przypadku każdej produkcyjnej bazy danych zbudowanej po 2010 roku, utf8mb4 jest jedynym uzasadnionym wyborem.

Ten przewodnik wyjaśnia dokładnie, dlaczego to rozróżnienie ma znaczenie, gdzie oryginalny projekt utf8 poszedł w złym kierunku, jak bezpiecznie przeprowadzić migrację oraz jak poprawnie skonfigurować MySQL na poziomie serwera, bazy danych, tabeli i połączenia.

Podstawowy problem: dlaczego utf8 MySQL jest wadliwy z założenia

Standard kodowania UTF-8 (RFC 3629) definiuje schemat o zmiennej szerokości, który używa 1 do 4 bajtów do reprezentowania każdego prawidłowego punktu kodowego Unicode — ponad 1,1 miliona możliwych znaków. Gdy MySQL wprowadził zestaw znaków `utf8` w wersji 4.1, implementacja była celowo ograniczona do 3 bajtów na znak. Był to celowy skrót inżynieryjny, a nie przeoczenie.

W tamtym czasie format wiersza InnoDB narzucał limit 767 bajtów na prefiksy kluczy indeksu. Obsługa 4-bajtowych znaków zmniejszyłaby maksymalną długość indeksowanego prefiksu dla kolumn `VARCHAR`, tworząc problemy ze zgodnością indeksów. Ograniczenie do 3 bajtów było pragmatycznym obejściem, które stało się długoterminowym zobowiązaniem.

Praktyczna konsekwencja: żaden punkt kodowy Unicode w Uzupełniającej Płaszczyźnie Wielojęzycznej (SMP) — punkty kodowe U+10000 i powyżej — nie może być przechowywany w kolumnie `utf8`. Obejmuje to:

• Wszystkie standardowe emoji (U+1F600 i dalej)
• Matematyczne symbole alfanumeryczne (U+1D400–U+1D7FF)
• Symbole notacji muzycznej
• Historyczne pisma, takie jak Linear B, gotyckie i klinowe
• Uzupełniające ujednolicone ideografy CJK (U+20000–U+2A6DF)
• Niektóre symbole walut i operatory techniczne dodane w ostatnich wersjach Unicode

Gdy aplikacja próbuje wstawić 4-bajtowy znak do kolumny `utf8`, MySQL zwraca błąd `Incorrect string value` lub, jeśli `sql_mode` jest permisywny, po cichu obcina dane. Ciche obcinanie jest prawdopodobnie bardziej niebezpiecznym wynikiem — aplikacja nie otrzymuje żadnego błędu, ale dane są uszkodzone.

utf8mb4: Poprawna implementacja

MySQL wprowadził utf8mb4 w wersji 5.5.3 (wydanej w 2010 roku) specjalnie w celu usunięcia tej wady. Sufiks `mb4` oznacza „wielobajtowy, maksymalnie 4 bajty”. Jest to ścisły nadzbiór `utf8` — każdy znak reprezentowalny w `utf8` jest identycznie reprezentowalny w `utf8mb4`. Podczas migracji z `utf8` do `utf8mb4` nie dochodzi do utraty danych.

utf8mb4 bezpośrednio odpowiada standardowi UTF-8 RFC 3629. Obsługuje pełną przestrzeń kodową Unicode od U+0000 do U+10FFFF bez ograniczeń.

utf8 vs utf8mb4: Porównanie funkcji

Wybór sortowania w utf8mb4

Wybór utf8mb4 jako zestawu znaków to tylko połowa decyzji. Sortowanie określa sposób porównywania, sortowania i indeksowania ciągów znaków. Niewłaściwe sortowanie powoduje subtelne, trudne do debugowania zachowanie zapytań.

utf8mb4_unicode_ci

Oparte na algorytmie sortowania Unicode (UCA). Poprawnie obsługuje reguły sortowania specyficzne dla języka. Nieco wolniejsze niż `utf8mb4_general_ci` ze względu na bardziej złożoną logikę porównywania, ale różnica w wydajności jest nieistotna na nowoczesnym sprzęcie.

utf8mb4_general_ci

Uproszczone sortowanie, które nie implementuje w pełni UCA. Szybsze w testach porównawczych z początku lat 2010., ale przewaga szybkości jest nieistotna na obecnych CPU. Niepoprawnie obsługuje niektóre przypadki brzegowe — na przykład traktuje pewne niemieckie znaki jako równoważne, gdy nie powinny być. Należy unikać w nowych projektach.

utf8mb4_0900_ai_ci

Dostępne w MySQL 8.0+. Oparte na Unicode 9.0 z porównaniem niewrażliwym na akcenty (`ai`) i niewrażliwym na wielkość liter (`ci`). Jest to zalecane domyślne ustawienie dla MySQL 8.0 i nowszych. Jest szybsze niż `utf8mb4_unicode_ci` i dokładniejsze.

utf8mb4_bin

Porównanie binarne — uwzględniające wielkość liter, uwzględniające akcenty, bez reguł specyficznych dla lokalizacji. Używaj, gdy potrzebujesz dokładnego dopasowania na poziomie bajtów, na przykład dla skrótów haseł lub identyfikatorów uwzględniających wielkość liter.

Zalecenie: Używaj `utf8mb4_0900_ai_ci` w MySQL 8.0+. Używaj `utf8mb4_unicode_ci` w MySQL 5.7 i wcześniejszych.

Implikacje dla pamięci masowej i indeksów

Częstym problemem podczas migracji z utf8 do utf8mb4 jest narzut pamięci masowej. W praktyce wpływ jest minimalny:

• Znaki ASCII (U+0000–U+007F) nadal zajmują dokładnie 1 bajt w obu kodowaniach.
• Większość znaków łacińskich, greckich, cyrylicy, arabskich i hebrajskich zajmuje 2 bajty w obu kodowaniach.
• Znaki CJK w BMP zajmują 3 bajty w obu kodowaniach.
• Tylko znaki uzupełniające (emoji, uzupełniające CJK) wymagają 4 bajtów — a te były po prostu niereprezentowalne w utf8 wcześniej.

Rzeczywistym problemem z indeksami jest limit prefiksu indeksu InnoDB wynoszący 767 bajtów w starszych konfiguracjach. W przypadku utf8mb4, przy najgorszym przypadku 4 bajtów na znak, prefiks indeksu `VARCHAR` o długości 191 znaków osiąga limit 767 bajtów. W przypadku `utf8` ten sam limit pozwalał na 255 znaków. Jeśli masz kolumny `VARCHAR(255)` z indeksami na całej kolumnie, podczas migracji możesz napotkać błędy `Specified key was too long`.

Rozwiązania:

• Włącz `innodb_large_prefix = ON` (MySQL 5.6/5.7), aby podnieść limit do 3072 bajtów.
• Użyj `ROW_FORMAT=DYNAMIC` lub `ROW_FORMAT=COMPRESSED` na dotkniętych tabelach.
• W MySQL 8.0 `innodb_large_prefix` jest domyślnie włączony, a parametr został usunięty.
• Skróć prefiksy indeksów: `INDEX (column(191))` zamiast `INDEX (column(255))`.

Jest to najczęstszy punkt awarii migracji i ten, który jest najczęściej niedostatecznie udokumentowany w podstawowych przewodnikach.

Jak przeprowadzić migrację bazy danych MySQL z utf8 do utf8mb4

Migracja jest prosta, ale wymaga precyzji. Pominięcie którejkolwiek warstwy — serwera, bazy danych, tabeli lub połączenia — powoduje, że aplikacja po cichu wraca do starego kodowania.

Krok 1: Wykonaj kopię zapasową bazy danych

Nigdy nie modyfikuj kodowania znaków w działającej bazie danych bez zweryfikowanej kopii zapasowej.

“`bash

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

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

“`

Flaga `–single-transaction` zapewnia spójną migawkę dla tabel InnoDB bez blokowania. Przed kontynuowaniem przechowaj kopię zapasową w lokalizacji oddzielonej od serwera bazy danych.

Krok 2: Zaktualizuj konfigurację serwera MySQL

Edytuj `/etc/mysql/my.cnf` lub `/etc/mysql/mysql.conf.d/mysqld.cnf` w zależności od dystrybucji:

“`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

“`

Uruchom ponownie MySQL:

“`bash

sudo systemctl restart mysql

“`

Krok 3: Konwertuj bazę danych

“`sql

ALTER DATABASE database_name

CHARACTER SET = utf8mb4

COLLATE = utf8mb4_unicode_ci;

“`

Krok 4: Konwertuj wszystkie tabele

Wygeneruj i wykonaj instrukcje `ALTER TABLE` dla każdej tabeli. Ręczne uruchamianie ich na dużych schematach jest podatne na błędy. Użyj tego zapytania, aby automatycznie wygenerować instrukcje:

“`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';

“`

Wykonaj każdą wygenerowaną instrukcję. Składnia `CONVERT TO CHARACTER SET` zmienia zarówno domyślne ustawienia tabeli, jak i wszystkie istniejące kolumny znaków w jednej operacji.

Krok 5: Napraw błędy długości indeksu

Jeśli napotkasz `Specified key was too long; max key length is 767 bytes`, zidentyfikuj problematyczny indeks:

“`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));

“`

W przypadku baz danych WordPress, kolumna `option_name` tabeli `wp_options` oraz kolumna `meta_key` tabeli `wp_postmeta` są częstymi źródłami tego błędu.

Krok 6: Zweryfikuj konwersję

“`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');

“`

Każda wartość `CHARACTER_SET_NAME` powinna wyświetlać `utf8mb4`.

Krok 7: Zaktualizuj ciągi połączeń aplikacji

Kodowanie serwera i schematu nie ma znaczenia, jeśli aplikacja łączy się przy użyciu niewłaściwego zestawu znaków. Kodowanie na poziomie połączenia zastępuje domyślne ustawienie serwera.

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'

});

“`

Brak ustawienia zestawu znaków połączenia jest najczęstszą przyczyną, dla której emoji nadal nie można wstawić po rzekomo kompletnej migracji.

Kwestie specyficzne dla WordPress

WordPress używa utf8mb4 jako domyślnego zestawu znaków od wersji 4.2 (kwiecień 2015). Jeśli uruchamiasz instalację WordPress na starszej bazie danych, która nigdy nie była migrowana, plik `wp-config.php` może nadal zawierać:

“`php

define('DB_CHARSET', 'utf8');

“`

Zmień to na:

“`php

define('DB_CHARSET', 'utf8mb4');

define('DB_COLLATE', 'utf8mb4_unicode_ci');

“`

WordPress zawiera również wbudowaną procedurę aktualizacji (`maybe_convert_table_to_utf8mb4()`), która uruchamia się podczas aktualizacji rdzenia. Jednak ta procedura nie zawsze obejmuje każdą tabelę, szczególnie te utworzone przez wtyczki. Ręczne podejście `ALTER TABLE` opisane powyżej jest bardziej niezawodne.

W środowisku Hostingu VPS z dostępem root możesz zautomatyzować cały ten proces za pomocą skryptu powłoki i zaplanować go jako jednorazowe zadanie cron, dając pełną kontrolę nad harmonogramem i rejestrowaniem.

Kwestie wydajnościowe

Wpływ utf8mb4 na wydajność w porównaniu z utf8 jest nieistotny dla zdecydowanej większości obciążeń:

• Zapytania odczytu: Brak mierzalnej różnicy dla znaków BMP. Znaki uzupełniające wymagają jednego dodatkowego bajtu I/O, który jest absorbowany przez buforowanie puli buforów.
• Zapytania zapisu: Identyczne dla zawartości ASCII i BMP. Nieznacznie wyższe dla znaków uzupełniających.
• Operacje na indeksach: Zmniejszona maksymalna długość prefiksu (191 vs 255 znaków dla indeksów pełnej szerokości) może wpływać na plany zapytań, jeśli masz indeksy na całej kolumnie dla długich kolumn `VARCHAR`. Sprawdź indeksy przed i po migracji.
• Pamięć: MySQL przydziela bufory o stałej szerokości dla operacji na ciągach znaków na podstawie maksymalnej liczby bajtów na znak. Przełączenie z utf8 (maks. 3 bajty) na utf8mb4 (maks. 4 bajty) zwiększa pamięć przydzieloną dla buforów sortowania w pamięci i tabel tymczasowych o około 33% dla operacji intensywnie korzystających z ciągów znaków. Na Serwerze Dedykowanym z dużą ilością RAM jest to bez znaczenia. W środowisku współdzielonym z ograniczoną pamięcią monitoruj `sort_buffer_size` i `tmp_table_size` po migracji.

Kiedy utf8 jest nadal akceptowalny

Istnieje wąski zestaw uzasadnionych powodów, aby zachować `utf8`:

• Ścisła zgodność ze starszymi systemami: Aplikacja używająca nieobsługiwanego ORM lub sterownika bazy danych, który nie może obsługiwać 4-bajtowych znaków. Jest to problem długu technicznego, a nie powód do bezterminowego zachowania utf8.
• Archiwalne bazy danych tylko do odczytu: Jeśli baza danych nigdy nie będzie otrzymywać nowych zapisów, a istniejące dane nie zawierają znaków uzupełniających, migracja dodaje ryzyko bez żadnych korzyści.
• Ograniczenia pamięci masowej: W ekstremalnych przypadkach brzegowych — systemach wbudowanych lub środowiskach z poważnymi ograniczeniami pojemności — marginalna różnica w pamięci masowej może mieć znaczenie. Nie dotyczy to żadnego standardowego scenariusza hostingu internetowego.

We wszystkich innych przypadkach utf8mb4 jest właściwym wyborem. Argument, że utf8 oszczędza miejsce, jest technicznie prawdziwy tylko dla znaków uzupełniających, które i tak były niereprezentowalne w utf8. Nie oszczędzasz miejsca na danych, których nie możesz przechowywać.

Wybór odpowiedniego środowiska hostingowego dla MySQL utf8mb4

Prawidłowa konfiguracja utf8mb4 wymaga dostępu do pliku konfiguracyjnego serwera MySQL (`my.cnf`). Wyklucza to większość środowisk hostingu współdzielonego, gdzie nie można modyfikować zmiennych na poziomie serwera.

Aby mieć pełną kontrolę nad kodowaniem znaków MySQL, sortowaniem, ustawieniami InnoDB i parametrami połączenia, potrzebujesz planu Hostingu VPS z dostępem root lub Serwera Dedykowanego. Oba dają bezpośredni dostęp do `/etc/mysql/my.cnf`, możliwość restartu usługi MySQL oraz swobodę konfigurowania `innodb_large_prefix`, `ROW_FORMAT` i innych parametrów wpływających na powodzenie migracji utf8mb4.

Jeśli zarządzasz wieloma bazami danych lub witrynami klientów, VPS z cPanel zapewnia graficzny interfejs do zarządzania bazami danych, zachowując jednocześnie podstawowy dostęp do serwera potrzebny do konfiguracji zestawu znaków. Dla zespołów preferujących elastyczność wiersza poleceń z lekkim panelem, Panele Sterowania VPS oferują kilka alternatyw dostosowanych do różnych przepływów pracy operacyjnej.

W przypadku projektów wymagających również bezpiecznej transmisji danych, połączenie migracji bazy danych z odpowiednio skonfigurowanym Certyfikatem SSL zapewnia, że dane zakodowane w utf8mb4 są chronione podczas przesyłania, a nie tylko w spoczynku.

Lista kontrolna decyzji technicznych

Użyj tej listy kontrolnej przed i po każdej migracji z utf8 do utf8mb4:

Przed migracją:

• [ ] Pełna kopia zapasowa `mysqldump` zweryfikowana i możliwa do przywrócenia
• [ ] Wersja MySQL potwierdzona (wymagana 5.5.3+ dla utf8mb4)
• [ ] Status `innodb_large_prefix` sprawdzony (włącz jeśli na MySQL 5.6/5.7)
• [ ] Wszystkie kolumny `VARCHAR(255)` z indeksami na całej kolumnie zidentyfikowane
• [ ] Kod zestawu znaków połączenia aplikacji przejrzany i zaktualizowany
• [ ] Okno konserwacji zaplanowane dla produkcyjnych baz danych

Po migracji:

• [ ] `SHOW VARIABLES LIKE 'character_set%'` pokazuje `utf8mb4` na poziomie serwera
• [ ] `SHOW CREATE TABLE` potwierdza `utf8mb4` na wszystkich przekonwertowanych tabelach
• [ ] Zapytanie `information_schema.COLUMNS` potwierdza brak pozostałych kolumn `utf8`
• [ ] `SET NAMES utf8mb4` na poziomie aplikacji lub odpowiednik potwierdzony w kodzie połączenia
• [ ] Test wstawiania emoji zaliczony na reprezentatywnej tabeli
• [ ] Punkt odniesienia wydajności zapytań porównany z metrykami sprzed migracji
• [ ] Długości indeksów zweryfikowane — brak cichego obcinania długich indeksowanych wartości

FAQ

Czy migracja z utf8 do utf8mb4 powoduje utratę danych?

Nie. utf8mb4 jest ścisłym nadzbiorem utf8 MySQL. Każdy znak przechowywany w kolumnie utf8 jest identycznie reprezentowalny w utf8mb4. Migracja jest nieniszcząca dla istniejących danych. Jedynym ryzykiem są błędy długości indeksu w kolumnach `VARCHAR(255)` z indeksami na całej kolumnie, które należy rozwiązać przez skrócenie prefiksu indeksu.

Dlaczego emoji nadal nie można wstawić po konwersji tabel do utf8mb4?

Najczęstszą przyczyną jest zestaw znaków połączenia aplikacji. Jeśli kod PHP, Python lub Node.js łączy się bez jawnego określenia `utf8mb4`, MySQL używa domyślnego ustawienia `character_set_client` serwera dla tej sesji. Dodaj `SET NAMES utf8mb4` lub odpowiedni parametr zestawu znaków do konfiguracji połączenia.

Jaka jest różnica między utf8mb4_unicode_ci a utf8mb4_0900_ai_ci?

`utf8mb4_unicode_ci` jest oparte na regułach sortowania Unicode 4.0 i jest standardowym wyborem dla MySQL 5.7. `utf8mb4_0900_ai_ci` jest oparte na Unicode 9.0, jest domyślnym w MySQL 8.0 i jest zarówno szybsze, jak i bardziej dokładne językowo. Używaj `utf8mb4_0900_ai_ci` w MySQL 8.0+ dla nowych projektów.

Czy przejście na utf8mb4 znacząco zwiększy rozmiar mojej bazy danych?

W praktyce nie. ASCII i większość znaków BMP używa tej samej liczby bajtów w obu kodowaniach. Tylko znaki uzupełniające (emoji, uzupełniające CJK) używają 4 bajtów — a te były niereprezentowalne w utf8 wcześniej. Narzut pamięci dla buforów sortowania wzrasta o około 33% dla operacji intensywnie korzystających z ciągów znaków, ale jest to nieistotne na każdym nowoczesnym serwerze.

Czy mogę skonfigurować utf8mb4 na hostingu współdzielonym?

Częściowo. Możesz ustawić zestaw znaków na poziomie bazy danych i tabeli używając instrukcji SQL `ALTER`, a także możesz określić zestaw znaków w ciągu połączenia aplikacji. Jednak nie możesz modyfikować `my.cnf` ani restartować MySQL na hostingu współdzielonym. Domyślne ustawienia na poziomie serwera pozostaną niezmienione, co oznacza, że nowe bazy danych tworzone przez panel hostingowy mogą domyślnie używać utf8. Pełna konfiguracja utf8mb4 wymaga VPS lub serwera dedykowanego z dostępem root.