Ordering MySQL全文搜索结果它们具有相同的分数

huangapple go评论61阅读模式
英文:

Ordering MySQL Full Text Search results they have the identical scores

问题

我正在使用MySQL全文搜索来尝试查找所有包含单词“lane”的结果,如下所示:

SELECT *
    MATCH (title) AGAINST ('lane') AS score
FROM
    `books`
WHERE
    MATCH (title) AGAINST ('lane') AS score
ORDER
    BY score DESC

这个方法可以正常工作并返回预期的结果。问题在于它们都具有相同的分数,因此它们在结果中随机出现。我想按它们第一次出现的顺序进行排序,类似这样:

Lanes
Lane Of The Day
Red Lane
House On The Lane
Search For Hidden Lane

或类似的方式,当它们都具有相同的分数时,我是否有其他排序选项?

英文:

I am using MySQL Full Text Search to try and find all results for the word lane like this..

	SELECT *
		MATCH (title) AGAINST ('lane') AS score
	FROM
		`books`
	WHERE
		MATCH (title) AGAINST ('lane') AS score
	ORDER
		BY score DESC

This is working and returning expected results, the problem is that they all have an equal score so they appear randomly in the results. I would like to sort them by first appearance, something like this...

Red Lane
Lanes
Search For Hidden Lane
House On The Lane
Lane Of The Day

Would turn into..

Lanes
Lane Of The Day
Red Lane
House On The Lane
Search For Hidden Lane

Or something similar, do I have any other options on ordering when they all return the same score?

答案1

得分: 1

以下是代码的翻译:

SELECT *,
    MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) AS score
FROM
    `books`
WHERE
    MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) > 0
ORDER
    BY score DESC, POSITION('lane' IN title)
title score
Lane Of The Day 0.009391550906002522
Red Lane 0.009391550906002522
House On The Lane 0.009391550906002522
Search For Hidden Lane 0.009391550906002522

fiddle

英文:

For your text you can use the Position if Lane in the text, but i still wonder how you got Lanes as result

    SELECT *,
        MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) AS score
    FROM
        `books`
    WHERE
        MATCH (title) AGAINST ('lane'IN BOOLEAN MODE) >0
    ORDER
        BY score DESC,POSITION('lane' IN  title)
title score
Lane Of The Day 0.009391550906002522
Red Lane 0.009391550906002522
House On The Lane 0.009391550906002522
Search For Hidden Lane 0.009391550906002522

fiddle

huangapple
  • 本文由 发表于 2023年3月7日 01:16:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75653857.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定