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: 0

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)

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's	able	about	above	according
accordingly	across	actually	after	afterwards
again	against	ain't	all	allow
allows	almost	alone	along	already
also	although	always	am	among
amongst	an	and	another	any
anybody	anyhow	anyone	anything	anyway
anyways	anywhere	apart	appear	appreciate
appropriate	are	aren't	around	as
aside	ask	asking	associated	at
available	away	awfully	be	became
because	become	becomes	becoming	been
before	beforehand	behind	being	believe
below	beside	besides	best	better
between	beyond	both	brief	but
by	c'mon	c's	came	can
can't	cannot	cant	cause	causes
certain	certainly	changes	clearly	co
com	come	comes	concerning	consequently
consider	considering	contain	containing	contains
corresponding	could	couldn't	course	currently
definitely	described	despite	did	didn't
different	do	does	doesn't	doing
don't	done	down	downwards	during
each	edu	eg	eight	either
else	elsewhere	enough	entirely	especially
et	etc	even	ever	every
everybody	everyone	everything	everywhere	ex
exactly	example	except	far	few
fifth	first	five	followed	following
follows	for	fORMer	formerly	forth
four	from	further	furthermore	get
gets	getting	given	gives	Go
goes	going	gone	got	gotten
greetings	had	hadn't	happens	hardly
has	hasn't	have	haven't	having
he	he's	hello	help	hence
her	here	here's	hereafter	hereby
herein	hereupon	hers	herself	hi
him	himself	his	hither	hopefully
how	howbeit	however	i'd	i'll
i'm	i've	ie	if	ignored
immediate	in	inasmuch	inc	indeed
indicate	indicated	indicates	inner	insofar
instead	into	inward	is	isn't
it	it'd	it'll	it's	its
itself	just	keep	keeps	kept
know	knows	known	last	lately
later	latter	latterly	least	less
lest	let	let's	like	liked
likely	little	look	looking	looks
ltd	mainly	many	may	maybe
me	mean	meanwhile	merely	might
more	moreover	most	mostly	much
must	my	myself	name	namely
nd	near	nearly	necessary	need
needs	neither	never	nevertheless	new
next	nine	no	nobody	non
none	noone	nor	normally	not
nothing	novel	now	nowhere	obviously
of	off	often	oh	ok
okay	old	on	once	one
ones	only	onto	or	other
others	otherwise	ought	our	ours
ourselves	out	outside	over	overall
own	particular	particularly	per	perhaps
placed	please	plus	possible	presumably
probably	provides	que	quite	qv
rather	rd	re	really	reasonably
regarding	regardless	regards	relatively	respectively
right	said	same	saw	say
saying	says	second	secondly	see
seeing	seem	seemed	seeming	seems
seen	self	selves	sensible	sent
serious	seriously	seven	several	shall
she	should	shouldn't	since	six
so	some	somebody	somehow	someone
something	sometime	sometimes	somewhat	somewhere
soon	sorry	specified	specify	specifying
still	sub	such	sup	sure
t's	take	taken	tell	tends
th	than	thank	thanks	thanx
that	that's	thats	the	their
theirs	them	themselves	then	thence
there	there's	thereafter	thereby	therefore
therein	theres	thereupon	these	they
they'd	they'll	they're	they've	think
third	this	thorough	thoroughly	those
though	three	through	throughout	thru
thus	to	together	too	took
toward	towards	tried	tries	truly
try	trying	twice	two	un
under	unfortunately	unless	unlikely	until
unto	up	upon	us	use
used	useful	uses	using	usually
value	various	very	via	viz
vs	want	wants	was	wasn't
way	we	we'd	we'll	we're
we've	welcome	well	went	were
weren't	what	what's	whatever	when
whence	whenever	where	where's	whereafter
whereas	whereby	wherein	whereupon	wherever
whether	which	while	whither	who
who's	whoever	whole	whom	whose
why	will	willing	wish	with
within	without	won't	wonder	would
would	wouldn't	yes	yet	you
you'd	you'll	you're	you've	your
yours	yourself	yourselves	zero

4. 全文限定条件

• 全文搜索只适用于 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 TABLE tbl_name QUICK;

注意，假如你使用  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=3 tbl_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 。这些语句通过服务器来执行，服务器知道使用哪个全文参数值更加合适。