MySQL Character Sets and Collations

Introduction¶

Character set bugs in MySQL are legendary, mostly because of one historical mistake: utf8 in MySQL is not UTF-8. It's a 3-byte-max subset that can't represent characters outside the Basic Multilingual Plane — no emoji, some CJK characters missing. The real thing is utf8mb4.

Always use utf8mb4¶

-- server default (my.cnf)
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci      -- MySQL 8; MariaDB: utf8mb4_unicode_ci

-- new database
CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- new table
CREATE TABLE posts (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

utf8mb4 stores all of Unicode in 1–4 bytes. There is no downside for Latin-heavy text (ASCII is still 1 byte).

Collations — how strings compare and sort¶

A collation is a set of rules for comparison/ordering, tied to a charset:

Consequences:

• WHERE name = 'jose' matches José under _ci accent-insensitive — often what you want for search, sometimes a surprise for exact-match logic.
• A UNIQUE index under _ci treats [email protected] and [email protected] as duplicates. Use utf8mb4_bin (or add a generated lowercase column) if you need case-sensitive uniqueness.
• Sorting: _ci sorts apple, Äpfel, banana linguistically; _bin sorts by code point (Ä after z).

The four levels¶

Charset/collation is set at server → database → table → column, each inheriting from the level above unless overridden. Plus the connection:

SHOW VARIABLES LIKE 'character_set%';
-- character_set_client, _connection, _results  <- the connection triplet
SET NAMES utf8mb4;             -- sets all three for this session

Most drivers have a charset option (charset=utf8mb4 in the DSN, ?charset=utf8mb4 in a URL). If the connection charset is latin1 but the data is UTF-8, you get double-encoding or mojibake even though the table is utf8mb4.

The 767-byte index limit (legacy)¶

Old MySQL (pre-5.7 / without innodb_large_prefix) capped an index key at 767 bytes. utf8mb4 is 4 bytes/char → VARCHAR(191) was the max fully-indexable length (191 × 4 = 764). Modern MySQL 8 with DYNAMIC row format allows 3072 bytes, so VARCHAR(255) indexes fine. If you see Specified key was too long, you're on old defaults — set ROW_FORMAT=DYNAMIC and innodb_large_prefix=ON (default in 8).

Fixing mojibake¶

Symptoms: é instead of é (UTF-8 bytes read as latin1), or ? (data lost at insert time — unrecoverable).

If the bytes are correct but tagged wrong (common: data inserted via a latin1 connection into a latin1 column but the bytes are actually UTF-8):

-- The "double convert" fix, per column. TEST ON A COPY.
ALTER TABLE t MODIFY col BLOB;                          -- strip charset interpretation
ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4;  -- reinterpret bytes as utf8mb4

Convert a whole table (when the current charset is genuinely what's stored):

ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Always dump a backup first; charset conversions are easy to get wrong and hard to undo.

Verification and troubleshooting¶

SELECT table_name, ccsa.character_set_name, t.table_collation
FROM information_schema.tables t
JOIN information_schema.collation_character_set_applicability ccsa
  ON ccsa.collation_name = t.table_collation
WHERE t.table_schema = 'app';

SELECT column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema='app' AND table_name='posts' AND character_set_name IS NOT NULL;

SELECT HEX(col), col FROM t WHERE id = 123;    -- inspect the actual bytes

• Emoji / some characters become ???? — column or connection is utf8 (3-byte) not utf8mb4, or the client didn't SET NAMES utf8mb4. The ? means the character was lost at write time — data is gone, fix forward.
• é everywhere — connection charset mismatch at insert time. Use the BLOB round-trip fix above.
• Illegal mix of collations error — a JOIN/comparison between columns with different collations. CONVERT(x USING utf8mb4) in the query, or ALTER the columns to match.
• Case-insensitive unique index rejecting distinct-looking values — _ci collation. Switch that column/index to _bin or a _cs collation.
• ORDER BY gives an unexpected order — collation rules; ORDER BY name COLLATE utf8mb4_bin for code-point order, or pick a language collation.
• SHOW CREATE TABLE shows the right charset but data is wrong — the table charset only affects new writes and how bytes are interpreted; existing bad bytes need conversion.

Related tools and reading¶

• On-site: Text diff, URL encoder / decoder.
• Related posts: Reading a MySQL EXPLAIN plan, Safe online schema changes in MySQL.