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 <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数据库引擎中支持全文本搜索。