MySQL "Incorrect string value" / emoji needs utf8mb4

MySQL's historic charset named "utf8" only stores three bytes per character. Emoji need four, so inserts die with Error 1366 — or silently become question marks.

utf8 vs utf8mb4 in MySQL

Emoji live on Unicode planes above U+FFFF. In UTF-8 they are 4 bytes. A utf8/utf8mb3 column cannot hold them, so MySQL raises:

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

With non-strict SQL modes some setups store ? instead — permanent data loss. See the crime-scene version in Wirebook's emoji lesson.

Convert existing data

-- Database default for new tables
ALTER DATABASE yourdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- One table (rewrites every text column)
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Or a single column
ALTER TABLE your_table MODIFY body TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• On MySQL 8+ the default collation is often utf8mb4_0900_ai_ci; either is fine — pick one and stay consistent.
• VARCHAR(255) under utf8mb4 may hit the 65535-byte row limit sooner (255×4). If ALTER fails, shorten indexes (e.g. prefix index) or use VARCHAR(191) for unique keys — a classic InnoDB issue.
• Backup first. CONVERT TO rewrites data; it will not resurrect emoji already stored as ?.

Connection charset (the silent trap)

Even with utf8mb4 columns, a connection still on utf8 will fail on the way in. Set it every time:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
-- or in my.cnf / mysqld.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

ORM / framework notes

Quick checklist

1. Reproduce with a single emoji insert; confirm Error 1366 or ?.
2. SHOW FULL COLUMNS FROM your_table; — look for utf8 / utf8mb3.
3. SHOW VARIABLES LIKE 'character_set%'; — client/connection/database should all say utf8mb4.
4. ALTER + fix the driver/ORM.
5. Re-test. For mystery blobs, What is this?; for UTF-8 byte length, Base64. JSON APIs: validate with the JSON formatter (JSON is UTF-8).

Related

Emoji shows as □ / tofu · Mojibake / ðŸ / é · Invisible zero-width characters · Base64 is not encryption · Wirebook emoji lesson · Anvil text X-ray API · UnicodeDecodeError utf-8

Questions

Published 5 November 2026.