MySQLのERROR 1267: 照合順序の不正な混在を修正する

エラーの内容

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

MySQLは照合順序のルールが異なる2つの文字列値を検出し、どちらを優先すべきか判断できない状態です。推測するのではなく、処理を中断します。

典型的なトリガー：一方のカラムが utf8mb4_unicode_ci、もう一方が utf8mb4_general_ci である2つのテーブル間のJOIN。一方のテーブルは6ヶ月前に作成され、もう一方は先週作成された場合、その間にサーバーのデフォルト設定が変更されたことが原因として考えられます。それだけで十分です。

発生する理由

MySQLのすべての文字列値には照合順序が設定されています。いずれかの照合順序が優先される場合は、2つの文字列の比較は問題なく行えます。しかし両方がIMPLICITとしてマークされている場合、どちらも優先されず、MySQLは処理を拒否してエラー1267が発生します。

主な原因：

異なる DEFAULT COLLATE 設定で異なる時期に作成されたテーブル
テーブルのデフォルトを上書きするカラムレベルの照合順序
異なる照合順序の値を返すストアドプロシージャや関数
テーブルの照合順序と同期が取れていないセッション変数 collation_connection
デフォルト設定が異なる別のサーバーから復元されたデータベース

ステップ1：不一致箇所の特定

修正を行う前に、どのカラムが不一致を起こしているかを正確に特定します。失敗しているクエリのテーブルに対して以下を実行してください：

-- 特定のテーブルの照合順序を確認する
SHOW FULL COLUMNS FROM your_table_name;

-- データベース全体のすべての文字列カラムをスキャンする
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_NAME, COLUMN_NAME;

データベースレベルのデフォルトも確認します：

SELECT
  SCHEMA_NAME,
  DEFAULT_CHARACTER_SET_NAME,
  DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database_name';

多くの場合、混在した状態が見られます。utf8mb4_unicode_ci を示すカラムと utf8mb4_general_ci を示すカラムが混在しているでしょう。それが問題箇所のリストです。

ステップ2：照合順序の不一致を修正する

オプションA — インラインCOLLATE（スキーマ変更不要）

特定のクエリのブロックを解除する最速の方法です。ALTERもダウンタイムも不要です：

SELECT *
FROM table_a a
JOIN table_b b
  ON a.name COLLATE utf8mb4_unicode_ci = b.name COLLATE utf8mb4_unicode_ci
WHERE a.status = 'active';

文字セットも異なる場合はCONVERTを使用します：

SELECT *
FROM table_a a
JOIN table_b b
  ON CONVERT(a.name USING utf8mb4) COLLATE utf8mb4_unicode_ci
   = CONVERT(b.name USING utf8mb4) COLLATE utf8mb4_unicode_ci;

これは応急処置として捉えてください。問題を一時的に解決しますが、カラムの不一致は依然として存在します。

オプションB — 問題のカラムをALTERする（推奨）

根本原因を直接修正します。標準化する照合順序を選択し、不一致のある各カラムを変更します：

-- 単一カラム
ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

複数カラムを一度に変更する場合：

ALTER TABLE table_b
  MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  MODIFY COLUMN email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

オプションC — テーブル全体を変換する

テーブルの半分が誤っている場合、カラムを1つずつ変更するのは面倒です。すべてを一度に変換します：

ALTER TABLE table_b
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

1つのステートメントですべての文字列カラムが更新されます。注意点：MySQLはテーブルの書き換え中にロックします。50万行のテーブルでは30〜60秒かかることがあるため、トラフィックの少ない時間帯にスケジュールしてください。

オプションD — データベースのデフォルトを修正する

新しいテーブルはデータベースのデフォルト照合順序を継承します。そのデフォルトが誤っていると、作成するすべてのテーブルで再び1267が発生します：

ALTER DATABASE your_database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

重要：これはこの時点以降に作成される新しいテーブルにのみ影響します。既存のテーブルは明示的にALTERするまで現在の照合順序を保持します。

ステップ3：修正を確認する

失敗したクエリを再実行します。正常に完了するはずです。次に、カラムの照合順序が正しいことを確認します：

-- カラムの照合順序が更新されたことを確認する
SHOW FULL COLUMNS FROM table_b;

-- またはinformation_schemaを使用する
SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'table_b';

変更したすべてのカラムで utf8mb4_unicode_ci が一貫して表示されている必要があります。変更漏れがあると、誰かがそのカラムをクエリした瞬間に再び1267が発生します。

補足情報

unicode_ci vs general_ci — どちらを選ぶべきか？

MySQL 5.7またはMariaDBを使用している場合は、utf8mb4_unicode_ci を選択してください。Unicode標準に準拠しており、アクセント付き文字やCJKスクリプトなど、多言語テキストを正しく処理できます。

MySQL 8.0以降を使用している場合、utf8mb4_0900_ai_ci が新しいデフォルトであり、ソートが多いクエリでは顕著に高速です。ただし、すべてのテーブルを一度に更新できる場合にのみ切り替えてください。部分的な採用は不一致の問題を再び引き起こします。

真のルール：1つの照合順序を選んですべてに使用することです。混在させることが1267の原因です。

my.cnfでサーバーのデフォルトを固定する

サーバーレベルで照合順序を設定することで、新しいデータベースとテーブルが自動的に正しい設定で開始されます：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

編集後にMySQLを再起動してください。以降に作成されるデータベースは、明示的な照合順序が指定されない限り、これらの値を継承します。

照合順序の競合源としてのストアドプロシージャ

プロシージャのパラメータには独自の照合順序宣言を持つことができ、テーブルのカラムと静かに競合することがあります。疑わしいプロシージャは以下で確認します：

SHOW CREATE PROCEDURE your_procedure_name;

パラメータリストの CHARACTER SET と COLLATE を確認してください。プロシージャが実際に値を比較する際にのみランタイムでエラーが表面化するため、そこでの不一致は見逃しやすいです。

多数のテーブルを一括修正する

修正すべきテーブルが多数ある場合は、手動で入力するのではなく、information_schema からALTERステートメントを生成します：

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS fix_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

本番環境で実行する前に出力内容を確認してください。各ステートメントは実行中にテーブルをロックします。テーブルサイズが大きい場合は、メンテナンスウィンドウ中に順序立てて実行してください。