FULLTEXT搜索短的查询语句无效解决方案

最新推荐文章于 2024-08-11 03:57:54 发布

原创最新推荐文章于 2024-08-11 03:57:54 发布 · 657 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#搜索引擎 #MySQL #Windows

数据库专栏收录该内容

6 篇文章

订阅专栏

本文详细解析了MySQL全文搜索的工作原理，包括如何使用MATCH()函数进行全文匹配，以及如何通过调整配置参数来优化全文搜索性能，确保查询效率。通过实际案例展示调整全文搜索参数后查询结果的变化，并提供了在不同场景下的最佳实践建议。

可以在http://www.aiice.com/314.html 查看更好效果！！

查看代码复制到剪贴板

mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+

mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

搜索引擎会忽略那些太常见的短语句如 the and 之类的单词都是索引不到的不信邪那验证下：

查看代码复制到剪贴板

mysql> SELECT COUNT(*) AS 'total verses',
-> COUNT(IF(vtext LIKE '%God%',1,NULL)) AS 'verses containing "God"',
-> COUNT(IF(vtext LIKE '%sin%',1,NULL)) AS 'verses containing "sin"'
-> FROM kjv;
+--------------+-------------------------+-------------------------+
| total verses | verses containing "God" | verses containing "sin" |
+--------------+-------------------------+-------------------------+
| 31102 | 4118 | 1292 |
+--------------+-------------------------+-------------------------+

mysql> SELECT COUNT(*) AS 'total verses',
    -> COUNT(IF(vtext LIKE '%God%',1,NULL)) AS 'verses containing "God"',
    -> COUNT(IF(vtext LIKE '%sin%',1,NULL)) AS 'verses containing "sin"'
    -> FROM kjv;
+--------------+-------------------------+-------------------------+
| total verses | verses containing "God" | verses containing "sin" |
+--------------+-------------------------+-------------------------+
|        31102 |                    4118 |                    1292 | 

+--------------+-------------------------+-------------------------+

看到了吧，其实正真的原因是因为：默认情况下搜索引擎并不会把少于4个字母的单词包括在内。
you can change by setting the ft_min_word_len servervariable. For example, to tell the indexing engine to include words containing three or morecharacters, add a set-variable line to the [mysqld] group of the /etc/my.cnf file (or
whatever option file you put server settings in):

在Windows下就在C:\Program Files\MySQL\MySQL Server 5.0\my.ini里面添加就行

查看代码复制到剪贴板

[mysqld]
set-variable = ft_min_word_len=3
或者
ft_min_word_len=3

[mysqld]
set-variable = ft_min_word_len=3
或者
ft_min_word_len=3

然后重启服务器重建TEXTFULL 索引否则无效

查看代码复制到剪贴板

mysql> ALTER TABLE kjv DROP INDEX vtext;
mysql> ALTER TABLE kjv ADD FULLTEXT (vtext);
//Then try out the new index to verify that it includes shorter words:
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
| 3878 |
+----------+
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
| 389 |
+----------+

mysql> ALTER TABLE kjv DROP INDEX vtext;
mysql> ALTER TABLE kjv ADD FULLTEXT (vtext);
//Then try out the new index to verify that it includes shorter words:
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
| 3878 |
+----------+
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
| 389 |
+----------+

至于结果比like要少是因为MATCH（）语句进行的FULLTEXT搜索对整个单词匹配！