Collation and charset mismatches are some of the quietest schema bugs in cross-database work: everything compiles, every migration script runs clean, and then a comparison, a join, or a unique constraint behaves differently than it did on the source system. Unlike a missing column or a type mismatch, a collation problem rarely throws an error at migration time — it shows up later, as duplicate rows that should have collided, a sort order that looks wrong to users, or an index that silently stops being used.
What collation and charset actually control
Charset defines which characters a column can store — UTF8, latin1, UTF16, and so on. Collation defines how those characters compare and sort — whether 'a' equals 'A', whether accented letters sort next to their unaccented equivalents, and how binary vs. linguistic ordering is applied. Two columns can hold identical bytes and still behave differently in a WHERE, JOIN, GROUP BY, or unique index, purely because their collations disagree.
Where mismatches quietly break migrations
- Join and comparison semantics change. A case-insensitive collation on the source and a case-sensitive one on the target means
WHERE email = 'User@Example.com'can return different rows after migration. - Unique constraints behave differently. A case-insensitive collation treats
'Fox'and'fox'as the same value for uniqueness purposes; a case-sensitive one does not. Rows that were distinct on the target can suddenly collide, or duplicates that were rejected on the source can slip through. - Sort order shifts. Application-level pagination or "sorted by name" views can silently reorder once collation changes, even though no data changed.
- Cross-table joins mixing collations often force an implicit conversion, which can quietly disable an index the query planner would otherwise have used.
Dialect-specific defaults that catch teams off guard
Every dialect picks its own defaults, and they rarely agree:
- MySQL and MariaDB apply collation at the server, database, table, and even column level, and older instances often still default to
latin1_swedish_cior autf8mb3variant rather than fullutf8mb4— a common source of emoji and multi-byte character truncation during a migration. - PostgreSQL ties collation to the OS or ICU locale at cluster creation time by default, so two "identical" schemas on different hosts can sort text differently unless collation is pinned explicitly per column.
- SQL Server and Azure SQL default to a server-level collation (commonly a case-insensitive one) that every new database inherits unless overridden — teams migrating from a case-sensitive Linux-hosted database are frequently surprised by this.
- Oracle separates character set (database-wide, hard to change after creation) from linguistic sort settings, which can trip up teams assuming charset and collation are the same knob.
How to catch collation drift before it ships
The safest point to catch a collation mismatch is before the migration runs, not after. A proper schema comparison should surface collation and charset differences at the column level as part of the diff, not bury them inside a generic "type changed" flag — they need their own line so a reviewer can decide whether the difference is intentional. Objects with collation-sensitive behaviour, like unique indexes and computed columns, deserve a second look during the same review, since the same nominal type can carry very different runtime behaviour depending on the collation attached to it.
Practical fixes when you find a mismatch
- Decide collation intent explicitly per column rather than inheriting server defaults — especially for anything used in a
JOIN, unique constraint, or user-facing sort. - Standardise on one charset (
UTF8/utf8mb4) across environments unless you have a specific reason not to; mixed charsets across source and target are a frequent cause of truncated or mangled data during migration. - Test uniqueness assumptions after any collation change — run a duplicate-check query against the new collation before enforcing a unique constraint on migrated data.
- Re-run comparisons after schema changes land, since collation can drift again the next time someone creates a table with a default they didn't think to override. See our guide to detecting schema drift for a repeatable process.
Get this into your normal workflow
Collation and charset problems are easy to miss in a manual review and easy to catch in an automated one. FoxSchema's comparison engine flags collation and charset differences alongside every other schema change, across all 10 supported dialects, so they show up in the same diff you're already reviewing before a migration ships. Run it locally with the CLI or self-host it in Docker and add a schema check to your deploy pipeline — check out our install guide to get started.
