MySQL全文索引問題

我有一張articles包含以下數據的表格

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  7 | ABCD                  | Following things are not upto date       |
|  8 | RockOn                | is the Following                         |
+----+-----------------------+------------------------------------------+
8 rows in set (0.00 sec)

當我將查詢寫為..

mysql> select * from articles where match(`body`) against('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

結果如預期。

但是當我發出查詢時

mysql >select * from articles where match(`body`) against('following' in boolean mode);
Empty set (0.00 sec)

為什麼它顯示空集，因為有與查詢對應的記錄。

我在正文上有全文索引。

mysql> show create table articles\G
*************************** 1. row ***************************
      Table: articles
Create Table: CREATE TABLE `articles` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(200) DEFAULT NULL,
 `body` text,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

它可能似乎following在stop words列表中。

你應該檢查停用詞，看看是不是這樣。

@John.Locke 的回答一針見血。+1 你的答案。

@DTest 的評論證實了這一點。對您的評論 +1。

以下是如何解決它：

步驟 01) 創建一個空的停用詞列表

$ echo -n > /var/lib/mysql/stopwords.txt

可選：使停用詞列表包含文章（沒有雙關語）‘a’,‘an’,’the’

$ echo "a"    > /var/lib/mysql/stopwords.txt
$ echo "an"  >> /var/lib/mysql/stopwords.txt
$ echo "the" >> /var/lib/mysql/stopwords.txt

步驟 02) 配置 mysql 以接受 FULLTEXT 索引中的所有內容減去您的自定義停用詞

[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

步驟 03) 重啟 mysql

$ service mysql restart

步驟 04) 重新索引表

mysql> ALTER TABLE articles DROP INDEX body;
mysql> ALTER TABLE articles ADD FULLTEXT (body);

試一試 ！！！

警告

每當您在自定義後重新索引 FULLTEXT 索引以允許索引更多單詞時，生成的索引肯定會更大。

引用自：https://dba.stackexchange.com/questions/9902