Průvodce změnou znakové sady MySQL: Převod na utf8mb4 (oprava mojibake)

1. Úvod

Proč můžete potřebovat změnit znakovou sadu MySQL

Znaková sada databáze je důležité nastavení, které určuje, jak jsou textová data kódována a zpracovávána pro uložení. V MySQL je výchozí znaková sada často latin1, což může způsobovat problémy při práci s japonštinou nebo jinými speciálními znaky. Zejména během migrace dat nebo standardizace systému se přepnutí na vhodnou znakovou sadu stává nezbytným.

Běžné problémy a jejich příčiny

Typické problémy související se znakovými sadami MySQL zahrnují následující.

1. Mojibake (rozbitý text)

• utf8 a latin1 jsou smíchány ve stejném prostředí
• Nastavení znakové sady na klientovi a serveru se neshodují

1. Problémy při vyhledávání

• Kvůli rozdílům v kolaci nejsou vráceny očekávané výsledky vyhledávání
• Pořadí řazení se liší od očekávaného

1. Problémy během migrace dat

• Emoji a speciální symboly nelze uložit, protože není použita utf8mb4
• Převod znakové sady není během exportu/importu správně zpracován

Cíle článku a struktura

Tento článek poskytuje komplexní vysvětlení změn znakových sad MySQL, od základních konceptů po to, jak změnit nastavení a řešení problémů.

Přehled

1. Základní znalosti o znakových sadách MySQL
2. Jak zkontrolovat aktuální znakovou sadu
3. Jak změnit znakovou sadu MySQL
4. Řešení problémů po změně
5. Jak změny znakové sady ovlivňují výkon
6. Doporučená nastavení (nejlepší postupy)
7. FAQ (často kladené otázky)

Po přečtení tohoto průvodce prohloubíte své pochopení znakových sad MySQL a budete schopni vybrat správná nastavení a vyhnout se běžným problémům.

2. Co je znaková sada MySQL? Základy

Co je znaková sada?

Znaková sada (Character Set) je soubor pravidel používaných k ukládání a zpracování znaků jako digitálních dat. Například při ukládání japonského znaku “あ” UTF-8 reprezentuje jako bajtovou sekvenci E3 81 82, zatímco Shift_JIS používá 82 A0.

V MySQL můžete specifikovat různé znakové sady na úrovni databáze nebo tabulky. Výběrem vhodné znakové sady můžete zabránit mojibake a usnadnit internacionalizaci.

Běžné znakové sady

Co je kolace?

Kolace je soubor pravidel používaných k porovnávání a řazení dat v rámci znakové sady. Například určuje, zda jsou “A” a “a” považovány za stejný znak a jak je určeno pořadí.

Běžně používané kolace

Rozdíly mezi utf8 a utf8mb4

utf8 v MySQL ve skutečnosti může uložit jen až 3 bajty na znak, takže nedokáže zpracovat některé speciální znaky (jako emoji nebo některé rozšířené CJK znaky). Naopak utf8mb4 podporuje až 4 bajty na znak, což je důvod, proč se moderním aplikacím doporučuje používat utf8mb4.

Proč byste měli přejít z utf8 na utf8mb4

1. Budoucí kompatibilita : Moderní systémy se stále více standardizují na utf8mb4.
2. Ukládání speciálních znaků a emoji : S utf8mb4 můžete bezpečně zpracovávat data v příspěvcích na sociálních sítích a v aplikacích pro zasílání zpráv.
3. Internacionalizace : Pro vícejazyčné systémy snižuje riziko mojibake.

Shrnutí

• Znaková sada určuje, jak jsou data uložena a zpracována.
• Kolace určuje, jak jsou znaky porovnávány.
• utf8 v MySQL je omezen na 3 bajty, takže se doporučuje utf8mb4.
• utf8mb4_unicode_ci je běžně doporučovaná kolace pro obecné použití.

3. Jak zkontrolovat aktuální znakovou sadu

Před změnou znakové sady MySQL je důležité zkontrolovat aktuální nastavení.
Protože znakové sady mohou být nastaveny na různých úrovních (databáze, tabulka, sloupec), měli byste přesně vědět, kde jsou změny potřeba.

Jak zkontrolovat aktuální znakovou sadu

Zkontrolujte znakovou sadu MySQL na úrovni serveru

First, check the default character set and collation settings for the entire MySQL server.

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Example output:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                        |
+--------------------------+----------------------------+

Check the Character Set per Database

To check the character set for a specific database, use the following query.

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

Example output

