MySQL照合順序（collation）の違い｜utf8mb4全種の比較表と選び方

MySQLの照合順序（collation）とは、文字列を「同じとみなすか」「どちらを先に並べるか」を決めるルールのことです。 同じ utf8mb4 でも、utf8mb4_0900_ai_ci なら 'A' = 'a' も 'は' = 'ば' も一致し、utf8mb4_bin ならどちらも別の文字として扱われます。照合順序は WHERE の検索結果だけでなく、UNIQUE 制約、GROUP BY、ORDER BY、JOIN の可否にまで影響します。

この記事では MySQL 8.0.46 で、utf8mb4 の主要10種の照合順序について「A=a」「は=ば」「は=ハ」「ハ=ﾊ」「ｱ=ア」「🍣=🍺」「末尾空白」「全角Ａ=A」などを実際に比較した一覧表を掲載します。名前の読み方、設定の確認SQL、ALTER での変換手順、Illegal mix of collations エラーの再現と直し方、用途別の選び方まで1ページで確認できます。

MySQL 8.0 は2026年4月にサポートが終了しています。この記事の手順は MySQL 8.4 LTS / 9.7 LTS でも同じように使えます。既定の照合順序はどちらも utf8mb4_0900_ai_ci です（9.7 公式マニュアルで確認）。検証は手元の MySQL 8.0.46 で行いました。

utf8mb4照合順序の早見表（実測）

MySQL 8.0.46 で SELECT '左' = '右' COLLATE 照合順序; を実行した結果です（○＝同じとみなす、×＝区別する）。

「末尾空白」は 'abc' = 'abc '（末尾に半角スペース1つ）の比較です。表から読み取れる要点は次の3つです。

照合順序の名前の読み方

照合順序の名前は「文字セット_ベース_言語_接尾辞」の形です。

PAD 属性（PAD SPACE / NO PAD）は名前には出てきませんが、末尾空白の扱いを決める大事な属性です。

自分の環境の属性は次のSQLで確認できます。

SELECT COLLATION_NAME, PAD_ATTRIBUTE, IS_DEFAULT
  FROM information_schema.COLLATIONS
 WHERE CHARACTER_SET_NAME = 'utf8mb4'
   AND COLLATION_NAME IN ('utf8mb4_general_ci','utf8mb4_unicode_ci','utf8mb4_unicode_520_ci',
       'utf8mb4_0900_ai_ci','utf8mb4_0900_as_ci','utf8mb4_0900_as_cs',
       'utf8mb4_ja_0900_as_cs','utf8mb4_ja_0900_as_cs_ks','utf8mb4_bin','utf8mb4_0900_bin')
 ORDER BY COLLATION_NAME;

実行結果（MySQL 8.0.46）:

+--------------------------+---------------+------------+
| COLLATION_NAME           | PAD_ATTRIBUTE | IS_DEFAULT |
+--------------------------+---------------+------------+
| utf8mb4_0900_ai_ci       | NO PAD        | Yes        |
| utf8mb4_0900_as_ci       | NO PAD        |            |
| utf8mb4_0900_as_cs       | NO PAD        |            |
| utf8mb4_0900_bin         | NO PAD        |            |
| utf8mb4_bin              | PAD SPACE     |            |
| utf8mb4_general_ci       | PAD SPACE     |            |
| utf8mb4_ja_0900_as_cs    | NO PAD        |            |
| utf8mb4_ja_0900_as_cs_ks | NO PAD        |            |
| utf8mb4_unicode_520_ci   | PAD SPACE     |            |
| utf8mb4_unicode_ci       | PAD SPACE     |            |
+--------------------------+---------------+------------+

8.0.46 の utf8mb4 には全部で89種の照合順序がありました（SELECT COUNT(*) FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4';）。多くは各言語用で、日本語環境で候補になるのは上の10種です。

比較表を自分の環境で再現するSQL

