当前位置:Gxlcms > mysql > MYSQL入门学习之三:全文本搜索_MySQL

MYSQL入门学习之三:全文本搜索_MySQL

时间:2021-07-01 10:21:17 帮助过:28人阅读

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

人气教程排行