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

385 IT屋

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

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



以下是我的搜索代码:

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




$ $ p $ SELECT * FROM名称匹配(myname)AGAINST('+和'BOOLEAN模式)
< code>

我已经关闭了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` ); 

本文地址:IT屋 » MySQL 5.6全文搜索当搜索单词“the”时出现问题。在PHP中