MySQL - 如何获得准确相关性的搜索结果

Question

我已经多次重新考虑了这个问题，但我从未真正找到一个合适的答案。

是否可能执行MySQL搜索以返回实际上按相关性准确排序的结果？

我正在尝试创建一个ajax搜索表单，在用户输入字段时提供建议，并且仅使用纯MySQL查询没有找到任何合适的解决方案。我知道有可用的搜索服务器，例如ElasticSearch，但我想知道如何仅使用原始MySQL查询来执行此操作。

---

我有一个学校科目表。 表中少于1200行，永远不会改变。 让我们执行一个基本的FULLTEXT搜索，其中用户开始键入“Bio”。

查询（“Bio ...”） - FULLTEXT BOOLEAN MODE

SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10

结果

name                                  |  relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics  |  1
Applied Biology                             |  1
Behavioural Biology                         |  1
Cell Biology                                |  1
Applied Cell Biology                        |  1
Developmental/Reproductive Biology          |  1
Developmental Biology                       |  1
Reproductive Biology                        |  1
Environmental Biology                       |  1
Marine/Freshwater Biology                   |  1

为了展示这些结果有多糟糕，以下是与一个简单的 LIKE 查询做比较，该查询显示了所有更相关但未被显示的结果：

查询 ("Bio...") - LIKE

SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name

结果

name                                  |  relevance
--------------------------------------------------------
Bio-organic Chemistry                       |  1
Biochemical Engineering                     |  1
Biodiversity                                |  1
Bioengineering                              |  1
Biogeography                                |  1
Biological Chemistry                        |  1
Biological Sciences                         |  1
Biology                                     |  1
Biomechanics, Biomaterials and Prosthetics  |  1
Biometry                                    |  1

你可能已经注意到，有很多主题并没有被建议出来，尽管这些更有可能是用户正在寻找的内容。

然而，使用 LIKE 的问题在于如何像 FULLTEXT 一样搜索跨多个单词和单词中间的内容。

我想要实现的基本排序如下：

1. 以搜索词开始的第一个单词
2. 以搜索词开始的第二个单词
3. 术语不在单词开头的单词
4. 如果没有进一步的相关性，则按字母顺序排列

---

所以我的问题是，如何通过 MySQL 搜索跨多个单词得到一个合理排序的建议列表？

- BadHorsie

Answer 1

你可以使用字符串函数，例如：

select id, name
from subjects
where name like concat('%', @search, '%')
order by 
  name like concat(@search, '%') desc,
  ifnull(nullif(instr(name, concat(' ', @search)), 0), 99999),
  ifnull(nullif(instr(name, @search), 0), 99999),
  name;

这将给您所有包含@search的条目。首先是以@search开头的条目，然后是在空格后面的条目，然后按出现位置排序，并按字母顺序排列。

name like concat(@search, '%') desc 顺便使用了MySQL的布尔逻辑。1 = true，0 = false，因此按此降序排序会先给出true。

SQL演示：http://sqlfiddle.com/#!9/c6321a/1

- Thorsten Kettner

Answer 2

对于像我一样来到这里的其他人：根据我的经验，为了达到最佳效果，您可以根据搜索词的数量使用条件语句。如果只有一个单词，请使用 LIKE '%word%'，否则请使用布尔全文搜索，例如：

if(sizeof($keywords) > 1){
   $query = "SELECT *,
             MATCH (col1) AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             AS relevance1,
             MATCH (col2) AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             AS relevance2
             FROM table1 c
             LEFT JOIN table2 p ON p.id = c.id
             WHERE MATCH(col1, col2) 
             AGAINST ('+word1* +word2*' IN BOOLEAN MODE) 
             HAVING (relevance1 + relevance2) > 0
             ORDER BY relevance1 DESC;";
    $execute_query = $this->conn->prepare($query);
}else{          
   $query = "SELECT * FROM table1_description c
             LEFT JOIN table2 p ON p.product_id = c.product_id
             WHERE colum1 LIKE ? AND column2 LIKE ?;";
        // sanitize
        $execute_query = $this->conn->prepare($query);
        $word=htmlspecialchars(strip_tags($keywords[0]));
        $word = "%{$word}%";
        $execute_query->bindParam(1, $word);
        $execute_query->bindParam(2, $word);
    }

