Introduction

The MySQL error about an illegal mix of collations appears when the server must compare or combine strings that use different collation rules. A common example is utf8mb4_unicode_ci on one column and utf8mb4_general_ci on another. The fix is usually not in the query alone; it is in making your schema consistent so MySQL no longer has to guess how strings should be compared.

Why the Error Happens

A character set defines how text is stored. A collation defines how text in that character set is compared and sorted. Two columns may both use utf8mb4 and still be incompatible for some operations if their collations differ.

This can fail in joins, WHERE comparisons, UNION, ORDER BY, string functions, and concatenation. For example:

1CREATE TABLE users (
2    username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
3);
4
5CREATE TABLE logins (
6    username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
7);
8
9SELECT u.username
10FROM users AS u
11JOIN logins AS l
12  ON u.username = l.username;

Even though both columns use utf8mb4, MySQL can reject the comparison because the collations are different.

Inspect the Actual Collations

Start by checking the column definitions, not just the database default. The mismatch often exists at the table or column level.

1SHOW FULL COLUMNS FROM users;
2SHOW FULL COLUMNS FROM logins;
3SHOW CREATE TABLE users;
4SHOW CREATE TABLE logins;

If multiple tables are involved, inspect every participating text column. The database default only affects newly created objects; it does not automatically rewrite older columns.

Short-Term Query Fix

If you need an immediate workaround for a single query, explicitly cast one side to the other collation:

1SELECT u.username
2FROM users AS u
3JOIN logins AS l
4  ON u.username COLLATE utf8mb4_unicode_ci = l.username;

You can do the same in a WHERE clause:

SELECT *
FROM users
WHERE username COLLATE utf8mb4_general_ci = 'alice';

This solves the comparison for that statement, but it is not the best long-term fix. Repeating COLLATE across the codebase is noisy and easy to miss.

Long-Term Schema Fix

The durable fix is to standardize the collation across related columns, tables, or the entire schema. For many older applications that means choosing one of the existing utf8mb4 collations and converting everything to it.

To convert one table:

ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

To convert one column:

1ALTER TABLE logins
2MODIFY username VARCHAR(50)
3CHARACTER SET utf8mb4
4COLLATE utf8mb4_unicode_ci;

If you are standardizing a whole schema, update the database default as well so new tables inherit the intended collation:

ALTER DATABASE appdb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

On MySQL 8, some teams prefer newer collations such as utf8mb4_0900_ai_ci. That can be a good choice, but consistency matters more than the specific collation name. Pick one standard and apply it everywhere relevant.

How to Choose Between unicode_ci and general_ci

utf8mb4_unicode_ci generally provides more linguistically correct comparisons than utf8mb4_general_ci. The general collation is older and simpler. In modern applications, the usual advice is to avoid mixing them rather than trying to keep both for performance reasons.

If your application already relies on one collation's exact comparison behavior, test carefully before converting production data. Sorting, uniqueness checks, and comparison outcomes can change.

Common Pitfalls

The biggest mistake is fixing only the query while leaving the schema inconsistent. The error then returns later in a join, migration, or report query.

Another common problem is updating the database default and assuming that old tables changed automatically. They did not. Existing tables and columns keep their previous definitions until you alter them explicitly.

It is also easy to overlook literals and connection settings. If the application connection uses a different character set or collation than the table, MySQL may still need coercion rules during comparison.

Finally, do not change collations blindly on large production tables. Rebuild cost, index behavior, and sort order changes should be evaluated before rollout.

Summary

• The error occurs when MySQL compares strings that use different collations.
• 'utf8mb4_unicode_ci and utf8mb4_general_ci can conflict even though both use utf8mb4.'
• 'SHOW FULL COLUMNS and SHOW CREATE TABLE help locate the real mismatch.'
• 'COLLATE in a query is a short-term workaround.'
• The long-term fix is to standardize table and column collations across the schema.