MySQLのCOLLATIONを理解しないと検索は壊れる
MySQLを使っていて、こんな経験はないでしょうか。
- 同じ文字列のはずなのに検索にヒットしない
- ORDER BYの並び順が直感と違う
- UNIQUE制約なのに重複登録できた（または逆に登録できない）
- LIKE検索の結果が環境によって変わる
多くの人はインデックスやSQLの書き方を疑います。
しかし原因はSQLではありません。
COLLATION（照合順序）です。
データベースにおいて、文字コードが「文字を保存するルール」だとすると、
COLLATIONは「文字を比較するルール」です。
そして検索・ソート・重複判定はすべて「比較」で動いています。
COLLATIONは何を決めているのか
MySQLは文字列をそのまま比較していません。
実際には「同じ文字と見なすか」を照合順序が決めています。
例えば次の文字列です。
- A
- a
人間は同じアルファベットだと考えますが、コンピュータでは別文字です。
ここでCOLLATIONが影響します。
代表的な例として次があります。
- utf8mb4_general_ci
- utf8mb4_bin
この2つは同じUTF-8でも意味がまったく違います。
_ci と _bin の違い
まず「ci」は case insensitive の略です。
つまり大文字小文字を区別しません。
SELECT 'A' = 'a';
utf8mb4_general_ci では結果は true になります。
一方で utf8mb4_bin はバイト単位比較です。
同じSQLでも結果が変わります。
つまり、アプリケーションの仕様がDB設定で変わる可能性があります。
日本語で何が起きるのか
COLLATIONの問題は英語より日本語で顕著になります。
例えば次です。
- ば（濁点分離）
- ば（合成文字）
見た目は同じです。
しかし内部表現は異なります。
照合順序によっては、
- 同一と判定される
- 別文字と判定される
どちらにもなります。
ここでUNIQUE制約があるとどうなるか。
ある環境では登録でき、別の環境ではエラーになります。
これはアプリのバグではありません。
COLLATIONの差です。
LIKE検索が効かない本当の理由
「LIKE '%あ%' が遅い」はよく知られています。
しかし「LIKE検索がヒットしない」ケースも存在します。
原因の1つがCOLLATIONです。
例えば全角と半角です。
- ｶﾀｶﾅ
- カタカナ
照合順序によっては一致扱いになります。
逆に一致しないこともあります。
その結果、検索結果が環境ごとに変わります。
インデックスの問題に見えますが、比較規則の問題です。
ORDER BYが変わる理由
ORDER BY もCOLLATIONの影響を受けます。
SELECT name FROM users ORDER BY name;
このSQLの並び順は「文字コード」ではなく
照合順序の定義で決まります。
例えば次の差が出ます。
- ア → あ → A
- A → あ → ア
ユーザーから見ると「バグ」に見えます。
しかしDBにとっては正しい挙動です。
COLLATIONが複数存在する理由
MySQLには非常に多くのCOLLATIONがあります。
これは言語ごとの並び順が違うためです。
英語、ドイツ語、フランス語、日本語では
「正しいソート順」が異なります。
つまりDBは言語依存の処理を行っています。
ここが多くの開発者が想定していない部分です。
よくある危険な状態
次の状態は実際によく起きます。
- DB：utf8mb4_unicode_ci
- テーブル：utf8mb4_general_ci
- カラム：utf8mb4_bin
この場合、比較結果がSQLごとに変わります。
- JOINが失敗する
- WHEREが効かない
- インデックスが使われない
原因特定は非常に困難になります。
SHOW FULL COLUMNS FROM users;
このコマンドでカラム単位のCOLLATIONを確認できます。
リスクと注意点
最も危険なのは、開発中は問題が出ないことです。
英数字中心のデータでは差が出ません。
本番で日本語が増えた瞬間、次の障害が起きます。
- 検索不具合
- 重複登録
- ソート不整合
しかもSQLは正しく見えるため、調査が長引きます。
後からCOLLATIONを変更すると、テーブル再構築が必要になり、
大規模データでは長時間ロックが発生することもあります。
どう設定すべきか
基本方針はシンプルです。
DB・テーブル・カラムでCOLLATIONを統一する
そして用途で使い分けます。
- 検索用途 → utf8mb4_unicode_ci 系
- 厳密一致 → utf8mb4_bin
特にログインIDやトークンは bin を使わないと
「a」と「A」が同一扱いになります。
COLLATIONの本質
多くの人はデータベースを「保存装置」と考えます。
しかし文字列を扱う場合、DBは比較エンジンでもあります。
文字コードが壊れていなくても、
照合順序が違えばアプリの挙動は変わります。
つまり、SQLのロジックだけでは検索の正しさは決まりません。
比較規則をどこに置くかという設計問題です。
MySQLのトラブルの中でも、COLLATIONは最も気づかれにくく、
しかし影響が広い設定の1つです。
