Mysql全文搜索找不到一个词-为什么？

Question

(mysql 5.1.36)

我试图找出为什么当我在全文查询中搜索单词“three”时没有得到任何结果。如果单词“three”是字段中唯一的文本，或者它是句子的一部分，我会返回相同的结果。

很容易重现:

CREATE TABLE `test`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `word` TEXT NOT NULL , PRIMARY KEY (`id`) ); 
ALTER TABLE `test` ADD FULLTEXT `NewIndex1` (`word`);
INSERT INTO `test`(`id`,`word`) VALUES ( NULL,'three');
INSERT INTO `test`(`id`,`word`) VALUES ( NULL,'thorn');

然后运行这些查询:

SELECT * FROM test WHERE word='three';

结果 = [1, '三']

SELECT word FROM test WHERE MATCH(word) AGAINST ('three*' IN BOOLEAN MODE );

结果 = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('three' IN BOOLEAN MODE);

结果 = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('thre*' IN BOOLEAN MODE );

结果 = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('thorn*' IN BOOLEAN MODE )

result = [ 2, '刺' ]

SELECT word FROM test WHERE MATCH(word) AGAINST ('thorn' IN BOOLEAN MODE)

result = [ 2, '刺' ]

SELECT word FROM test WHERE MATCH(word) AGAINST ('thor*' IN BOOLEAN MODE)

result = [ 2, '刺' ]

为什么全文搜索“three”没有返回结果？

Answer 1

因为 three 是停用词，fulltext 将忽略该词并且不返回与词“three”相关的任何结果。基本上，存在停用词以提高性能，并且不会返回包含“is, are, there, be...”等词的每一行。这是 list停用词，检查它们。