1.理解全文本搜索
MySQL数据库支持几种基本的数据库引擎中,并不是所有的引擎都支持本文所描述的全文本搜索。在两种最常用的引擎MyISAM和InnoDB中,只有前者支持全文本搜索,而后者不支持。所以如果需要对表进行全文本搜索,则需要把该表的引擎设置为MyISAM。
全文本搜索有比LIKE匹配和正则表达式匹配更强的功能,在对表进行全文本搜索时,MySQL不需要分表查看每个行,不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。这样,MySQL可以有效的决定哪些词匹配(哪些行包含他们),哪些词不匹配,他们的匹配频率,等等。
2.使用全文本搜索
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断的重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
在索引之后,SELECT可与Match()和Against()一起使用以实际执行搜索。
2.1启用全文本搜索支持
一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句,它给出被索引列的一个逗号分隔的列表。
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
这里只指定了一个列note_text进行索引,如果需要也可以指定多个列。
在定义之后,MySQL自动维护该索引。增加、删除和更新行之后,索引会随之自动更新。
可以在创建表时使用FULLTEXT,也可以稍后使用。
注意:不要在导入数据时使用FULLTEXT。
2.2进行全文本搜索
在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()制定被搜索的列,Against() 指定要使用的搜索表达式:
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('rabbit')\G
输出结果:
*************************** 1. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparen
tly less effective now.
*************************** 2. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
2 rows in set (0.00 sec)
注意:1。传递给Match()的值必须与FULLTEXT()定义的相同。如果指定多个列,则必须列出它们且次序正确。
2. 除非使用BINARY方式,否则全文本搜索不区分大小写。
下面演示排序的工作方式:
SELECT note_text,MATCH(note_text) Against('rabbit') AS rank
FROM `productnotes`
输出结果:
*************************** 1. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
rank: 0
*************************** 2. row ***************************
note_text: Can shipped full, refills not available.
Need to order new can if refill needed.
rank: 0
*************************** 3. row ***************************
note_text: Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.
rank: 0
*************************** 4. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
rank: 1.59055435657501
*************************** 5. row ***************************
note_text: 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.
rank: 0
*************************** 6. row ***************************
note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR).
rank: 0
*************************** 7. row ***************************
note_text: Please note that no returns will be accepted if safe opened using explosives.
rank: 0
*************************** 8. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
rank: 0
*************************** 9. row ***************************
note_text: Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
rank: 0
*************************** 10. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
rank: 1.64080536365509
*************************** 11. row ***************************
note_text: Shipped unassembled, requires common tools (including oversized hammer).
rank: 0
*************************** 12. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
rank: 0
*************************** 13. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
rank: 0
*************************** 14. row ***************************
note_text: Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.
rank: 0
14 rows in set (0.00 sec)
在本例中。使用Match()和Against()建立一个计算列(别名rank),此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见,不包含rabbit的行等级为0.确实包含词rabbit的两行每行都有一个值,文本中词靠前的行的等级值比靠后的等级值高。
如果指定多个搜索项,则包含多数匹配词的行将具有更高的等级值。
2.3使用查询扩展
利用查询扩展,能找出可能相关的结果,即使他们并不精确包含所要查找的词。
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('anvils' WITH QUERY EXPANSION)
输出结果:
*************************** 1. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
*************************** 2. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
*************************** 3. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
*************************** 4. row ***************************
note_text: Please note that no returns will be accepted if safe opened using explosives.
*************************** 5. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
*************************** 6. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
*************************** 7. row ***************************
note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR).
7 rows in set (0.00 sec)
可以看出,只有第一行包含词anvils,因此等级最高。第二行与词anvils无关,但因为它包含第一行中的两个词(customer和recommend),所以也被检索出来,第三行业包含相同的词,但他们,他们文本中的位置更靠后且分开的更远,所以排在第三。
2.4 布尔文本搜索
MySQL同时还支持布尔方式(boolean mode)。可以支持以下细节:
■要匹配的词;
■要排斥的词;
■排列提示;
■表达式分组;
■其他。
布尔文本搜索在没有建立索引的情况下也能使用,但速度会变得非常缓慢。
全文本布尔搜索操作符
布尔操作符 说明
+ 包含,词必须存在
- 排除,词不必出现
> 包含,而且增加等级值
< 包含,且减小等级值
() 把词组成子表达式
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语
举例说明这些操作符的使用:
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('+rabbit +bait' IN BOOLEAN MODE)
/*这个搜索匹配包含词rabbit和bait的行*/
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('rabbit bait' IN BOOLEAN MODE)
/*没有指定操作符,匹配包含rabbit和bait的任意一个的行*/
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('"rabbit bait"' IN BOOLEAN MODE)
/*匹配短语rabbit bait*/
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('>rabbit <carrot"' IN BOOLEAN MODE)
/*增加前者等级,减少后者等级*/
SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
/*搜索匹配词safe和combination,并降低后者等级*/
2.5使用说明
■在索引全文本数据时,短词被忽略且从索引中排除。短词定义为三个或三个以下字符的词(数目可以更改)
■MySQL带有一个内建的stopword列表,这些词在索引中是被忽略的,如果需要,可以覆盖这个列表。
■许多词出现的频率很高,搜索他们没用处。MySQL规定如果一个词出现50%以上的行中,则作为一个stopword忽略。这个规则不适用于全文本布尔搜索。
■如果表中行数少于三行,全文本搜索不返回结果。(因为每个词或者不出现,或者至少出现在50%的行中)
■忽略词中的单引号。例如,don't索引为dont。
■不具有词分隔符(如汉语和日语)的语言不能恰当地返回全文搜索结果。
■仅在MyISAM数据库引擎中支持全文本搜索。