目次
- 照合順序（collation）
- 照合順序（collation）で何が違うのか
- 照合順序はカラム単位で変更するサンプル
- 照合順序（collation）をピンポイントに指定して使うと便利なケース
- どの照合順序（collation）がベストなのか
照合順序（collation）
照合順序（collation）は文字列を比較する際の照合順序のことです。
MariaDB（MySQL）で日本語を使うならコードセットはほぼ「utf8mb4」一択なのですが、それで使える照合順序には以下の４つがあります。
- utf8mb4_unicode_ci
- utf8mb4_general_ci
- utf8mb4_bin
- utf8mb4_0900_ai_ci
ただ、utf8mb4_0900_ai_ciは、MySQL8.0以降で追加されたものなので、MariaDB10.3で試してみましたが、未サポートでした。
照合順序のことを深く考えることは、普段、あまりないです。
少なくとも、自分のまわりでこれをネタに会話してるのを聞いたこともありません。
ただ、ちゃんと考えたほうがいいかもしれないなと思うことがあり、整理してみることにしました。
照合順序（collation）で何が違うのか
とりあえず、MariaDB10.3で使える３つを書くと。
- utf8mb4_unicode_ci：大文字小文字／全角半角を区別しません。
- utf8mb4_general_ci：大文字小文字を区別しません。
- utf8mb4_bin：完全に文字の一致を照合します。
みたいになります。
末尾の「ci」は「大文字小文字を区別しない」を表し、「bin」は「バイナリレベル＝完全一致」を表しています。
unicodeとgeneralは共に「マルチリンガル」を意味しますが、unicodeのほうが、よりあいまい性が高い・・と、いう感じの命名規則になっているようです。
これが、どう違うか実際にためしてみます。
照合順序はカラム単位で変更するサンプル
動作確認のために、こんな感じでテーブルを作ってみます。
CREATE TABLE `test_col` (
`int_id` INT(11) NOT NULL,
`int_no` INT(11) NOT NULL,
`str_default` VARCHAR(255) NULL DEFAULT NULL,
`str_utf_bin` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_bin',
`str_uni_ci` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`str_gen_ci` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`int_id`, `int_no`) USING BTREE
)ENGINE=InnoDB
実行してできたテーブルのカラムの照合順序を確認してみます。
SHOW FULL COLUMNS FROM test_col;;
無指定の場合は「utf8mb4_general_ci」になってます。
つまり、これがMariaDBのデフォルト照合順序ということみたいです。
この各カラムにこんな感じの文字列をインサートして、SELECTをかけて動きの違いを見ていこうかと思います。
- 半角小文字「abcdef」
- 半角大文字「ABCDEF」
- 全角小文字「ａｂｃｄｅｆ」
- 全角大文字「ＡＢＣＤＥＦ」
- 全角カタカナ「カタカナ」
- 全角ひらがな「かたかな」
- 半角カタカナ「ｶﾀｶﾅ」
insertに使ったSQLです。
insert into test_col values(1,1,'abcdef','abcdef','abcdef','abcdef');
insert into test_col values(1,2,'ＡＢＣＤＥＦ','ＡＢＣＤＥＦ','ＡＢＣＤＥＦ','ＡＢＣＤＥＦ');
insert into test_col values(1,3,'カタカナ','カタカナ','カタカナ','カタカナ');
insert into test_col values(1,4,'かたかな','かたかな','かたかな','かたかな');
insert into test_col values(2,1,'ABCDEF','ABCDEF','ABCDEF','ABCDEF');
insert into test_col values(2,2,'ｶﾀｶﾅ','ｶﾀｶﾅ','ｶﾀｶﾅ','ｶﾀｶﾅ');
insert into test_col values(2,3,'ａｂｃｄｅｆ','ａｂｃｄｅｆ','ａｂｃｄｅｆ','ａｂｃｄｅｆ');
さて。
完全一致の「utf8mb4_bin」のカラム
文字列の一致を条件にSELECTをかけてみます。
select int_id,int_no,str_utf_bin from test_col where str_utf_bin = 'abcdef';
結果は
select int_id,int_no,str_utf_bin from test_col where str_utf_bin = 'カタカナ';
結果は
うん。
完全一致したレコードのみが抽出されてますね。
次はデフォルトの「utf8mb4_general_ci」のカラム
上記と同じSQLで、対象のカラムを「utf8mb4_general_ci」を指定したカラムに変えてみます。
select int_id,int_no,str_gen_ci from test_col where str_gen_ci = 'abcdef';
結果は
なるほど、大文字・小文字の区別はされなくなりました。
でも。
select int_id,int_no,str_gen_ci from test_col where str_gen_ci = 'カタカナ';
こちらの結果は同じです。
今度はあいまい度の高い「utf8mb4_unicode_ci」のカラム
同じSQLで対象カラムを「unicode_ci」の照合順序のカラムにします。
select int_id,int_no,str_uni_ci from test_col where str_uni_ci = 'abcdef';
結果は
確かに、全角・半角・大文字・小文字の区別なく比較されているのがわかります。
カタカナのほうも
select int_id,int_no,str_uni_ci from test_col where str_uni_ci = 'カタカナ';
見事に、カタカナ・ひらがな・半角カタカナの区別なく、一致してしまってます。
この結果を見る限り、照合順序（collation）はSQLの実行結果に大きな影響がでることがわかります.。
フレームワーク（railesとかLaravelとか）だと指定の照合順序（collation）があったりするみたいですしね。
照合順序（collation）をピンポイントに指定して使うと便利なケース
ぱっと思いついたのは、文章データに対してLIKEで検索かけるときに「utf8mb4_unicode_ci」の照合順序は便利だなということです。
まあ、なんかの理由で無加工の文章データがあったしたら・・的な非常に限定的なケースの話ではありますが。
だからと言って、全体を「utf8mb4_unicode_ci」にするのは愚策です。
あいまい度が高い＝間違いなく遅い・・でしょうし。
だから、全体はそれ以外にしておいて、必要な条件だけ照合順序を変更するみたいにすれば、ちょっとした裏技に使えそうです。
例えば、「utf8mb4_general_ci」のカラムに対しての以下のSQL
select int_id,int_no,str_gen_ci from test_col where str_gen_ci like '%タカ%';
普通に実行すると、結果はこう
ですけど、like条件の前に「COLLATE utf8mb4_unicode_ci」をつけてやれば。
select int_id,int_no,str_gen_ci from test_col where str_gen_ci COLLATE utf8mb4_unicode_ci LIKE '%タカ%';
結果はこう。
ネタとして知っておいてもいいかなと思うわけです。
どの照合順序（collation）がベストなのか
この問題の答えはありません。
厳密な「utf8mb4_bin」を使うべきだという人もいます。
ユニコードの4バイト文字の完全なサポートという理由で「utf8mb4_unicode_ci」を使うべきという人もいます。
実際のところ、利用するシステムによって答えが違って当然であって、無理にベストを決める必要性もないと思ってます。
でも。
トラブルが起きた時に現場で調査とかをする立場から言わせてもらえば、「なんでもいいから、一度決めたら変更しないでくれ」の一言につきます。
なぜかというと。
何らかの原因で照合順序が変わってしまったことが原因でおきるトラブルは原因調査が非常に困難だからです。
考えてもみてください。
ある日突然に、ユーザから連絡がきて、いつもの出力される結果が違う・・といわれたとします。
当然、基本SQLの条件とかプログラムのロジックを調べます。
いつも使っている開発環境では現象が再現しません。
当たり前です。
問題はないのですから。
正直、途方にくれて、ユーザの罵声を浴びながら、悩み続けることになるわけです。
そんな数日をすごしたあげく、実はシステム担当者が作業をしたときに、元のテーブルに設定されていた特殊な照合順序のことに気づかずに、デフォルトでalterかけてしまっていたみたいだ・・なんて、言われてブチ切れない人がいたら会ってみたいものです。
だから、そんな不幸な人をださないために。
- MariaDBを使うならデフォルトの「utf8mb4_general_ci」にしておく
- テーブルやカラム単位もcreate文の指定で照合順序を変更することは禁止しておく
- 必要ならSQLの条件指定のところで「COLLATE」して使うようにする
ようにしてほしいなというのが、自分の意見です。
まあ・・超超レアケースでしょうけどね。
可能性ゼロではないので。
ではでは。
