MySQLのエラー『Illegal Mix of Collations』の解決方法

MySQLでSQLを実行した際に「Illegal mix of collations」エラーが発生する場合がある。このエラーは、異なる照合順序（collation）を持つ文字列同士を比較・結合しようとしたときに発生する。照合順序の違いによって演算が正しく行えないため、エラーが出力される。

1. エラーの発生条件

このエラーは主に以下のような操作で発生する。

• 異なる照合順序のカラム同士でJOINする
• 異なる照合順序のカラムと文字列リテラルを比較する
• 異なる照合順序の値をUNIONやORDER BYでまとめる

SELECT * FROM users u
JOIN customers c ON u.name = c.name;
-- 片方がutf8mb4_general_ci、もう片方がutf8mb4_unicode_ciだとエラー

2. エラーのメッセージ内容

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

このように、どの照合順序が衝突しているかが表示される。

3. 照合順序とは何か

照合順序（collation）は、文字列の比較方法やソートのルールを定義する。たとえば、`utf8mb4_general_ci` はアクセントや大文字小文字の違いを無視して比較するが、`utf8mb4_bin` はバイナリレベルで厳密に比較する。

4. テーブル定義で異なる照合順序がある例

CREATE TABLE users (
  id INT,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
);

CREATE TABLE customers (
  id INT,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

5. COLLATE句で照合順序を明示的に揃える

照合順序の違いをSQL内で解消するには、COLLATE句を使って合わせる。

SELECT * FROM users u
JOIN customers c ON u.name COLLATE utf8mb4_unicode_ci = c.name;

または、両方とも `utf8mb4_general_ci` に揃えるなども可能。

6. カラムの照合順序を確認する方法

SHOW FULL COLUMNS FROM users;
SHOW FULL COLUMNS FROM customers;

`Collation` の列で各カラムの照合順序を確認できる。

7. 照合順序の変換方法（ALTER TABLE）

テーブルの既存カラムの照合順序を統一する場合はALTER TABLEを使用する。

ALTER TABLE users MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

すべての文字列型カラムに対して同様の操作を行う。

8. デフォルトの照合順序を揃える（テーブル・データベース）

テーブルやデータベースの作成時に、照合順序を明示的に指定することで将来的な衝突を防げる。

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE users (
  id INT,
  name VARCHAR(255)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

9. 照合順序が一致していないとJOINやWHERE句でエラー

比較演算子（=, LIKE, > など）を使うとき、片方が `utf8mb4_bin` など厳密な比較を要求する照合順序だと、自動変換ができずにエラーになる。

10. ORDER BYやGROUP BYでもエラーになるケース

UNIONやORDER BYなどで異なる照合順序のカラムが混在しているとエラーになる。

SELECT name FROM users
UNION
SELECT name FROM customers;

この場合もCOLLATEで揃える必要がある。

SELECT name COLLATE utf8mb4_unicode_ci FROM users
UNION
SELECT name FROM customers;

11. アプリケーション側のバインド値にも注意

PHPやNode.jsなどのアプリケーションからSQLを投げる場合、バインドする値の文字コードがテーブルと異なると衝突することがある。その場合は明示的に `COLLATE` を使うか、接続時に `SET NAMES` を使って照合順序を統一する。

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

12. 問題の根本を防ぐための運用ルール

照合順序の混在を防ぐために、以下を運用ルールにするのが望ましい。

• すべてのテーブルで同じCHARACTER SETとCOLLATEを使う
• 新規カラム追加時にもCOLLATEを意識する
• データベース作成時にCHARSETとCOLLATEを明示