早見表は次のSQLを照合順序ごとに実行して作りました。COLLATE の後ろを差し替えれば、ほかの照合順序もすぐ確認できます。

-- MySQL 8.0.46 / 8.4 / 9.7。クライアントは utf8mb4 で接続すること
SET NAMES utf8mb4;
SELECT
  'A'   = 'a'    COLLATE utf8mb4_0900_ai_ci AS `A=a`,
  'は'  = 'ば'   COLLATE utf8mb4_0900_ai_ci AS `は=ば`,
  'は'  = 'ハ'   COLLATE utf8mb4_0900_ai_ci AS `は=ハ`,
  'ハ'  = 'ﾊ'    COLLATE utf8mb4_0900_ai_ci AS `ハ=ﾊ`,
  'ｱ'   = 'ア'   COLLATE utf8mb4_0900_ai_ci AS `ｱ=ア`,
  '🍣'  = '🍺'   COLLATE utf8mb4_0900_ai_ci AS `寿司=ビール`,
  'abc' = 'abc ' COLLATE utf8mb4_0900_ai_ci AS `末尾空白`,
  'Ａ'  = 'A'    COLLATE utf8mb4_0900_ai_ci AS `全角A=A`;

+------+---------+---------+---------+---------+------------------+--------------+-----------+
| A=a  | は=ば   | は=ハ   | ハ=ﾊ    | ｱ=ア    | 寿司=ビール      | 末尾空白     | 全角A=A   |
+------+---------+---------+---------+---------+------------------+--------------+-----------+
|    1 |       1 |       1 |       1 |       1 |                0 |            0 |         1 |
+------+---------+---------+---------+---------+------------------+--------------+-----------+

あわせて、表に入れなかった比較も測りました。

0900_ai_ci では「ゃ」と「や」も同じになります。「きゃく」と「きやく」を区別したい検索では、ja 系の照合順序が向いています。

主要な照合順序の特徴

UNIQUE制約・GROUP BY・ORDER BYへの影響

照合順序は比較だけでなく、「重複かどうか」と「並び順」を決めるルールでもあります。

大文字小文字だけ違うメールアドレスが重複エラーになる

CREATE TABLE members_ci (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) COLLATE utf8mb4_0900_ai_ci,
  UNIQUE KEY uk_email (email)
);
INSERT INTO members_ci (email) VALUES ('Taro@example.com');
INSERT INTO members_ci (email) VALUES ('taro@example.com');

ERROR 1062 (23000) at line 6: Duplicate entry 'taro@example.com' for key 'members_ci.uk_email'

_ci の照合順序では Taro と taro が同じ値なので、UNIQUE 制約が「同じメールアドレス」と判定しました。同じ定義を utf8mb4_0900_bin にすると、2件とも登録できます。

CREATE TABLE members_bin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) COLLATE utf8mb4_0900_bin,
  UNIQUE KEY uk_email (email)
);
INSERT INTO members_bin (email) VALUES ('Taro@example.com'),('taro@example.com');
SELECT * FROM members_bin;

+----+------------------+
| id | email            |
+----+------------------+
|  1 | Taro@example.com |
|  2 | taro@example.com |
+----+------------------+

どちらが正しいかは要件次第です。メールアドレスを「大文字小文字違いは同じ人」と扱いたいなら _ci、ログインIDのように1文字ずつ厳密に区別したいなら _bin が合います。

GROUP BY と DISTINCT の件数が変わる

CREATE TABLE tags (name VARCHAR(20) COLLATE utf8mb4_0900_ai_ci);
INSERT INTO tags VALUES ('はし'),('ばし'),('ハシ'),('ﾊｼ'),('Apple'),('apple'),('APPLE'),
                        ('banana'),('Banana'),('🍣'),('🍺');

SELECT name, COUNT(*) FROM tags GROUP BY name;                                     -- 列の照合順序（0900_ai_ci）
SELECT name COLLATE utf8mb4_ja_0900_as_cs_ks AS name, COUNT(*) FROM tags GROUP BY 1; -- ja_ks で集計

