MySQL中的FULLTEXT搜索不会返回任何行 [英] FULLTEXT search in MySQL does not return any rows
问题描述
我有点失落,这看起来像是一个愚蠢的错误 - 但我不知道那会是什么。这里是测试会话:
mysql>放桌子文章;
查询OK,0行受影响(0.02秒)
mysql> CREATE TABLE articles(body TEXT,title VARCHAR(250),id INT NOT NULL auto_increment,PRIMARY KEY(id))ENGINE = MYISAM;
查询OK,0行受影响(0.02秒)
mysql> ALTER TABLE文章ADD FULLTEXT(body,title);
查询OK,0行受影响(0.02秒)
记录:0重复:0警告:0
mysql>插入文章(主体)值('玛雅');
查询OK,1行受影响(0.00秒)
mysql> SELECT * FROM articles WHERE MATCH(title,body)AGAINST('Maya');
空集(0.00秒)
mysql>选择*从文章
- > ;
+ ------ + ------- + ---- +
| body |标题| id |
+ ------ + ------- + ---- +
|玛雅| NULL | 1 |
+ ------ + ------- + ---- +
1行(0.00秒)
这是关于i486((Ubuntu)上的debian-linux-gnu)的mysqld Ver 5.1.37-1ubuntu5。
以下是简单剪切和粘贴的脚本(请尝试一下并验证它是否适用于您的系统):
CREATE TABLE articles(body TEXT,title VARCHAR(250),id INT NOT NULL auto_increment,PRIMARY KEY(id))ENGINE = MYISAM;
ALTER TABLE文章ADD FULLTEXT(body,title);
插入文章(主体)值('Maya');
SELECT * FROM文章WHERE MATCH(标题,正文)AGAINST('Maya');
在MySQL中,有三种全文搜索:
- 布尔搜索
- 自然语言搜索(默认使用)
- 查询扩展搜索
来自 MySQL手册记录:
自然语言搜索将
解释为搜索字符串作为
自然人类语言(
自由文本中的短语)中的短语。没有特殊的
操作员。停用词列表适用。
此外,在50%或更多的行中出现
的单词是
,这被认为是常见的并且不匹配。
全文搜索如果给出了IN NATURAL
LANGUAGE MODE修饰符或者给出了
no修饰符,那么它们是自然的
语言搜索。
例如,尝试添加两条记录:
INSERT INTO articles(body)VALUES('Some text '),('另一个文本');
再次运行相同的SELECT - 它将起作用。
作为一种解决方法,您可以使用布尔模式,它没有这个50%规则:
选择*从文章中匹配(标题,正文)反对('玛雅'在布尔模式);
I am a bit lost, this looks like some silly mistake - but I have no clue what that can be. Here is the test 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)
This is on "mysqld Ver 5.1.37-1ubuntu5 for debian-linux-gnu on i486 ((Ubuntu))".
Here is the script for simple cut and paste (please try it and verify if it works on your system):
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');
In MySQL there are three types of full-text searches:
- boolean search
- natural language search (used by default)
- query expansion search
From 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.
For example, try to add two more records:
INSERT INTO articles(body) VALUES ('Some text'), ('Another text');
And run the same SELECT again - it will work.
As a workaround, you can use boolean mode, which doesn't have this "50%" rule:
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('Maya' IN BOOLEAN MODE);
这篇关于MySQL中的FULLTEXT搜索不会返回任何行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!