mysql - MySQL全文搜索对具有相同值的所有结果进行评分

Question

我有一个带有列名的表 products，我想对其进行全文搜索。

上面有数据：

name, other columns ...
...
BR26 AF-1, ...
BR26 AF-2, ...
BR26 AF-3, ...
BR26 AF-4, ...
BR26 AF-5, ...
BR26 AF-5, ...
BR26 AF-6, ...
BR26 AF-7, ...
BR26 AF-8, ...
BR26 AF-9, ...
BR26 AF-10, ...
BR26 AF-11, ...
BR26 AF-12, ...
BR26 Beton, ...
... 

所以我做了：

ALTER TABLE products ADD FULLTEXT(name);

现在我准备了一个查询来查找并返回按匹配分数相关性排序的匹配结果。

我已经尝试过 IN BOOLEAN MODE

SELECT *, MATCH (name) AGAINST ( 'BR26 AF-1' IN NATURAL LANGUAGE 
MODE ) as score FROM products WHERE MATCH (name) AGAINST ( 
'BR26 AF-1' IN NATURAL LANGUAGE MODE ) AND active='1' ORDER BY 
score DESC

实际结果和分数：

name, score, other columns ...
BR26 AF-5, 3.1776015758514404, ...
BR26 AF-2, 3.1776015758514404, ...
BR26 AF-3, 3.1776015758514404, ...
BR26 AF-4, 3.1776015758514404, ...
BR26 AF-1, 3.1776015758514404, ...
BR26 Beton, 3.1776015758514404, ...
BR26 AF-6, 3.1776015758514404, ...
BR26 AF-7, 3.1776015758514404, ...
BR26 AF-8, 3.1776015758514404, ...
BR26 AF-9, 3.1776015758514404, ...
BR26 AF-10, 3.1776015758514404, ...
BR26 AF-11, 3.1776015758514404, ...
BR26 AF-12, 3.1776015758514404, ... 

预期结果和分数：

name, score, other columns ...
BR26 AF-1, <highest score>, ...
BR26 AF-2, <lower score>, ...
BR26 AF-3, <lower score>, ...
BR26 AF-4, <lower score>, ...
BR26 AF-5, <lower score>, ...
BR26 AF-6, <lower score>, ...
BR26 AF-7, <lower score>, ...
BR26 AF-8, <lower score>, ...
BR26 AF-9, <lower score>, ...
BR26 AF-10, <lower score>, ...
BR26 AF-11, <lower score>, ...
BR26 AF-12, <lower score>, ...
BR26 Beton, <lower score>, ... 

我做错了什么没有得到预期的结果顺序？在我看来，“BR26 AF-1”将是 100% 匹配，因此它应该有最高分。

标签： mysqlsearchfull-text-searchscoring

Answer 1

你能试一下吗 ：

SELECT *, MATCH (name) AGAINST ( 'BR26 Beton' IN NATURAL LANGUAGE 
MODE ) as score FROM products WHERE MATCH (name) AGAINST ( 
'BR26 Beton' IN NATURAL LANGUAGE MODE ) AND active='1' ORDER BY 
score DESC

如果您得到不同的结果，我怀疑这是因为 MySQL 使用连字符 (-) 作为分隔符，并将 BR26、AF 和 1 作为三个不同的词进行搜索。

有几种方法可以解决这个问题，包括使用布尔模式并将搜索词用双引号括起来。请参阅此 SO 帖子： 如何允许在搜索查询中使用连字符进行全文搜索