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_rankFROM imagesWHERE 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 occurencesFROM imagesWHERE 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 firstFROM  imagesWHERE  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 firstFROM  imagesWHERE  MATCH(search_tags) AGAINST(REPLACE(concat("'car'"," ")," ","* ")  IN BOOLEAN MODE)ORDER BY prio, relevance_rank DESC;