Character set, collation and the emoji trap

MySQL has a character set called utf8 which is not UTF-8. It stores at most three bytes per character, and every emoji and some rarer characters need four. Text that includes one is either rejected or silently truncated at that point - which is why a comment can vanish from a paragraph onwards.

Use utf8mb4, everywhere

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And in the connection, which is the half people miss

// PDO
new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $u, $p);

; my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Which collation

• utf8mb4_unicode_ci - sorts and compares by Unicode rules across languages. The safe default.
• utf8mb4_0900_ai_ci - MySQL 8 default, newer Unicode, slightly faster.
• utf8mb4_bin - exact bytes. Use for tokens and hashes, never for names.

Collation decides comparison. Under a case-insensitive collation, WHERE name = 'ali' matches Ali - usually what you want for a search and not what you want for an API key.

Find what is still wrong

SELECT table_name, table_collation
  FROM information_schema.tables
 WHERE table_schema = 'shop' AND table_collation NOT LIKE 'utf8mb4%';

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

Columns are converted separately from tables. A table declared utf8mb4 can still hold latin1 columns from years ago, and those are the ones that break.