Why some joins fail or behave oddly.
WHAT COLLATION IS
The rules for comparing and sorting text: whether case matters, how accented characters compare.
Set per database, per table and per column.
THE PROBLEM
Joining two columns with different collations produces an error about illegal mix of collations, or silently unexpected results.
HOW IT HAPPENS
A table imported from a different server A table created separately with a different default An old installation partly upgraded
CHECKING
phpMyAdmin's Structure view shows the collation per table, and per column when you expand it.
Look for one table differing from the rest.
FIXING IT
The Operations tab for a table offers a collation change, with an option to apply it to all columns.
Back up first. Converting large tables takes time.
WHAT TO STANDARDISE ON
utf8mb4 with a modern collation, matching across the whole database.
WHY IT MATTERS BEYOND ERRORS
Different collations sort and compare differently, so a search may match on one table and not another.
