MySQLの照合順序とutf8mb4：検索結果が変わる理由と設定

MySQLで「はは」を検索したら「パパ」まで出てきた。そんな経験はないでしょうか。原因はデータの壊れではなく、照合順序（collation）という「文字の比べ方のルール」にあります。MySQL 8.0の既定である utf8mb4_0900_ai_ci は、大文字と小文字、濁点と半濁点、ひらがなとカタカナ、全角と半角をまとめて「同じ文字」とみなします。この記事では、文字コード（character set）と照合順序の違いから始めて、主な照合順序で比較結果がどう変わるかを表で整理します。後半ではデータベース・テーブル・列ごとの設定方法と、UNIQUE制約やJOINで起きやすい落とし穴も扱います。

このシナリオで考える

会員テーブル members にニックネームを保存する場面を考えます。照合順序を指定せずに作ると、MySQL 8.0では utf8mb4_0900_ai_ci が使われます。

SET NAMES utf8mb4;

CREATE TABLE members (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  nickname VARCHAR(50) NOT NULL
) DEFAULT CHARSET = utf8mb4;   -- 照合順序は既定の utf8mb4_0900_ai_ci

INSERT INTO members (nickname) VALUES
  ('はは'), ('パパ'), ('ハハ'), ('ﾊﾊ'), ('HAHA'), ('haha');

ここで「はは」を探してみます。

SELECT id, nickname FROM members WHERE nickname = 'はは';

+----+----------+
| id | nickname |
+----+----------+
|  1 | はは     |
|  2 | パパ     |
|  3 | ハハ     |
|  4 | ﾊﾊ       |
+----+----------+

4件ヒットしました。英字の「HAHA」と「haha」も、'haha' で探せば両方返ってきます。これが照合順序の働きです。

文字コードと照合順序の違い

文字コードは「文字をどのバイト列で保存するか」、照合順序は「保存した文字をどう比べて並べるか」を決めます。照合順序は必ずどれか1つの文字コードに属します。名前は 文字コード_言語_UCAバージョン_感度 の形になっています。

項目	決めること	例
文字コード	保存するバイト列と使える文字の範囲	utf8mb4、utf8mb3
照合順序	比較（=、LIKE、GROUP BY）と並べ替え（ORDER BY）のルール	utf8mb4_0900_ai_ci、utf8mb4_bin

名前の末尾にある感度の記号は、次の意味になります。

• ai / as：アクセント（日本語では濁点・半濁点）を無視する / 区別する
• ci / cs：大文字小文字を無視する / 区別する
• ks：ひらがなとカタカナを区別する（kana-sensitive）
• bin：バイト値（コードポイント）でそのまま比べる

なお utf8 は現在 utf8mb3 の別名です。1文字3バイトまでしか扱えず、絵文字を保存できません。新しく作るテーブルは utf8mb4 を選んでください。

照合順序ごとの比較結果を確かめる

同じ比較でも、照合順序を変えると結果が変わります。COLLATE 句を付けると、その式だけ照合順序を切り替えられます。

SELECT
  'はは' = 'パパ' COLLATE utf8mb4_0900_ai_ci    AS ai_ci,
  'はは' = 'パパ' COLLATE utf8mb4_ja_0900_as_cs AS ja_as_cs,
  'はは' = 'パパ' COLLATE utf8mb4_bin           AS bin;

+-------+----------+-----+
| ai_ci | ja_as_cs | bin |
+-------+----------+-----+
|     1 |        0 |   0 |
+-------+----------+-----+

1が「同じ」、0が「違う」です。主な組み合わせをまとめると次のようになります。

比較	0900_ai_ci	ja_0900_as_cs	ja_0900_as_cs_ks	bin
‘abc’ = ‘ABC’（大文字小文字）	1	0	0	0
‘はは’ = ‘パパ’（濁点・半濁点）	1	0	0	0
‘カ’ = ‘ｶ’（全角・半角）	1	0	0	0
‘か’ = ‘カ’（ひらがな・カタカナ）	1	1	0	0
‘🍣’ = ‘🍺’（絵文字どうし）	0	0	0	0

日本語を正確に区別したいなら utf8mb4_ja_0900_as_cs_ks が最も細かく、あいまい検索をしたいなら utf8mb4_0900_ai_ci が向いています。bin は確実ですが、並び順が文字コード順になり、五十音順にはなりません。

