mysql関数全文検索関数

構文:

 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 文字です。

ストップ ワード内の単語は無視されます。または「some」は意味的に非意味であると考えるにはあまりにも一般的な単語です。組み込みのストップワードがありますが、ユーザー定義のリストを通じてオーバーライドできます。

語彙およびクエリ内のすべての正しい単語は、その In に基づいています。このように、多くの文書に出現する単語は、その特定の単語の重要性により、その単語の重要性が低くなります (また、多くの単語でさえ重要性がゼロになります)。 、その後、単語の重要性が結合され、行の関連性を計算するために使用されます

非常に小さなテーブルの場合は、大きな語彙で使用するのが最適です (実際、慎重に調整されている場合)。単語の意味値が完全に反映されていないため、このモードでは奇妙な結果が生成されることがあります。たとえば、「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. 全文停止字

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

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    

それぞれ 教育 例 八 どちらか

他の 他の場所 十分 完全に 特に

et etc さえ これまでも すべて

みんなみんな すべて どこでも ex

まさに 例 除く 遠い いくつか

5 番目 最初 5 続いた 次の

は以前 以前 これから

4 から さらに さらに get

gets get given gives go

goes going gone gotゲットしました

ご挨拶 しましたあまりにも起こらなかったのは、彼が持っていないことはありませんでした。 「d i'll

i'm i've ie if 無視された場合

即時 in asmuch inc indeed

indicate indicated indicatesその限りでは

その代わりに 中へ 内向き ではない

それ それだろう それは

自体 ちょうど 維持する 維持する 維持する

知っている 知っている 最後 最近

後で 後ほど 最も少ない

les t しましょう しましょう いいね いいね

らしい 少し 見る 探している 見える

ltd 主に たくさん かもしれない かもしれない

私 意味一方 単に かもしれない

もっと さらに 最も ほとんど とても

しなければならない 私 自分自身 名前 つまり

次 近く ほぼ 必要な

が必要どちらでも 決してない それにもかかわらず 新しい

次 9 いいえ 誰も ない

なし 誰も 通常 ない

何も 小説 今 いいえここで 明らかに

の オフ 頻繁に ああ 大丈夫

大丈夫 古い オン 1 回 1 つ

もの のみ または その他

他の人 そうでなければ、私たちの私たちのもの

自分たち 外 外側 全体

自身の 特定の 特に あたり おそらく

配置 お願いします プラス おそらく

おそらく提供 que かなり qv

むしろ rd re 本当に 合理的

に関して 関係なく よろしく 比較的 それぞれ

正しい 言った 同じ 見た 言う

言う言う 2番目 2番目に 見る

見る らしい 見えた らしい らしい

見た 自分 自分自身 賢明な 送信した

真剣 真剣にいくつかの

彼女 すべき すべきではない 6 歳から

だから 誰か 誰か 何とか 誰か

何か いつか 時々 なんとなく どこかで

すぐに ごめんなさい指定された 特定する 特定する

まだ サブ そのような sup 確かに

t take taken tell tends

th ther Thanks thanx

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

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

MATCH() が IN BOOLEAN MODE でない限り、MATCH() 列リストは、テーブル内の一部の 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 を使用してテーブル インデックスを変更する操作 (修復や分析など) を実行する場合は、 use 別途指定しない限り、単語長の最小値と最大値、およびストップワードのデフォルトのフルテキスト パラメーター値を使用して FULLTEXT インデックスを再構築します。これにより、クエリが失敗します。

この問題は、サーバーのみがこれらのパラメーターを知っているために発生します。それらの保存場所は MyISAM インデックス ファイル内にありません。サーバーの最小または最大の単語長またはストップ ワードを変更した場合、この問題を回避するには、mysqld に使用するのと同じ ft_min_word_len、ft_max_word_len、および ft_stopword_file 値を myisamchk に指定します。たとえば、最小ワード長を 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

myisamchk を使用する代わりに、REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE、または ALTER TABLE を使用してください。これらのステートメントはサーバーによって実行され、どのフルテキスト パラメーター値がより適切であるかを認識します。