规范化MySQL全文评分 [英] Normalise MySQL fulltext score

问题描述

我正在做一个非常标准的MySQL MATCH（...）AGAINST（...）查询。我将每行的分数作为列分数返回。这里的问题是，分数可能超过1，所以得到一个百分比是棘手的（乘以100），因为我可以得到的分数大于1.我的问题是，我可以规范化分数列，所以我得到所有分数从0到1，但保持它们的比例（例如，4分为1，2分为0.5等，但4可以是动态上限）。

我的查询如下：

  SELECT *，MATCH（body）AGAINST（'string' ）AS分数
 FROM home_posts 
 WHERE MATCH（body）AGAINST（'string'）
  

解决方案

在MySQL MATCH 中的分数不是一个百分比，试图将它转换为这样的方法完全是错误的方法。您可以在 http://forge.mysql.com/wiki/MySQL_Internals_Algorithms中阅读它背后的数学＃全文_搜索

编辑：
我会尝试

  SELECT *，
 MATCH（body）AGAINST（'string'）AS得分，
（MATCH（body）AGAINST（'string'）/ maxScore）AS normalisedScore 
 FROM home_posts，
（SELECT MAX（MATCH（body）AGAINST（'string'））AS maxScore 
 FROM home_posts）maxScoreTable 
 WHERE MATCH（body）AGAINST（'string'）
  

I'm doing a pretty standard MySQL MATCH(...) AGAINST(...) query. I'm returning the score of each row as column score. The problem here is that score can be more than 1, so getting a percentage is tricky (multiplying by 100) due to the fact I can get scores greater than 1. My question is, can I normalise the score column so I get all scores from 0 to 1, but keep them in proportion (for example, a score of 4 would be 1, and a score of 2 would become 0.5, etc, but 4 can be a dynamic upper bound).

My query is as follows:

SELECT *, MATCH(body) AGAINST ('string') AS score 
FROM home_posts 
WHERE MATCH(body) AGAINST ('string') 

解决方案

The score in a MySQL MATCH is not a percentage and trying to convert it to such would be the completely wrong approach altogether. You can read the math behind it at http://forge.mysql.com/wiki/MySQL_Internals_Algorithms#Full-text_Search

EDIT: I would try

SELECT *, 
    MATCH(body) AGAINST ('string') AS score, 
    (MATCH(body) AGAINST ('string') / maxScore) AS normalisedScore
FROM home_posts, 
    (SELECT MAX(MATCH(body) AGAINST ('string')) AS maxScore 
     FROM home_posts) maxScoreTable
WHERE MATCH(body) AGAINST ('string') 

这篇关于规范化MySQL全文评分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！