古い照合順序と「寿司ビール問題」

MySQL 5.7までよく使われた utf8mb4_general_ci と utf8mb4_unicode_ci には、別の問題があります。絵文字のような4バイト文字をすべて同じ文字として扱ってしまいます。

SELECT
  '🍣' = '🍺' COLLATE utf8mb4_unicode_ci  AS unicode_ci,
  '🍣' = '🍺' COLLATE utf8mb4_0900_ai_ci  AS ai_ci;

+------------+-------+
| unicode_ci | ai_ci |
+------------+-------+
|          1 |     0 |
+------------+-------+

寿司とビールが「同じ」と判定されるため、俗に寿司ビール問題と呼ばれます。8.0の _0900 系に移ると解消します。

照合順序を設定する方法

照合順序は4つの階層で決まり、下の階層ほど優先されます。指定がなければ上の階層の設定を引き継ぎます。

1. サーバー：my.cnf の collation-server
2. データベース：CREATE DATABASE ... COLLATE
3. テーブル：CREATE TABLE ... COLLATE
4. 列：列定義の COLLATE

-- データベース単位
CREATE DATABASE shop
  CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs_ks;

-- 列単位（メールアドレスは大文字小文字を無視、表示名は厳密に）
CREATE TABLE accounts (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  email        VARCHAR(255) COLLATE utf8mb4_0900_ai_ci,
  display_name VARCHAR(50)  COLLATE utf8mb4_ja_0900_as_cs_ks
) DEFAULT CHARSET = utf8mb4;

-- 既存テーブルを変換（全行を書き直すので大きいテーブルは要注意）
ALTER TABLE members
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs_ks;

今の設定は次のSQLで確かめられます。

SHOW VARIABLES LIKE 'collation%';
SHOW FULL COLUMNS FROM members;   -- Collation列に列ごとの照合順序が出る

サーバー側の既定を変えたい場合は、my.cnf の [mysqld] に character-set-server = utf8mb4 と collation-server = utf8mb4_ja_0900_as_cs_ks を書いて再起動します。既にあるデータベースやテーブルには反映されない点に気をつけてください。

よくある落とし穴

照合順序は検索だけでなく、制約やJOINにも効いてきます。特に次の4つで困ることが多いでしょう。

• UNIQUE制約が重複扱いする：ai_ci の列にUNIQUEを付けると、「はは」の後に「パパ」を入れようとして Duplicate entry エラーになります。ユーザー名など区別したい列は、列単位で厳密な照合順序にしておきます。
• Illegal mix of collations エラー：照合順序の違う列どうしを = で比べたりJOINしたりすると発生します。テーブルごとに作った時期が違うと起きやすいので、列の照合順序をそろえて直します。
• COLLATE句でインデックスが効かなくなる：WHERE nickname = 'はは' COLLATE utf8mb4_bin のように列と違う照合順序で比べると、インデックスが使われない場合があります。頻繁に使う比べ方は、列の定義そのものを変えるほうが安全です。
• 末尾の空白の扱い：_0900 系は NO PAD のため、'a' と 'a ' を別の文字列として扱います。古い utf8mb4_general_ci などは末尾の空白を無視するため、移行後に一致件数が変わることがあります。

PostgreSQLでは文字コードはデータベース単位で決まり、照合順序は列や式ごとに指定します。大文字小文字を無視した比較には、ICUの非決定的照合順序や citext 型を使うのが一般的です。

まとめ

文字コードは保存の形、照合順序は比べ方のルールです。MySQL 8.0の既定 utf8mb4_0900_ai_ci は、濁点・大文字小文字・全角半角・かなの違いをまとめて無視します。あいまい検索には便利ですが、ユーザー名のUNIQUE制約などでは思わぬ重複エラーの原因になります。区別したい列は utf8mb4_ja_0900_as_cs_ks や utf8mb4_bin を列単位で指定し、照合順序は列どうしでそろえておきましょう。

参考リンク

• MySQL 8.0 Reference Manual: Unicode Character Sets
• MySQL 8.0 Reference Manual: Specifying Character Sets and Collations
• MySQL 8.0 Reference Manual: Using COLLATE in SQL Statements
• PostgreSQL Documentation: Collation Support

アイキャッチ画像: Photo by Nathaniel Shuman on Unsplash