SOFTELメモ

</> 技術者募集

【MySQL】大文字小文字、全角半角区別しないでマッチする検索をしたい

問題

select * from member where namae like '%サトウ%';

こんなSQLで、namaeがサトウ、サトウ、さとう、サトウ(一部半角)何でもマッチさせたい!

答え

では、これで。

select * from member where namae collate utf8_unicode_ci like '%サトウ%';

データベースがutf8でないときは、もうひとつ変換を入れて、

/* ERROR 1253: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'ujis' など言われたら */

select * from member where convert(namae using utf8) collate utf8_unicode_ci like '%サトウ%';

数字の全角/半角のゆれも吸収できる。以下のようなSQLで、品番が123で始まる、123456や123456(全角)などが抽出できる。

select * from item where hinban collate utf8_unicode_ci like '123%';

collate で照合順序をutf8_unicode_ciに指定することで、数字、アルファベット、半角カナ、全角カナ、ひらがな、カタカナと、かなり広範囲にわたってマッチするようになる。

カラム自体に collate utf8_unicode_ci の設定をすることができるが、いつもそうなっていると意図しないレコードが検索に引っかかる可能性が出てくるので、必要なときにだけ、つどSQLで指定すればよいと思います。

関連するメモ

コメント(3)

通りすがり 2013年5月27日 09:49

utf8_unicode_ciにすると「ざどう」でもひっかからないですか?
参考リンク
http://blog.goo.ne.jp/gusyazero/e/0fa3848ea72a933108de91dca3db53c5

yoshimura 2013年5月27日 10:15

ひっかかりますね。

select 'さとう' collate utf8_unicode_ci like '%ザ%'; /* → 真 */
select 'さとう' collate utf8_unicode_ci like '%さ%'; /* → 真 */
select 'さとう' collate utf8_unicode_ci like '%サ%'; /* → 真 */
select 'さとう' collate utf8_unicode_ci like '%ザ%'; /* → 偽 */

お知らせありがとうございます。
この辺は把握した上で使う必要がありそうですね。

[MySQL] utf8_general_ci と utf8_unicode_ci の違い | プログラマの雑記帳 2014年11月22日 07:52

[…] 【MySQL】大文字小文字、全角半角区別しないでマッチする検索をしたい at softelメモhttps://www.softel.co.jp/blogs/tech/archives/1877 […]