MySQL学习系列之十三——全文本搜索(1)

这一篇内容讲述如何使用MySQL的全文本搜索功能进行高级的数据查询和选择。

首先来说明一些全文本搜索的基本概念:并非所有引擎都支持全文本搜索。两个最常用的引擎为MyISAm和InnoDB，前者支持全文本搜索，而后者不支持。在创建表的时候可以设置该属性。

在前面的章节中，我们介绍了LIKE关键字，它利用通配操作符匹配文本。

虽然使用LIKE关键字这种搜索机制非常有用，但是存在以下一些不足：

（1）性能：通配符通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

（2）明确控制：使用通配符，很难（而且并不总是能）明确的控制匹配什么和不匹配什么。例如：指定一个只必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或不匹配。

（3）智能化的结果：虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。

上面这些限制以及更多的限制都可以通过全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效的决定哪些词匹配（哪些行包含它们，哪些词不匹配，它们匹配的频率等）。

下面我们来介绍如何使用全文本搜索。

1.启用全文本搜索支持

一般在创建表时启用全文本搜索。create table语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

可以使用下面create语句来创建demo表

create table productnotes(

note_id int not null auto_increment,

prod_id char(10) not null,

note_date datetime not null,

note_text text null,

primary key(note_id),

fulltext(note_text)

)engine = MyISAM;

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(101, 'TNT2', '2005-08-17',

'Customer complaint:

Sticks not individually wrapped, too easy to mistakenly detonate all at once.

Recommend individual wrapping.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(102, 'OL1', '2005-08-18',

'Can shipped full, refills not available.

Need to order new can if refill needed.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(103, 'SAFE', '2005-08-18',

'Safe is combination locked, combination not provided with safe.

This is rarely a problem as safes are typically blown up or dropped by customers.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(104, 'FC', '2005-08-19',

'Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, and suitable for use as rabbit bait.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(105, 'TNT2', '2005-08-20',

'Included fuses are short and have been known to detonate too quickly for some customers.

Longer fuses are available (item FU1) and should be recommended.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(106, 'TNT2', '2005-08-22',

'Matches not included, recommend purchase of matches or detonator (item DTNTR).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(107, 'SAFE', '2005-08-23',

'Please note that no returns will be accepted if safe opened using explosives.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(108, 'ANV01', '2005-08-25',

'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(109, 'ANV03', '2005-09-01',

'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(110, 'FC', '2005-09-01',

'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(111, 'SLING', '2005-09-02',

'Shipped unassembled, requires common tools (including oversized hammer).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(112, 'SAFE', '2005-09-02',

'Customer complaint:

Circular hole in safe floor can apparently be easily cut with handsaw.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(113, 'ANV01', '2005-09-05',

'Customer complaint:

Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(114, 'SAFE', '2005-09-07',

'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.

Comment forwarded to vendor.'

);

在这个语句中，有一个字段为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。

2.进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

下面举例说明：

select note_text from productnotes where Match(note_text) Against('rabbit');

这个语句检索单个列note_text。由于where子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于这两行包含rabbit，这两行被返回。

（1）使用完整的Match()说明：传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

（2）搜索不区分大小写：除非使用BINARY方式（目前还没有介绍），否则全文搜索不区分大小写。

描述到这里，大家可能觉得上面的搜索结果可以用简单的LIKE子句完成，如下所示：

select note_text from productnotes where note_text like '%rabbit%';

得到了同样的结果，但是顺序不同。两个语句都不包含order by子句。这就是全文本搜索的一个优势了，全文本搜索可以对结果进行排序，具有较高等级的行先返回（因为这些行可能是你真正想要的行）。

我们通过以下SQL来演示排序如何工作

select note_text,Match(note_text) Against('rabbit') as rank from productnotes;

这个语句中，在select而不是where子句中使用Match()和Against()。这使得所有的行都被返回。Match()和Against()用来建立一个计算列（别名rank），此列包含全文本搜索计算出的等级值。等级由

MySQL根据行中词条的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来，如图，不包含rabbit的等级为0，包含rabbit的两个行每行都有一个等级值。

这个案例说明了全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

这篇内容到这里就结束了，在这篇内容中，我们简单介绍了全文本搜索的基本概念以及使用的基本语法。在下一篇中，我们将会描述稍微复杂一些的使用场景。

MySQL学习系列之十二——组合查询

MySQL学习系列之十一——创建联结

MySQL学习系列之十——联结

MySQL学习系列之九——子查询

MySQL学习系列之八——分组数据

MySQL学习系列之七——用聚集函数汇总数据

MySQL学习系列之六——创建计算字段

MySQL学习系列之五——通配符

MySQL学习系列之四——数据过滤和过滤数据

MySQL学习系列之三——不做限制的查询

MySQL学习系列之二——创建表及对表结构的基本操作

MySQL学习系列之一——MySQL简介

MySQL案例使用表建表以及插入数据语句

MySQL建表语句2