utf8mb4 in MySQL: Storing Emoji and Full Unicode

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

MySQL’s charset named utf8 is not UTF-8. It is a three-bytes-per-character subset (utf8mb3) frozen in time before emoji, and any four-byte character — emoji, many CJK ideographs, mathematical symbols — fails to store in it. utf8mb4 is the real UTF-8. On MySQL 8.0 and later it is the default for new tables, but tables created on older versions, or with explicit utf8 in their DDL, still carry the trap.

The Failure

CREATE TABLE notes_mb3 (
  body VARCHAR(100)
) CHARACTER SET utf8mb3;

INSERT INTO notes_mb3 (body) VALUES ('launch day 🚀');

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

\xF0\x9F\x9A\x80 is the rocket emoji’s four bytes. With strict SQL mode off, this arrives as a warning instead and the value is silently truncated at the emoji, which is worse: you find out weeks later, from the data.

Find What Needs Converting

Ask the metadata which tables and columns still use three-byte utf8:

SELECT table_schema, table_name, column_name, character_set_name
FROM information_schema.columns
WHERE character_set_name = 'utf8mb3'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Check the schema and table defaults too, or new columns will be born wrong:

SELECT schema_name, default_character_set_name
FROM information_schema.schemata;

Convert

CONVERT TO CHARACTER SET rewrites the table, converting both the column definitions and the stored data:

ALTER TABLE notes_mb3 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Then fix the defaults so future objects inherit the right charset:

ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

On a large table this is a full rewrite; treat it like any other blocking ALTER and run it with your normal online-migration tooling. See Schema migrations. Two things to check before converting:

Index length. A VARCHAR(255) unique index needed 765 bytes under utf8mb3 but 1,020 under utf8mb4. Modern InnoDB defaults (DYNAMIC row format) allow 3,072 bytes, so this mostly bites tables carried forward with REDUNDANT or COMPACT row formats from very old servers. If the ALTER fails with a max-key-length error, check the table’s row format first.
The connection charset. Storage is only half the path. If the client connection negotiates utf8mb3, emoji die in transit even though the column could hold them. Verify what your session actually uses:

SHOW VARIABLES LIKE 'character_set_c%';

You want character_set_client and character_set_connection both reading utf8mb4. Most modern drivers do this by default; older JDBC and PHP configurations may pin utf8.

Collation, Briefly

Charset is what bytes mean; collation is how they compare. For utf8mb4 on MySQL 8.x, utf8mb4_0900_ai_ci is the default and the right general answer: accent-insensitive, case-insensitive, correct Unicode 9.0 ordering. Mixing collations across tables that get joined on string columns forces conversions and can skip indexes, so pick one and use it everywhere. Character sets and collations covers the comparison rules in depth.

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

The Failure

Find What Needs Converting

Convert

Collation, Briefly

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​The Failure

​Find What Needs Converting

​Convert

​Collation, Briefly

​See also

The Failure

Find What Needs Converting

Convert

Collation, Briefly

See also