可能有一种更简单的方法可以做到这一点..不知怎的,我掉进了这个兔子洞..但它经过测试并且有效(返回结果的百分比)
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 的similar_text 这样的值 - 你最好将这项工作转移给客户端......
全文搜索的相关性是如何衡量的? https://stackoverflow.com/questions/237970/full-text-search-relevance-is-measured-in
http://forums.mysql.com/read.php?107,125239,146610#msg-146610 http://forums.mysql.com/read.php?107,125239,146610#msg-146610
http://seminex.blogspot.com/2005/06/mysql-relevance-in-fulltext-search.html http://seminex.blogspot.com/2005/06/mysql-relevance-in-fulltext-search.html