MySQL基础教程10 —— 函数之全文搜索功能

语法：

MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

MySQL支持全文索引和搜索功能。MySQL中的全文索引类型FULLTEXT的索引。 FULLTEXT索引仅可用于MyISAM表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引， 其速度比把资料输入现有FULLTEXT索引的速度更为快。

全文搜索同MATCH()函数一起执行。

mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> );Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO articles (title,body) VALUES -> (’MySQL Tutorial’,’DBMS stands for DataBase ...’), -> (’How To Use MySQL Well’,’After you went through a ...’), -> (’Optimizing MySQL’,’In this tutorial we will show ...’), -> (’1001 MySQL Tricks’,’1. Never run mysqld as root. 2. ...’), -> (’MySQL vs. YourSQL’,’In the following database comparison ...’), -> (’MySQL Security’,’When configured properly, MySQL ...’);Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST (’database’);+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)

MATCH()函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数而被给定。对于表中的每一行, MATCH()返回一个相关值，即,搜索字符串和MATCH()表中指定列中该行文字之间的一个相似性度量。

在默认状态下,搜索的执行方式为不区分大小写方式。然而,你可以通过对编入索引的列使用二进制排序方式执行区分大小写的全文搜索。例如，可以向一个使用latin1字符集的列给定latin1_bin的排序方式，对于全文搜索区分大小写。

如上述所举例子，当MATCH()被用在一个WHERE语句中时，相关值是非负浮点数。零相关的意思是没有相似性。相关性的计算是基于该行中单词的数目,该行中独特子的数目，资料库中单词的总数，以及包含特殊词的文件(行)数目。

