TopolearnWrong character sets lose emoji and accented text; collations silently change comparisons and uniqueness.
A character set decides which characters can be stored and how they are encoded; a collation decides how strings compare and sort.
Use utf8mb4, the default since MySQL 8.0. It is real UTF-8 and stores every Unicode character, including emoji. The older utf8 name is an alias for utf8mb3, which stores at most 3 bytes per character and can't hold emoji or some CJK characters — inserting them fails or truncates. It's deprecated; convert old tables with ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4.
The default collation is utf8mb4_0900_ai_ci, based on Unicode 9.0:
ai= accent-insensitive:'e' = 'é'is true.ci= case-insensitive:'a' = 'A'is true.
This affects WHERE, ORDER BY, GROUP BY and unique indexes: with a _ci collation, 'Ana@x.com' and 'ana@x.com' collide in a unique email column — usually what you want. For case-sensitive comparisons use utf8mb4_0900_as_cs or utf8mb4_bin on that column, or COLLATE in a single expression.
Character sets and collations are set at server, database, table and column level, and the connection has its own (SET NAMES utf8mb4). Drivers should be configured for utf8mb4 too, or text gets mangled in transit even if the table is correct.
Example
CREATE TABLE member (
email VARCHAR(255) NOT NULL,
nickname VARCHAR(50) COLLATE utf8mb4_0900_as_cs,
UNIQUE KEY uk_email (email)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
INSERT INTO member VALUES ('ana@example.com', 'Ana 🎲');
INSERT INTO member VALUES ('ANA@example.com', 'ana');
-- ERROR 1062 (23000): Duplicate entry 'ANA@example.com' for key 'member.uk_email'
SELECT 'resume' = 'Résumé'; -- 1 (ai_ci)
SELECT 'resume' = 'Résumé' COLLATE utf8mb4_0900_as_cs; -- 0
SELECT nickname FROM member WHERE nickname = 'ANA 🎲'; -- empty: column is as_csRemember
- Use utf8mb4; legacy utf8 means utf8mb3 and can't store emoji
- _ai_ci collations ignore accents and case, including in unique keys
- Set the connection and driver to utf8mb4 too
Watch out for
- Declaring tables as CHARSET=utf8 and losing emoji or getting 'Incorrect string value' errors.