MySQL

MySQL Collation Mismatch "Illegal Mix of Collations" in JOIN

The "Illegal mix of collations" error means you're comparing or joining two text values that use different collations (sorting/comparison rules) — MySQL refuses to guess which collation's rules should win, and requires you to explicitly resolve the mismatch rather than silently picking one.

The Problem

A query joining or comparing columns from different tables fails with:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) 
and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Both columns appear to be normal text columns, storing similar-looking data, which makes the error confusing until the specific collation difference is understood.

Why It Happens

A collation defines the rules MySQL uses for comparing and sorting text (case sensitivity, accent sensitivity, and locale-specific ordering rules). Two text columns can use the same character set (like utf8mb4) but different collations (like utf8mb4_general_ci versus utf8mb4_unicode_ci), and MySQL won't automatically compare values across genuinely different collations without explicit guidance on which rules should apply. Common causes:

  • Tables created at different times, possibly with different MySQL default collation settings in effect at each table's creation time, ending up with inconsistent collations across what should be logically related columns
  • A column explicitly specified a particular collation for a specific reason (like case-sensitive comparison) while a related column elsewhere used the server's general default, without the mismatch being intentional or noticed
  • Data migrated or imported from a different MySQL server or version with different collation defaults, carrying that mismatch into the new environment
  • A comparison between a column and a string literal in the query itself, where the literal's implicit collation (based on connection settings) doesn't match the column's own defined collation

The Fix

First, identify the actual collations involved for each relevant column:

SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
AND column_name IN ('email', 'user_email');

For a permanent, consistent fix, align the collations of the mismatched columns so they match going forward, rather than working around it in every individual query:

ALTER TABLE orders MODIFY email VARCHAR(255) COLLATE utf8mb4_unicode_ci;

Choose one collation standard for your entire application and apply it consistently across new tables and columns going forward, to prevent this class of mismatch from recurring as the schema grows — utf8mb4_unicode_ci (or the newer utf8mb4_0900_ai_ci on MySQL 8+) are common, sensible defaults for general-purpose text unless you have a specific reason requiring different behavior for a particular column.

Set your server or database-level default collation explicitly, so newly created tables consistently use the intended collation without needing to specify it manually every time:

ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For an immediate fix to a specific problematic query without altering the underlying table schema (useful when you don't have permission to alter the table, or want a quick fix before a proper migration), explicitly specify the collation to use for the comparison directly within the query:

SELECT * FROM orders o
JOIN users u ON o.email COLLATE utf8mb4_unicode_ci = u.email COLLATE utf8mb4_unicode_ci;

This forces both sides of the comparison to use the same explicit collation for this specific query, resolving the immediate error without requiring a schema change — appropriate as a quick fix or in situations where you can't modify the table structure directly, though a genuine schema-level fix is preferable for long-term consistency across every query touching these columns, not just the one you happened to fix.

Still Not Working?

If you've aligned column collations but still see the error, check whether the mismatch is actually coming from the connection's own collation settings interacting with a string literal in your query, rather than two table columns directly:

SHOW VARIABLES LIKE 'collation_connection';

A query comparing a column against a literal string (WHERE email = 'user@example.com') uses the connection's collation for that literal, and if it differs from the column's own collation, the same error can occur even though only one side is an actual table column. Explicitly casting the literal with a matching COLLATE clause resolves this specific variant of the issue the same way as the column-to-column case shown above.

You might also like