MySQL の照合順序の命名規則

MySQL の照合順序って難しいですよね。

使いこなすと便利そうだけれども。

照合順序に関するおさらいとか。

そもそも照合順序って

"照合順序"とは、MySQL （に限らないけど）が文字を比較するときの基準・規則です。"Collation" という英単語からきています。「照合順序」という和訳からはわかりにくいのですが、「照合（等しいか等しくないか）と順序（順番が前か後か）を比較する際のルール」です。「＞」「＝」「＜」のどれかということですね。
例えば、SQL文で次の比較文はどう評価されるでしょうか？

‘A’ = ‘a’

プログラミングとかを少しやっていると、 False が返される気がしてきますが、MySQLは「照合順序」によって True も False も返却しうります。
具体的には照合順序が "Unicode\_general\_ci"`（バイト列をユニコードとして読み取り、通常規則で照合、ただし大文字小文字は同一視；結構一般的なものです） だと、 'A' = 'a' は True となります。
あとは細かい例で行くとドイツ語の "ß" = "ss" とか、 Unicode で A を示すバイト列は二種類あるのですが、はたして 0x0041 と 0xFF21 は等しいのか 'A' = 'Ａ' かどうかとかがこれで決まります。


照合順序の命名規則

具体的に命名規則は次の規則に乗っ取っています。

ビット列をどの文字コードで解釈するか

名前の一番最初、Unicode とか utf8 とか ascii とかです。
実際に DB に収納されているのは単なるビット列（例えば 01000001 ）なので、それがどの文字列に該当するのかを示すのがこの値になります。まぁ説明不要ですね。ちなみに例は ascii で解釈した場合の ‘A’ です。

どの文化（言語）で比較するのか

後半は bin とひとくくりにされる場合と、「文化（言語）_大文字小文字比較」の組み合わせで指定される場合があります。
"bin" の場合、バイナリ文字比較なので、一切の文字は個別の物として扱われます。照合する順序はバイナリ順です。
「バイナリ比較なら文字コード関係ないじゃん。ascii_bin も utf8_bin もいっしょじゃね？」と思われるかもしれませんが、utf8 みたいな可変長文字コードをシングルバイトずつ比較していくと文字の比較にならないといった事があるようなないような気がします。（ここらに関してはまだ勉強不足です。少なくともutf8ならシングルバイトずつ比較しても同じ順序が得られるように Unicode -> utf8 へのマッピングがなされていたはずですが）
bin 以外の場合、照合順序は文字コードの後ろに二項目が続きます。先に後ろのほうに関して説明すると、

ci	Case Insensitive	大文字小文字を区別しない
cs	Case Sensitive	大文字小文字を区別する

です。で、そもそもどのどの大文字小文字が対応するのかとかそういうことを区別するのが、アンダーバー "\_" で区切られた中心の文字列です。
代表的な物が、utf8_Unicode_ci とutf8_general_ci の二つです。
照合順序における bin と general_ci と Unicode_ci の違いは以下のような感じになります。

もちろんバイナリでは値は違う

SET NAMES ‘utf8′ COLLATE ‘utf8_bin’;
select CAST( CHAR(65) AS CHAR ) = CAST( CHAR(97) AS CHAR ),
CAST( CHAR(65) AS CHAR ) = CAST( CHAR(239) AS CHAR );
# select ‘A’ = ‘Ａ’, ‘A’ = ‘a’;
# 以下同じ。
> 0, 0

general 比較

SET NAMES ‘utf8′ COLLATE ‘utf8_general_ci’;
SELECT ‘A’ = ‘Ａ’, ‘A’ = ‘a’;
> 0, 1

Unicode 比較

SET NAMES ‘utf8′ COLLATE ‘utf8_Unicode_ci’;
SELECT ‘A’ = ‘Ａ’, ‘A’ = ‘a’; # A と全角のA、Aと小文字のA
> 1, 1

Unicode 比較すると、全角のアルファベットも通常アルファベットと同じ位置にソートされます。日本語の濁音と清音とかも同じ位置です。

SET NAMES ‘utf8′ COLLATE ‘utf8_Unicode_ci’;
SELECT c WHERE c = ‘か’ FROM all_hiragana;
|c |
—-
|か|
|が|

になります。色々便利で面白い機能なんですが、場合によってはシンプルに ascii_bin で扱ってほしくなったりもしますね。

リンク集

• 第9章 キャラクタセットサポート