Resolve Character Set and Collation Conflicts in MySQL

Last updated: August 28, 2026.

A character set defines how text is encoded; a collation defines comparison and sorting rules. Errors appear when expressions combine incompatible collations or legacy columns cannot represent incoming text.

Inventory the database

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'appdb'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Plan conversion

• Prefer utf8mb4 for full Unicode text.
• Choose a collation that matches comparison requirements.
• Back up and test ALTER TABLE conversions on real data.
• Set the client connection character set explicitly.
• Review index length and uniqueness behavior before conversion.

Reference: MySQL 8.4 character set reference.