mysql - 我可以在 [0; 中获得 MySQL FULLTEXT 行列吗？ 1]范围？

Question

我正在为任意文本/短语实现类似自动建议的功能。我想向用户提供一些绝对 相关性，而不仅仅是找到的项目之间的相对排名。即，如果数据库中的某些行包含以准确形式和顺序输入的文本，我需要一个大约“1”的排名。

例如，让我们搜索“我喜欢水果”:

• 对于内容为“我喜欢水果 和蔬菜”的行，我希望排名为 1 或更低。
• 对于内容为“我喜欢新鲜水果”的行，我希望结果小于 1，但仍然很高，比如 0.7。<

这可以用 MySQL 和 FULLTEXT 来完成吗？而不是 [0,1] 我得到的值是 2.7 或 1.2 甚至 0.6 完全匹配。它出什么问题了？

这是我的测试表:

表格数据:

id  text
1   Lorem ipsum dolor
2   You can search an index, and organize and present search results.
3   The Search API can index any number of documents.
4   Each field has a name and a type.
5   Each field is required.
7   Cras dapibus. Vivamus elementum semper nisi. 
8   Aenean vulputate eleifend tellus. Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim. 
9   Aenean imperdiet. Etiam ultricies nisi vel augue. Curabitur ullamcorper ultricies nisi. 
10  Nam eget dui. Etiam rhoncus. 

查询 1:

SET @str := 'Lorem ipsum dolor';
SELECT id, TEXT, MATCH (TEXT) AGAINST (@str) rank FROM test WHERE MATCH (TEXT) AGAINST (@str);

->

 id text    rank
 1  Lorem ipsum dolor   1.280059814453125

这个“1.28”是什么意思？如果这里有“1”就好了。

查询 2:

SET @str := 'Each field is required.';
SELECT id, TEXT, MATCH (TEXT) AGAINST (@str) rank FROM test WHERE MATCH (TEXT) AGAINST (@str); 

->

 id text    rank
 5  Each field is required. 1.7639520168304443
 4  Each field has a name and a type.   0.8533731698989868

再来一场完整比赛，我希望这里也有“1”。

查询 3:

SET @str := 'Aenean leo ligula, porttitor eu';
SELECT id, TEXT, MATCH (TEXT) AGAINST (@str) rank FROM test WHERE MATCH (TEXT) AGAINST (@str);

->

id  text    rank
8   Aenean vulputate eleifend tellus. Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim.  3.5851094722747803
9   Aenean imperdiet. Etiam ultricies nisi vel augue. Curabitur ullamcorper ultricies nisi.     0.4266865849494934

也完全匹配，返回神秘的'3.58'。

我不需要精确的数字，只需要 [0,1] 之间的数字，这样我就可以确定是否存在完全(或几乎完全)匹配。如果结果完全包含搜索到的字符串，我需要大约 0.8..1。或者它只是一个错误的工具？ FULLTEXT 实际上不是全文，因为我不知道它是否完全匹配。

Answer 1

MySQL 使用 an n-dimensional vector product with some fudge factors为全文匹配生成相关值，这意味着这些值不能以超出给定数据集和查询的一般方式进行规范化。 (话又说回来，你为什么需要它们？对同一个数据集的类似查询已经产生了类似的相关值，并且对不同查询的结果进行规范化无论如何也无济于事。)

就是说，没有什么能阻止您自己对查询返回的结果集中的排名值进行标准化，使用每行的简单计算:

row_normalized_rank = row_returned_rank / highest_returned_rank

这将需要对结果集进行两次扫描，一次用于识别返回的最高排名值，另一次用于根据返回的最高值对每一行的排名值进行归一化；您可能可以使用非常糟糕的嵌套查询来完成它，但最好还是用代码来完成。

您也可以自己添加一个软糖因素，以提高完全匹配的排名；考虑绝对排名调整，例如:

SELECT id, text, (MATCH (text) AGAINST (@str) +
                  IF(text LIKE CONCAT("%", @str, "%"), 1, -1)) AS rank
FROM test
WHERE MATCH (text) AGAINST (@str);

或数量级的扩展，例如:

SET @fudge := 2;
SELECT id, text, (MATCH (text) AGAINST (@str) *
                  IF(text LIKE CONCAT("%", @str, "%"), @fudge, 1/@fudge)) AS rank
FROM test
WHERE MATCH (text) AGAINST (@str);

当然可以根据口味进行调整，但这应该有助于为您提供更符合您在排名行为方面寻找的东西。