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

问题描述

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

以下是我的搜索代码：

  SELECT * FROM names WHERE MATCH（myname）AGAINST（'+ the'IN BOOLEAN MODE）
  

：

$ $ p $ SELECT * FROM名称匹配（myname）AGAINST（'+和'BOOLEAN模式）

我已经关闭了CNF文件中的停用词，使用 ft_min_word_len将最小字母更改为3 = 3 AND innodb_ft_min_token_size = 3

我正在使用innodb。并且返回27000个结果，'the'从大约500,000条记录的db返回17000，所以我没有达到50％的记录，即使如此，我使用布尔模式。我找不到另一个打破这个词的词。任何想法？

解决方案

好的，我终于明白了这一点。它看起来像我已经myisam停用词清除，但不是innodb的。这比myisam稍微难一些，但是以下是其他人可能需要的步骤：

在你的/etc/my.cnf（或我的。 ini）添加这些行：

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

确保你的表是innodb并添加一个名为 value ，varchar（？），utf8_general_ci的列。

  innodb_ft_enable_stopword = 1 
 innodb_ft_server_stopword_table = settings / innodb-stopwords 
  

重新启动您的mysql服务器。

并重新创建全文索引。

如果您不想重新启动服务器，您可以动态设置变量（也可以更新cnf / ini文件进行下一步服务器重启）

   -  innodb_ft_enable_stopword = 1 
 --innodb_ft_server_stopword_table = db_name / table_name 
  

我没有看到任何解决方法来重新创建索引...您可以在一个命令中完成，但表格被锁定您的用户不会收到错误：

  ALTER TABLE`tablename` DROP INDEX indexname，ADD FULLTEXT（`columnname` ）; 
  

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)

This one works fine:

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

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

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?

解决方案

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:

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

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.

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

Restart your mysql server.

Drop and recreate your fulltext indexes.

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`);

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