Everything works until a user types 👍 into a comment box:
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 'body' at row 1
Or worse, there's no error at all, and the emoji comes back as ????. The cause is one of MySQL's oldest naming mistakes.
MySQL's utf8 isn't UTF-8
Real UTF-8 uses 1 to 4 bytes per character. MySQL's original utf8 character set stores at most 3 bytes. It's now officially called utf8mb3, and utf8 is a deprecated alias for it. Three bytes cover the Basic Multilingual Plane (most living scripts), but not:
- emoji (😀 is
F0 9F 98 80, four bytes), - many CJK extension characters,
- mathematical alphanumerics and historic scripts.
The bytes in the error message give it away: a sequence starting with \xF0 is always a 4-byte character.
The real UTF-8 character set is utf8mb4. It's the server default from MySQL 8.0 on, but schemas created on 5.x, or tables copied from old dumps, often still use utf8/utf8mb3.
Check all four layers
The character set applies at four levels, and any one of them can break the chain:
-- Server and connection
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%'
OR Variable_name LIKE 'collation%';
-- Database
SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA WHERE schema_name = 'app';
-- Columns that aren't utf8mb4
SELECT table_name, column_name, character_set_name
information_schema.COLUMNS
table_schema
character_set_name
character_set_name ;
