MariaDB(MySQL)の照合順序の話

ぼくが初めてデータベースという存在を知ったのは25年ほど前のBtrieveだったと記憶していますが、検索／ソートができて自分でコーディングするよりも早いことに感動したものです。

RDBMS (Relational DataBase Management System) が普及しはじめてからはSQL文だけで集計処理はおろか、表結合まで出来るし、いったいどこまで生産効率が上がってしまうのかと恐ろしくも感じました。

実際、RDBMSが出た当時はSQL ServerやOracle、非常に安価な案件ではAccessなんて例もありましたが、業務システムを開発する上で利用するのはデータの蓄積、参照、集計処理くらいなもので、RDBMS間でSQL文にもそこまで大きな違いはなく、どれも似たようなコーディングで済むため開発効率はとても上がりました。

細かいことをいえばストアドプロシージャの有無や作り方、関数名とか違ったりしますが、そういう部分はフロントエンド側で吸収できる場合も多いです。やたらトリガーとか使いたがるシステムもありましたが、そういうRDBMSに依存した仕様は後々システムの移植作業で難儀したりするんですよね。

と、話がそれましたが、要するにDBエンジンにはSQL文を投げたら結果を返してくれる機能しか求めていなかったぼくですが、ひょんなきっかけでそのエンジンの開発現場で働くことになりました。^※1

そして、今まで自分が思っていたよりもずっと深く「日本語」について考慮されていることを知りました。

それが照合順序だったんですね。

照合順序とは

データベースにおける照合順序とは文字と文字の比較^※1や並び順における重み付け^※2で使われるルールのことです。特にSQL Server等の商用データベースシステムでは多くの照合順序が実装されており、そこがオープンソース系のDBとの大きな違いとアピールしているPRも見かけたことがあります。

ぼくと同じくらい長年ITの現場で働いている知人でも知らなかったりもするのですが、SQL Serverの日本語版だとデフォルトでは「久々」の「々」は前の文字の繰り返しと判定されたり、長音記号「ー」は前の文字の母音と判定したり、かなり複雑なことをしていたりします。例）「ケーキ」＝「ケエキ」

なんでそんなことを、と思うかも知れませんが、辞書や電話帳の並び順がそうなっているからで、そういう順序をDBにやってもらうことで成り立っているシステムが存在するのです。

MariaDB(MySQL)の照合順序

MariaDBで日本語を扱う際に選ぶであろう照合順序は基本的にこの3つです。

• utf8mb4_general_ci …英字の大文字小文字を区別しない。ほかは区別する。
• utf8mb4_unicode_ci …英字の大文字小文字を区別しないし、半角全角も、ひらがなカタカナも区別しない
• utf8mb4_bin …全部区別する。文字コードがすべて。

厳密に言うと前半の utf8mb4 が文字セットで、後半の general_ci/unicode_ci/bin が照合順序です。

照合順序一覧について

指定できる照合順の一覧が知りたい場合はクエリ画面で show collation; と実行すれば一覧が確認できます。show collation like 'utf8mb4%'; とかLIKE句を使うことで、utf8mb4の照合順序一覧を見ることも可能。

末尾の _ci について

Case Insensitiveの略で大文字小文字を区別しない、という意味です。大文字のことをupper-case、小文字のことをlower-caseと呼びますし、プログラマーなら馴染み深い単語ではないでしょうか。sensitive/insensitiveはそれぞれ敏感/鈍感という意味なので、そのまんまですね。

この _ci という表記はMariaDB(MySQL)に限ったことではなく、SQL Serverでもデフォルトの照合順序は Japanese_CI_AS と命名されていたりします。

CI = Case Insensitive ... 大文字小文字を区別しない

AS = Accent Sensitive ... 濁音、半濁音を区別する

サーバーの照合順序

以前の記事、「WindowsにMariaDBをインストールする」でMariaDBをインストールしている場合、セットアップウィザードで [Use UTF8 as default server’s character set] にチェックを入れていると思うので、サーバーの照合順序は utf8_general_ci になっているはずです。

そう、utf8mb4_general_ci ではなく utf8_general_ci なんですね。

utf8とutf8mb4の違い

MariaDB(MySQL)以外のデータベースではutf8をutf8とutf8mb4に区別しているのは聞いたことがないので調べてみたのですが、どうもMySQLは歴史的な事情で1文字3バイトまでしか格納できないutf8と4バイトまで格納できるutf8mb4に分かれているとのこと。

