mysql - MATCH AGAINST Mysql 没有给出正确的结果

Question

我正在运行 SQL 查询，如下所示:

SELECT a.id as id,a.name as name , a.price as price , a.saleprice as saleprice, a.images as images, a.slug as slug,a.hover_name as hover_name, MATCH (a.name) AGAINST ('+"blue cushion"') AS title_relevance FROM gc_products a WHERE ( ( ( MATCH (a.name) AGAINST ('+"blue cushion"') ) OR (a.sku like '%blue cushion%') ) and (a.enabled=1) ) ORDER BY title_relevance DESC 

但是它给了我错误的结果，它还向我显示了名称类似于“蓝色蜡烛”、“红色垫子”的记录

我也尝试过

         MATCH (a.name) AGAINST ('blue cushion')

     MATCH (a.name) AGAINST ('+blue cushion')

MATCH (a.name) AGAINST ('+blue +cushion')

这两个词不一定要在一起。它们可以出现在名称中的任何位置，但两个词必须出现在名称中。 “+”号表示 0 个或多个，而我至少需要一次。

但同样的结果来了。我希望如果有多个单词需要匹配，那么它应该匹配名称中的所有单词。

 MATCH (a.name) AGAINST ('blue +cushion')

Answer 1

您必须使用IN BOOLEAN MODE修饰符，因为默认情况下MySQL执行natural language search .

With this modifier, certain characters have special meaning at the beginning or end of words in the search string.

来源:Boolean Full-Text Searches

只需像这样重写您的查询:

SELECT a.id as id,a.name as name , a.price as price , a.saleprice as saleprice,
    a.images as images, a.slug as slug,a.hover_name as hover_name,
    MATCH (a.name) AGAINST ('+"blue cushion"' IN BOOLEAN MODE) AS title_relevance
    FROM gc_products a
    WHERE ( ( ( MATCH (a.name) AGAINST ('+"blue cushion"' IN BOOLEAN MODE) )
    OR (a.sku like '%blue cushion%') )
    AND (a.enabled=1) )
    ORDER BY title_relevance DESC