MySQL或者单词,MySQL搜索任何单词，而不是所有单词

The following code works for searching, but it doesn't work if you enter too much text into the search box.

For instance, if you search the phrase 'text in English' you receive a number of results. However, if you search the phrase 'text in English Language' it delivers NO result, because the word 'language' isn't in the fields it is searching.

In other words, the query is searching for the phrase in the text, not just any part of it.

I'm using this query:

$sqlcommand = "SELECT id,page_title,url,search_description,text1,text2,text3

FROM pages

WHERE concat(text1, ':', text2, ':', text3) LIKE '%$searchquery%'";

I'm looking for some advice, but I'm guessing the LIKE '%$searchquery%' part is the problem.

Cheers

Richard

解决方案

First of all you really want a full text search for this.

If you for some reason want to try to do it with pure SQL then

first thing you need to do is to split you search string into words

and filter out common words (pronouns, conjunctions, prepositions, etc) using some sort of stop list or/and filter out any words less then 2 or 3 characters long.

Something like this might do it for starters:

$search_string = 'text in English Language';

$stop_list = array('in', 'on', 'I', 'me', 'he', 'she');

$search_words = explode(' ', $search_string);

$keywords = array_diff($search_words, $stop_list);

You'll get in $keywords

array(3) {

[0]=>

string(4) "text"

[2]=>

string(7) "English"

[3]=>

string(8) "Language"

}

Now having a keywords array you can build a query like this

SELECT id, page_title, url, search_description, text1, text2, text3,

(text LIKE '%text%') +

(text LIKE '%English%') +

(text LIKE '%language%') rank

FROM

(

SELECT id, page_title, url, search_description, text1, text2, text3,

CONCAT_WS(' ', text1, text2, text3) text

FROM pages p

) q

WHERE text LIKE '%text%'

OR text LIKE '%English%'

OR text LIKE '%language%'

HAVING rank > 1 -- play with cut-off rank value to get most relevant results

ORDER BY rank DESC

Sample output:

+------+------------+------+--------------------+-------+------------+-----------------+---------------------------------+------+

| id | page_title | url | search_description | text1 | text2 | text3 | text | rank |

+------+------------+------+--------------------+-------+------------+-----------------+---------------------------------+------+

| 3 | page3 | url3 | NULL | text | English | language | text English language | 3 |

| 1 | page1 | url1 | NULL | text | in English | text in English | text in English text in English | 2 |

+------+------------+------+--------------------+-------+------------+-----------------+---------------------------------+------+

Note: this query will be way slow and way less functional than any FTS solution.

Here is