MySQL 按最强的 LIKE 排序 SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11144394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order SQL by strongest LIKE?
提问by Shazam
I have the following query:
我有以下查询：
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?
是否有根据最相关的结果进行排序的策略？
回答by Dominic Goulet
If you mean that col_1 is more relevant than col_2 and so on, then :
如果您的意思是 col_1 比 col_2 等更相关，那么：
select *
,case when col_1 like '%$keyword%' then 1
when col_2 like '%$keyword%' then 2
when col_3 like '%$keyword%' then 3
end as [priority]
from table_name
where col_1 like '%$keyword%'
or col_2 like '%$keyword%'
or col_3 like '%$keyword%'
order by [priority]
If you meant with the most column match then :
如果您的意思是列匹配最多，那么：
select *
,(case when col_1 like '%$keyword%' then 1 else 0 end) +
,(case when col_2 like '%$keyword%' then 1 else 0 end) +
,(case when col_3 like '%$keyword%' then 1 else 0 end) as [priority]
from table_name
where col_1 like '%$keyword%'
or col_2 like '%$keyword%'
or col_3 like '%$keyword%'
order by [priority] desc
回答by McGarnagle
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 likequeries, 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):
以下是 MySQL 计算相关性的方式（来自链接）：
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.
在 WHERE 子句中使用 MATCH() 时，如前面所示的示例中所示，返回的行将首先自动按相关性最高的进行排序。相关值是非负浮点数。零相关性意味着没有相似性。相关性是根据行中的单词数、该行中唯一单词的数量、集合中的单词总数以及包含特定单词的文档（行）数计算的。
To create a full-text index on an existing table, use the FULLTEXTmodifier:
要在现有表上创建全文索引，请使用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);