+----------------+----------------------+----------------------+
| SCHEMA_NAME    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------+----------------------+----------------------+
| my_database   | utf8mb4               | utf8mb4_unicode_ci   |
+----------------+----------------------+----------------------+

Check a Table’s Character Set

This is how to check the character set for a specific table.

SHOW CREATE TABLE table_name;

Example output

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Checkpoints

• DEFAULT CHARSET=latin1 → Not utf8mb4 , so a change is needed
• COLLATE=latin1_swedish_ci → Switching to utf8mb4_unicode_ci is usually more appropriate

Check a Column’s Character Set

To inspect the character set at the column level, run the following SQL.

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' 
AND TABLE_NAME = 'table_name';

Example output

+-------------+--------------------+----------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME       |
+-------------+--------------------+----------------------+
| name        | latin1             | latin1_swedish_ci    |
| email       | utf8mb4            | utf8mb4_unicode_ci   |
+-------------+--------------------+----------------------+

In this example, the name column uses latin1, so it is recommended to change it to utf8mb4.

Summary

• MySQL character sets are configured at multiple levels (server, database, table, column).
• By checking the character set at each level, you can apply the right changes.
• Use commands like SHOW VARIABLES and SHOW CREATE TABLE to fully understand the current configuration.

4. How to Change the MySQL Character Set

By changing the MySQL character set appropriately, you can prevent mojibake and support multilingual data more smoothly.
In this section, we explain how to update settings at each level: server-wide, database, table, and column.

Change the Server-Wide Default Character Set

To change the server-wide default character set, you need to edit the MySQL configuration file (my.cnf or my.ini).

Steps

1. Open the configuration file

• On Linux: bash sudo nano /etc/mysql/my.cnf

• On Windows: wp:list /wp:list

  • Open C:\ProgramData\MySQL\MySQL Server X.X\my.ini

1. Add or change the character set settings Add or update the following lines under the mysqld section.

   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
   

3. Restartujte MySQL

   sudo systemctl restart mysql
   

Na Windows:

net stop MySQL && net start MySQL

4. Ověřte změnu

   SHOW VARIABLES LIKE 'character_set_server';
   

Změna znakové sady na úrovni databáze

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ověřte změnu

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'mydatabase';

Změna znakové sady na úrovni tabulky

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ověřte změnu

SHOW CREATE TABLE users;

Změna znakové sady na úrovni sloupce

ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ověřte změnu

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'mydatabase' 
AND TABLE_NAME = 'users';

Ověření po změně a důležitost záloh

Pro zachování integrity dat po změně znakové sady postupujte podle těchto kroků.

Zálohujte svá data

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Znovu zkontrolujte nastavení

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE TABLE users;

Vložte a zobrazte testovací data

INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');
SELECT * FROM users;

Shrnutí

• Změna znakové sady na úrovni serveru : Upravte my.cnf a nastavte character-set-server=utf8mb4
• Změna znakové sady databáze : ALTER DATABASE mydatabase CHARACTER SET utf8mb4
• Změna znakové sady tabulky : ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4
• Změna znakové sady sloupce : ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4
• Po změnách vždy ověřte nastavení a otestujte svá data

5. Řešení problémů po změně znakové sady

Po změně znakové sady MySQL můžete narazit na situace, kdy systém nefunguje správně nebo se uložená data zkomolí. V této sekci podrobně vysvětlujeme běžné problémy a jak je opravit.

Příčiny mojibake a jak je opravit

Pokud se po změně znakové sady objeví mojibake, jsou běžné následující příčiny.

Oprava #1: Správně nastavte znakovou sadu klienta

SET NAMES utf8mb4;

Oprava #2: Správně převést existující data

UPDATE users SET name = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

Poznámky při převodu z latin1 na utf8mb4

Bezpečný postup

1. Zálohujte aktuální data

   mysqldump -u root -p --default-character-set=latin1 mydatabase > backup.sql
   

2. Změňte znakovou sadu databáze

   ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

3. Změňte znakovou sadu tabulky

   ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

4. Znovu importujte data

   mysql -u root -p --default-character-set=utf8mb4 mydatabase < backup.sql
   

Data nejsou po změně vyhledávatelná

Případ #1: Vyhledávání pomocí LIKE nefunguje

SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%Tanaka%';

Případ #2: Změnil se řazení

SELECT * FROM users ORDER BY BINARY name;

Opatření na straně aplikace

Pro PHP

mysqli_set_charset($conn, 'utf8mb4');

Pro Python (MySQL Connector)

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydatabase",
    charset="utf8mb4"
)

For Node.js (MySQL2)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  charset: 'utf8mb4'
});

Summary

