MySQL搜索任何单词，而不是所有单词 [英] MySQL Searching for ANY word rather than ALL the words

问题描述

以下代码可用于搜索，但是如果您在搜索框中输入太多文本，则该代码将无效.

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

例如，如果您搜索短语英语文本"，则会收到许多结果.但是，如果您搜索短语英语文本"，则不会产生任何结果，因为单词"language"不在搜索字段中.

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.

我正在使用此查询:

$sqlcommand = "SELECT id,page_title,url,search_description,text1,text2,text3 
             FROM pages 
             WHERE concat(text1, ':', text2, ':', text3) LIKE '%$searchquery%'";

我正在寻找一些建议，但我猜是LIKE '%$searchquery%'部分是问题所在.

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

欢呼

理查德

推荐答案

首先您真的想要全文搜索.

如果您出于某种原因想要尝试使用纯SQL进行操作，那么

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

• 您需要做的第一件事是将搜索字符串分成单词
• 并使用某种停止列表过滤掉常见单词(代词，连词，介词等)，或者/并且过滤掉长度不超过2或3个字符的所有单词.

类似的事情对于初学者来说可以做到:

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);

您将进入$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

示例输出:

+------+------------+------+--------------------+-------+------------+-----------------+---------------------------------+------+
| 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 |
+------+------------+------+--------------------+-------+------------+-----------------+---------------------------------+------+

注意:与任何FTS解决方案相比，此查询的运行速度都会缓慢且功能会降低.

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

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于MySQL搜索任何单词，而不是所有单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！