はじめに

Hello World！
YouTubeでエンジニア向けのTipsを紹介しているやっすんです！！

突然ですが、文字列の順序のルール (Collation) って、どういうものがあるか知っていますか？
今回はMySQLのCollationについて詳しく紹介していきます。

【YouTube動画】 MySQL みんながよくわかっていない Collation について解説します！並び替えに使うよ！

Collationの復習

utf8mb4_ja_0900_as_cs_ks という文字列がどういう意味か不安な方は以下のQiita記事か動画を確認してください！

【Qiita】MySQLがバージョン5から8に飛んだ謎、意外と知らないCharset、Collationのこと#Collationについて

【YouTube動画】MySQLがバージョン5から8に飛んだ謎、意外と知らないCharset、Collationのこと

Collationごとの文字の比較

Collationの違いによって区別する文字と区別しない文字に差があります！
今回、下に表でまとめました！

動画の方が見やすいと思うので、以下リンクを貼っておきます。
Collationとは何か

基本的には、Collationにciがあれば半角・全角関係なく、大文字と小文字を区別しません。
そのため、「A」と「a」または「Ａ」と「ａ」を区別するのはutf8mb4_binとcsが付いているところです。
binはバイナリの略で、文字が異なれば区別します。

日本語の「あ」と「ぁ」も大文字と小文字の違いで区別されそうですが、ここがややこしいです。
なぜかgeneral_ciの場合、「あ」と「ぁ」を区別します。
general_ciは今では使わない方が良いと言われているので、深くは追いませんが、参考程度に知っておくと良いと思います。

半角の「A」と全角の「Ａ」もややこしいです。
日本語版であるutf8mb4_ja_0900_の場合、両者を区別しません。
しかし、general_ciでは区別します。

utf8mb4とutf8mb4_jaでは平仮名・片仮名の扱いが異なります。
前者ではcsで平仮名・片仮名・大文字・小文字を区別しますが、後者ではcsでは片仮名・平仮名を区別しません。

このようにCollationによって、文字の扱いが直感的でないということは頭に入れておくと、いざという時役立つと思います！

utf8mb4_0900_	ai_ci	as_ci	as_cs
A, a	区別しない	区別しない	区別する
Ａ, ａ	区別しない	区別しない	区別する
A, Ａ	区別しない	区別しない	区別する
あ, ぁ	区別しない	区別しない	区別する
あ, ア	区別しない	区別しない	区別する
は, ば, ぱ	区別しない	区別する	区別する

utf8mb4_ja_0900_	as_cs	as_cs_ks
A, a	区別する	区別する
Ａ, ａ	区別する	区別する
A, Ａ	区別しない	区別しない
あ, ぁ	区別する	区別する
あ, ア	区別しない	区別する
は, ば, ぱ	区別する	区別する

utf8mb4_	bin	general_ci
A, a	区別する	区別しない
Ａ, ａ	区別する	区別しない
A, Ａ	区別する	区別する
あ, ぁ	区別する	区別する
あ, ア	区別する	区別する
は, ば, ぱ	区別する	区別する

[参考]
MySQL徹底入門第4版 MySQL 8.0対応

utf8mb4_ja_0900_as_csとutf8mb4_0900_as_csの違い

*動画ではこの部分で説明しています。

utf8mb4_ja_0900_as_csは平仮名・片仮名を区別せず、utf8mb4_0900_as_csは区別しません。
それ以外にソートにも違いがあります。

utf8mb4_ja_0900_as_csの方は文字を音読みでソートします！

例えば、「亜」「伊」「宇」「栄」「奥」という文字があったとき、それぞれ以下のようにソートします。

utf8mb4_ja_0900_as_csの場合
亜 < 伊 < 宇 < 栄 < 奥

utf8mb4_0900_as_csの場合
亜 < 伊 < 奥< 宇 < 栄

ソートの計算 (Unicode Collation Algorithm)

*動画ではこの部分で説明しています。

文字には以下のように、Weightと呼ばれる0000で区切られた値が割り当てられています。

例えば、「a」と「A」の場合は以下のような値になります。
はじめの1C47は文字の種類、次の0020はアクセント、最後の0002が大文字・小文字を表しています。

aのWeight
=> 1C47 0000 0020 0000 0002

Weightの値はCollationによって変わることがありますが、以下のようにして求めることができます。

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs;
mysql> SELECT HEX(WEIGHT_STRING('a'));
1C470000002000000002
mysql> SELECT HEX(WEIGHT_STRING('A'));
1C470000002000000008

この値の大小によって文字をソートしていきます。
そのため、aとAを比べた場合、a < Aの順に並びます。

ちなみに文字列の場合、a9とA0の場合はどうなるでしょうか？
Weightを合わせて、その結果でソートしていきます。

まずaと9のWeightは以下のようになります。

a => 1C47 0000 0020 0000 0002
9 => 1C46 0000 0020 0000 0002

文字列では単純にWeightを足すのではなく、種類の同じものを後ろに挿入していく形式になります。
そのため、以下のような値になります。

1C47 1C46 0000 0020 0020 0000 0002 0002

足算にすると、異なる文字列なのに同じになってしまう問題が発生してしまうため、このようになっているのだと思います。
上記のような計算をするため、a9とA0を比較した場合、A0 < a9の順になります。

0 => 1C3D 0000 0020 0000 0002
9 => 1C46 000 000 200 000 0002
0は1c3Dで9よりもWeightが小さい！
合わせると、1C47 1C3D (a9の初めの部分)と1C47 1C46 (A0の初めの部分)で比較することになるので、A0の方が小さい！

まとめ

今回は大変ややこしく、混乱しやすいCollationについて解説しました。
何か間違いや指摘、感想等ありましたら、コメントよろしくお願いします。

twitterやyoutubeでのコメントもお待ちしています！