mysql全文检索

关注

转载最新推荐文章于 2026-07-21 15:16:59 发布 626 阅读 AI 写同款· GEO 优化 ›

MySQL 全文检索的实现这有朋友聊到他们的系统中要接入全文检索，这让我想起了很久以前为一个很古老的项目添加搜索功能的事儿。一提到全文检索，我们首先就会想到搜索引擎。也就是用一个词、一段文本搜索出匹配的内容。一般这种技术都有对应的实现方式，ES（ElasticSearch）就是专门干这个的，如果你们的业务中明确需要全文检索，或者简单一点说，需要根据关键词搜索出匹配的内容，那就直接用 ES 就好了。无论你怎么调研，都不推荐使用 MySQL 实现这种需求，显而易见，MySQL 作为关系型数据库，本身就不适合做搜索这种需求。阅读详情

http://androider.javaeye.com/blog/643486

mysql到版本3.23.23时，开始支持全文检索，通过语句SELECT ... FROM ... MATCH(...)AGAINST(...)来在整个表中检索是否有匹配的，全文索引是一个定义为fulltext的类型索引，应用在myisam表中。值得一提的是对于一个大的数据库来说，把数据装载到一个没有fulltext索引的表中，然后再添加索引，这样速度会非常快，但是把数据装载到一个已经有fulltext索引的表中，这样速度非常慢的。

首页要先明白mysql的全文检索原理：mysql使用的是一个非常简单的剖析器来将文本分隔成词，空格、标点等，比如‘welcomtoyou’将分隔为三个词‘welcom’、‘to’、‘you’，但是对中文来说，比如‘人力方网站正式上线’，这将无法分隔，因此目前mysql只支持英文的全文检索。

下面我们通过实例来一步步把全文检索的过程解释清楚：

首页我们建立表与初始化数据

Sql代码

CREATE TABLE IF NOT EXISTS `category` (
`id` int(10) NOT NULL auto_increment,
`fid` int(10) NOT NULL,
`catname` char(255) NOT NULL,
`addtime` char(10) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `catname` (`catname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `category` (`id`, `fid`, `catname`, `addtime`) VALUES
(1, 0, 'welcome to you!', '1263363380'),
(2, 0, 'hello phpjs,you are welcome', '1263363416'),
(3, 0, 'this is the fan site of you', '1263363673');

在具体实例之前，我们分析下msyql全文检索的语法：函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST()的参数被给定。搜索以忽略字母大小写的方式执行。说白了就是MATCH给定匹配的列（fulltext类型索引），AGAINST给定要匹配的字符串，多个用空格、标点分开，mysql会自动分隔。

1、

SQL代码

SELECT * FROM `category` WHERE MATCH(catname) AGAINST('phpjs')

返回结果：

id fid catname addtime
2 0 hello phpjs,you are welcome 1263363416

匹配出了含有phpjs关键字的行数据。

2、

SQL代码

SELECT * FROM `category` WHERE MATCH (catname) AGAINST ('this')

按照上面的思路，第三行数据含有this，因此应该可以匹配出第三行数据的，但事实却奇怪得很，返回结果为空，为什么呢？

原来是mysql指定了最小字符长度，默认是4，必须要匹配大于4的才会有返回结果，可以用SHOW VARIABLESLIKE'ft_min_word_len'来查看指定的字符长度，也可以在mysql配置文件my.ini 更改最小字符长度，方法是在my.ini 增加一行比如：ft_min_word_len = 2，改完后重启mysql即可。

3、这里我们要确定把最小字符改为2了，因为3行记录都有‘you’，因此心想，匹配‘you’就可以返回所有结果了

SQL代码

SELECT * FROM `category` WHERE MATCH (catname) AGAINST ('you')

返回结果还是为空，大跌眼镜了吧，这又是为什么呢？

原来mysql在集和查询中的对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重)，因为在这个特定的集中，它有较低的语义值。否则，如果词是较少的，它将得到一个较高的权重，mysql默认的阀值是50%，上面‘you’在每个文档都出现，因此是100%，只有低于50%的才会出现在结果集中。

4、有人会想，我不去管权重大小，只要有匹配的就给我返回结果集中，那么该如何做呢？

mysql到 4.0.1 时，可以使用 IN BOOLEAN MODE 修饰语来执行一个逻辑全文搜索

SQL代码