这并没有回答您提出的问题,但我认为将此条件添加到查询的WHERE子句中:
OR EXISTS ( SELECT 1
FROM categories k
WHERE k.id = pc.category_id
AND k.title IN ('Test1','Test2')
)
在
GROUP BY
无需扫描
GROUP_CONCAT
,如果规范要返回类别标题与指定搜索词之一匹配的行。
如果不需要实际返回派生的
keywords
列中,我会避免使用分组方式。
SELECT p.id AS id
, p.title AS title
, p.title AS company
FROM products p
JOIN companies co
ON co.id = p.company_id
WHERE MATCH (p.title) AGAINST ('Test')
OR MATCH (co.title) AGAINST ('Test')
OR EXISTS ( SELECT 1
FROM categories k
JOIN product_categories pc
ON pc.category_id = k.id
WHERE pc.product_id = p.id
AND k.title IN ('Test1','Test2')
)
ORDER BY p.id