MySQL全文搜索-使用完全匹配和通配符搜索单词，但首先获得完全匹配其他匹配

Question

我使用布尔全文搜索从数据库列中搜索单词。我想获得与我的记录完全匹配的记录，还包括通配符搜索。我得到了正确的结果，但我想首先通过完全匹配来设置该结果的顺序。有没有可能的方法来实现这一目标？我还想对每个单词进行搜索并按相关性获取记录。我试过使用以下查询，但它不起作用，因为它给了我错误的记录排名。    SELECT      id,      search_tags,      MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE) AS relevance_rank    FROM      images    WHERE      MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE)    ORDER BY relevance_rank DESC;我希望查询的输出是   id  search_tag             relevance_rank   11  car,cards,food,code    2.1669161319732666   12  car,water,sky          2.1669161319732665   13  carrier,food,drink     2.1669161319732664，但实际输出就像   id  search_tag             relevance_rank1   11  carrier,food,drink     2.1669161319732666   12  car,cards,food,code    2.1669161319732666   13  car,water,sky          2.1669161319732666

Answer 1

该查询将整个单词的记录排名更高，但car,cards排名与 just 相同，car因此它仍然不是最佳的。

SELECT id, search_tags, 

    MATCH(search_tags) AGAINST('car *') + 

    MATCH(search_tags) AGAINST('car') AS relevance_rank

FROM images

WHERE MATCH(search_tags) AGAINST('car *') 

OR MATCH(search_tags) AGAINST('car')

ORDER BY 3 DESC

+----+---------------------+----------------+

| 身份证 | search_tags | 相关性_排名|

+----+---------------------+----------------+

| 12 | 汽车，卡片，食物，代码| 0.1677478002 |

| 13 | 汽车，水，天空| 高分辨率照片| CLIPARTO 0.1677478002 |

| 11 | 载体，食物，饮料| 高分辨率照片| CLIPARTO 0.0093915509 |

+----+---------------------+----------------+

下一个查询还会查看确切单词在字符串中出现的次数。更好的结果，但可能有点太复杂了。

SELECT id, search_tags, 

    MATCH(search_tags) AGAINST('car *') + 

    MATCH(search_tags) AGAINST('car') AS relevance_rank,

    LENGTH(search_tags) - 

    LENGTH(REPLACE(search_tags, 'car', space(LENGTH('car')) -1 )) AS occurences

FROM images

WHERE MATCH(search_tags) AGAINST('car *') 

OR MATCH(search_tags) AGAINST('car')

ORDER BY 3 DESC, 4 DESC

+----+---------------------+-----+----- -------+

| 身份证 | search_tags | 相关性_排名| 发生|

+----+---------------------+-----+----- -------+

| 12 | 汽车，卡片，食物，代码| 0.1677478002 | 2 |

| 13 | 汽车，水，天空| 高分辨率照片| CLIPARTO 0.1677478002 | 1 |

| 11 | 载体，食物，饮料| 高分辨率照片| CLIPARTO 0.0093915509 | 1 |

+----+---------------------+-----+----- -------+

Answer 2

使用Union时应该工作：

SELECT

  id,

  search_tags,

  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ")  IN BOOLEAN MODE) AS relevance_rank,

1 as prio -- sort by priority first

FROM

  images

WHERE

  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," "," ")  IN BOOLEAN MODE)

ORDER BY relevance_rank DESC 

UNION SELECT

  id,

  search_tags,

  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE) AS relevance_rank,

 2 as prio -- sort by priority first

FROM

  images

WHERE

  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE)

ORDER BY prio, relevance_rank DESC;