- JediCate

---

小提示：我建议使用count()而不是sizeof()，因为它是一个很少使用的别名，在大多数其他编程语言中具有不同的含义。 - BadHorsie

Answer 3

这是我使用以上答案的组合所能得到的最佳结果：

$searchTerm = 'John';
// $searchTerm = 'John Smit';
if (substr_count($searchTerm, ' ') <= 1)
    $sql = "SELECT id, name
    FROM people
    WHERE name like '%{$searchTerm}%')
    ORDER BY
      name LIKE '{$searchTerm}%') DESC,
      ifnull(nullif(instr(name, ' {$searchTerm}'), 0), 99999),
      ifnull(nullif(instr(name, '{$searchTerm}'), 0), 99999),
      name
    LIMIT 10";
}
else {
$searchTerm = '+' . str_replace(' ', ' +', $searchTerm) . '*';
$sql = "SELECT id,name, MATCH(lead.name) AGAINST('{$searchTerm}' IN BOOLEAN MODE) AS SCORE
        FROM lead
    WHERE MATCH(lead.name) AGAINST('{$searchTerm}' IN BOOLEAN MODE)
    ORDER BY `SCORE` DESC
    LIMIT 10";

确保在列上设置全文索引（如果使用多列，则设置多个列），并使用OPTIMIZE table_name重置索引。

最好的事情是，如果您键入Jo，那么名字为Jo的人将比John排名更高，这正是您想要的！

- friek108

---

请注意，除非使用MATCH/AGAINST的第二种方法，否则全文索引对此无济于事。 - DougW

Answer 4

我根据您描述的排序尝试了这个。

SET @src := 'bio';
SELECT name,
name LIKE (CONCAT(@src,'%')),
         LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(name,' ',2),' ',-1),LENGTH(@src)) = @src,
         name LIKE (CONCAT('%',@src,'%'))
FROM subjects
ORDER BY name LIKE (CONCAT(@src,'%')) DESC,
         LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(name,' ',2),' ',-1),LENGTH(@src)) = @src DESC,
         name LIKE (CONCAT('%',@src,'%')) DESC,
         name

http://sqlfiddle.com/#!9/6bffa/1

我想也许你甚至想要包括@src出现的次数在VARCHAR字段中计算字符串出现的次数？

- Tin Tran

Answer 5

为了得到你想要的结果，你可以尝试将几个“case when…”语句与mysql的regexp结合起来，这将根据你的要求为每一行给出一个精确的分数。 Regexp可能是你缺少的拼图中的一部分：请参见https://dev.mysql.com/doc/refman/5.6/en/regexp.html（我正在手机上回答，所以很难格式化答案或举例说明）。

- Mark B

Answer 6

MATCH(s.name) AGAINST('"Applied Bio"' IN BOOLEAN MODE)

上述语句将搜索确切的搜索词，这意味着这两个单词必须存在于每个记录中。

ORDER BY s.name like concat("Applied Bio", '%') desc,
ifnull(nullif(instr(s.name, concat(' ', "Applied Bio")), 0), 99999),
ifnull(nullif(instr(s.name, "Applied Bio"), 0), 99999),
s.name

按照以搜索词开头的第一个单词排序。

完整的SQL语句：

SELECT SQL_NO_CACHE 
s.id, s.name
FROM subjects s use index(name_fulltext) 
WHERE 
MATCH(s.name) AGAINST('"Applied Bio"' IN BOOLEAN MODE) 
GROUP BY s.id 
ORDER BY 
s.name like concat("Applied Bio", '%') desc,
ifnull(nullif(instr(s.name, concat(' ', "Applied Bio")), 0), 99999),
ifnull(nullif(instr(s.name, "Applied Bio"), 0), 99999),
s.name
LIMIT 100;

- rusly