MySQLでの検索に関する質問です。例えば単語一つだけで検索する場合は


SELECT * FROM mytable WHERE mycolumn LIKE '%検索語句%';

のようにセレクト文を書けばいいと思うのですが、よくGoogleでやるように検索語句を二つ以上、スペースを空けて入力した場合には、どのようなセレクト文を書けばいいのでしょうか。よろしくお願いします。

回答の条件
  • 1人3回まで
  • 登録:2008/01/03 17:32:21
  • 終了:2008/01/03 19:21:28

ベストアンサー

id:bayan No.2

bayan回答回数100ベストアンサー獲得回数132008/01/03 19:11:33

ポイント40pt

1) 入力値を個々の検索語句に分割する (SQLではなくプログラミング言語側で行う)

2) それぞれの検索語句についての LIKE 句を AND や OR で連結したSQLを書く

ってな感じですかね。

全てを含む行を抽出したい場合

SELECT * FROM mytable WHERE mycolumn LIKE '%検索語句A%' AND mycolumn LIKE '%検索語句B%';

いずれかを含む行を抽出したい場合

SELECT * FROM mytable WHERE mycolumn LIKE '%検索語句A%' OR mycolumn LIKE '%検索語句B%';

PHPで書いてみました。

(MySQLとかよく知らんで適当に書いてますので、つっこみください)

<?php
$input = "餅 飽きた カレー";

// 全角空白を半角空白にそろえるなら
// http://www.php.net/manual/ja/function.mb-convert-kana.php
// $input = mb_convert_kana($input,"s");

// 連続する空白文字で分割
// http://www.php.net/manual/ja/function.preg-split.php
$keywords = preg_split("/[\s]+/",$input);

// LIKE を作成して配列に格納する
// クオートやエスケープもする
$tmp = array();
foreach($keywords as $kw){
  if($kw == ""){
    // 空っぽなら無視
  }else{
    $tmp[] = " mycolumn LIKE '%".mysql_real_escape_string($kw)."%' ";
  }
}

$sql = "SELECT * FROM mytable "; 
if(count($tmp) > 0){
  // AND なり OR で連結してWHERE を作成
  $sql .= "WHERE " . implode("AND",$tmp);
}
echo $sql;
?>
id:mine-D

おおお。ありがとうございます。非常に分かりやすいです。言語もちょうどPHPで書いていますので、大助かりです。

2008/01/03 19:21:08

その他の回答(1件)

id:y-kawaz No.1

y-kawaz回答回数1421ベストアンサー獲得回数2262008/01/03 18:48:07

ポイント35pt

プログラム側でキーワードをパース(解析)して、複数条件にするだけです。そういうSQL文の書き方があるわけではありません。

例えば「検索語句1 検索語句2 検索語句3」という文字列があったら空白文字で分割して複数のLIKE文をANDでつなげて以下のようなSQL文を作って検索するイメージです。

SELECT * FROM mytable
WHERE mycolumn LIKE '%検索語句1%'
  AND mycolumn LIKE '%検索語句2%'
  AND mycolumn LIKE '%検索語句3%'

Googleのように大量データを全文検索するケースでは実際はこんなに単純な検索はしていないでしょうが、小規模なデータベースならこれで十分かと思います。

id:mine-D

なるほどなるほど。ANDで複数条件を指定するだけなんですね。ありがとうございます。

2008/01/03 19:06:24
id:bayan No.2

bayan回答回数100ベストアンサー獲得回数132008/01/03 19:11:33ここでベストアンサー

ポイント40pt

1) 入力値を個々の検索語句に分割する (SQLではなくプログラミング言語側で行う)

2) それぞれの検索語句についての LIKE 句を AND や OR で連結したSQLを書く

ってな感じですかね。

全てを含む行を抽出したい場合

SELECT * FROM mytable WHERE mycolumn LIKE '%検索語句A%' AND mycolumn LIKE '%検索語句B%';

いずれかを含む行を抽出したい場合

SELECT * FROM mytable WHERE mycolumn LIKE '%検索語句A%' OR mycolumn LIKE '%検索語句B%';

PHPで書いてみました。

(MySQLとかよく知らんで適当に書いてますので、つっこみください)

<?php
$input = "餅 飽きた カレー";

// 全角空白を半角空白にそろえるなら
// http://www.php.net/manual/ja/function.mb-convert-kana.php
// $input = mb_convert_kana($input,"s");

// 連続する空白文字で分割
// http://www.php.net/manual/ja/function.preg-split.php
$keywords = preg_split("/[\s]+/",$input);

// LIKE を作成して配列に格納する
// クオートやエスケープもする
$tmp = array();
foreach($keywords as $kw){
  if($kw == ""){
    // 空っぽなら無視
  }else{
    $tmp[] = " mycolumn LIKE '%".mysql_real_escape_string($kw)."%' ";
  }
}

$sql = "SELECT * FROM mytable "; 
if(count($tmp) > 0){
  // AND なり OR で連結してWHERE を作成
  $sql .= "WHERE " . implode("AND",$tmp);
}
echo $sql;
?>
id:mine-D

おおお。ありがとうございます。非常に分かりやすいです。言語もちょうどPHPで書いていますので、大助かりです。