具体的にはJIS X 0213の第3～4水準文字の極一部^※1と絵文字（😊とか）は1文字4バイト使うのでutf8mb4が必須です。レガシーなシステムは別として今後作るシステムでは文字セットはutf8mb4で良いでしょう。

照合順序が適用される順序

MariaDB(MySQL)にはサーバーの照合順序、データベースの照合順序、テーブルの照合順序、カラムの照合順序と、それぞれに照合順序が指定できるので頭の中がこんがらがりそうですが、別に難しいことはありません。

例えば、create database testdb;とだけ入力して実行した場合はサーバーの照合順序がデータベースに設定される、というだけの話しなのです。

なので、無理にサーバーとデータベースの照合順序を合わせなければ！とか考えなくて良いんですね。だって、レンタルサーバーとかの場合、サーバー側の照合順序はutf8_general_ciに設定されていて、ユーザー側では触れない、とか普通にあるじゃないですか。

そんなときでも create database testdb collate utf8mb4_general_ci;とか明示的に指定してあげれば良いのです。場合によってはデータベースが1つしか使えない、その照合順序もレンタルサーバー側で決められている、というケースもあります。

その場合は テーブルを作る際に照合順序を指定してあげれば良いのです。

utf8mb4_general_ciとunicode_ciとbinの違い

collate句を使えばデータベース、テーブル、カラムそれぞれに照合順序を指定することができますし、select文にすらcollate句は使えます。なので、実際にそれぞれの照合順序がどんな動きになるのか試すのも簡単。

select 'a'='A' collate utf8mb4_general_ci;
select 'a'='A' collate utf8mb4_unicode_ci;
select 'a'='A' collate utf8mb4_bin;

こんなselect文を実行して、1が表示されれば一致、0が表示されれば不一致ということです。

上記の例ではutf8mb4_general_ciとutf8mb4_unicode_ciでは1、utf8mb4_binでは0が返ってきますので、前の2つは大文字小文字を区別しない、最後のbinは大文字小文字を区別する、とわかるわけです。

気になる文字を比較してみた

比較した文字		general_ci	unicode_ci	bin
a	A	○	○	×
あ	ア	×	○	×
ア	ｱ	×	○	×
ハ	ﾊﾞ	×	○	×
a	Ａ	×	○	×
+	＋	×	○	×
-	－	×	○	×
1	１	×	○	×
1	①	×	○	×
1	一	×	×	×
-	ー	×	×	×

○=一致　×=不一致

フォントによってはちょっとわかりづらいでしょうか。

5行目の「a」と「Ａ」は半角小文字のaと全角大文字のＡを比較しています。general_ciでは不一致ですが、unicode_ciでは一致していますね。6行目の「+」と「＋」はプラス記号の半角と全角で、こちらも同様です。

最後の2行は漢数字の「一（いち）」と長音記号「ー」ですが、これはさすがにunicode_ciでも不一致でした。

ちょっと以外なのは「1」と「①」がunicode_ciでは一致するあたりでしょうか。

このようにunicode_ciでは半角・全角を問わず、それどころか「ハ」「ﾊﾞ」も一致するため、「ハハ」と「ババ」と「パパ」が一致するなんて不具合だ！などと叫ぶ人もいるくらいです。でも、ビックカメラとビッグカメラが一致するのは便利、と考えるケースもあると思うので、用途次第で使い分ければ良いんじゃないですかね。

ぼくは基本的に utf8mb4_bin を使います。utf8mb4_general_ciがデフォルトなんだからそれでも構わないと思いますし、例えばユーザーIDを登録するようなテーブルでは大文字小文字を区別しないutf8mb4_general_ciのほうが重複チェックが楽なのかも知れません。

また、掲示板やメールのようなシステムで本文の部分は出来るだけあいまいな検索をしたい、という要件がある場合はそこだけutf8mb4_unicode_ciを使うのも良いでしょうね。

ぼくがutf8mb4_binを使いたがる理由は寿司ビール問題という冗談のような問題のためです。

寿司ビール問題とは

寿司ビール問題とか呼ばれはじめたのはTwitterでつぶやかれたのがきっかけだったと思いますが、絵文字の寿司（🍣）とビール（🍺）がMySQLだと同一とみなされる、という話しです。

select '🍣'='🍺' collate utf8mb4_general_ci;
select '🍣'='🍺' collate utf8mb4_unicode_ci;
select '🍣'='🍺' collate utf8mb4_bin;

実際、こんなSQL文を流してみれば一目瞭然なのですが、utf8mb4_general_ciやunicode_ciだと🍣と🍺が一致しちゃいます。これ、バグじゃなくて正式な仕様らしいんですよね。UCA 4.0.0という仕様に準拠していて、絵文字は同じ重みが設定されているとか。

