具有高级条件的 Mysql，按相关性排序

Question

我对搜索功能有一个SQL查询，有点复杂，但它工作得很好。

SELECT
    SQL_CALC_FOUND_ROWS p.id as id,
    p.title as title,
    co.title as company,
    p.price as price,
    p.image_url as image_url FROM products p 
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories cat ON pc.category_id = cat.id
JOIN companies co ON p.company_id = co.id
        WHERE MATCH(p.title) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(p.description) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(cat.title) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(co.title) AGAINST("nalle" IN BOOLEAN MODE)
    GROUP BY p.id
    ORDER BY p.title
    LIMIT :offset, :limit

现在它按产品标题排序，但它应该将最相关的结果放在首位。这意味着，如果它同时匹配p.title和p.description则比仅匹配p.title更相关。最好的匹配是匹配所有四个。

由于查询的复杂性，我不知道如何计算它。

我猜我需要SELECT ??? as points... ORDER BY points .

Answer 1

如果是InnoDB，也许你可以试试这个，我不确定MyISAM：

SELECT
    SQL_CALC_FOUND_ROWS p.id as id,
    p.title as title,
    co.title as company,
    p.price as price,
    p.image_url as image_url,
    MATCH(p.title, p.description, cat.title, co.title)
         AGAINST("nalle" IN BOOLEAN MODE) as Relevance
FROM products p 
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories cat ON pc.category_id = cat.id
JOIN companies co ON p.company_id = co.id
WHERE MATCH(p.title, p.description, cat.title, co.title) AGAINST("nalle" IN BOOLEAN MODE)
GROUP BY p.id
ORDER BY Relevance DESC, p.title
LIMIT :offset, :limit

在这里的示例中，您可以看到他们如何计算分数。如果您要查找的单词是在更多字段中找到的，则分数会更高。