2008/01/03 19:21:08
  • id:chuken_kenkou
    LIKEで'%語句%'といった検索は、インデクスを有効利用できず、性能を出せない検索方法です。
    大規模なシステムや性能を重視する場合は、「やってはいけない」操作です。
  • id:chuken_kenkou
    MoSQLというのがありますので、参考まで。

    http://mosql.jp/index.php/%E3%83%A1%E3%82%A4%E3%83%B3%E3%83%9A%E3%83%BC%E3%82%B8
  • id:mine-D
    >chuken_kenkou さん
    LIKE使わない方がいいんですか。具体的にはどんなSQL文が理想的なのでしょう?
    知る限りではかなり大規模なシステムでもオープンソースのSQLデータベースを使っているところは多いように思います。そうなると当然膨大な量のデータから高速に語句を引っ張ってくるような工夫、チューニングがされているんじゃないか、と思うんですね。MoSQLは高速に検索を行えるデータベースなのかもしれませんが、Yahoo!やGoogleで使われているという話は聞きません。ですので、汎用的なSQLで、具体的に使えるSQL文が知りたいなぁと思い、質問させていただきました。
  • id:y-kawaz
    MySQLでは MATCH AGAINST というのを使うと全文検索出来るようです。
    http://www.ironhearts.com/diary/archives/000868.html
  • id:bayan
    すみません。y-kawaz さんが「小規模なデータベースなら」と但し書きされていましたが。私もそんなつもりで回答していました。
    chuken_kenkou さんも「大規模なシステムや性能を重視する」場合とされています。

    LIKEの引数がワイルドカード(%)で始まる場合はインデックスが利用されません。

      「MySQL は、LIKE の引数がワイルドカード文字で始まらない文字列定数で
       ある場合に、LIKE 比較にもインデックスを使用します。」
       http://dev.mysql.com/doc/refman/4.1/ja/mysql-indexes.html

    そのため、表内のデータを全件総なめすることなりますので、数万件とか数十万件とかになってくると厳しいと思います。

    まぁ対象データのサイズにもよるでしょうし、実データで確認しないといけませんが、レコード件数が1000件くらいであれば問題にならないとは思います。
    http://www.daito.ac.jp/~ikeuchi/webdb/mysql_5.html

    データベースエンジンの持つ全文検索機能を使わず、汎用的なSQLでということになると、私だったら次のようなことを考えます。


    次の表を用意します。

    A. 検索対象のデータを格納した表 (データNo がプライマリキー)
    B. キーワードの表 (キーワードNoがプライマリキー、キーワードテキストにインデックスを張る)
    C. どのキーワードがどのデータが含まれるかの表 (キーワードNoとデータNoで複合キー)
    ※=キーワードとデータの関連表

    表にデータを追加したときに以下を行います。

    A に追加したデータから形態素解析などでデータからキーワードを抽出する。
    B をキーワードで検索してそのキーワードNoを得る。
    キーワードが B に未登録だったら、そのキーワードNoを得る。
    C にキーワードとデータの関連を登録する。
    ※データを削除するときにCからキーワードとデータの関連を削除することも必要ですね。


    検索の考え方は次のような感じ。

    (1) 入力されたキーワードで (B) を検索してキーワードNoを得る。
    (2) 得られたキーワードNoで (C) を検索しキーワードを含むデータのデータNoを得る。
    (3) データNoで (A) を検索し該当データを得る。


    表とその内容のイメージとしては次のような感じ。

    A. 表 data

    data_no | data_text
    ---------+----------------------------------------------------------------------------
    1 | 人力検索はてなは人が答える検索サイト。
    2 | どう探してよいか分からない、たくさんの人に教えて欲しい、そんな質問に答えます。
    3 | なんでも探してみますのできいて下さい。

    B. 表 keyword

    keyword_no | keyword_text
    ------------+-------------
    1 | 人力検索
    2 | はてな
    3 | 検索
    4 | たくさん
    5 | 質問
    6 | なんでも
    7 | 探して

    C. 表 keyword_data

    keyword_no | data_no
    ------------+--------
    1 | 1
    2 | 1
    3 | 1
    4 | 2
    5 | 2
    6 | 3
    7 | 2
    7 | 3


    キーワード「はてな」を含むデータを検索するSQLの例

    -- (3)
    SELECT * FROM data
    WHERE data_no IN (
     -- (2)
    SELECT data_no FROM keyword_data
    WHERE keyword_no IN (
    -- (1)
    SELECT keyword_no
    WHERE keyword_text = 'はてな'
    )
    )

    結果は、

    data_no | data_text
    ---------+--------------------------------------
    1 | 人力検索はてなは人が答える検索サイト。

    になってくれると思う。
    (手元にMySQL環境がないので確認はしてません)
  • id:bayan
    ありゃりゃ、コメント文は半角スペースでレイアウトできないのですね。。。

    MoSQL面白そうですね。
    というかマスコットに弱いかも。。。
    未確認ですがMySQLを改造して作っているように見受けられます。
  • id:mine-D
    y-kawaz様、bayan様、フォローありがとうございます。じっくりやってみますね。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません