MySQL utf8mb4 and collations: utf8 vs utf8mb4, converting

In MySQL, utf8 is not UTF-8. It is an alias for utf8mb3, a three-byte subset that cannot store any character outside the Basic Multilingual Plane - which includes every emoji, many CJK characters and a range of mathematical and historic scripts. utf8mb4 is real UTF-8, and it is the default character set from MySQL 8.0 onwards, with utf8mb4_0900_ai_ci as the default collation. If you are creating a database today, use utf8mb4 everywhere: the database, the tables, the columns and the connection. If you inherited a database on utf8 or latin1, it can be converted, but how you do it depends on whether the data in it is stored correctly, and getting that wrong is how text turns into é.

This guide explains the difference, how to choose a collation, the connection settings that have to match, and how to convert an existing database safely on MySQL 8.0 or 8.4 LTS.

utf8, utf8mb3 and utf8mb4#

UTF-8 encodes each character in one to four bytes. ASCII takes one, most European and Middle Eastern letters two, most of Chinese, Japanese and Korean three, and everything above U+FFFF - emoji, rarer CJK, musical symbols - four. When MySQL added Unicode support in 2002, it capped its utf8 at three bytes per character to save space, and that decision has been tripping people up ever since.

From MySQL 8.0.30, the server reports utf8mb3 rather than utf8 in SHOW CREATE TABLE and information_schema, which makes the situation visible at last. The plan is for utf8 to become an alias for utf8mb4 in a future release; until it does, never write utf8 in a schema, a configuration file or a connection string. Write utf8mb4.

Trying to store an emoji in a utf8mb3 column under the default strict SQL mode fails loudly:

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

\xF0 is the first byte of a four-byte sequence. Without strict mode, MySQL truncates or replaces the character with ? and carries on, which is worse: the data is silently damaged. If you see question marks where users typed emoji, either the column or the connection is not utf8mb4.

Collations: what the names mean#

A character set says how characters are stored. A collation says how they compare and sort: whether a equals A, whether e equals é, and where ß goes. Every character column has both. The collation name encodes its behaviour:

The 0900 collations are based on version 9.0.0 of the Unicode Collation Algorithm and are both more correct and, in MySQL 8, faster than the old unicode_ci ones. They differ from the old collations in two ways worth knowing:

• Characters outside the BMP sort and compare properly. In utf8mb4_unicode_ci and utf8mb4_general_ci, all four-byte characters compare equal to each other - a column with a unique index cannot hold both the sushi emoji and the beer emoji, because to the collation they are the same character. 0900 collations treat them as distinct.
• Trailing spaces count. The 0900 collations are NO PAD: 'abc' and 'abc ' are different. The older ones are PAD SPACE and treat them as equal. This changes the result of comparisons and unique checks on data with stray trailing spaces.

Choosing one

For most applications, keep the default utf8mb4_0900_ai_ci. Case- and accent-insensitive matching is what people expect from a search box and a login form: Ana@Example.com finds ana@example.com, cafe finds café.

The cost of insensitivity shows up in unique indexes. With ai_ci, resume and résumé are equal, so a unique index on a username column refuses the second one. That is usually desirable for usernames - it stops impersonation by accent - and sometimes surprising for other data. For columns that need exact matching (tokens, hashes, case-sensitive codes), use utf8mb4_0900_bin or utf8mb4_0900_as_cs on that column only:

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;

Tokens and hashes are pure ASCII, so ascii with ascii_bin stores them at one byte per character and compares them exactly. Language-specific collations are worth it when sort order matters to users in one language - Turkish dotted and dotless i, Swedish å ä ö after z, Spanish ñ.

Character sets at every level#

Character set and collation can be set at five levels, each inheriting from the one above when not given:

1. Server: character_set_server and collation_server, defaults for new databases.
2. Database: CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, defaults for new tables.
3. Table: DEFAULT CHARSET=..., the default for new columns.
4. Column: what is actually used for storage.
5. Connection: how text travels between client and server.

The column level is the one that decides what is stored. Changing a database's default with ALTER DATABASE changes nothing about existing tables; it only affects tables created afterwards. This catches people who "converted" a database and still get Incorrect string value errors.

-- 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%';

The connection character set#

The server stores bytes in the column's character set, but it needs to know what character set the client is sending. Three session variables describe that: character_set_client (what the client sends), character_set_connection (what statements are interpreted in) and character_set_results (what results are sent back as). SET NAMES utf8mb4 sets all three, and every driver has an option that does it at connect time:

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

A connection declaring latin1 while the application actually sends UTF-8 is the root cause of most mojibake: MySQL faithfully converts each byte of the UTF-8 text as if it were a Latin-1 character, and é (two bytes in UTF-8) arrives as é. Set the charset in the connection string, not with a SET NAMES query after connecting, so a reconnecting pool does not lose it. PHP PDO and MySQL and MySQL remote connections show the full connection setup per runtime.

Index size and the VARCHAR(191) folklore#

utf8mb4 reserves four bytes per character in index key length calculations. InnoDB's maximum index key is 3072 bytes with the DYNAMIC row format, the default since 5.7, which allows a full index on VARCHAR(768). The older COMPACT and REDUNDANT formats, and MySQL 5.6 by default, limited keys to 767 bytes - 191 characters of utf8mb4 - which is why so many frameworks and tutorials still use VARCHAR(191) for indexed columns.

On MySQL 8 with DYNAMIC tables, you do not need 191. Use the length your data needs. If an old table refuses an index with Specified key was too long; max key length is 767 bytes, check its row format:

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;

