删除mysql ft_stopword_file后无结果 [英] No results after removing mysql ft_stopword_file

问题描述

我有一个电影数据库，其中包含有关电影的信息，是的，我们是开放的.

I have a film database that contains information about a film called Yes, We're Open.

搜索数据库时，我遇到一个问题，即搜索是的我们是开放的"会返回另一个标题，该标题的描述中包含我们是"和开放"，但不是是"，即使我需要布尔模式下的所有单词(例如，是的，我们是开放的"在发送为查询之前都会翻译为'+yes +we\'re +open').

When searching the database, I'm having an issue wherein a search for "yes we're open" returns another title that has the words "we're" and "open" but not "yes" in its description, even though I require all words in boolean mode (i.e. "yes we\'re open" is translated to '+yes +we\'re +open' before it's sent as a query).

我认为这是因为内置的停用词列表中包含是".但是，当我设置ft_stopword_file = ""，重新启动mysql，然后repair table [tablename] quick我正在搜索的表时，搜索是的我们已经打开"时，没有任何结果.我在下面包含了my.cnf.这是MySQL 5.0.22版.有什么想法吗?

I assumed this was because "yes" is in the built-in stopwords list. However, when I set ft_stopword_file = "", restart mysql, and then repair table [tablename] quick the table that i'm searching, I get NO results on a search for "yes we're open". I've included my my.cnf below. This is MySQL version 5.0.22. Any ideas?

[mysqld]
query-cache-type = 1
query-cache-size = 8M
max_allowed_packet=500M
ft_min_word_len=2
ft_stopword_file = ""

[myisamchk]
ft_min_word_len=2

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

这是一些示例查询:

#1-带有内置停用词文件

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)     | 
+--------------------------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT title, MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) as title_description_genre_country_score FROM `films` WHERE MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) AND `hidden` <> '1' ORDER BY `title_description_genre_country_score` DESC ;
+-----------------+---------------------------------------+
| title           | title_description_genre_country_score |
+-----------------+---------------------------------------+
| Yes, We?re Open |                                     1 | 
| Present/Future  |                                     1 | 
+-----------------+---------------------------------------+
2 rows in set (0.00 sec)

....然后编辑my.cnf，添加ft_stopword_file="" .....

.... then edit my.cnf, add ft_stopword_file="".....

#2没有停用词文件

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         |                | 
+--------------------------+----------------+
5 rows in set (0.00 sec)

mysql> REPAIR TABLE `films` QUICK;
+-------------------------+--------+----------+----------+
| Table                   | Op     | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| db.films                | repair | status   | OK       | 
+-------------------------+--------+----------+----------+
1 row in set (0.14 sec)

mysql> SELECT title, MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) as title_description_genre_country_score FROM `films` WHERE MATCH(title,description,genre,country) AGAINST (' +yes +we\'re +open' IN BOOLEAN MODE) AND `hidden` <> '1' ORDER BY `title_description_genre_country_score` DESC ;
Empty set (0.00 sec)

编辑#2:创建表格:

mysql> SHOW CREATE TABLE db.films\G;
*************************** 1. row ***************************
Table: films
Create Table: CREATE TABLE `films` (
  `id` varchar(8) NOT NULL default '',
  `title` varchar(255) default NULL,
  `hidden` tinyint(1) default '0',
  `featured` tinyint(1) default NULL,
  `type` varchar(255) default NULL,
  `subtype` varchar(255) default NULL,
  `summary` text,
  `description` text,
  `image_url` varchar(255) default NULL,
  `trailer_url` varchar(255) default NULL,
  `slug` varchar(255) default NULL,
  `category` varchar(255) default NULL,
  `parent` varchar(255) default NULL,
  `related` varchar(255) default NULL,
  `sponsor` varchar(255) default NULL,
  `genre` varchar(255) default NULL,
  `country` varchar(255) default NULL,
  `copresenters` varchar(255) default NULL,
  `original_title` varchar(255) default NULL,
  `director` varchar(255) default NULL,
  `executive_producer` varchar(255) default NULL,
  `producer` varchar(255) default NULL,
  `cinematographer` varchar(255) default NULL,
  `writer` varchar(255) default NULL,
  `editor` varchar(255) default NULL,
  `sound` varchar(255) default NULL,
  `cast` varchar(255) default NULL,
  `language` varchar(255) default NULL,
  `trt` varchar(255) default NULL,
  `year` varchar(255) default NULL,
  `subtitles` varchar(255) default NULL,
  `format` varchar(255) default NULL,
  `color` varchar(255) default NULL,
  `premiere_status` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`),
  KEY `type` (`type`),
  KEY `subtype` (`subtype`),
  KEY `slug` (`slug`),
  KEY `category` (`category`),
  KEY `parent` (`parent`),
  KEY `hidden` (`hidden`),
  KEY `featured` (`featured`),
  KEY `copresenters` (`copresenters`),
  KEY `original_title` (`original_title`),
  KEY `director` (`director`),
  KEY `executive_producer` (`executive_producer`),
  KEY `producer` (`producer`),
  KEY `cinematographer` (`cinematographer`),
  KEY `writer` (`writer`),
  KEY `editor` (`editor`),
  KEY `sound` (`sound`),
  KEY `cast` (`cast`),
  KEY `language` (`language`),
  KEY `trt` (`trt`),
  KEY `year` (`year`),
  KEY `subtitles` (`subtitles`),
  KEY `format` (`format`),
  KEY `color` (`color`),
  KEY `premiere_status` (`premiere_status`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `summary` (`summary`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `genre` (`genre`),
  FULLTEXT KEY `country` (`country`),
  FULLTEXT KEY `title,description` (`title`,`description`),
  FULLTEXT KEY `title,description,genre,country` (`title`,`description`,`genre`,`country`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

推荐答案

将stopwords.txt变量设置为空字符串('')将禁用所有

Setting stopwords.txt variable to the empty string ('') disables all stopword filtering. (Remove some words which you don't require...suggestion..)

由您创建的全文搜索索引需要销毁并创建一个新的索引.

Indexes of FULL TEXT SEARCH which was created by you needs to be destroy and create new one.

在更改此变量或停用词文件的内容之后，必须重新构建FULLTEXT索引.

FULLTEXT indexes must be rebuilt after changing this variable or the contents of the stopword file.

然后使用REPAIR TABLE tbl_name QUICK.

Then Use REPAIR TABLE tbl_name QUICK.

这篇关于删除mysql ft_stopword_file后无结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！