最强的LIKE命令SQL？ [英] Order SQL by strongest LIKE?

问题描述

我有以下查询：

SELECT * FROM table_name
WHERE (col_1 LIKE '%$keyword%'
    OR col_2 LIKE '%$keyword%'
    OR col_3 LIKE '%$keyword%')
  AND .... <some optional filters> ... 

是否有根据最相关的结果排序的策略？

is there a strategy to sort based on the most relevant results?

推荐答案

当你谈到相关性时，你真的想要MySQL支持的自然语言搜索全文搜索。语法与正常的 like 查询不同，您需要向表中添加一个特殊的索引，但按照相关性排序是可能的。

When you talk about "relevance", you really want natural language search, which is supported by MySQL full-text searches. The syntax is different than normal like queries, and you need to add a special index to the table, but ordering by relevance is possible this way.

以下是MySQL计算相关性（来自链接）的方法：

Here's how MySQL computes relevance (from the link):

当在WHERE子句中使用MATCH如前所示，返回的行将首先按最高相关性自动排序。相关性值是非负浮点数。零相关性意味着没有相似性。相关性是根据行中的字数，该行中唯一字的数量，集合中字的总数以及包含特定字的文档（行）数量计算得出的。

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

要在现有表格上创建全文索引，请使用 FULLTEXT 修饰符：

CREATE FULLTEXT INDEX index_name
ON table_name (col1, col2, col3)

然后您可以执行类似这样的查询以按相关性顺序检索结果：

Then you can perform a query like this to retrieve results in order of relevance:

SELECT * FROM table_name
WHERE MATCH (col1,col2,col3)
AGAINST ('keyword' IN NATURAL LANGUAGE MODE);

这篇关于最强的LIKE命令SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！