collationの罠だよー

という訳で罠にハマったわよ。ごきげんよう。

「は」「ぱ」「パ」「ﾊﾟ」などの全角・半角のゆらぎを気にせず検索したい
そんな時はテーブル・カラムにcollationを設定するか、SELECT時にcollationを設定するのはよく知られたお話ね。
「utf8_unicode_ci」は『大文字・小文字,濁点・半濁点』を区別しない
「utf8_bin」は『大文字・小文字,濁点・半濁点』を区別する(バイナリ)
そのへんについての詳細は、優しい人がまとめてくれているわ。
(mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索 http://d.hatena.ne.jp/end0tknr/20100613/1276427626)

select title from node where title = 'ぱ' collate utf8_unicode_ci;
select title from node where title = 'は' collate utf8_unicode_ci;
select title from node where title = 'パ' collate utf8_unicode_ci;
select title from node where title = 'ハ' collate utf8_unicode_ci;
select title from node where title = 'ﾊﾟ' collate utf8_unicode_ci;
select title from node where title = 'ﾊ' collate utf8_unicode_ci;

これ全部「ぱ」でも「は」でも「パ」でも「ハ」でも「ﾊﾟ」でもセレクトできる訳だけど
（でも「ぱ」と「は」まで可能なのは吸収しすぎよね。そんなに気を使ってくれなくてもいいのに。）
一文字だけに検索する事ってなかなかない事態なのよね。で、みんな多分LIKE検索だと思うの。

select title from node where title like '%ぱ%' collate utf8_unicode_ci;
select title from node where title like '%は%' collate utf8_unicode_ci;
select title from node where title like '%パ%' collate utf8_unicode_ci;
select title from node where title like '%ハ%' collate utf8_unicode_ci;
select title from node where title like '%ﾊ%' collate utf8_unicode_ci;

と、ここまではパンツだろうがぱんつだろうがﾊﾟﾝﾂだろうきゃりーぱみゅぱみゅだろうが拾ってくれるんだけど

select title from node where title like '%ﾊﾟ%' collate utf8_unicode_ci;

こうなるともーダメ。Empty setよ。だいたい半角で検索しようなんていうのがおかしいと思うんだけど、
そんな理屈が通用しないのが尋常ってやつなのよ。悲しいわね。御無体よね。

どうやらオフィシャルの説明によると（11.5.1. String Comparison Functions　http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html）

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

「LIKEは一文字ごとの比較で、=での比較とは違うから、おんなじ動きをするとは限らないわよ」

ホーリーシットよ。どういう事よ。かなしきマルチバイト諸国よ。
今回はPHPとmysqlでやってるから事前に検索値にプログラム側から半角カナを全角カナに変換して
mb_convert_kana('ﾊﾟﾝﾂ','KV');ってしておけばいいと思うけど、
これmysqlだけで処理しようとしたらどうすればいいのかしらね！
そんな訳で今日はここまで。今週末は最後の新年会があるのよ。いつまでも新年気分引きずってる場合じゃないから、一区切りつけてくるわ！ちゃお！