Character sets define which characters can be stored, while collations define how strings are compared and sorted. Applications can encounter collation errors when tables, columns, connection settings or SQL expressions use incompatible collations. A safe fix starts with inspection rather than blindly converting the whole database.
Inspect the actual schema
Check server, database, table and column character sets and collations. Also inspect the connection character set used by the PHP application. A database may use utf8mb4 while older tables or individual columns still use a different collation.
Understand comparison errors
An error may occur when a query compares two text expressions with incompatible collations. Explicit COLLATE can be appropriate for a targeted comparison, but it should use a collation supported by the deployed database version. Applying COLLATE to every expression without understanding the schema can hide inconsistencies.
Plan a conversion
Before changing table or column definitions, take a verified backup and test on a copy. Confirm the database engine's supported collations and review index length limits, unique constraints and foreign-key relationships. A conversion can change comparison behavior and reveal values that become duplicates under the new collation.
Configure the PHP connection
Set the connection character set explicitly, typically to utf8mb4, and keep SQL files saved in UTF-8. Do not confuse a connection setting with a schema migration; both layers need to be consistent.
Checklist
- Inspect server, database, table and column collations.
- Confirm the PHP connection uses the intended character set.
- Use targeted COLLATE only when justified.
- Test conversions against a database copy.
- Verify indexes, unique values and multilingual content after migration.