bitscn.com
mysql入门学习之三：全文本搜索
一、理解全文本搜索
1、myisam支持全文本搜索，而innodb不支持。
2、在使用全文本搜索时，mysql不需要分别查看每个行，不需要分别分析和处理每个词。mysql创建指定列中各词的一个索引，搜索可以针对这些词进行。这样mysql可以快速有效地决定哪些词匹配，哪些词不匹配，它们匹配的频率，等等。
二、使用全文本搜索
1、为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，mysql会自动进行所有的索引和重新索引。
在索引之后，select可与match()和against()一起使用以实际执行搜索。
2、一般在创建表时启用全文本搜索。
[sql]
create table productnotes
(
note_id int not nullauto_increment,
note_text text null,
primary key(note_id),
fulltext(note_text)
)engine=myisam;
在定义之后，mysql自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
3、不要在导入数据时使用fulltext。
4、进行全文本搜索
match()指定被搜索的列，against()指定要使用的搜索表达式。
[sql]
mysql> select * from productnotes
-> wherematch(note_text) against('designed');
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| 6 | limslink isdesigned to interface output from chromatography data sy
stems (cdss) to lims. |
| 5 | this line ofproprietary reagents, containers, and automation tools
is designed for genomics and drug discovery research. |
+---------+---------------------------------------------------------------------
------------------------------------------------------+
2 rows in set (0.03 sec)
5、传递给match()的值必须与fulltext()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
6、除非使用binary方式，否则全文本搜索不区分大小写。
[sql]
mysql> select * from productnotes
-> where binarymatch(note_text) against('line');
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| 5 | this line ofproprietary reagents, containers, and automation tools
is designed for genomics and drug discovery research. |
+---------+---------------------------------------------------------------------
------------------------------------------------------+
1 row in set (0.05 sec)
7、全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。
等级由mysql根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。文本中词先前的行的等级值比词靠后的行的等级值高。
[sql]
mysql> select note_id, match(note_text) against('this line')as rank,note_text
-> fromproductnotes
-> wherematch(note_text) against('this line');
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| note_id | rank | note_text
|
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| 5 |0.81339610830754 | this line of proprietary reagents,. containers, a
nd automation tools is designed. for genomics and drugdiscovery .research. |
| 7 |0.76517958501676 | specificities include both alpha–beta and beta–
beta. this line from chromatography .data systems (cdss) and tolims. |
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
8、查询扩展
在使用查询扩展时，mysql对数据和索引进行两遍扫描来完成搜索。
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，mysql检查这些匹配行并选择所有有用的词；
再次，mysql再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
表中的行越多，使用查询扩展返回的结果越好。
查询扩展功能在mysql4.1.1中引入。
[sql]
mysql> select note_id, match(note_text) against('this line')as rank,note_text
-> fromproductnotes
-> where match(note_text)against('this line' with query expansion);
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| note_id | rank | note_text
|
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| 5 | 0.81339610830754| this line of proprietary reagents,. containers, a
nd automation tools is designed. for genomics and drugdiscovery .research. |
| 7 |0.76517958501676 | specificities include both alpha–beta and beta–
beta. this line from chromatography .data systems (cdss) and tolims. |
| 3 | 0 | human s-100. monoclonal.and polyclonal specifici
ties include both alpha–beta and beta–beta isoforms. |
| 6 | 0 | limslink is .designed to interfaceoutput. from c
hromatography .data systems (cdss) and to lims. |
| 1 | 0 | peptool allows users tostore, manage. analyze, a
nd visualize protein data. |
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
9、布尔文本搜索（boolean mode）
以布尔方式，可以提供关于如下内容的细节：
要匹配的词；
要排斥的词；
排列提示；（指定某些词比其他词更重要）
表达式分组；
另外一些内容。
[sql]
mysql> select note_id,note_text
-> fromproductnotes
-> wherematch(note_text) against('line' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | this line ofproprietary reagents,. containers, and automation tools
is designed. for genomicsand drug discovery .research. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. this line fro
m chromatography .data systems (cdss) and to lims. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)
即使没有fulltext索引也可以使用布尔文本搜索。但是非常缓慢。
mysql> select note_id,note_text/*匹配line且不包含systems*/
-> fromproductnotes
-> wherematch(note_text) against('line -systems*' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | this line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select note_id,note_text/*匹配line且匹配systems*/
-> fromproductnotes
-> wherematch(note_text) against('+line +systems' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------+
| 7 | specificitiesinclude both alpha–beta and beta–beta. this line fro
m chromatography .data systems (cdss) and to lims. |
+---------+---------------------------------------------------------------------
---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select note_id,note_text/*匹配line或匹配systems*/
-> fromproductnotes
-> wherematch(note_text) against('line systems' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | this line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
| 6 | limslink is.designed to interface output. from chromatography .data
systems (cdss) and tolims. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. this line fro
m chromatography .data systems (cdss) and to lims. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select note_id,note_text/*匹配短语*/
-> fromproductnotes
-> wherematch(note_text) against('this line' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | this line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. this line fro
m chromatography .data systems (cdss) and to lims. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)
10、使用说明
l 在索引全文本数据时，短词被忽略且从索引中排除。短词的定义为那些具有3个或脸上以下字符的词（如果需要，这个数目可以更新）。
l mysql带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。
l mysql规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于in boolean mode。
l 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
l 忽略词中的单引号。如，don’t索引为dont。
l 不具有词分隔符的语言不能恰当地返回全文本搜索结果。
bitscn.com