Mysql
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 索引以允許索引更多單詞時,生成的索引肯定會更大。