Use utf8mb4 for text

A character set defines which characters a column can store. Use utf8mb4 for new MySQL applications: it stores every Unicode character, using up to four bytes each.

The trap is historical. MySQL 8.4 keeps the old utf8 name as a deprecated alias for the three-byte utf8mb3 character set. Despite the name, utf8mb3 cannot store characters that need four bytes — which includes every emoji and many CJK characters. With a utf8mb3 column, saving a title like Ship it 🚀 fails in the default strict mode:

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

In older non-strict setups the same insert silently truncated the text at the emoji, which is worse: the data loss surfaced weeks later as user complaints. So spell out utf8mb4 and never rely on the bare utf8 name.

Inspect what the current database uses:

SELECT @@character_set_database, @@collation_database;

For our notes_app database this returns utf8mb4 and utf8mb4_0900_ai_ci, because we created it with explicit settings.

Collations decide comparisons

A collation controls equality and sorting. utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive, so a unique tag named Résumé conflicts with resume. You can watch it happen:

INSERT INTO tags (name) VALUES ('Résumé');
INSERT INTO tags (name) VALUES ('resume');
-- ERROR 1062 (23000): Duplicate entry 'resume' for key 'tags.name'

The two strings differ byte for byte, yet the collation calls them equal, and the unique index enforces that definition of equality.

That behavior is often useful for names and tags — users expect Flavio and flavio to be the same person. If case or accents carry meaning in your product, choose and test a different collation before storing data. utf8mb4_0900_as_cs compares accent- and case-sensitively. Changing a collation after millions of rows exist means rebuilding indexes and re-checking uniqueness, so this is a decision to make early.