MySQL 基礎チュートリアル 10 - 関数全文検索関数

構文:

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

MySQL はフルテキスト インデックス作成と検索機能をサポートしています。 MySQL のフルテキスト インデックス タイプ FULLTEXT インデックス。 FULLTEXT インデックスは MyISAM テーブルでのみ使用でき、CREATE TABLE ステートメントの一部として CHAR、VARCHAR、または TEXT カラムから作成することも、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() 関数は、データベース内で文字列の自然言語検索を実行します。データベースは、FULLTEXT に含まれる 1 つまたは 2 つの列のセットです。検索文字列は AGAINST() の引数として与えられます。テーブル内の行ごとに、MATCH() は相関値、つまり、検索文字列と MATCH() テーブル内の指定された列のその行のテキストとの間の類似性の尺度を返します。

デフォルトでは、検索は大文字と小文字を区別せずに実行されます。ただし、インデックス付き列に対してバイナリ ソートを使用すると、大文字と小文字を区別した全文検索を実行できます。たとえば、latin1 文字セットを使用する列に latin1_bin ソート方法を指定すると、全文検索で大文字と小文字が区別されるようになります。

上記の例のように、WHERE ステートメントで MATCH() が使用される場合、関連する値は非負の浮動小数点数です。相関関係がゼロとは、類似性がないことを意味します。関連性の計算は、行内の単語数、行内の一意の数、データベース内の単語の総数、および特定の単語を含むファイル (行) の数に基づいて行われます。

自然言語全文検索の場合、MATCH() 関数で指定された列が、テーブル内の一部の FULLTEXT インデックスに含まれる列と同じである必要があります。上記のクエリでは、MATCH() 関数で指定された列 (タイトルと全文) が、article テーブルの 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() を 2 回指定する必要があります。1 回目は SELECT リストで、もう 1 回目は WHERE 句でです。これにより、MySQL オプティマイザは 2 つの MATCH() 呼び出しが同一であることを認識し、全文検索コードを 1 回だけアクティブ化するため、追加のハウスキーピングは発生しません。

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 の実行は、単一単語の文字プロトタイプ (文字、数字、アンダースコア部分) のシーケンスを単語として扱います。このシーケンスには一重引用符 (') を含めることもできますが、1 行に 1 つしか含めることはできません。 これは、aaa'bbb は 1 つの単語として扱われ、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

寻找包含单词“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. 全文停止字

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

4. 全文修飾

• 全文検索は MyISAM テーブルにのみ適用されます。

• 全文検索は、ほとんどのマルチバイト文字セットで機能します。 Unicode は例外で、ucs2 文字セットの代わりに utf8 文字セットを使用できます。

• 中国語や日本語などの表意文字言語にはカスタム区切り文字がありません。したがって、FULLTEXT パーサー は、これらの言語または他の言語の単語の始まりと終わりを判断できません。

• 単一のテーブルで複数の文字セットがサポートされている場合、FULLTEXT インデックス内のすべての列で同じ文字セットとライブラリを使用する必要があります。

• MATCH() 列リストは、MATCH() が IN BOOLEAN MODE でない限り、テーブル内の一部の FULLTEXT インデックス定義の列リストと正確に一致する必要があります。

• AGAINST() の引数は定数文字列でなければなりません。

5. MySQL 全文検索の微調整

MySQL の全文検索機能には、ユーザーが調整できるパラメーターはほとんどありません。 MySQL ソース配布を使用している場合は、一部の変更にはソース コードの修正が必要となるため、全文検索のパフォーマンスをより詳細に制御できます。

より効果的にするには、全文検索を慎重に調整する必要があることに注意してください。実際、デフォルトのパフォーマンスを変更しても、ほとんどの場合、パフォーマンスが低下するだけです。 何をしようとしているのか理解していない限り、MySQL ソースを変更しないでください。

以下で説明するフルテキスト変数のほとんどは、サーバーの起動時に設定する必要があります。これらを変更するには、サーバーを再起動する必要があります。サーバーの実行中は変更されません。

一部の変数の変更では、テーブル内の FULLTEXT インデックスを再構築する必要があります。関連する操作手順は、この章の最後に記載されています。

• ft_min_word_len および ft_max_word_len システム引数は、インデックス付き単語の最小および最大長を指定します。デフォルトの最小値は 4 文字で、デフォルトの最大値は使用する MySQL のバージョンによって異なります。値を変更した場合は、FULLTEXT インデックスを再構築する必要があります。 たとえば、3 文字の単語を検索可能にしたい場合は、次の行を選択ファイルに移動して ft_min_word_len 変数を設定できます:

· [mysqld]

· ft_min_word_len=3

その後、サーバーにアクセスし、FULLTEXT インデックスを再構築します。表の後ろの説明にある myisamchk に関する注記にも特に注意してください。

• デフォルトのストップワードをオーバーライドするには、ft_stopword_file システム変数を設定します。変数値は、ストップ ワードを含むファイル パス名、またはストップ ワード フィルタリングを停止するために使用される空の文字列である必要があります。この変数の値またはストップワード ファイルの内容を変更した後、FULLTEXT インデックスを再構築します。

ストップ ワードは自由形式です。つまり、改行、スペース、カンマなどの英数字以外の文字を使用してストップ ワードを区切ることができます。 例外として、単語の一部とみなされるアンダースコア文字 (_) と一重引用符 (') が含まれます。ストップワード文字セットは、サーバーのデフォルト文字セットです。

• 自然言語クエリの 50% のしきい値は、選択した特定のトレードオフによって決まります。これを防ぐには、myisam/ftdefs.h で次の行を探します:

· #define GWS_IN_USE GWS_PROB

その行を次のように変更します:

#define GWS_IN_USE GWS_FREQ

その後、MySQL を再コンパイルします。現時点ではインデックスを再構築する必要はありません。注: これを行うと、MATCH() 関数に適切な相関値を提供する MySQL の機能が 大幅に 低下します。このような一般的な単語を検索する必要がある場合は、50% のしきい値に従わないため、代わりに IN BOOLEAN MODE を使用することをお勧めします。

• ブール全文検索に使用される演算子を変更するには、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 。这些语句通过服务器来执行，服务器知道使用哪个全文参数值更加合适。

 以上就是MySQL基础教程10 —— 函数之全文搜索功能的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！