MySQL "Incorrect string value": store emoji and everything else with utf8mb4

1. See what you have

The column’s own character set is what counts:

SHOW FULL COLUMNS FROM notes;
SHOW CREATE TABLE notes\G

To list everything in the database that isn’t utf8mb4:

SELECT table_name, column_name, character_set_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

And for the connection:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

The bytes in the message hint at which case you have:

2. Convert the table

This one converts the columns, along with their data:

ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

To set the default for new tables too:

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

On MariaDB, or MySQL 5.7, use utf8mb4_unicode_ci, because the 0900 collations don’t exist there.

ALTER DATABASE doesn’t change the tables you already have, and neither does changing the server’s defaults. Each existing table has to be converted.

A table rewrite can take a long time and lock it. On a big table, test on a copy, take a backup first, and use a tool such as pt-online-schema-change or gh-ost.

3. Fix the connection

The connection has to use utf8mb4 too, or the data gets altered on the way:

Importing a dump? Use mysql --default-character-set=utf8mb4 shop < dump.sql, with mysqldump --default-character-set=utf8mb4 on the way out.

And set defaults on the server, for everything new:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

4. “Specified key was too long”

The conversion can fail with this on older setups:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Now each character can take 4 bytes, so VARCHAR(255) means up to 1,020 bytes. MySQL 5.7 and 8.0 allow 3,072 bytes on tables with ROW_FORMAT=DYNAMIC, which is the default, so it’s mostly older tables that hit this:

ALTER TABLE users ROW_FORMAT=DYNAMIC;

Or index less of it:

CREATE INDEX idx_users_email ON users (email(191));

A prefix index can’t enforce full uniqueness, though. For a UNIQUE column, the better fix is a shorter column. An email doesn’t need more than 254 characters, and VARCHAR(191) usually fits.

5. Query errors after converting

Two columns or a column and a connection using different collations give this:

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

So convert all related tables, particularly those you join on text columns. Mixed character sets on a join also stop it from using an index, so it gets slow without any error.

The new collation also changes comparisons. utf8mb4_0900_ai_ci ignores case and accents, so 'e' = 'é' is true. Your UNIQUE columns might now see two old values as equal, and the conversion stops with a duplicate key error. Find those first, or pick a different collation for that column.

6. Characters already show as garbage

Seeing é instead of é, or question marks? That data was already damaged. A common cause is a client sending UTF-8 over a latin1 connection. Converting the table won’t repair it, and can make it worse, so test on a copy first. Question marks are lost for good, as the original was replaced when it was stored.