MySQL FULLTEXT无法正常工作 [英] MySQL FULLTEXT not working

查看:192
本文介绍了MySQL FULLTEXT无法正常工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我创建了一个测试表(使用MyISAM类型,带有一个单一的表单文本字段 a )并输入了一些示例数据。现在,如果我是正确的,下面的查询应该返回这两行:

  SELECT * FROM test WHERE MATCH(a)AGAINST '数据库')

然而它不返回任何值。我已经做了一些研究,我尽我所能地做了一切正确的事 - 表是MyISAM表,FULLTEXT索引已设置。我试着从提示符和phpMyAdmin中运行查询,但没有运气。我错过了一些至关重要的东西吗?






更新:这种情况似乎不适用于我的实际表格:

  CREATE TABLE IF NOT EXISTS`uploads`(
(11)NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`size` int(11)NOT NULL,
`type` text NOT NULL,
'alias` text NOT NULL,
`md5sum` text NOT NULL,
`uploaded` datetime NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 6;

我正在使用的数据:

  INSERT INTO`uploads`(`id`,`name`,`size`,`type`,`alias`,`md5sum``` uploaded`)VALUES 
(1,'04 Sickman.mp3',5261182,'audio / mp3','1','df2eb6a360fbfa8e0c9893aadc2289de','2009-07-14 16:08:02'),
(2,'07 Dirt .mp3',5056435,'audio / mp3','2','edcb873a75c94b5d0368681e4bd9ca41','2009-07-14 16:08:08'),
(3,'header_bg2.png',16765,' image / png','3','5bc5cb5c45c7fa329dc881a8476a2af6','2009-07-14 16:08:30'),
(4,'page_top_right2.png',5299,'image / png','4 ','53ea39f826b7c7aeba11060c0d8f4e81','2009-07-14 16:08:37'),
(5,'todo.txt',392,'text / plain','5','7ee46db77d1b98b145c9a95444d8dc67',' 2009-07-14 16:08:46');

我现在正在执行的查询是:

  SELECT * FROM`uploads` WHERE MATCH(name)AGAINST('header'IN BOOLEAN MODE)

哪个应该返回第3行,header_bg2.png。相反,我得到另一个空的结果集。我的布尔搜索选项如下:

  mysql>显示像'ft_%'这样的变量; 
+ -------------------------- + ---------------- +
|变量名|值|
+ -------------------------- + ---------------- +
| ft_boolean_syntax | + - ><()〜*:& | |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (内置)|
+ -------------------------- + ---------------- +
5行(0.02秒)

标题在字长限制内,我怀疑这是一个停止词(我不知道如何获得列表)。任何想法?

解决方案

添加更多数据。默认情况下,MySQL会忽略表中50%或更多行中的任何单词,因为它认为这将是一个噪音字。

表格中的行很少,通常经常达到这个50%的限制(例如,如果你有两行,每个单词至少有50% )。


I'm attempting to add searching support for my PHP web app using MySQL's FULLTEXT indexes.

I created a test table (using the MyISAM type, with a single text field a) and entered some sample data. Now if I'm right the following query should return both those rows:

SELECT * FROM test WHERE MATCH(a) AGAINST('databases')

However it returns none. I've done a bit of research and I'm doing everything right as far as I can tell - the table is a MyISAM table, the FULLTEXT indexes are set. I've tried running the query from the prompt and from phpMyAdmin, with no luck. Am I missing something crucial?


UPDATE: Ok, while Cody's solution worked in my test case it doesn't seem to work on my actual table:

CREATE TABLE IF NOT EXISTS `uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `size` int(11) NOT NULL,
  `type` text NOT NULL,
  `alias` text NOT NULL,
  `md5sum` text NOT NULL,
  `uploaded` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

And the data I'm using:

INSERT INTO `uploads` (`id`, `name`, `size`, `type`, `alias`, `md5sum`, `uploaded`) VALUES
(1, '04 Sickman.mp3', 5261182, 'audio/mp3', '1', 'df2eb6a360fbfa8e0c9893aadc2289de', '2009-07-14 16:08:02'),
(2, '07 Dirt.mp3', 5056435, 'audio/mp3', '2', 'edcb873a75c94b5d0368681e4bd9ca41', '2009-07-14 16:08:08'),
(3, 'header_bg2.png', 16765, 'image/png', '3', '5bc5cb5c45c7fa329dc881a8476a2af6', '2009-07-14 16:08:30'),
(4, 'page_top_right2.png', 5299, 'image/png', '4', '53ea39f826b7c7aeba11060c0d8f4e81', '2009-07-14 16:08:37'),
(5, 'todo.txt', 392, 'text/plain', '5', '7ee46db77d1b98b145c9a95444d8dc67', '2009-07-14 16:08:46');

The query I'm now running is:

SELECT * FROM `uploads` WHERE MATCH(name) AGAINST ('header' IN BOOLEAN MODE)

Which should return row 3, header_bg2.png. Instead I get another empty result set. My options for boolean searching are below:

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

"header" is within the word length restrictions and I doubt it's a stop word (I'm not sure how to get the list). Any ideas?

解决方案

Add more data. By default MySQL will ignore any word that is in 50% or more of the rows in the table as it considers it would be a 'noise' word.

With very few rows in a table, it is common to hit this 50% limit often (ie. if you have two rows, every word is in at least 50% of the rows!).

这篇关于MySQL FULLTEXT无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