mysql - 使用 Union mysql 以 bool 模式进行权重搜索

Question

我正在使用此查询跨多个表进行搜索，该查询正在工作，但是我想权衡与列“标题”的匹配高于与列描述的匹配。

SELECT 'about' AS a.about,title,null article ,null description

FROM about a 

WHERE 
(
MATCH(a.about) AGAINST ('\"$search\"' IN BOOLEAN MODE)
)

UNION
SELECT 'articles' AS null, b.title,b.article,b.description
from articles b 

WHERE
(
MATCH(b.title,b.article,b.description) AGAINST ('\"$search\"' IN BOOLEAN MODE)
)

我尝试将“AS相关性1”添加到比赛末尾，如下所示，但它没有返回结果:

SELECT 'about' AS a.about,title,null article ,null description

FROM about a 

WHERE 
(
MATCH(a.about) AGAINST ('\"$search\"' IN BOOLEAN MODE)
)

UNION
SELECT 'articles' AS null, b.title,b.article,b.description
from articles b 

WHERE
(
MATCH(b.title,b.article,b.description) AGAINST ('\"$search\"' IN BOOLEAN MODE) AS relevence_1,
MATCH(b.title,b.article,b.description) AGAINST ('\"$search\"' IN BOOLEAN MODE) AS relevence_2
)

ORDER BY (relevance_1 * 3) + (relevance_2 * 2)  DESC

Answer 1

经过大量搜索后，我发现这种方法有效并且非常可读:

SELECT 'about' AS a.about,title,null article ,null description,MATCH(a.title) AGAINST ('\"$search\"' IN BOOLEAN MODE) as scale

FROM about a 

WHERE 
(
MATCH(a.about) AGAINST ('\"$search\"' IN BOOLEAN MODE)
)

UNION
SELECT 'articles' AS null, b.title,b.article,b.description,MATCH(b.title) AGAINST ('\"$search\"' IN BOOLEAN MODE) as scale
from articles b 

WHERE
(
MATCH(b.title,b.article,b.description) AGAINST ('\"$search\"' IN BOOLEAN MODE)
)

ORDER BY scale  DESC