mysql - 与查询匹配不起作用 - 参数不正确

Question

我有一个包含文章的表格和一个包含类别的表格。每个类别都有许多关键字，我想使用这些关键字来确定一篇文章是否属于某个类别。

我正在使用下面的查询:

SELECT 
  path,
  title,
  description,
  keywords
FROM
  (
  SELECT 
    path, 
    keywords, 
    (select title from article where id = 164016) as title,
    (select description from article where id = 164016) as description
  FROM 
    categories c 
) as x
WHERE 
  MATCH (title, description) AGAINST ('my keywords' IN BOOLEAN MODE)  

由于某种原因，由于 MATCH 的参数不正确，此查询无法正常工作，但我无法弄清楚它是什么。

Answer 1

使用boolean fulltext search以启用表达式的精确匹配。但是，此类搜索存在一些限制，如上面链接的文档所述:

A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. The words might not be in the index because of a combination of factors: if they do not exist in the text, are stopwords, or are shorter than the minimum length of indexed words.

上面还意味着，如果搜索表达式中存在停用词或短于最小长度的单词，那么 MySQL 将不会返回任何匹配项。

SELECT path, keywords, MATCH (c.keywords) AGAINST ('"Here is some text"' IN BOOLEAN MODE) as relevance
FROM categories c
WHERE MATCH (c.keywords) AGAINST ('"Here is some text"' IN BOOLEAN MODE) 

如果您想要完全精确的匹配，则不能使用全文搜索。您需要使用 like 运算符或 = 运算符。

更新

1. title 在本例中是一个没有全文索引的计算字段。

2. 反对()

takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

( source )

您在 against() 函数中有一个字段名称，这是不允许的。