ERROR 1267 (HY000): Illegal Mix of Collations in MySQL

Error Message#

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

The two collations and the operation change with the query. Common variations:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
ERROR 1267 (HY000): Illegal mix of collations for operation 'UNION'
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'

If you see 1271 instead of 1267, it’s the same collation-mismatch problem, just raised by a function/expression (such as CONCAT) rather than a direct comparison. Treat them together.

What Triggers This Error#

MySQL was asked to compare or combine two strings whose collations are incompatible, and it cannot decide which one wins. Almost every time, this traces back to a charset migration that converted some objects and missed others. The error message names both collations, which is the fastest clue to where the mismatch lives:

• JOIN across columns with different collations: utf8mb4_general_ci on one table, utf8mb4_unicode_ci on the other.
• Column compared to a literal: the column's collation differs from collation_connection, and the literal takes the connection's.
• UNION of mismatched columns: the two branches resolve to different collations.
• MySQL 8 default against legacy tables: new tables get utf8mb4_0900_ai_ci, while anything restored from a 5.7 dump keeps utf8mb4_general_ci.
• Stored routine parameter mismatch: a parameter's collation differs from the column it is compared against inside the routine.

Fix by Scenario#

JOIN across columns with different collations#

This is the one you will hit most. Two tables were created months apart with different defaults, and a join on their text keys blows up.

SELECT u.name, o.id
FROM users u
JOIN orders o ON u.email = o.email;
-- ERROR 1267: Illegal mix of collations ... for operation '='

Confirm the mismatch first:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
  AND COLUMN_NAME = 'email'
  AND TABLE_NAME IN ('users', 'orders');

For a one-off query, force a collation on the comparison:

SELECT u.name, o.id
FROM users u
JOIN orders o
  ON u.email = o.email COLLATE utf8mb4_unicode_ci;

That gets you unblocked, but it is a patch. If you find yourself adding COLLATE to query after query, the columns themselves disagree and you should fix them once (see Prevention) rather than annotating every join forever.

Column compared to a literal#

A plain WHERE col = 'value' can fail when the column's collation differs from collation_connection. The literal inherits the connection collation, the column has its own, and MySQL refuses to guess.

SHOW VARIABLES LIKE 'collation_connection';
-- e.g. utf8mb4_0900_ai_ci, while the column is utf8mb4_general_ci

Either align the session with the column:

SET collation_connection = 'utf8mb4_general_ci';

Or pin the literal's collation in the predicate:

SELECT * FROM users WHERE name = 'José' COLLATE utf8mb4_general_ci;

UNION of mismatched columns#

When two SELECT branches return text with different collations, the UNION cannot pick one. Normalize both sides with CONVERT():

SELECT CONVERT(email USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM users
UNION
SELECT CONVERT(email USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM legacy_users;

MySQL 8 default against legacy tables#

MySQL 8.0 and later default new tables to utf8mb4_0900_ai_ci. Tables restored from a 5.7 dump keep utf8mb4_general_ci. Any comparison across that line fires 1267. Pick one target collation for the whole schema and convert the stragglers to it:

ALTER TABLE legacy_users
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Convert consistently. A schema with _general_ci, _unicode_ci, and _0900_ai_ci scattered across tables will keep producing this error until you settle on one.

Stored routine parameter mismatch#

A procedure parameter declared without an explicit collation takes the database default, which may not match the column it touches.

CREATE PROCEDURE find_user(IN p_email VARCHAR(255))
BEGIN
  SELECT * FROM users WHERE email = p_email COLLATE utf8mb4_unicode_ci;
END;

Pin the collation in the comparison, or declare the parameter with the matching CHARACTER SET and COLLATE.

Prevention#

• Standardize on one charset and one collation across server, database, table, and column. As of MySQL 8.4, utf8mb4 with a single collation is the right default: utf8mb4_0900_ai_ci for a fresh deployment, or utf8mb4_unicode_ci if you are matching an existing fleet.
• After a 5.7 to 8.0 upgrade or a restore, audit for drift before it bites:

SELECT DISTINCT TABLE_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND DATA_TYPE IN ('varchar','char','text')
ORDER BY COLLATION_NAME;

• Set the connection collation in every application connection string, with SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci', so literals match your columns.
• If you genuinely need two collations in one schema, expect to write explicit COLLATE clauses on every cross-collation comparison. Coercion will not save you.