2つフィールドを連結して検索するには

質問

MySQLの初心者です。よろしくお願いします。

１つのテーブルにある２つのフィールドのうち、どちらかにキーワードが入っている場合（両方に入っている場合も含む）を抽出したいのですが、検索するキーワードが1個の場合は、下記のようにすれば、うまく動くのですが、
●1個のキーワードで検索する場合
（例として、２つのフィールドのうちどちらかに「山」のキーワードが入っている場合を抽出）

select * from table WHERE (item_name LIKE "%山%" or outline LIKE "%山%" )

●テーブルの内容(テーブル名：table）
item_name | outline
==========+=========
山がある　+　山と川
----------+----------
山がある　+　川と森
----------+---------
山がある　+ 谷と林
---------------------

質問したいことは、上記のテーブルから、item_nameとoutlineのフィールドのうち、２つのキーワード「山」と「川」が両方含まれるレコードを抽出したいです。上記のテーブルでいうと、抽出の結果、１行目と2行目が抽出したいのです。

ちなみに、下記のように記述したら、上記のテーブルで1行目だけ抽出されます。
select * from table WHERE (item_name LIKE "%山%" and item_name LIKE "%川%" or outline LIKE "%山%" and outline LIKE "%川%" )

他にも、下記のように試してみたのですが、うまく抽出できませんでした。
SELECT * FROM table WHERE (concat(item_name,outline) LIKE '%山%' and concat(item_name,outline) LIKE '%川%')

item_nameとoutlineのフィールドを結合して、結合した文字列で、「山」と「川」の両方が入っているレコードを抽出する方法はありますか？

２つのフィールドの文字列をあわせたなかから、「山」と「川」のキーワードが両方含まれるレコードを抽出したいのですが、何かよい方法はありますでしょうか。ご回答よろしくお願いいたします。

回答No.1

yambejp · ベストアンサー

まず第一に、'%山%'というような書き方は、インデックスがまっとうに
きかないので、高速化は期待できせん。
またCONCATしたものに対する検索もインデックスが利きません。
よって処理としてはできるけど、SQLらしくない処理だという前提で
ご理解ください。

あとサンプルとはいえテーブルにtableという名前をつけるのは
いただけません。混乱の元なので注意ください。そのうえで例示の・・・

SELECT * FROM yamakawa_table WHERE CONCAT(item_name,outline) LIKE '%山%' AND CONCAT(item_name,outline) LIKE '%川%'

ってやりましたが、特に問題なさそうですが・・・
これは条件が片方ならヒットするということですか？
バージョンによっては日本語検索の精度にバラつきがあるので
そのせいかもしれませんね

なお、ホントはだめなんですが、上記のSQLを以下のように書いても
同じ結果になるようです。

SELECT *,concat(item_name,outline) AS K FROM yamakawa_table HAVING (K LIKE '%山%' AND K LIKE '%川%')

（しつこいようですが、ホントはだめなんですよー）

効率は変わりませんが考え方としてはこんな感じでもいけそうです

SELECT * FROM yamakawa_table WHERE (item_name LIKE '%山%' OR outline LIKE '%山%')+(item_name LIKE '%川%' OR outline LIKE '%川%') >=2

補足

ご回答いただき、ありがとうございました。
テーブル名に関しまして、教えていただき、今後気をつけます。

バージョンによって、日本語検索の制度にバラつきとのことで、違うバージョンで試してみました。
利用しているレンタルサーバーが、２つのバージョンのMySQLを利用できる環境にありますので、MySQL 4.0.26でやってみたら、うまくいきませんでしたが、MySQL 5.0.51のほうで、同じようにやってみましたら、うまく検索できました。
ありがとうございました。

それから、CONCATを利用したものもインデックスがきかないとありましたが、同じ内容のものをSQLらしく書く方法がありましたら、ご回答いただければと思います。
よろしくお願いいたします。

回答No.3

yambejp

>同じ内容のものをSQLらしく書く方法がありましたら

実は構造的にいじらないと無理なんですよ、ちょっとレベルが高い処理
になるので、いまはまだ時期尚早かもしれませんね
SQLで高速にLIKEがつかえるのは前方一致か後方一致のいずれか、
すなわちLIKE '山%'かLIKE '%山'のどちらかしか有効ではありません。
（遅くてもいいならご提示の方法でもいけます）

もしやるなら・・・kakasiなどで分かち書きをして全文検索するとか、
キーワードを別途専用にテーブルで管理するとかいろいろ手間がかかります。

お礼

いろいろと教えていただき、ありがとうございました。
感謝いたします。
まだまだ初心者なので、これからいろいろ勉強していきたいと思います。
ありがとうございました。

回答No.2

masa6272

行数の多いテーブルでは、SQLでの全文検索は効率が悪くなります。
英語だと、単語単位の検索を高速にするインデックスをテーブルに貼れますが・・・
とはいっても、必要な場合はありますね。こんなのはどうでしょう?

SELECT * FROM a
WHERE concat(item_name,outline) REGEXP '山.*川|川.*山';

お礼

ご回答いただき、ありがとうございました。
教えていただいた方法で、うまく検索することができました。
いろいろ勉強になり、感謝いたします。