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

Source: https://www.bytebase.com/reference/mysql/error/1267-illegal-mix-of-collations/

---

## Error Message

```sql
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:

```sql
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.

```sql
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:

```sql
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:

```sql
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.

```sql
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:

```sql
SET collation_connection = 'utf8mb4_general_ci';
```

Or pin the literal's collation in the predicate:

```sql
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()`:

```sql
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:

```sql
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.

```sql
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:

```sql
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.

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) can require a consistent charset and collation on all new tables and columns, stopping the drift that causes 1267 before it reaches production. See also [ERROR 1366: Incorrect String Value](/reference/mysql/error/1366-incorrect-string-value) and [ERROR 1071: Specified Key Was Too Long](/reference/mysql/error/1071-specified-key-was-too-long) for the related charset traps.