+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| はし   |        4 |
| Apple  |        3 |
| banana |        2 |
| 🍣     |        1 |
| 🍺     |        1 |
+--------+----------+
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| はし   |        1 |
| ばし   |        1 |
| ハシ   |        2 |
| Apple  |        1 |
| apple  |        1 |
| APPLE  |        1 |
| banana |        1 |
| Banana |        1 |
| 🍣     |        1 |
| 🍺     |        1 |
+--------+----------+

同じ11行でも、0900_ai_ci では5グループ、ja_0900_as_cs_ks では10グループになりました。ja_ks で「ハシ」が2件なのは、全角「ハシ」と半角「ﾊｼ」を同じとみなすためです。utf8mb4_bin で集計すると11グループに分かれました。COUNT(DISTINCT name) も同じルールで数えるため、0900_ai_ci では 5 です。

また、グループの代表として表示される値（上の「はし」「Apple」）は、同じとみなされた値のうちどれか1つです。表記をそろえて表示したい場合は、登録時に正規化しておくと安定します。

ORDER BY の並び順

SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ' ') FROM tags;                                   -- 0900_ai_ci
SELECT GROUP_CONCAT(name ORDER BY name COLLATE utf8mb4_ja_0900_as_cs_ks SEPARATOR ' ') FROM tags;
SELECT GROUP_CONCAT(name ORDER BY name COLLATE utf8mb4_0900_as_cs SEPARATOR ' ') FROM tags;
SELECT GROUP_CONCAT(name ORDER BY name COLLATE utf8mb4_bin SEPARATOR ' ') FROM tags;

読み取れることは次のとおりです。

同じ値とみなされる行の並びを固定したいときは、ORDER BY name, id のように2つ目のキーを足します。

現在の照合順序を確認するSQL

照合順序は「サーバー → データベース → テーブル → 列」の順に既定値が引き継がれ、実際に比較に使われるのは列の照合順序です。接続（クライアント）にも別の照合順序があります。

-- サーバー・データベース・接続
SELECT @@character_set_server, @@collation_server, @@collation_database,
       @@character_set_connection, @@collation_connection;

-- データベース
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'colltest';

-- テーブル
SELECT TABLE_NAME, TABLE_COLLATION
  FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'colltest' ORDER BY 1;

-- 列
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'colltest' AND COLLATION_NAME IS NOT NULL ORDER BY 1, 2;

-- 値や式の照合順序
SELECT COLLATION('abc'), COLLATION(code) FROM items_old LIMIT 1;

実行結果（抜粋）:

+------------------------+--------------------+----------------------+----------------------------+------------------------+
| @@character_set_server | @@collation_server | @@collation_database | @@character_set_connection | @@collation_connection |
+------------------------+--------------------+----------------------+----------------------------+------------------------+
| utf8mb4                | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci   | utf8mb4                    | utf8mb4_0900_ai_ci     |
+------------------------+--------------------+----------------------+----------------------------+------------------------+
+-------------+--------------------+
| TABLE_NAME  | TABLE_COLLATION    |
+-------------+--------------------+
| items_new   | utf8mb4_0900_ai_ci |
| items_old   | utf8mb4_unicode_ci |
| members_bin | utf8mb4_0900_ai_ci |
| members_ci  | utf8mb4_0900_ai_ci |
| tags        | utf8mb4_0900_ai_ci |
+-------------+--------------------+
+-------------+-------------+--------------------+--------------------+
| TABLE_NAME  | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+-------------+-------------+--------------------+--------------------+
| items_new   | code        | utf8mb4            | utf8mb4_0900_ai_ci |
| items_old   | code        | utf8mb4            | utf8mb4_unicode_ci |
| items_old   | name        | utf8mb4            | utf8mb4_unicode_ci |
| members_bin | email       | utf8mb4            | utf8mb4_0900_bin   |
| members_ci  | email       | utf8mb4            | utf8mb4_0900_ai_ci |
| tags        | name        | utf8mb4            | utf8mb4_0900_ai_ci |
+-------------+-------------+--------------------+--------------------+

