如何以百分比形式获取mysql匹配的结果? [英] how to get the result of mysql match against in form of percentage?

问题描述

我在mysql中使用与(Val)匹配(Col1).

select match(body) against(body_var) from articles;

现在在完全匹配的情况下，我得到的结果是数字(例如14.43). 这个数字是什么意思?而主要问题是我可以百分数形式获得结果吗(例如0.94)
谢谢你的帮助

解决方案

可能有一种更简便的方法.结果)

SELECT (mthCount / ttlCount) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

它返回一个记录/结果，列为mPercent

您也可以将其四舍五入到小数点后两位...

SELECT FORMAT((mthCount / ttlCount),2) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

正如我所说..我针对358行进行了50次匹配测试 50/350 = 0.1396648 ...(对于第一个结果) 四舍五入后为0.14

---

如果您希望将单个结果的相关性值转换为百分比-这真的不会发生...

MATCH/AGAINST中的相关性值不是百分比匹配的良好指标..互联网上对此进行了深入介绍..搜索将相关性值转换为百分比" ...

如果您想按相关性匹配百分比对结果进行排序，而第一个结果始终具有100％的相关性，则可以...

对于尝试获取类似于PHP的相似文本的值-最好将其工作分担给客户端...

测量全文搜索的相关性?

http://forums.mysql.com/read .php?107,125239,146610#msg-146610

http://seminex.blogspot.com /2005/06/mysql-relevance-in-fulltext-search.html

I am using Match (Col1) Against (Val) in mysql.

select match(body) against(body_var) from articles;

now in case of completely match i am getting result as a number (for example 14.43). what does this number mean? and the main question is can i get the result in percentage form (for example 0.94)
thanks for your help

解决方案

There is probably a MUCH easier way to do this.. Somehow i fell down the rabbit hole on this one.. But its tested and works (returns percentage of results)

SELECT (mthCount / ttlCount) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

it returns one record/result with the column mPercent

You could also have it round to two decimal places...

SELECT FORMAT((mthCount / ttlCount),2) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

As i said.. I tested it against 358 rows with 50 matches 50/350 = 0.1396648... (for first result) 0.14 for rounded result

---

If you are looking to convert the relevance value to a percent for a single result - it isnt really going to happen...

The relevance value from the MATCH/AGAINST is not a good indicator of percent match.. This is covered in depth throught the internet.. Search for "Convert Relevance value to percent" ...

If you wanted to order your results by relevence match percent, with the first result always having 100% relevence, you can do that...

As for trying to get a value like PHP's similar_text - you are better to offload that work to the client...

Full-text search relevance is measured in?

http://forums.mysql.com/read.php?107,125239,146610#msg-146610

http://seminex.blogspot.com/2005/06/mysql-relevance-in-fulltext-search.html

这篇关于如何以百分比形式获取mysql匹配的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！