主页 / dba / 问题 / 9902
Accepted
Abdul Manaf
Asked: 2011-12-30 02:57:15 +0800 CST

MySQL全文索引问题

  • 772

我有一张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)
mysql myisam

2 个回答

  1. Best Answer

    似乎following在stop words列表中。
    你应该检查停用词，看看是不是这样。

    • 3

  2. @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 索引以允许索引更多单词时，生成的索引肯定会更大。

    • 2

相关问题