Converting an existing database#

First, work out what is really in the columns, because there are two very different situations:

• Correctly stored data in the wrong charset. A latin1 column holding Latin-1 text, or a utf8mb3 column holding valid text. MySQL knows what the characters are and can convert them. Use CONVERT TO.
• Mis-labelled data. A latin1 column that actually holds UTF-8 bytes, because the application wrote UTF-8 over a latin1 connection for years. Everything looks fine in the application (which reads it back the same wrong way) and wrong in phpMyAdmin or a dump. CONVERT TO would double-encode it. This needs the binary round trip below.

To tell which you have, look at a known accented value with 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 is é in Latin-1. C3A9 is é in UTF-8, sitting in a column that thinks it holds two Latin-1 characters.

Correctly stored: 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 changes the table default and every text column, converting the data. It rebuilds the table with the copy algorithm, so writes are blocked for the duration and you need free disk space for a full copy. Converting from latin1 may also promote TEXT columns to MEDIUMTEXT so they can still hold the same number of characters; check the schema afterwards if your ORM cares. Generate the statements for every table:

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

Mis-labelled: the binary round trip

Telling MySQL to reinterpret the bytes without converting them means passing through a binary type, which has no character set:

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 goes through VARBINARY, TEXT through BLOB, MEDIUMTEXT through MEDIUMBLOB. Restate the full column definition on the way back, including NOT NULL and defaults, because MODIFY replaces it. Indexes on the column survive. Then fix the application's connection charset before it writes anything else, or new rows will be wrong in the opposite direction.

A conversion plan for a live application#

The ALTER statements are the easy part. The order around them is what keeps a live site working.

1. Inventory. Run the information_schema queries above and list every table and column that is not utf8mb4, with sizes. Small tables convert in seconds; a multi-gigabyte table can take long enough to need a maintenance window, because CONVERT TO blocks writes while it copies.
2. Diagnose. For each table with non-ASCII text, check a few known values with HEX() and decide whether it is correctly stored or mis-labelled. A single database can contain both, if the application's connection settings changed at some point in its history.
3. Fix the connection first, where it is safe. If the data is correctly stored, switching the application's connection to utf8mb4 before converting the tables is harmless: MySQL converts between the connection and the column. If the data is mis-labelled, the connection change and the binary round trip have to happen together, with writes stopped in between.
4. Rehearse on a copy. Restore last night's dump into a scratch database, run the full conversion, and check the same known values, a sort order and a search. Time it - that is your window.
5. Run it for real, table by table, largest last, with a fresh backup taken immediately before.
6. Set the defaults at the database level so new tables are born correct, and pin the charset and collation in your migrations so a framework does not quietly create the next table with something else.
7. Search for the leftovers: stored procedures and views carry the character set that was current when they were created, and SHOW CREATE PROCEDURE shows it. Recreate them after the conversion.

Frameworks deserve a specific look. Laravel's config/database.php sets charset and collation for the connection and for the tables its migrations create; older projects often pin utf8mb4_unicode_ci, which is fine but should be the same as every other table to avoid the mixed-collation error below. Django's MySQL backend uses the OPTIONS charset key. WordPress sets DB_CHARSET and DB_COLLATE in wp-config.php and has used utf8mb4 since version 4.2; an older site that predates that upgrade may still have tables in utf8mb3 that the upgrade routine skipped because of index length limits. In every case, the framework setting and the actual table definitions should agree - the setting only applies to what the framework creates next.

Collation errors and how to fix them#

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

Two columns with different collations compared in a join or WHERE. This usually happens after a partial migration: old tables in utf8mb4_unicode_ci, new ones in the 8.0 default. Converting the remaining tables to one collation is the real fix; COLLATE utf8mb4_0900_ai_ci on one side of the comparison is the quick one, and it prevents index use on that side.

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

A MySQL 8 dump being loaded into MySQL 5.7 or a server from another family. The 0900 collations exist only in MySQL 8. Restore into MySQL 8 if you can; otherwise replace the collation name throughout the dump. Migrating MySQL to a new host deals with version mismatches in general.

FAQ#

Should I use utf8mb4_unicode_ci or utf8mb4_0900_ai_ci?

On MySQL 8, utf8mb4_0900_ai_ci. It follows a newer version of the Unicode rules, distinguishes emoji and other four-byte characters, and is faster in MySQL 8's implementation. Keep unicode_ci only where a database must also load into MySQL 5.7 or another server family that lacks the 0900 collations.

Does utf8mb4 take more space than utf8?

Not for the same text. UTF-8 stores each character in as many bytes as it needs, so ASCII is one byte in both. The difference is only the characters utf8mb3 cannot store at all. What grows is the reserved size for indexes and in-memory temporary tables, which plan for four bytes per character.

Why do I see question marks instead of emoji?

The text passed through something that is not utf8mb4 - the column, the table, or most often the connection - and MySQL was not in strict mode, so it replaced the characters instead of refusing them. Check the column with SHOW CREATE TABLE, the connection with SHOW SESSION VARIABLES LIKE 'character_set_%', and set charset=utf8mb4 in the driver.

Can I make one query case-sensitive without changing the column?

Yes: WHERE code = 'AbC' COLLATE utf8mb4_0900_as_cs. It works but cannot use an index built with the column's own collation, so it scans. For a column that is always compared case-sensitively, change its collation instead.

Does ALTER DATABASE convert my tables?

No. It only changes the default for tables created afterwards. Existing tables and their columns keep their character set until you run ALTER TABLE ... CONVERT TO on each.