• Post-change issues generally fall into three categories: client settings, data conversion, and application settings.
• To prevent mojibake, standardize the client character set using SET NAMES utf8mb4.
• Watch for LIKE search and sort order changes, and specify COLLATE when needed.
• Set utf8mb4 in your application as well to avoid encoding mismatches.

6. How Character Set Changes Affect Performance

When changing the MySQL character set to utf8mb4, there are several performance considerations, such as increased storage usage and index limitations.
In this section, we explain the impact and the best countermeasures.

Increased Storage Usage

Compared to MySQL’s utf8, utf8mb4 can use up to 4 bytes per character,
so the overall table size may increase.

Max bytes per character by character set

For example, with utf8, VARCHAR(255) is up to 765 bytes (255×3),
but with utf8mb4, it becomes up to 1020 bytes (255×4).

Countermeasure

ALTER TABLE posts MODIFY COLUMN title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Increased Index Size

MySQL enforces a maximum index key length.
After switching to utf8mb4, index entries become larger, and you may hit the limit—making indexes unusable.

Check index impact

SHOW INDEX FROM users;

Example error

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Countermeasure

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Impact on Query Performance

Changing the character set to utf8mb4 may affect query execution speed.

Operations that may be affected

• LIKE searches over large datasets
• ORDER BY processing
• JOIN query performance

Countermeasure

CREATE INDEX idx_name ON users(name(100));

Memory Usage and Buffer Tuning

With utf8mb4, memory usage may increase.

Recommended settings

[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 128M

Summary

• Switching to utf8mb4 increases storage usage.
• Index sizes increase and may exceed limits.
• Query performance can be affected.
• Because memory usage may increase, buffer sizes may need tuning.

7. Recommended Settings (Best Practices)

By setting MySQL character sets appropriately, you can maintain data integrity while optimizing performance.
In this section, we present recommended MySQL character set configurations and explain key points for an optimal setup.

Recommended MySQL Character Set Configuration

Recommended my.cnf Settings

1. MySQL Server Character Set Settings

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
skip-character-set-client-handshake
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
query_cache_size = 128M

2. Client-Side Character Set Settings

[client]
default-character-set = utf8mb4

Recommended Database Settings

CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To change an existing database character set:

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Recommended Table Settings

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Změna znakové sady pro existující tabulky

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Rozdíly mezi utf8mb4_general_ci a utf8mb4_unicode_ci

✅ Pokud potřebujete vícejazyčnou podporu nebo přesné řazení, zvolte utf8mb4_unicode_ci.

Optimalizace indexů

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Shrnutí

• Kombinace utf8mb4 + utf8mb4_unicode_ci je doporučena.
• Standardizujte nastavení serveru (my.cnf) a znakové sady připojení.
• Explicitně specifikujte utf8mb4 na úrovni databáze, tabulky a sloupce.
• Použijte VARCHAR(191) k vyhnutí se omezením délky klíče indexu.
• Použijte utf8mb4_unicode_ci pro přesná srovnání.

8. Často kladené otázky

Zde jsou běžné otázky z praxe týkající se změny znakových sad MySQL.
Také se zabýváme tím, jak řešit chyby a jak vybrat nejlepší nastavení.

Jaký je rozdíl mezi utf8 a utf8mb4?

SHOW VARIABLES LIKE 'character_set_server';

Způsobí změna znakové sady MySQL ztrátu dat?

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Jak opravit mojibake, pokud nastane?

UPDATE users SET name = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

Jaká jsou rizika při konverzi z latin1 na utf8mb4?

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ovlivní přechod na utf8mb4 výkon?

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Který mám použít: utf8mb4_general_ci nebo utf8mb4_unicode_ci?

Zpomalí se dotazy po přechodu na utf8mb4?

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Shrnutí

✅ utf8mb4 je doporučeno. utf8 není doporučeno kvůli jeho omezením.
✅ Před provedením změn vždy zkontrolujte nastavení pomocí SHOW VARIABLES.
✅ Používejte workflow exportu/importu k prevenci mojibake.
✅ Zvažte omezení indexů a použijte VARCHAR(191) tam, kde je to vhodné.
✅ Pro výkon přidejte vhodné indexy.

Závěrečné poznámky

Změna znakové sady MySQL není jen jednoduchá úprava konfigurace – je to kritický úkol, který může ovlivnit integritu dat a výkon.
Dodržením správných nastavení a postupů můžete migrovat na utf8mb4 bezpečně a efektivně.

🔹 Postupujte podle kroků v tomto článku a nastavte svou znakovou sadu správně! 🔹