ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)
ID: database/mysql-charset-mismatch
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 8 | active | — | — | — |
Root Cause
Two columns or expressions in a query use incompatible collations and MySQL cannot determine which collation to use for comparison. Common when joining tables that were created with different default collations, or after upgrading from MySQL 5.7 (utf8/utf8_general_ci default) to MySQL 8.0 (utf8mb4/utf8mb4_0900_ai_ci default).
genericWorkarounds
-
90% success Convert all tables to a consistent character set and collation
Choose a target: utf8mb4 with utf8mb4_unicode_ci (or utf8mb4_0900_ai_ci for MySQL 8.0+). Convert each table: ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Set database default: ALTER DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Warning: this locks each table during conversion.
-
85% success Set consistent defaults for the connection and server to prevent future mismatches
In my.cnf: character-set-server = utf8mb4, collation-server = utf8mb4_unicode_ci. In connection strings, add charset=utf8mb4. Verify with: SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%'; Ensure all three levels (server, database, table) match.
Dead Ends
Common approaches that don't work:
-
Using CONVERT() or COLLATE on every query to work around the mismatch
70% fail
Adding COLLATE or CONVERT to queries is a per-query workaround that prevents index usage on the converted column (causes full table scans). It must be added to every query that compares mismatched columns, creating maintenance burden and performance issues.
-
Changing the server default character set without converting existing tables
80% fail
The server default only affects new tables and columns. Existing tables retain their original character set and collation. The mismatch between old and new tables persists, and the error continues for queries joining them.