members_bin のように、テーブルの既定（0900_ai_ci）と列の照合順序（0900_bin）が違うことはよくあります。比較の挙動を調べるときは、列の COLLATION_NAME まで確認するのが確実です。

Illegal mix of collations エラーの再現と直し方

照合順序の違うテーブルを JOIN する

CREATE TABLE items_old (code VARCHAR(20), name VARCHAR(50))
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE items_new (code VARCHAR(20), stock INT)
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO items_old VALUES ('A-001','ノート'),('a-002','ペン');
INSERT INTO items_new VALUES ('A-001',10),('a-002',5);

SELECT o.code, o.name, n.stock FROM items_old o JOIN items_new n ON o.code = n.code;

ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

列どうしはどちらも IMPLICIT（暗黙）という同じ強さなので、MySQL がどちらのルールで比較するか決められずにエラーになります。

直し方1：その場で COLLATE を付ける

SELECT o.code, o.name, n.stock
  FROM items_old o JOIN items_new n ON o.code = n.code COLLATE utf8mb4_0900_ai_ci;

+-------+-----------+-------+
| code  | name      | stock |
+-------+-----------+-------+
| A-001 | ノート    |    10 |
| a-002 | ペン      |     5 |
+-------+-----------+-------+

明示した COLLATE は EXPLICIT 扱いになり、優先されます。ただし、インデックスのある列側に COLLATE を付けると、そのインデックスで絞り込めなくなる点に注意します。

ALTER TABLE tags ADD INDEX idx_name(name);
EXPLAIN SELECT * FROM tags WHERE name = 'apple';
EXPLAIN SELECT * FROM tags WHERE name COLLATE utf8mb4_bin = 'apple';

実行結果（一部の列を省略）:

| type  | possible_keys | key      | ref   | rows | Extra                    |
| ref   | idx_name      | idx_name | const |    3 | Using index              |
| index | idx_name      | idx_name | NULL  |   11 | Using where; Using index |

1行目はインデックスで3行に絞り込み（type=ref）、2行目はインデックス全体を読んでいます（type=index）。COLLATE は「定数側」や「インデックスのない側」に付けると影響を抑えられます。インデックスの使われ方は別記事「SQLのインデックスが効かない10のパターン」でも扱っています。

直し方2：テーブルを変換して照合順序をそろえる（恒久対応）

ALTER TABLE items_old CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'colltest' AND TABLE_NAME = 'items_old';

+------------+-------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME     |
+------------+-------------+--------------------+
| items_old  | code        | utf8mb4_0900_ai_ci |
| items_old  | name        | utf8mb4_0900_ai_ci |
+------------+-------------+--------------------+

変換後は COLLATE なしの JOIN がそのまま通りました。

接続の照合順序が原因になるケース

以前からの設定を引き継いだアプリやドライバが SET NAMES utf8mb4 COLLATE utf8mb4_general_ci で接続していると、ユーザー変数やプレースホルダの値が general_ci になり、列（0900_ai_ci）と比較してエラーになることがあります。

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
SET @c = 'A-001';
SELECT COLLATION(@c);
SELECT * FROM items_new WHERE code = @c;

+--------------------+
| COLLATION(@c)      |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

WHERE code = @c COLLATE utf8mb4_0900_ai_ci で通りますが、根本的には接続の照合順序を列とそろえる（ドライバの接続オプションで utf8mb4_0900_ai_ci を指定する、SET NAMES utf8mb4 だけにする）のが確実です。

ALTER で照合順序を変換する手順

ALTER DATABASE だけでは既存テーブルが変わらないことを確認しました。

ALTER DATABASE colltest CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs;
SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'colltest';
SELECT TABLE_COLLATION FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'colltest' AND TABLE_NAME = 'items_old';