まぁ絵文字で検索するとかあんまり考えづらいですし、utf8mb4_general_ciでも基本的には問題ないと思うんですけど、最近のTwitterとか見ているとユーザー名に普通に絵文字入ってたりしますし、うーん今後作るシステムでは utf8_bin をメインにして、大文字小文字の処理は自前で書くかなぁと思い始めたり。

そんな感じです。

漢数字の並び順について

厳密には照合順序とはあまり関係ない話ですが、ソートに関することなので一応触れておきます。

漢数字はソートしたときに「一二三四五六七八九」と並ばずに「一七三九二五八六四」と並びます。

これもバグではなく、Unicodeだと文字コードがそういう順序で登録されているからどうしようもないんですね。部首の画数順に登録されたとかなんとか。このへんはMySQLに限らず、PHPだろうと他の言語だろうとUnicodeを使っている以上、配列に入れてソートしてみると同じ順になります。

じゃあ別の文字コードならどうか、と懐かしの cp932_japanese_ci を使ったとしてもダメです。cp932は「一九五三四七二八六」の順に登録されているので、数字順にならないことに変わりはありません。結局のところ、漢数字は漢字であって、漢字を文字コードでソートするというのがよくないってことなのでしょう。漢字をソートしたいならよみがな付けようよ、と。

実例としては住所登録で起きそうな問題ですが、丁目でソートしたときにこんなふうに並んでンン？となる人が多いようです。

• 一丁目
• 三丁目
• 二丁目

こういうのは住所のよみがな項目を作ってそちらに「1ちょうめ」「2ちょうめ」「3ちょうめ」と入れ、ソートする際はよみがな項目を使うのがスマートだと思います。ユーザーに入力させるのではなく、登録実行時に自動的に漢数字を数値に変換して保存してあげると素敵ですね。PHPなら str_replace('一', '1', $address); とかそんな感じでしょうか。arrayを使うとスマートに書けそうですが、本題から外れるので別の機会に。

もうデータが出来上がってしまっていてどうしても、ど～～～してもSQL文だけでやりたい、という場合はストアドファンクションとか使った実装ですかねー。実際そういった要件があがってきて、スマートじゃないなーと思いつつも対応した記憶があります。

このへんはさすがに照合順序の話しから脱線しすぎますので、機会があればストアドファンクションの書き方と合わせて別の記事にするかもしないかも。

まとめ

• MariaDB(MySQL)の照合順序で主に使うのはutf8mb4_general_ci/unicode_ci/bin
• utf8では絵文字が使えないので出来ればutf8mb4を使う
• utf8mb4_general_ciでは全ての絵文字が同じコード扱いになる
• 漢数字が数字順に並ばないのは仕様

文字コードについては1記事ではとても書ききれないくらい複雑なので、興味のある方、特に職業としてデータベースに携わっている方はきちんとした日本語処理に関する本を一冊は読んで頂きたい。

ぼくがデータベースエンジンの開発に携わっていた頃にぼろぼろになるまで読み込んだ本があるのですが、20年近く前に出版された本なのでさすがに今となっては技術書というよりは歴史書に近いかも知れません。でも良い本なので機会があれば読んでほしい。

16ビット(2バイト)しか扱えない時代から、常用漢字だけで2000文字以上、漢和辞典によると5万文字以上の漢字をもつ日本語をどのように扱うのか試行錯誤してきた歴史があります。

Unicodeが登場したとき、1文字をすべて16ビットにすれば65535文字も使えるから世界中の文字コードを扱える！という謳い文句だったのですが、正直日本の技術者は失笑していたと思います。全部入れろとは言わないけれど、ちょっと珍しい漢字を登録すると日本語だけでも12000文字くらいになります。

かといって、全部の文字を32ビットにするとデータ量が爆発的に増えます。いくらコンピューターの進化が早いとは言っても、節約できるところは節約していかないとどれだけディスク容量や通信回線が太くなっても賄いきれません。そうして1～4バイトの可変長であるUTF8が普及したわけですが、これにも問題点がないわけじゃありません。

英数字については従来どおり1文字1バイトで済むから良いものの、それ以外に至っては本来2バイトで済むところが3～4バイトになっているわけですし、可変長ということは頭から順番に読み込んでいかないと文字の切り取り操作もできません。

まだしばらくはUTF8が主流だとは思いますが、いつかUTF32の固定長が当たり前の時代がくるのかも知れませんね。