MySQL utf8mb4 Collation Choices: 0900_ai_ci, general_ci, or bin

Collations look like the most skippable decision in a schema. Then a customer named Müller cannot log in because his email matches two rows, a JOIN between two microservice-owned tables stops using an index, and a deploy fails with "Illegal mix of collations." Every one of those incidents traces back to a default someone accepted years earlier without reading it.

MySQL 8.0 changed the default collation for utf8mb4 from utf8mb4_general_ci to utf8mb4_0900_ai_ci, which means most fleets that have been around a while are running a mix of both, plus the occasional utf8mb4_bin column someone added for exact matching. These notes cover what actually differs between them, where the mix bites, and the order of operations for converging on one.

What the three collations actually do

A collation is the rule set for comparing and sorting strings; the character set (utf8mb4) only defines the encoding. The three you will meet in the wild:

• utf8mb4_0900_ai_ci — the 8.0 default. Implements Unicode Collation Algorithm 9.0.0; accent-insensitive, case-insensitive. Linguistically correct comparisons: the German sharp s equals "ss", accented characters equal their base forms, and comparisons cover the full Unicode range including supplementary characters.
• utf8mb4_general_ci — the legacy pre-8.0 default. A simplified per-character comparison that predates proper UCA support. Case-insensitive, mostly accent-insensitive for Latin-1-ish characters, but with real correctness gaps: it treats the sharp s as equal to a single "s", and it does not handle multi-character comparisons or many scripts correctly.
• utf8mb4_bin — byte-wise comparison of the encoded characters. "Abc" and "abc" are different strings. No linguistic rules at all, which is exactly what you want for tokens, hashes, API keys, and case-sensitive identifiers.

There is one more difference that causes subtle bugs: padding behavior. The 0900 family is NO PAD, while general_ci and most older collations are PAD SPACE, meaning they treat trailing spaces as insignificant in comparisons. Under general_ci, the string 'abc' with a trailing space equals 'abc'; under 0900_ai_ci it does not. Unique keys, deduplication jobs, and login lookups can all change behavior across this boundary. You can see the semantics directly:

SELECT 'Muller'  = 'Müller' COLLATE utf8mb4_0900_ai_ci AS ai_ci_match,
       'Muller'  = 'Müller' COLLATE utf8mb4_general_ci AS general_match,
       'Strasse' = 'Straße' COLLATE utf8mb4_0900_ai_ci AS sharp_s_0900,
       'Strasse' = 'Straße' COLLATE utf8mb4_general_ci AS sharp_s_general,
       'abc '    = 'abc'    COLLATE utf8mb4_general_ci AS pad_space,
       'abc '    = 'abc'    COLLATE utf8mb4_0900_ai_ci AS no_pad;

Run that on your own server before a migration and keep the output in the migration ticket. It makes the semantic change concrete for reviewers who think a collation change is a formality.

Which one to pick

My defaults, having lived with the alternatives: utf8mb4_0900_ai_ci for human-language text — names, titles, descriptions, anything a person searches for — because its matching is what users expect. utf8mb4_bin (or the BINARY-adjacent VARBINARY where appropriate) for machine identifiers: tokens, external IDs, base64 values, anything where "equal" must mean "identical bytes". If you need case-sensitive but accent-aware behavior, utf8mb4_0900_as_cs exists, though I rarely reach for it. What I never choose for new work is general_ci; its only remaining virtue is compatibility with what you already have, and that virtue evaporates the day you finish migrating.

While choosing, also retire the utf8 alias wherever you see it. In MySQL 8.0 the bare name utf8 still means utf8mb3, the three-byte subset that cannot store emoji or most supplementary characters, and utf8mb3 is deprecated. Any column audit that surfaces utf8mb3 columns has found two migrations, not one: the character set has to widen to utf8mb4 before the collation question is even meaningful, and that conversion is itself a table rebuild with the same index-length considerations around 191-character prefix limits that older setups worked around.

One entity worth special care is email addresses. Case-insensitive matching on the whole address is common and usually fine in practice, but a unique key under an accent-insensitive collation means jose@example.com and josé@example.com collide. For login identifiers, I store a normalized lowercase copy and keep the unique key on a binary-ish collation, so the database enforces exactly the equality the application defines.

The JOIN problem: collation mismatch disables indexes

Here is the operational bite. When you JOIN or compare two columns with different collations, MySQL must coerce one side to the other's collation. Coercing a column means applying a conversion to every row's value, and a comparison against a converted value cannot use that column's index. The query still runs — correctly, even — but a 2 ms indexed lookup becomes a 40-second scan the day the tables get big enough to notice.

This failure mode loves service boundaries: team A created their table in 2019 under general_ci, team B created theirs in 2024 under the 0900 default, and the cross-service reconciliation query joins them on a VARCHAR key. When the collations cannot be coerced by the usual rules — for example, comparing two columns of equal coercibility — you get the explicit version of the problem instead: ERROR 1267, "Illegal mix of collations". The explicit error is friendlier than the silent index loss, because at least it shows up in testing. Audit for the mix before it audits you:

SELECT TABLE_NAME,
       COLUMN_NAME,
       CHARACTER_SET_NAME,
       COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND CHARACTER_SET_NAME IS NOT NULL
  AND COLLATION_NAME <> 'utf8mb4_0900_ai_ci'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Anything that query returns which participates in a JOIN key belongs at the top of your migration list.

Migration order of operations

Converting collations on a live system rewards paranoia. The sequence that has worked for me:

• 1. Audit. Inventory every column collation (the query above), and map which columns join to which. The JOIN graph, not the table list, defines your batches.
• 2. Fix defaults first. Set the server, database, and table defaults to the target collation so new columns stop making the problem worse.
• 3. Test semantics on real data. Look for rows that are distinct under the old collation but equal under the new one, especially in unique keys. This is the step that finds the two Müllers before ALTER TABLE does, since the conversion will fail on the duplicate key — or worse, succeed and change query results.
• 4. Convert in JOIN-connected groups. ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci is a table-rebuilding COPY operation, so schedule it like one — the locking and replica-lag considerations from MySQL online DDL vs PostgreSQL DDL locks all apply, and big tables call for online schema change tooling. Convert tables that join to each other in the same window, or add explicit COLLATE clauses to the bridging queries during the transition so indexes keep working.
• 5. Re-verify plans. After each batch, EXPLAIN the hot JOIN queries and confirm index usage returned.

The mistake to avoid is converting tables in arbitrary order over months. Every partially converted week is a week where some JOIN somewhere is running without its index.

How this compares to PostgreSQL

If you operate both engines, the mental models differ enough to cause mistakes in both directions. PostgreSQL ties collation to the database (or column) via OS or ICU locales, treats equality byte-wise for unique indexes under most deterministic collations, and has its own migration pain around ICU versioning. MySQL puts collation into every comparison's semantics, including uniqueness. I wrote a fuller comparison in MySQL utf8mb4 vs PostgreSQL UTF-8; the summary is that neither model is strictly better, but assuming one engine's behavior while operating the other is how login bugs get shipped.

Where MonPG fits

To be clear about what we sell: MonPG is a PostgreSQL monitoring platform, and MySQL support is coming soon — it does not monitor MySQL today. Collation problems earn a place in that roadmap because they surface as monitoring signals first: a JOIN whose latency steps up 100x after a schema change, a rebuild-heavy ALTER saturating a replica, a duplicate-key error spike during a conversion. Progress lives on the MySQL monitoring (coming soon) page. Teams that also run PostgreSQL can start there now, where query-plan and latency regression evidence of exactly this kind is already part of the product.