目次
はじめに
本ブログでは、MySQL 8.4のCollation（照合順序）について、分かりやすく紹介したいと思います！！
「そもそもCollationって何？」、「なんで重要なの？」、「設定を間違えると何か起こるの？」…
そんな疑問をお持ちの方もいると思いますので、このブログで記載したいと思います。
Collationとは？
Collationとは、「文字列をどのように比較・並び替えするか定めたルールブック」です。
例えば：
- 「A」と「a」は同じとみなす？
- アクセント記号「é」と「e」は同じ？
- 日本語の「は」と「ハ」、扱いは？
- 日本語の「あ」、「ア」、「a」、「ｱ」の並び順は？
文字を「比較」して「同じ」と見るか、「違う」と見るか、
どのようなルールで文字を「並べ替え」するかは、 Collation（照合順序）の設定次第です！
Collationのデフォルト値は？
本検証は、MySQL8.0.26およびMySQL8.4.4で検証していますが、
どちらもデフォルト値は、utf8mb4_0900_ai_ciです。
|
1 2 3 4 5 6 7 |
mysql> SELECT @@collation_server; +--------------------+ | @@collation_server | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+ 1 row in set (0.01 sec) |
utf8mb4_0900_ai_ciとは？
utf8mb4_0900_ai_ciは、MySQL 8.0で新しく導入されたUnicode照合順序です。
この名前を分解すると以下のような意味になります：
- utf8mb4: UTF-8の4バイト実装（絵文字や特殊文字を含む全てのUnicode文字をサポート）
- 0900: Unicode Collation Algorithm (UCA) 9.0.0に基づくという規格
- ai: Accent Insensitive（アクセント記号を区別しない）
- ci: Case Insensitive（大文字小文字を区別しない）
簡単に特徴を表すと以下のようになります。
JohnとjohnとJOHNは同じとして扱われるcaféとcafeも同じとして扱われる- 日本語のひらがな・カタカナ・漢字もほぼ問題なく処理できる
なおMySQL8.0およびMySQL8.4にて、
なぜデフォルトになっているかという点については、以下3つの理由が考えられます。
- Unicode 9.0.0準拠で最新の文字処理標準に対応
- 絵文字や多言語文字を広範囲にサポート
- パフォーマンスと精度のバランスが良い
どの程度の文字に対応しているか？
まず表で簡単に纏めると以下のようになります。
「対応」と記載している言語については、一部特殊文字を除いてサポートされているというイメージです！
なお対応言語に関しては、表内にすべて記載すると長くなってしまいますので、
公式リファレンスの「表10.3 Unicode 照合言語指定子」をご参照ください。
| 文字種 | 対応状況 | 例 |
|---|---|---|
| 基本ラテン文字 | ◯ 完全対応 | A-Z, a-z |
| 拡張ラテン文字 | ◯ 完全対応 | À, É, Ñ, ü |
| キリル文字 | ◯ 対応 | А, Б, В |
| 日本語 | ◯ 対応 | ひらがな、カタカナ、漢字 |
| 中国語 | ◯ 対応 | 簡体字、繁体字 |
| 絵文字 | ◯ 対応 | 😀, 👍, 🌟 |
| その他Unicode | ◯ 広範囲対応 | スペイン語など |
どんなシチュエーションで利用できるのか？
先ほどご紹介したように、
パフォーマンスと精度のバランスが良いので、以下のシチュエーションでの利用に適しています。
- 一般的なWebアプリケーションでの利用
- 大文字小文字、アクセントを区別せず、ざっくりとした商品名などのフリーワード検索処理
- 多言語対応が必要なシステムでの利用
Collationの種類と特徴について
デフォルト値以外にもCollationの種類は多く存在します。
本ブログではその中から一部をご紹介します。
ケース1:utf8mb4_0900_as_ci
大文字小文字を区別、アクセントは区別しない
- as: Accent Sensitive（アクセント区別）
- ci: Case Insensitive（大文字小文字を区別しない）
| 項目 | 説明 |
|---|---|
| 動作例 | John ≠ john、résumé ≠ resume、Résumé = résumé |
シチュエーション:
- フランス語など、アクセントの有無が意味を変える言語に対応
- 大文字小文字の違いは無視してもよいが、アクセントは重要な場合
デメリット:
- アクセントの違いによって、検索時にヒットしないケースがある
- アプリケーション側での大文字小文字統一が必要
ケース2:utf8mb4_0900_as_cs
大文字小文字もアクセントも区別する
- as: Accent Sensitive（アクセント区別）
- cs: Case Sensitive（大文字小文字を区別）
| 項目 | 説明 |
|---|---|
| 動作例 | cafe ≠ café、Cafe ≠ cafe |
シチュエーション:
- 厳密な文字列の一致が必要なシステム（例：ログインIDやパスワード、トークンなど）
- 開発者や管理者向けの技術的データ（大文字・小文字・アクセントを厳密に扱う）
デメリット:
- 検索精度は高いが、その代わりユーザビリティが下がる
ケース3:utf8mb4_ja_0900_as_cs_ks
日本語厳密一致（ひらカナ・全半角含む）
- ja: 日本語専用照合順序（Unicode 9.0）
- as: Accent Sensitive（アクセント区別）
- cs: Case Sensitive（大文字小文字を区別）
- ks: Kana Sensitive（ひらがな・カタカナを区別）
| 項目 | 説明 |
|---|---|
| 動作例 | あ ≠ ア ≠ ァ ≠ ｱ |
シチュエーション:
- 日本語データを厳密に比較・ソートしたいとき
- 名前や住所など、「ひらがな」「カタカナ」「全角」「半角」の区別が意味を持つ場面
デメリット:
- 非常に厳密な比較となるため、フリーワード検索に使うとヒットしないケースが増える
データ準備
本検証は、MySQL8.4.4で検証を行っています。
今回は検証用に１つのテーブルを作成し、様々な種類の文字データを投入します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
mysql> #Create Table mysql> create table word_play (target_word varchar(100)); Query OK, 0 rows affected (0.33 sec) mysql> #Data Insert mysql> insert into word_play values -> ('A'), -> ('a'), -> ('Ａ'), -> ('ａ'), -> ('1'), -> ('１'), -> ('あ'), -> ('ぁ'), -> ('ア'), -> ('ァ'), -> ('は'), -> ('ぱ'), -> ('ば'), -> ('ハ'), -> ('バ'), -> ('パ'), -> ('cafe'), -> ('café'), -> ('CAFE'), -> ('Café') -> ; Query OK, 20 rows affected (0.01 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> select * from word_play; +-------------+ | target_word | +-------------+ | A | | a | | Ａ | | ａ | | 1 | | １ | | あ | | ぁ | | ア | | ァ | | は | | ぱ | | ば | | ハ | | バ | | パ | | cafe | | café | | CAFE | | Café | +-------------+ 20 rows in set (0.00 sec) |
実践編：Collationと文字列比較
ケース1:utf8mb4_0900_ai_ci（デフォルト）
デフォルトでは大文字小文字・アクセントは区別しません。
これらの値は「同じ文字」とみなされて一致してしまいますので、
フリーワード検索では便利ですが、厳密な照合には不向きです。
そのため、「CAFE」、「cafe」、「café」、「Café」は全て同一とみなされます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_ai_ci = 'CAFE' -> ; +-------------+ | target_word | +-------------+ | cafe | | café | | CAFE | | Café | +-------------+ 4 rows in set (0.00 sec) |
また、ひらがな・カタカナや濁点・半濁点も区別しません。
この点も許容できる範囲なのか事前に確認しておくと、
後々のトラブルを防止につながります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_ai_ci = 'あ' -> ; +-------------+ | target_word | +-------------+ | あ | | ぁ | | ア | | ァ | +-------------+ 4 rows in set (0.01 sec) mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_ai_ci IN ('ぱ','ば') -> ; +-------------+ | target_word | +-------------+ | は | | ぱ | | ば | | ハ | | バ | | パ | +-------------+ 6 rows in set (0.00 sec) |
ケース2:utf8mb4_0900_as_ci
こちらはアクセントは区別していますが、大文字小文字は区別しない設定です。
そのため、「café」と「cafe」は異なる文字として扱われますが、「CAFE」と「cafe」は同じとみなされます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_as_ci = 'CAFE' -> ; +-------------+ | target_word | +-------------+ | cafe | | CAFE | +-------------+ 2 rows in set (0.00 sec) |
デフォルト値と同様に日本語の場合は、
ひらがな・カタカナや濁点・半濁点は区別しません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_as_ci = 'あ' -> ; +-------------+ | target_word | +-------------+ | あ | | ぁ | | ア | | ァ | +-------------+ 4 rows in set (0.00 sec) mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_as_ci IN ('ぱ','ば') -> ; +-------------+ | target_word | +-------------+ | ぱ | | ば | | バ | | パ | +-------------+ 4 rows in set (0.00 sec) |
ケース3:utf8mb4_ja_0900_as_cs_ks
最後に日本語に特化したケースを見ていきます。
全ての文字を厳密に区別するため、完全一致のみが検索結果として返されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_ja_0900_as_cs_ks = 'CAFE' -> ; +-------------+ | target_word | +-------------+ | CAFE | +-------------+ 1 row in set (0.02 sec) |
日本語の場合は、
ひらがな・カタカナ・濁点・半濁点・大小文字まで厳密に区別されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_ja_0900_as_cs_ks = 'あ' -> ; +-------------+ | target_word | +-------------+ | あ | +-------------+ 1 row in set (0.00 sec) mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_ja_0900_as_cs_ks IN ('ぱ','ば') -> ; +-------------+ | target_word | +-------------+ | ぱ | | ば | +-------------+ 2 rows in set (0.00 sec) |
実践編：Collationとソート順
ケース1:utf8mb4_0900_ai_ci（デフォルト）
デフォルトのCollationでは、
文字の種類（大文字小文字、アクセント、カナ等）は区別していませんので、
ソート後の結果を見るとひらがな、カタカナ、濁点・半濁点の並びに統一感がありません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> SELECT target_word -> FROM word_play -> ORDER BY target_word COLLATE utf8mb4_0900_ai_ci; +-------------+ | target_word | +-------------+ | 1 | | １ | | a | | Ａ | | ａ | | A | | Café | | CAFE | | café | | cafe | | ァ | | ア | | ぁ | | あ | | は | | ぱ | | ば | | ハ | | バ | | パ | +-------------+ 20 rows in set (0.02 sec) |
ケース2:utf8mb4_0900_as_cs
こちらはデフォルト値よりも厳密な文字比較が行われ、
大文字小文字・アクセントを区別し、ソートしていますので、
デフォルト値よりも統一感が出ているように見えます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> SELECT target_word -> FROM word_play -> ORDER BY target_word COLLATE utf8mb4_0900_as_cs; +-------------+ | target_word | +-------------+ | 1 | | １ | | a | | ａ | | A | | Ａ | | cafe | | CAFE | | café | | Café | | ぁ | | あ | | ァ | | ア | | は | | ハ | | ば | | バ | | ぱ | | パ | +-------------+ 20 rows in set (0.01 sec) |
ケース3:utf8mb4_ja_0900_as_cs_ks
日本語専用の厳密ソートにより、
ひらがな・カタカナ・濁点・半濁点まで全て区別されてソートされます。
以下の結果を見ると、
「数値」⇒「アルファベット」⇒「日本語（五十音順）」の順序になって
いるように見えます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> SELECT target_word -> FROM word_play -> ORDER BY target_word COLLATE utf8mb4_ja_0900_as_cs_ks; +-------------+ | target_word | +-------------+ | 1 | | １ | | a | | ａ | | Ａ | | A | | cafe | | CAFE | | café | | Café | | ぁ | | ァ | | あ | | ア | | は | | ハ | | ば | | バ | | ぱ | | パ | +-------------+ 20 rows in set (0.02 sec) |
Collationの注意事項
ケース1:COLLATEのWHERE句の指定位置
COLLATEはWHERE句の左辺・右辺どちらでも記述することができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word = 'あ' COLLATE utf8mb4_0900_ai_ci -> ; +-------------+ | target_word | +-------------+ | あ | | ぁ | | ア | | ァ | +-------------+ 4 rows in set (0.01 sec) |
ただし、以下のように文字列リテラル側にCOLLATEを付与する場合は注意が必要です。
IN句の場合は、複数の値の中から比較をしますので、
IN句の文字列リテラル１つ１つに対して、COLLATEを付与する必要があります。
そのため通常はカラム側に指定することをお勧めいたします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word IN ('ぱ','ば') COLLATE utf8mb4_0900_ai_ci -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE utf8mb4_0900_ai_ci' at line 6 mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word IN ( -> 'ぱ' COLLATE utf8mb4_0900_ai_ci, -> 'ば' COLLATE utf8mb4_0900_ai_ci -> ); +-------------+ | target_word | +-------------+ | は | | ぱ | | ば | | ハ | | バ | | パ | +-------------+ 6 rows in set (0.01 sec) |
なお文字列リテラル側にCOLLATEを付与した場合、
COLLATEの種類を統一しないとエラーになります…
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word IN ( -> 'ぱ' COLLATE utf8mb4_0900_ai_ci, -> 'ば' COLLATE utf8mb4_0900_as_ci -> ); ERROR 1270 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT), (utf8mb4_0900_ai_ci,EXPLICIT), (utf8mb4_0900_as_ci,EXPLICIT) for operation ' IN ' |
ケース2:UNIONを利用した場合の怪奇現象…
まずは以下２つのSQL文と実行結果をご確認ください。
検索条件やCOLLATEは異なるものを使用しており、実行結果も異なっています。
この２つのSQLをUNIONすると実行結果はどうなるのか？想像できますか？？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> #クエリ１ mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_ai_ci = 'ぱ' -> ORDER BY target_word COLLATE utf8mb4_0900_ai_ci -> ; +-------------+ | target_word | +-------------+ | は | | ぱ | | ば | | ハ | | バ | | パ | +-------------+ 6 rows in set (0.00 sec) mysql> #クエリ２ mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_as_ci = 'ば' -> ORDER BY target_word COLLATE utf8mb4_0900_as_ci -> ; +-------------+ | target_word | +-------------+ | ば | | バ | +-------------+ 2 rows in set (0.01 sec) |
なんと「は」が出力されます！？
これは不具合なのでは…と思われる方もいると思いますが、
謎を解くカギは、UNIONとCollationの関係性にあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_ai_ci = 'ぱ' -> UNION -> SELECT -> target_word -> FROM -> word_play -> WHERE -> target_word COLLATE utf8mb4_0900_as_ci = 'ば' -> ; +-------------+ | target_word | +-------------+ | は | +-------------+ 1 row in set (0.00 sec) |
UNION は以下の手順で処理されます。
- 各SELECTの結果を取得
- 結果を共通のCollationでマージ（重複文字の排除）
- 重複排除後、1件にまとめる
処理イメージとしては以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
■1.各SELECTの結果を取得 #クエリ１のSELECT結果 | target_word | +-------------+ | は | | ぱ | | ば | | ハ | | バ | | パ | +-------------+ #クエリ２のSELECT結果 +-------------+ | target_word | +-------------+ | ば | | バ | +-------------+ ■2.結果を**共通のCollationでマージ**（重複文字の排除） #まずは下準備としてクエリ１と２のSELECT結果をまとめると以下のようになります。 | target_word | +-------------+ | は | --クエリ１の結果 | ぱ | --クエリ１の結果 | ば | --クエリ１の結果 | ハ | --クエリ１の結果 | バ | --クエリ１の結果 | パ | --クエリ１の結果 | ば | --クエリ２の結果 | バ | --クエリ２の結果 +-------------+ #「どのCollationが優先され、UNION時のマージ処理が行われるか…」 #答えはクエリ１で指定したCOLLATE（utf8mb4_0900_ai_ci）が使われます。 ■3.重複排除後、1件にまとめる #utf8mb4_0900_ai_ciのルールとして… #「大文字・小文字、アクセント、ひらがな・カタカナ、濁点・半濁点は区別しない」 #つまり「ぱ」「ば」「は」「パ」「バ」「ハ」は、同じ文字列として扱うため、 #その結果、たまたま最初に登場した「は」だけが出力されます。 #これが “怪奇現象” の正体です！？ | target_word | +-------------+ | は | --重複なしのため出力対象 | ぱ | --「は」=「ぱ」であるため、重複とみなし出力対象外 | ば | --「は」=「ば」であるため、重複とみなし出力対象外 | ハ | --「は」=「ハ」であるため、重複とみなし出力対象外 | バ | --「は」=「バ」であるため、重複とみなし出力対象外 | パ | --「は」=「パ」であるため、重複とみなし出力対象外 | ば | --「は」=「ば」であるため、重複とみなし出力対象外 | バ | --「は」=「バ」であるため、重複とみなし出力対象外 +-------------+ |
ケース3:テーブル結合時のCOLLATE不一致
今まではSELECT文中でCOLLATEを付与していましたが、
以下のようにテーブルやカラムレベルで設定することも可能です。
そうすると、クエリごとに指定する必要がなくなるというメリットが生まれます！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> CREATE TABLE user_data ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(50) COLLATE utf8mb4_0900_ai_ci, -> password_hash VARCHAR(255) COLLATE utf8mb4_0900_as_cs, -> display_name VARCHAR(100) COLLATE utf8mb4_ja_0900_as_cs_ks -> ); Query OK, 0 rows affected (0.25 sec) mysql> CREATE TABLE login_info ( -> username VARCHAR(50) COLLATE utf8mb4_0900_as_cs, -> login_date DATETIME(6) -> ); Query OK, 0 rows affected (0.06 sec) mysql> ALTER TABLE user_data -> MODIFY COLUMN password_hash VARCHAR(255) -> COLLATE utf8mb4_ja_0900_as_cs_ks; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 |
ただし、テーブル結合するカラムの照合順序が異なるとエラーになりますので…
照合順序はどちらかに統一する必要があります！
|
1 2 3 4 5 6 |
mysql> SELECT -> main.* -> FROM user_data main -> INNER JOIN login_info sub -> ON main.username = sub.username ; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_0900_as_cs,IMPLICIT) for operation '=' |
まとめ
皆さんは「なぞなぞ」や「なぞかけ」といった言葉遊びをご存じだと思いますが、それぞれ左脳と右脳のどちらが使われるかご存じでしょうか？
- なぞなぞは「右脳でひらめく」遊び
- なぞかけは「左脳で論理的に組み立てる」芸
です。
どちらも楽しめば、
脳が活性化して、不具合もすぐに見抜けるようになる…かもしれませんね！！
それでは、最後になぞかけです…
「MySQL 8.4のCollation」とかけまして、
「推理小説の名探偵」と解きます。
そのこころは…
どちらも “微妙な違い” を見逃しません。
SQL文の見た目は正しそう…でも想定と結果が違っている！？
そんな事件に巻き込まれないよう、本ブログが参考になれば幸いです！！
