在ubuntu上更改mysql ft_min_word_len不起作用 [英] mysql ft_min_word_len change on ubuntu does not work

问题描述

我正在尝试执行完整的测试搜索。为此，我首先将/etc/mysql/my.cnf上的 ft_min_word_len = 2 的值更改为

  [mysqld] 
＃
＃*基本设置
＃
 
＃
＃*重要
＃如果您对这些设置进行了更改，并且系统使用了apparmor，那么
＃还需要调整/etc/apparmor.d/usr.sbin.mysqld。 
＃
 ft_min_word_len = 2 
  

现在保存并重新启动服务器。
我知道如果我已经有一个索引与表中的FULLTEXT我将需要删除索引和重建，或修复表。
但是我创建了表格作为

  create table 
`comments` 
（` id` int（11），
`comment` varchar（200），
`iduser` int（11），
`date_added` datetime 
）
 ENGINE = MyISAM数据; 
 
 ALTER TABLE注释
 ADD FULLTEXT INDEX comment_index 
（comment）; 
  

然后在上面的表格中我手动添加了一些注释。

当我尝试搜索某些内容时：

  SELECT * FROM注释MATCH（评论）反对（'the'）; //the是非常常见的单词，用于查看我的测试结果
  

它返回0行。

然而，如果我将AGAINST设置为4的字长，它就可以工作。

我试图检查ft_变量作为

  mysql>显示像'ft_％'这样的变量; 
 + -------------------------- + ---------------- + 
 |变量名|值| 
 + -------------------------- + ---------------- + 
 | ft_boolean_syntax | +  - ><（）〜*：& | | 
 | ft_max_word_len | 84 | 
 | ft_min_word_len | 2 | 
 | ft_query_expansion_limit | 20 | 
 | ft_stopword_file | （内置）| 
 + -------------------------- + ---------------- + 
  

有趣的是在 /etc/mysql/my.cnf 我只能看到 ft_min_word_len ，但是 ft_max_word_len 不存在，更重要的是搜索长度小于4不工作atall。

这让我疯狂，不知道是否有其他的配置写完所有的东西，似乎也找不到它们。

任何帮助，将不胜感激。

我的开发机器中的Mysql版本是

  mysql Ver 14.14 Distrib 5.1.63，为debian-linux-gnu（i686）使用readline 6.2 
  

解决方案

我能够找到问题和解决方法。

全文搜索设置很好，因为我想用alt东2字搜索，而我现在在进行更多测试时，我发现它随机搜索少于2个字符的字，并忽略其他。

以下是一个例子

  CREATE TABLE articles（
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY，
 title VARCHAR（200），
 body TEXT，
 FULLTEXT（title，body）
）; 
 
 INSERT INTO articles（title，body）VALUES 
（'MySQL Tutorial'，'DBMS'代表DataBase ...'），
（'How to Use MySQL Well' ，'经过...'后），
（'优化MySQL'，'在本教程中我们将展示...'），
（'1001 MySQL Tricks'，'1。 （...'），
（'MySQL vs. YourSQL'，'在下面的数据库比较中...'），
（'MySQL Security'，'When配置正确，MySQL ...'）; 
 
 mysql> SELECT * FROM articles WHERE MATCH（title，body）AGAINST（'use'）; 
空集（0.00秒）
 
 mysql> SELECT * FROM articles WHERE MATCH（title，body）AGAINST（'Use'）; 
空集（0.00秒）
 
 mysql> SELECT * FROM articles WHERE MATCH（title，body）AGAINST（'you'）; 
空集（0.00秒）
 
 mysql> SELECT * FROM articles WHERE MATCH（title，body）AGAINST（'the'）; 
空集（0.00秒）
 
 mysql> SELECT * FROM articles WHERE MATCH（title，body）AGAINST（'vs.'）; 
空集（0.00秒）
  

但下列作品

  mysql> SELECT * FROM articles 
  - >匹配（标题，正文）反对（'跑'）; 
 + ---- + ------------------- + -------------------- ----------------- + 
 | id |标题| body | 
 + ---- + ------------------- + -------------------- ----------------- + 
 | 4 | 1001 MySQL技巧| 1.永远不要以root身份运行mysqld。 2. ... | 
 + ---- + ------------------- + -------------------- ----------------- + 
  

其他人显然发现它的 ft_stopword_file ，它有一个单词列表，如果有其中一个搜索发生，就不会做任何事情。

清单在这里 http：// dev。 mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

所以在这种情况下，允许任何单词搜索的长度至少为2字符长

• 将ft_min_word_len设置为2


• 然后在mysql配置文件中， etc / mysql / my.cnf添加 ft_stopword_file ='path / to / stopword_file.txt'


• 如果需要，我们可以将此文件留空。

另外一件事，一旦我们做了上述设置，我们需要重新启动mysql，如果我们更改 ft_min_word_len


I am trying to implement the full test search. And for this the first thing I did change the value of ft_min_word_len = 2 on /etc/mysql/my.cnf as

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
ft_min_word_len = 2

Now Saved that and restarted the server. I am aware that if I already have an index with FULLTEXT in a table I will need to drop the indexes and rebuilt, or repair the table. But I have created the table as

create table 
`comments` 
(   `id` int(11), 
    `comment` varchar(200), 
    `iduser` int(11) , 
    `date_added` datetime
) 
ENGINE=MyISAM;

ALTER TABLE comments
ADD FULLTEXT INDEX comment_index
(comment);

Then in the above table I have some comments added manually.

When I try to search something as

SELECT * FROM comments where MATCH (comment) AGAINST ('the') ; // "the" is very common word of length to see my test result

It returns 0 rows.

However if I set AGAINST with a word length of 4 it works.

I tried to check the ft_ variables as

mysql>  show variables like 'ft_%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 2              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+

Interesting thing is in /etc/mysql/my.cnf I can only see ft_min_word_len but the ft_max_word_len is not there and more importantly the search less than length 4 does not work atall.

This is making me crazy and not sure if there is some other config which is over writing everything and seems like not able locate them either.

Any help would be appreciated.

Mysql Version in my development machine is

mysql  Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (i686) using readline 6.2  

解决方案

I was able to found the issue and the fix.

The full text search setting was good since I wanted to search with alt east 2 words and I had ft_min_word_len = 2

Now while doing more testing I found it randomly does search for few 2 character words and ignores other.

Here is an example

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('use');
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('Use');
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('you');
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('vs.');
Empty set (0.00 sec)

But the following works

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('run');
+----+-------------------+-------------------------------------+
| id | title             | body                                |
+----+-------------------+-------------------------------------+
|  4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-------------------+-------------------------------------+

So its something else and apparently found its the ft_stopword_file which has a list of words and does not do anything if any search happens with one of them.

The list is here http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

So in this case to allow any word search of length at least 2 character long

• Set the ft_min_word_len to 2
• Then in the mysql config file , for debian /etc/mysql/my.cnf add ft_stopword_file='path/to/stopword_file.txt'
• We can leave this file blank if needed.

Oh one more thing once we do the above settings we need to restart mysql and if we change ft_min_word_len then we need to re-built the index or repair the table.

这篇关于在ubuntu上更改mysql ft_min_word_len不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！