MySQL全文搜索总是有0个结果？ [英] MySQL Fulltext search always has 0 results?

问题描述

我读过使用全文搜索比使用LIKE %%更快。

  SELECT *，
 MATCH（pages）我已经更新了我的脚本，但它总是总是有0个结果。 AGAINST（'doodle'）AS分数
 FROM书
匹配（页）反对（'涂鸦'）
 ORDER BY得分DESC 
  

$关键字长度大于4个字符，我将页面列作为全文索引。我在这个格式的yankee doodle的页面列中有涂鸦字样。

我也试过这个

  SELECT *，
 MATCH（pages）AGAINST（'doodle'IN BOOLEAN MODE）AS score 
 FROM books 
 WHERE MATCH（pages）AGAINST '涂鸦'IN BOOLEAN MODE）; 
  

它们都不起作用：\

解决方案

全文搜索有一些奇怪的怪癖。例如， 此页面的最后一段可能是您遇到问题的原因：

....例如，虽然前面显示的文章表格的每一行都有MySQL这个词， word不会产生任何结果：

  mysql> SELECT * FROM articles 
  - > WHERE MATCH（title，body） （'MySQL'）; 
空集（0.00秒）
  

搜索结果为空，因为单词MySQL至少有50％行。因此，它被有效地视为一个停止词。对于大型数据集，这是最理想的行为：自然语言查询不应该从1GB表中返回每隔一行。对于小数据集，这可能不太理想。

这里的答案是添加更多行或使用布尔搜索。

如果您需要加权搜索结果，请查看评论 John Craig发表于2009年12月16日下午7:01 在链接页面上提供解决方法建议。

I have read that using fulltext searching is faster then using LIKE %%. I have updated my script but it always seems to always have 0 results.

SELECT *,
MATCH(pages) AGAINST('doodle') AS score
FROM books
WHERE MATCH(pages) AGAINST('doodle')
ORDER BY score DESC 

The $keyword is longer then 4 chars and I have index the pages column as fulltext. I have "doodle" in the pages column in this format "yankee doodle".

I have also tried this

SELECT *,
MATCH(pages) AGAINST ('doodle' IN BOOLEAN MODE) AS score 
FROM books 
WHERE MATCH(pages) AGAINST ('doodle' IN BOOLEAN MODE)"; 

Neither of them works :\

解决方案

Fulltext search has some bizarre quirks.

For example, the behaviour described in the last paragraphs of this page could be the reason for your problem:

.... for example, although the word "MySQL" is present in every row of the articles table shown earlier, a search for the word produces no results:

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('MySQL');
 Empty set (0.00 sec)

The search result is empty because the word "MySQL" is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large data sets, this is the most desirable behavior: A natural language query should not return every second row from a 1GB table. For small data sets, it may be less desirable.

The answer here would be to add more rows, or use boolean search.

If you need weighted search results, check out the comment Posted by John Craig on December 16 2009 7:01pm on the linked page for a workaround suggestion.

这篇关于MySQL全文搜索总是有0个结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！