MySQL：为什么在全文中分数总是1？ [英] MySQL: Why score is always 1 in Fulltext?

问题描述

如果我运行这个查询并打印每行的分数，它们总是1：

以下是一些示例查询结果：

  First | Last |得分
 ------------------------------ 
 Jonathan |布什| 1 
 Joshua |吉尔伯特| 1 
 Jon |乔纳斯| 1 
  

这是我运行的查询：

$ b $ （首先，最后）AGAINST（'Jon'BOOLEAN模式）AS分数
FROM用户
匹配的地方（首先，最后）反对（'乔恩'BOOLEAN模式）
ORDER BY得分DESC;

解决方案

布尔模式只支持二进制答案，意思是0或1搜索字符串是否出现在列中。要得到一个十进制结果来计算一个权重，你必须在索引列上使用匹配。

你可以用这种布尔模式来获得你的wheight：

$ p $ SELECT *，（（1.3 *（MATCH（column1）AGAINST（'BOOLEAN MODE'））+
（0.6 *（MATCH（column2）AGAINST（'query'IN BOOLEAN MODE））））AS相关
FROM表WHERE（MATCH（column1，column2）AGAINST
（'query'IN BOOLEAN MODE ））ORDER BY相关性DESC


布尔模式的优点在于，索引列但只有0,1作为结果，非布尔模式返回一个十进制结果，但只能应用于索引列...另请参见 here 。

If I run this query and print the score of each rows, they are always 1:

Here are some sample query results:

First     |  Last     | Score
------------------------------
Jonathan  |  Bush     | 1
Joshua    |  Gilbert  | 1
Jon       |  Jonas    | 1

And this is the query that I run:

SELECT First, Last, MATCH(First, Last) AGAINST ('Jon' IN BOOLEAN MODE) AS score 
FROM users 
WHERE MATCH(First, Last) AGAINST('Jon' IN BOOLEAN MODE)
ORDER BY score DESC;

解决方案

The BOOLEAN MODE supports only binary answers, means 0 or 1 whether the search string appears in the column or not. To get a decimal result to calculate a weight, you have to use match-against on indexed columns.

You can use the boolean mode this way to get your wheight either:

SELECT *, ((1.3 * (MATCH(column1) AGAINST ('query' IN BOOLEAN MODE))) +
(0.6 * (MATCH(column2) AGAINST ('query' IN BOOLEAN MODE)))) AS relevance
FROM table WHERE ( MATCH(column1,column2) AGAINST
('query' IN BOOLEAN MODE) ) ORDER BY relevance DESC

The advantage of the boolean mode is that you can use it on non-indexed columns but only with 0,1 as result, the non-boolean mode returns a decimal result but can only be applied on indexed columns... see also here.

这篇关于MySQL：为什么在全文中分数总是1？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！