+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_ja_0900_as_cs  |
+------------------------+
+--------------------+
| TABLE_COLLATION    |
+--------------------+
| utf8mb4_unicode_ci |
+--------------------+

変換前に重複を確認する

区別する照合順序（_bin）から区別しない照合順序（_ci）へ変えると、UNIQUE 制約のある列で重複が生まれて ALTER が止まります。

ALTER TABLE members_bin MODIFY email VARCHAR(100) COLLATE utf8mb4_0900_ai_ci;

ERROR 1062 (23000) at line 1: Duplicate entry 'taro@example.com' for key 'members_bin.uk_email'

変換先の照合順序で GROUP BY すると、事前に重複を洗い出せます。

SELECT email COLLATE utf8mb4_0900_ai_ci AS email_ci, COUNT(*) AS cnt, GROUP_CONCAT(id) AS ids
  FROM members_bin GROUP BY email_ci HAVING cnt > 1;

+------------------+-----+------+
| email_ci         | cnt | ids  |
+------------------+-----+------+
| Taro@example.com |   2 | 1,2  |
+------------------+-----+------+

変換作業のチェックリスト

用途別の照合順序の選び方

迷ったら「テーブル既定は utf8mb4_0900_ai_ci、厳密に区別したい列だけ utf8mb4_0900_bin」という組み合わせにすると、扱いやすく、JOIN 時の照合順序の衝突も起きにくくなります。

MySQL 8.0・8.4・9.7 での違い

よくある質問

Q. utf8mb4_general_ci と utf8mb4_0900_ai_ci のどちらを使えばよいですか？ A. 新しく作るなら既定の utf8mb4_0900_ai_ci が扱いやすいです。実測のとおり、general_ci は絵文字どうしが一致し、日本語の濁点やかなを区別します。0900_ai_ci は絵文字を区別し、かなや濁点を同一視します。既存のテーブルが general_ci なら、混在による Illegal mix を避けるため、まずはそろえることを優先します。

Q. 大文字小文字を区別して検索したいときは、照合順序を変えるしかありませんか？ A. 一時的なら WHERE name = 'Apple' COLLATE utf8mb4_0900_bin のように比較側の値に付けられます。ただし列側に付けるとインデックスで絞り込めなくなるため、常に区別するなら列の照合順序を _bin や _cs に変えるのが確実です。

Q. 照合順序を変えると既存データは書き換わりますか？ A. 文字そのものは変わりません。変わるのは比較・並び順・重複判定のルールです。ただし CONVERT TO はテーブルを再構築するため、大きなテーブルでは時間がかかります。

Q. 「は」と「ハ」は区別したいが、全角と半角のカナは同じにしたいです。 A. 実測では utf8mb4_ja_0900_as_cs_ks が「は≠ハ」「ハ＝ﾊ」でした。この組み合わせに合います。

まとめ

• 照合順序は「同じとみなすか」「並び順」を決めるルールで、検索・UNIQUE・GROUP BY・ORDER BY・JOIN に影響する
• 既定の utf8mb4_0900_ai_ci は大文字小文字・濁点・かな・全角半角を同一視し、絵文字は区別する
• 末尾空白は PAD 属性次第。0900系は NO PAD で区別、utf8mb4_bin や unicode_ci は PAD SPACE で無視
• ja_0900_as_cs_ks は「は≠ハ」「ハ＝ﾊ」。日本語を細かく区別したいときの候補
• Illegal mix of collations は照合順序の不一致。COLLATE で一時対応、ALTER TABLE ... CONVERT TO で恒久対応
• _bin から _ci への変換は重複チェックを先に。変換先の照合順序で GROUP BY すれば洗い出せる
• 迷ったらテーブル既定は 0900_ai_ci、厳密に区別したい列だけ 0900_bin にする

株式会社RJCでは、データベースの文字列の扱い方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。