mysql - MySQL 中的全文搜索不返回任何行

Question

我有点迷茫，这看起来像是一些愚蠢的错误 - 但我不知道那可能是什么。这是测试 session :

mysql> drop table articles;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE articles (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE articles ADD FULLTEXT(body, title);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into articles(body) values ('Maya');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from articles
    -> ;
+------+-------+----+
| body | title | id |
+------+-------+----+
| Maya | NULL  |  1 |
+------+-------+----+
1 row in set (0.00 sec)

这是关于“mysqld Ver 5.1.37-1ubuntu5 for debian-linux-gnu on i486 ((Ubuntu))”。

这里是简单剪切和粘贴的脚本(请尝试并验证它是否适用于您的系统):

CREATE TABLE articles (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id)) ENGINE = MYISAM;
ALTER TABLE articles ADD FULLTEXT(body, title);
insert into articles(body) values ('Maya');
SELECT * FROM articles  WHERE MATCH(title, body) AGAINST('Maya');     

Answer 1

在MySQL中有三种类型的全文搜索:

• bool 搜索
• 自然语言搜索(默认使用)
• 查询扩展搜索

来自 MySQL manual entry :

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.

例如，尝试再添加两条记录:

INSERT INTO articles(body) VALUES ('Some text'), ('Another text');

然后再次运行相同的 SELECT - 它会起作用。

作为解决方法，您可以使用 bool 模式，它没有这个“50%”规则:

SELECT * FROM articles  WHERE MATCH(title, body) AGAINST('Maya' IN BOOLEAN MODE);