mysql 全文本检索的列_MySQL使用全文本搜索

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

/*增加前者等级，减少后者等级*/

SELECT note_text

FROM `productnotes`

WHERE MATCH(note_text) Against('+safe +(

/*搜索匹配词safe和combination，并降低后者等级*/

2.5使用说明

■在索引全文本数据时，短词被忽略且从索引中排除。短词定义为三个或三个以下字符的词(数目可以更改)

■MySQL带有一个内建的stopword列表，这些词在索引中是被忽略的，如果需要，可以覆盖这个列表。

■许多词出现的频率很高，搜索他们没用处。MySQL规定如果一个词出现50%以上的行中，则作为一个stopword忽略。这个规则不适用于全文本布尔搜索。

■如果表中行数少于三行，全文本搜索不返回结果。(因为每个词或者不出现，或者至少出现在50%的行中)

■忽略词中的单引号。例如，don't索引为dont。

■不具有词分隔符(如汉语和日语)的语言不能恰当地返回全文搜索结果。

■仅在MyISAM数据库引擎中支持全文本搜索。