複数のカラムをまたいで検索するには、どの様にすれば良いのでしょうか？

Question

｜--｜------｜------｜
｜no｜miyoji｜namae ｜
｜--｜------｜------｜
｜01｜山田 ｜太郎 ｜
｜--｜------｜------｜
｜02｜田中 ｜次郎 ｜
｜--｜------｜------｜
｜03｜鈴木 ｜三郎 ｜
｜--｜------｜------｜

上記の様なテーブルがあります。

1つのカラムだったら、
SELECT * FROM test WHERE miyoji LIKE '%山田%'";
で良いと思うのですが
「山田太郎」とか「山田太」で検索した場合、
｜--｜------｜------｜
｜01｜山田 ｜太郎 ｜
｜--｜------｜------｜
を検索結果として該当させたいと思います。

上記の様な結果を表示するには、どのようなクエリーを実行すれば良いのでしょうか？

Answer 1

SELECT * FROM test WHERE CONCAT(miyoji,namae) like '%田太%'

で可能です。

補足：
ちなみにmiyojiとnamaeでインデクスを作成しないとテーブル全検索になってしまいますので、miyojiとnamaeでインデクスを作成すれば、インデクスを使った検索が可能です。

Comments

[KiyoshiMotoki]

質問に対する回答としては正しいのですが、

> miyojiとnamaeでインデクスを作成すれば、インデクスを使った検索が可能です。

は、誤りです。

理由は2つあり、
1つ目はワイルドカード（"%"および"_"）で始まる値との比較ではインデックスを使用できないこと、
　http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

2つ目は、信頼できる情報源が見つからないので何ですが、（concatなどの）関数を適用したカラムは、インデックスを使用できなくなること
　http://dba.stackexchange.com/questions/45840/index-mysql-concatenated-columns

です。

[A-pZ]

なるほど、mysql5.6での日本語リファレンスでも以下の様な記述はありましたね。

LIKE '%string%' を使用し、string が 3 文字より長い場合、MySQL は Turbo Boyer-Moore アルゴリズムを使用して、文字列のパターンを初期化してから、このパターンを使用して検索をより迅速に実行します。

しかしEXPLAINで結果を出すと、複合インデクスを使っている旨を出すので、EXPLAINの結果そのものは「使う候補にあげてる」けれど、「それは本来のインデクスではない」可能性と、「インデクスを使わない可能性はありそう」ということですね。

指摘ありがとうございました。

Answer 2

1.テーブル変更で対応
miyojiカラムとnamaeカラムを結合したhogeカラムを追加し、hoge LIKE '山田%'で検索する

2.SQLで対応【その1】
WHERE (miyoji LIKE '山田%' OR namae LIKE '山田%')

3.SQLで対応【その2】
CONCATを利用してmiyojiとnamaeを連結します。
※インデックスはきかないです。
WHERE CONCAT(miyoji, namae) LIKE '山田%'

4.SQLで対応【その3】
UNIONを利用して別々に条件を指定する。
SELECT * FROM HOGE
WHERE miyoji LIKE '山田%'
UNION
SELECT * FROM HOGE
WHERE namae LIKE '山田%'

explainで一番早くなりそうなSQLを探してみるしかないですが、、
私なら1のテーブル変更にします。

Comments

[unau]

2, 4 は「山田太」での検索（ LIKE '%山田太%' ) で引っかからないのではないでしょうか。
2 と同じことですが、CONCAT したカラムを持つ view を作るのも手でしょうね。

[LILI.IRON.FIST]

そうですね。前にも%が必要ですね。