条件検索の方法について

フィールド　(name) に「いちごがむ」「いちごじゅーす」「みかんがむ」「みかんじゅーす」がある場合、文字の先頭に『いちご』がつく情報を抽出するsql文は以下になります。

SELECT * FROM test WHERE name LIKE 'いちご%'


では、フィールド (name)　に「いちご」「みかん」があるとします。

検索文字『いちごがむ』（もしくは『いちごじゅーす』）からnameの「いちご」箇所を抽出するにはどのようなsqlを発行すればよいのでしょうか？

よろしくお願いします。

No.3 ベストアンサー 回答者： yambejp 回答日時： 2007/04/07 19:14

select * from test where

'ももたろうは鬼をたいじしました' like concat(name,'%')

とすればよいですが、SQL的にはかなり効率がわるいので実用に
耐えないかもしれないですね。

この回答への補足

concatという関数があったのですか！

a like 'b%' 　の真逆のsqlが 　'b' like concat(a'%') で発行できるのですね、すばらしいです。

>>SQL的にはかなり効率がわるいので実用に耐えないかもしれないですね。

NO2さんの方法と、NO3さんの方法のどちらでも期待通りの動作が実現できるのですが、どちらの方法が適しているでしょうか？　それとも、違う方法を考えたほうがよろしいでしょうか？


ps.　アクセス集計サイトはどのようにドメインを取得しているのでしょうかね・・・、疑問です。

補足日時：2007/04/07 20:08

この回答へのお礼

スピードを比べてみたらconcatの方が多少速かったので、今回はこちらを使わせていただきます。
ありがとうございました。

お礼日時：2007/04/11 14:27

No.4 回答者： chukenkenkou 回答日時： 2007/04/07 20:18

#1、#2回答者です。

検索条件で列名（今回の場合は、name列）を加工（部分抽出など）せずに条件式に指定することで、インデクスを利用できるかと思ったのですが、explainの結果では#2回答のような指定でもMySQLではインデクスを利用してくれないようです。
したがって、今回のような操作では、母体件数に比例して処理時間がかかります。
そういう点に留意した上で、実装して良いかの判断をしてください。

No.2 回答者： chukenkenkou 回答日時： 2007/04/07 17:51

#1回答者です。

文章から、キーワードを検索するのですか？
すごい発想ですね。
キーワードから文章を検索するというのが、普通の発想なのですけどね。

文章の先頭には、必ず検索したいキーワードがあるのでしょうか？
例えば、
「○○○は～」
「××を～」
といった形式でしょうか？
あと、これは趣味などで行うのでしょうか？
もし仕事なら、発想を考え直したほうがいいです。

以上の条件がOKなら、以下のSQLで希望の結果を得られると思います。

＝＝＝表定義例＝＝＝
create table test
(id int,
name varchar(30));

＝＝＝格納データ例＝＝＝
insert into test values
(1,'いちご'),(2,'みかん'),(3,'ぱいなっぷる'),(4,'もも'),(5,'ももたろう');

＝＝＝検索SQL例＝＝＝
select id,name
from test
where name=substring('ぱいなっぷるはコンビニに100円で売っています',1,char_length(name));
select id,name
from test
where name=substring('いちごがむを昨日食べました',1,char_length(name));
select id,name
from test
where name=substring('ももが大好きです',1,char_length(name));
select id,name
from test
where name=substring('ももたろうは鬼をたいじしました',1,char_length(name));

この回答への補足

char_length(name)でnameの長さを取得して、substringでキーワードを切るのですか！　いやすごいです、sqlでこんな方法があるとは知りませんでした。

実は、自分のＨＰのリンク集に載っているサイトのみを対象とした逆アクセスランキングを、phpとmysqlを用いて設置しようと思いたちました。

sitename　　　　　　url　　　　　　　　　　　　　count
-------　　　----------------　　　　　　-------
あ　　　　　　http://aaa.com/　　　　　　　　　　10
い　　　　　　http://bbb.com/　　　　　　　　　　　8
う　　　　　　http://ccc.com/sub1/　　　　　　　15
え　　　　　　http://ccc.com/sub2/　　　　　　　15

実装にあたり、リファラー( http://aaa.com/aa.html　、　 http://aaa.com/aa.php?hoge=7　など)　と　データベースフィールドurlとの関連付けが必要なのですが、リファラーを直接データベースフィールドurlにヒットさせることができれば、カウントアップ等のプログラムを一番手っ取り早く作れますので、どうにかできないものかと当質問をさせていただきました。

>> もし仕事なら、発想を考え直したほうがいいです。

仕事というわけではないのですが、この方法はやめたほうがよろしいでしょうか？　たしかに、すべてのurlに対し　char_length　と　substring　を施すので、リンク数が多くなると、動作が重くなるような気がします。(sqlは詳しくないので、なんともいえませんが・・・)

補足日時：2007/04/07 20:04

No.1 回答者： chukenkenkou 回答日時： 2007/04/07 15:37

質問内容が不明確です。

検索する基の表のデータと、得たい結果を示すと、回答する側も分かりやすいです。

>では、フィールド (name)　に「いちご」「みかん」があるとします

これは、格納済データのことですよね？
この文と、この文以降は、どういう関係があるのでしょうか？

>検索文字『いちごがむ』（もしくは『いちごじゅーす』）からnameの「いちご」箇所を抽出するには

最初に示された
「SELECT * FROM test WHERE name LIKE 'いちご%'」
で検索した結果から、「いちごがむ」といった列値が検索された場合、「いちご」部分だけを抜き出したいのですか？

MySQLのバージョンは、何ですか？MySQLでは、4.0以前と4.1以降で、この辺の仕様が変わっています。

「いちご」部分だけを抜き出したいなら、
MySQL 4.0までなら、「select substring(name,1,6) from ～」
MySQL 4.1からは、「select substring(name,1,3) from ～」
といった記述で可能です。

意図が違っていたら、補足説明してください。

この回答への補足

質問内容が不明確で申し訳ありませんでした。MySQLのバージョンは5.0です。

テーブル名　test
　
id 　　　　　 name
---- 　　 ---------
1 　　　　　いちご
2 　　　　 みかん
3 　　　　 ぱいなっぷる
4 　　　　　もも
5 　　　　 ももたろう
---- 　　----------

以上のテーブルがある場合、

『ぱいなっぷるはコンビニに100円で売っています』という検索文字では「3,ぱいなっぷる」がヒット。

『いちごがむを昨日食べました』という検索文字では「1,いちご」がヒット。

『ももが大好きです』という検索文字では「4,もも」がヒット。

『ももたろうは鬼をたいじしました』という検索文字では「4,もも」と「5,ももたろう」がヒット。


このような検索結果が得られるには、どのようなSQL文になるのか知りたかった次第であります。ちなみに、検索は前方一致(最初の文字列が同じならヒット)を望んでいます。

よろしくお願いいたします。

補足日時：2007/04/07 17:13