搜索单词“the"时的 MySQL 5.6 全文搜索问题在 PHP 中 [英] MySQL 5.6 Full Text Search Issue when Searching for the word "the" in PHP

问题描述

当我的搜索在全文搜索中包含单词the"时，我遇到了问题.

Im having an issue when my search includes the word "the" in my full text search.

这是我的问题搜索代码:

Here is my search code with the problem:

SELECT * FROM names WHERE MATCH(myname) AGAINST ('+the' IN BOOLEAN MODE)

这个很好用:

SELECT * FROM names WHERE MATCH(myname) AGAINST ('+and' IN BOOLEAN MODE)

我已经关闭了 CNF 文件中的停用词，使用 ft_min_word_len=3 和 innodb_ft_min_token_size=3

I already turned off the stop words in my CNF file, changed the min letters to 3 using both ft_min_word_len=3 AND innodb_ft_min_token_size=3

我正在使用 innodb.并返回 27000 个结果，'the' 从大约 500,000 条记录的数据库中返回 17000 个结果，所以我没有达到 50% 的标记，即便如此，我使用的是布尔模式.我找不到另一个词来打破这一点.有什么想法吗?

I'm using innodb. And returns 27000 results and 'the' returns 17000 from a db of about 500,000 records so im not hitting the 50% mark, even so, I'm using boolean mode. I can't find another word that breaks this. Any ideas?

推荐答案

好的，我终于想通了.看起来我已经清除了 myisam 停用词，但没有清除 innodb 停用词.与 myisam 相比，这有点难，但这里是其他可能需要它的人的步骤:

Ok, I finally figured this out. It looks like I already had the myisam stopwords cleared but not the innodb ones. It's a little but harder to do than for myisam but here are the steps for anyone else that might need it:

在您的/etc/my.cnf(或 Windows 上的 my.ini)中添加以下几行:

In your /etc/my.cnf (or my.ini on windows) add these lines:

创建一个停用词表.我在一个名为 settings 的数据库和一个名为 innodb-stopwords 的表中创建了我的.你不能只设置innodb_ft_enable_stopword = 0，你必须创建并链接到一个表.

Create a stopwords table. I made mine in a db called settings and a table called innodb-stopwords. You cannot just set innodb_ft_enable_stopword = 0, you have to create and link to a table.

确保您的表是 innodb 并添加名为 value、varchar(?)、utf8_general_ci 的列.您可以将其留空或向表中添加值.

Make sure your table is innodb and add a column called value, varchar(?), utf8_general_ci. You can leave it empty or add values to the table.

innodb_ft_enable_stopword = 1  
innodb_ft_server_stopword_table = settings/innodb-stopwords

重启你的 mysql 服务器.

Restart your mysql server.

删除并重新创建全文索引.

Drop and recreate your fulltext indexes.

如果不想重启服务器，可以动态设置变量(同时更新cnf/ini文件以备下次服务器重启)

If you don't want to restart the server, you can dynamically set the variables with (also update the cnf/ini file for the next server restart)

--innodb_ft_enable_stopword=1
--innodb_ft_server_stopword_table=db_name/table_name

我没有看到任何重新创建索引的解决方法...不过您可以在一个命令中完成此操作，因此该表始终处于锁定状态并且您的用户不会收到错误:

I don't see any workaround to recreating the index... you can do it in one command though so the table is locked the whole time and your users don't get errors:

ALTER TABLE `tablename` DROP INDEX indexname, ADD FULLTEXT(`columnname`);

这篇关于搜索单词“the"时的 MySQL 5.6 全文搜索问题在 PHP 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！