为什么 MySQL 全文索引不起作用？
在尝试了一切之后，我终于创建了这个测试表：
CREATE TABLE test_table (
id int(11) NOT NULL AUTO_INCREMENT,
title text NOT NULL,
PRIMARY KEY (id),
FULLTEXT KEY title (title)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
使用以下测试数据：
INSERT INTO test_table (id, title) VALUES
(1, 'Evolving intelligence bayesian filtering power behind');
我希望以下查询返回 1 行：
SELECT * FROM test_table WHERE MATCH (title) AGAINST ('intelligence');
但它返回一个空集。
我在同一个 mysql 实例中有另一个数据库，其中全文搜索按预期工作。但对于我创建的每个新数据库，全文都不起作用。我重建了索引，修复了表，甚至使用 myisam_ftdump 检查了索引。我没主意了。
您对这个问题有什么想法吗？先感谢您。
After trying everything I could, I finally created this test table:
CREATE TABLE test_table (
id int(11) NOT NULL AUTO_INCREMENT,
title text NOT NULL,
PRIMARY KEY (id),
FULLTEXT KEY title (title)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
With the following test data:
INSERT INTO test_table (id, title) VALUES
(1, 'Evolving intelligence bayesian filtering power behind');
I would expect the following query to return 1 row:
SELECT * FROM test_table WHERE MATCH (title) AGAINST ('intelligence');
But it returns an empty set.
I have another database in the same mysql instance where full text searching works as expected. But for every new database I have created, full text doesn't work. I have rebuilt the indexes, repaired the tables, even inspected the index with myisam_ftdump. I'm out of ideas.
Do you have any idea about this problem? Thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
只是为了澄清。如果您的表没有包含足够的数据（例如 1 行），则全文搜索将不起作用。对于要编入索引的单词，它们在 50% 的行中的出现频率需要较低。
因此“智能”出现在 100% 的行中，因此不会建立索引。
添加至少 2 行（不含“智能”一词），它将开始工作。 mySQL 文档中并没有明确说明这一点，并且经常让新手感到困惑。那是（我也是）因为我们都用小数据集进行测试。
直流
Just for clarification. if your table does not contain enough data (e.g. 1 row) a fulltext search will not work. for words to be indexed they need to appear less common than in 50% of the rows.
So 'intelligence' occurs in 100% of the rows and is therefore not indexed.
Add at least 2 more rows without the word intelligence and it will start working. This is not made clear in the mySQL documentation and often catches newbies out. that's (me too) because we all test with small datasets.
DC