对于自然语言全文搜索，要求MATCH()函数中命名的列和你的表中一些FULLTEXT索引中包含的列相同。对于前述问讯， 注意，MATCH()函数(题目及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分别搜索题目和全文，应该对每个列创建FULLTEXT索引。

或者也可以运行布尔搜索或使用查询扩展进行搜索。

上面的例子基本上展示了怎样使用返回行的相关性顺序渐弱的MATCH()函数。而下面的例子则展示了怎样明确地检索相关值。返回行的顺序是不定的，原因是 SELECT语句不包含WHERE或ORDER BY子句：

mysql> SELECT id, MATCH (title,body) AGAINST (’Tutorial’) -> FROM articles;+----+-----------------------------------------+| id | MATCH (title,body) AGAINST (’Tutorial’) |+----+-----------------------------------------+| 1 | 0.65545833110809 || 2 | 0 || 3 | 0.66266459226608 || 4 | 0 || 5 | 0 || 6 | 0 |+----+-----------------------------------------+6 rows in set (0.00 sec)

下面的例子则更加复杂。询问返回相关值，同时对行按照相关性渐弱的顺序进行排序。为实现这个结果，你应该两次指定MATCH()： 一次在SELECT列表中而另一次在WHERE子句中。这不会引起额外的内务操作，原因是MySQL优化程序注意到两个MATCH()调用是相同的，从而只会激活一次全文搜索代码。

mysql> SELECT id, body, MATCH (title,body) AGAINST -> (’Security implications of running MySQL as root’) AS score -> FROM articles WHERE MATCH (title,body) AGAINST -> (’Security implications of running MySQL as root’);+----+-------------------------------------+-----------------+| id | body | score |+----+-------------------------------------+-----------------+| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 || 6 | When configured properly, MySQL ... | 1.3114095926285 |+----+-------------------------------------+-----------------+2 rows in set (0.00 sec)

表中有2行(0.00秒)

MySQL FULLTEXT执行将任何单字字符原形(字母、数字和下划线部分)的序列视为一个单词。这个序列或许也包含单引号(’),但在一行中不会超过一个。 这意味着aaa’bbb会被视为一个单词，而aaa’’bbb则被视为2个单词。位于单词之前或其后的单引号会被FULLTEXT分析程序去掉；’aaa’bbb’会变成 aaa’bbb。

FULLTEXT分析程序会通过寻找某些分隔符来确定单词的起始位置和结束位置，例如’ ’ (间隔符号)、, (逗号)以及. (句号)。假如单词没有被分隔符分开，(例如在中文里),则FULLTEXT分析程序不能确定一个词的起始位置和结束位置。为了能够在这样的语言中向FULLTEXT索引添加单词或其它编入索引的术语，你必须对它们进行预处理，使其被一些诸如'之类的任意分隔符分隔开。

一些词在全文搜索中会被忽略：

任何过于短的词都会被忽略。 全文搜索所能找到的词的默认最小长度为4个字符。停止字中的词会被忽略。禁用词就是一个像“the” 或“some” 这样过于平常而被认为是不具语义的词。存在一个内置的停止字， 但它可以通过用户自定义列表被改写。

词库和询问中每一个正确的单词根据其在词库和询问中的重要性而被衡量。通过这种方式，一个出现在许多文件中的单词具有较低的重要性(而且甚至很多单词的重要性为零),原因是在这个特别词库中其语义价值较低。反之，假如这个单词比较少见，那么它会得到一个较高的重要性。然后单词的重要性被组合，从而用来计算该行的相关性。

这项技术最适合同大型词库一起使用(事实上,此时它经过仔细的调整)。对于很小的表，单词分布并不能充分反映它们的语义价值， 而这个模式有时可能会产生奇特的结果。例如,虽然单词 “MySQL” 出现在文章表中的每一行，但对这个词的搜索可能得不到任何结果：

mysql>SELECT * FROM articles

->WHERE MATCH (title,body) AGAINST (’MySQL’);

找不到搜索的词(0.00秒)

这个搜索的结果为空，原因是单词 “MySQL” 出现在至少全文的50%的行中。 因此,它被列入停止字。对于大型数据集，使用这个操作最合适不过了----一个自然语言问询不会从一个1GB的表每隔一行返回一次。对于小型数据集，它的用处可能比较小。

一个符合表中所有行的内容的一半的单词查找相关文档的可能性较小。事实上,它更容易找到很多不相关的内容。我们都知道，当我们在因特网上试图使用搜索引擎寻找资料的时候，这种情况发生的频率颇高。可以推论，包含该单词的行因其所在特别数据集而被赋予较低的语义价值。 一个给定的词有可能在一个数据集中拥有超过其50%的域值，而在另一个数据集却不然。

当你第一次尝试使用全文搜索以了解其工作过程时，这个50%的域值提供重要的蕴涵操作：若你创建了一个表，并且只将文章的1、2行插入其中， 而文中的每个单词在所有行中出现的机率至少为 50%。那么结果是你什么也不会搜索到。一定要插入至少3行，并且多多益善。需要绕过该50%限制的用户可使用布尔搜索代码。

1.布尔全文搜索

利用IN BOOLEAN MODE修改程序，MySQL也可以执行布尔全文搜索：

mysql> SELECT * FROM articles WHERE MATCH (title,body) -> AGAINST (’+MySQL -YourSQL’ IN BOOLEAN MODE);+----+-----------------------+-------------------------------------+| id | title | body |+----+-----------------------+-------------------------------------+| 1 | MySQL Tutorial | DBMS stands for DataBase ... || 2 | How To Use MySQL Well | After you went through a ... || 3 | Optimizing MySQL | In this tutorial we will show ... || 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... || 6 | MySQL Security | When configured properly, MySQL ... |+----+-----------------------+-------------------------------------+

这个问询检索所有包含单词“MySQL”的行，但不检索包含单词“YourSQL”的行。

布尔全文搜索具有以下特点：

它们不使用50%域值。.它们不会按照相关性渐弱的顺序将行进行分类。你可以从上述问询结果中看到这一点：相关性最高的行是一个包含两个“MySQL” 的行，但它被列在最后的位置，而不是开头位置。即使没有FULLTEXT，它们仍然可以工作，尽管这种方式的搜索执行的速度非常之慢。最小单词长度全文参数和最大单词长度全文参数均适用。停止字适用。

布尔全文搜索的性能支持以下操作符：

+

一个前导的加号表示该单词必须出现在返回的每一行的开头位置。

-

一个前导的减号表示该单词一定不能出现在任何返回的行中。

(无操作符)

在默认状态下(当没有指定+或–的情况下)，该单词可有可无，但含有该单词的行等级较高。这和MATCH() ... AGAINST()不使用IN BOOLEAN MODE修改程序时的运作很类似。

> <

这两个操作符用来改变一个单词对赋予某一行的相关值的影响。>操作符增强其影响，而<操作符则减弱其影响。请参见下面的例子。

( )

括号用来将单词分成子表达式。括入括号的部分可以被嵌套。

~

一个前导的代字号用作否定符， 用来否定单词对该行相关性的影响。 这对于标记“noise（无用信息）”的单词很有用。包含这类单词的行较其它行等级低，但因其可能会和-号同时使用，因而不会在任何时候都派出所有无用信息行。

*

星号用作截断符。于其它符号不同的是，它应当被追加到要截断的词上。

'

一个被括入双引号的短语(‘'’)只和字面上包含该短语输入格式的行进行匹配。全文引擎将短语拆分成单词，在FULLTEXT索引中搜索该单词。非单词字符不需要严密的匹配：短语搜索只要求符合搜索短语包含的单词且单词的排列顺序相同的内容。例如，'test phrase'符合'test, phrase'。

若索引中不存在该短语包含的单词，则结果为空。例如，若所有单词都是禁用词，或是长度都小于编入索引单词的最小长度，则结果为空。

以下例子展示了一些使用布尔全文符号的搜索字符串：

’apple banana’

寻找包含至少两个单词中的一个的行。

’+apple +juice’

寻找两个单词都包含的行。

’+apple macintosh’

寻找包含单词“apple”的行，若这些行也包含单词“macintosh”， 则列为更高等级。

’+apple -macintosh’

寻找包含单词“apple” 但不包含单词 “macintosh”的行。

’+apple +(>turnover <strudel)’

寻找包含单词“apple”和“turnover” 的行，或包含“apple” 和“strudel”的行(无先后顺序),然而包含 “apple turnover”的行较包含“apple strudel”的行排列等级更为高。

’apple*’

寻找包含“apple”、“apples”、“applesauce”或“applet”的行。

’'some words'’

寻找包含原短语“some words”的行(例如,包含“some words of wisdom” 的行，而非包含“some noise words”的行)。注意包围词组的‘'’ 符号是界定短语的操作符字符。它们不是包围搜索字符串本身的引号。

2.全文搜索带查询扩展

全文搜索支持查询扩展功能(特别是其多变的“盲查询扩展功能”)。若搜索短语的长度过短,那么用户则需要依靠全文搜索引擎通常缺乏的内隐知识进行查询。这时，查询扩展功能通常很有用。例如,某位搜索 “database” 一词的用户，可能认为“MySQL”、“Oracle”、“DB2”and“RDBMS”均为符合 “databases”的项，因此都应被返回。这既为内隐知识。

在下列搜索短语后添加WITH QUERY EXPANSION，激活盲查询扩展功能(即通常所说的自动相关性反馈)。它将执行两次搜索，其中第二次搜索的搜索短语是同第一次搜索时找到的少数顶层文件连接的原始搜索短语。这样,假如这些文件中的一个 含有单词 “databases” 以及单词 “MySQL”,则第二次搜索会寻找含有单词“MySQL” 的文件，即使这些文件不包含单词 “database”。下面的例子显示了这个不同之处：

mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST (’database’);+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)mysql> SELECT * FROM articles -> WHERE MATCH (title,body) -> AGAINST (’database’ WITH QUERY EXPANSION);+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 1 | MySQL Tutorial | DBMS stands for DataBase ... || 5 | MySQL vs. YourSQL | In the following database comparison ... || 3 | Optimizing MySQL | In this tutorial we will show ... |+----+-------------------+------------------------------------------+3 rows in set (0.00 sec)

另一个例子是Georges Simenon搜索关于Maigret的书籍,这个用户不确定“Maigret”一词的拼法。若不使用查询扩展而搜索“Megre and the reluctant witnesses” 得到的结果只能是的“Maigret and the Reluctant Witnesses” 。 而带有查询扩展的搜索会在第二遍得到带有“Maigret”一词的所有书名。

注释：盲查询扩展功能很容易返回非相关文件而增加无用信息，因此只有在查询一个长度很短的短语时才有必要使用这项功能。

3.全文停止字

以下表列出了默认的全文停止字：

a’sableaboutaboveaccordingaccordinglyacrossactuallyafterafterwardsagainagainstain’tallallowallowsalmostalonealongalreadyalsoalthoughalwaysamamongamongstanandanotheranyanybodyanyhowanyoneanythinganywayanywaysanywhereapartappearappreciateappropriatearearen’taroundasasideaskaskingassociatedatavailableawayawfullybebecamebecausebecomebecomesbecomingbeenbeforebeforehandbehindbeingbelievebelowbesidebesidesbestbetterbetweenbeyondbothbriefbutbyc’monc’scamecancan’tcannotcantcausecausescertaincertainlychangesclearlycocomcomecomesconcerningconsequentlyconsiderconsideringcontaincontainingcontainscorrespondingcouldcouldn’tcoursecurrentlydefinitelydescribeddespitediddidn’tdifferentdodoesdoesn’tdoingdon’tdonedowndownwardsduringeacheduegeighteitherelseelsewhereenoughentirelyespeciallyetetcevenevereveryeverybodyeveryoneeverythingeverywhereexexactlyexampleexceptfarfewfifthfirstfivefollowedfollowingfollowsforformerformerlyforthfourfromfurtherfurthermoregetgetsgettinggivengivesgogoesgoinggonegotgottengreetingshadhadn’thappenshardlyhashasn’thavehaven’thavinghehe’shellohelphenceherherehere’shereafterherebyhereinhereuponhersherselfhihimhimselfhishitherhopefullyhowhowbeithoweveri’di’lli’mi’veieifignoredimmediateininasmuchincindeedindicateindicatedindicatesinnerinsofarinsteadintoinwardisisn’titit’dit’llit’sitsitselfjustkeepkeepskeptknowknowsknownlastlatelylaterlatterlatterlyleastlesslestletlet’slikelikedlikelylittlelooklookinglooksltdmainlymanymaymaybememeanmeanwhilemerelymightmoremoreovermostmostlymuchmustmymyselfnamenamelyndnearnearlynecessaryneedneedsneitherneverneverthelessnewnextninenonobodynonnonenoonenornormallynotnothingnovelnownowhereobviouslyofoffoftenohokokayoldononceoneonesonlyontoorotherothersotherwiseoughtouroursourselvesoutoutsideoveroverallownparticularparticularlyperperhapsplacedpleasepluspossiblepresumablyprobablyprovidesquequiteqvratherrdrereallyreasonablyregardingregardlessregardsrelativelyrespectivelyrightsaidsamesawsaysayingsayssecondsecondlyseeseeingseemseemedseemingseemsseenselfselvessensiblesentseriousseriouslysevenseveralshallsheshouldshouldn’tsincesixsosomesomebodysomehowsomeonesomethingsometimesometimessomewhatsomewheresoonsorryspecifiedspecifyspecifyingstillsubsuchsupsuret’staketakentelltendsththanthankthanksthanxthatthat’sthatsthetheirtheirsthemthemselvesthenthencetherethere’sthereaftertherebythereforethereintheresthereuponthesetheythey’dthey’llthey’rethey’vethinkthirdthisthoroughthoroughlythosethoughthreethroughthroughoutthruthustotogethertootooktowardtowardstriedtriestrulytrytryingtwicetwoununderunfortunatelyunlessunlikelyuntiluntoupuponususeusedusefulusesusingusuallyvaluevariousveryviavizvswantwantswaswasn’twaywewe’dwe’llwe’rewe’vewelcomewellwentwereweren’twhatwhat’swhateverwhenwhencewheneverwherewhere’swhereafterwhereaswherebywhereinwhereuponwhereverwhetherwhichwhilewhitherwhowho’swhoeverwholewhomwhosewhywillwillingwishwithwithinwithoutwon’twonderwouldwouldwouldn’tyesyetyouyou’dyou’llyou’reyou’veyouryoursyourselfyourselveszero4.全文限定条件全文搜索只适用于MyISAM表。全文搜索可以同大多数多字节字符集一起使用。Unicode属于例外情况;可使用utf8字符集,而非ucs2字符集。诸如汉语和日语这样的表意语言没有自定界符。因此，FULLTEXT分析程序不能确定在这些或其它的这类语言中词的起始和结束的位置。若支持在一个单独表中使用多字符集，则所有FULLTEXT索引中的列 必须使用同样的字符集和库。MATCH()列列表必须同该表中一些FULLTEXT索引定义中的列列表完全符合,除非MATCH()在IN BOOLEAN MODE。对AGAINST()的参数必须是一个常数字符串。5.微调MySQL全文搜索

MySQL的全文搜索容量几乎不具有用户调节参数。假如你拥有一个MySQL源分布，你就能对全文搜索性能行使更多控制，原因是一些变化需要源代码修改。

注意，为了更加有效，需要对全文搜索谨慎调节。实际上，在大多数情况下修改默认性能只能降低其性能。除非你知道自己在做什么，否则不要改变MySQL源。

下述的大多数全文变量必须在服务器启动时被设置。为了改变它们，还要重新启动服务器；在服务器正在运行期间，他们不会被改变。

一些变量的改变需要你重建表中的FULLTEXT索引。本章结尾部分给出了其有关操作说明。

ft_min_word_len and ft_max_word_len系统自变量规定了被编入索引单词的最小长度和最大长度。默认的最小值为四个字符；默认的最大值取决于使用的MySQL版本。假如你改变任意一个值，那么你必须重建你的FULLTEXT索引。 例如，若你希望一个3字符的单词变为可查找项，则可以通过将以下行移动到一个供选择文件里，从而设置ft_min_word_len变量：

· [mysqld]

· ft_min_word_len=3

然后重新启动服务器，重建你的FULLTEXT索引。同时还要特别注意该表后面的说明中的关于myisamchk的注释。

若要覆盖默认停止字，则可设置ft_stopword_file系统变量。变量值应为包含停止字的文件路径名,或是用来截止禁用词过滤的空字符串。在改变了这个变量的值或禁用词文件的内容后,重建你的FULLTEXT索引。

停止字是自由形态的，换言之，你可使用任何诸如newline、space或comma这样的非字母数字字符来分隔禁用词。 下划线字符(_)和被视为单词的一部分的单引号(’)例外。停止字字符集为服务器默认字符集。

自然语言查询的50%阈值由所选择的特别权衡方案所决定。若要阻止它，myisam/ftdefs.h中寻找以下行：

· #define GWS_IN_USE GWS_PROB

将该行改为：

#define GWS_IN_USE GWS_FREQ

然后重新编译MySQL。此时不需要重建索引。注释：这样做你会严重的By降低MySQL为MATCH()函数提供合适的相关值得能力。假如你争得需要搜索这样的普通词，而使用IN BOOLEAN MODE代替的效果更好，因为它不遵循50%阈值。

要改变用于布尔全文搜索的操作符，设置ft_boolean_syntax系统变量。 这个变量也可以在服务器运行时被改变，但你必须有SUPER特权才能这么做。在这种情况下不需要重建索引。

假如你改变了影响索引的全文变量(ft_min_word_len、ft_max_word_len或ft_stopword_file),或假如你改变了禁用词文件本身，则你必须在改变和重新启动服务器后重建你的FULLTEXT索引。这时，要重建索引， 只需进行一个QUICK修理操作：

mysql>REPAIR TABLEtbl_nameQUICK;

注意，假如你使用myisamchk来执行一项修改表索引的操作(诸如修理或分析),则使用最小单词长度和最大单词长度以及停止字的默认全文参数值重建FULLTEXT索引，除非你已另外指定。这会导致问询失败。

发生这个问题的原因是只有服务器认识这些参数。它们的存储位置不在 MyISAM索引文件中。若你已经修改了最小单词长度或最大单词长度或服务器中的停止字，为避免这个问题，为你对mysqld所使用的myisamchk指定同样的ft_min_word_len、ft_max_word_len和ft_stopword_file值。例如，假如你已经将最小单词长度设置为3,则你可以这样修改一个带有myisamchk的表：

shell>myisamchk --recover --ft_min_word_len=3tbl_name.MYI

为保证myisamchk及服务器对全文参数使用相同的值， 可将每一项都放在供选文件中的[mysqld]和[myisamchk]部分：

[mysqld]

ft_min_word_len=3

[myisamchk]

ft_min_word_len=3

使用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE或ALTER TABLE来代替使用myisamchk。这些语句通过服务器来执行，服务器知道使用哪个全文参数值更加合适。