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修改程序时的运作很类似。

> 021c18ccb59b6067bed130a15fabd8d0 操作符增强其影响，而 3a11c09e5eab1dd0cd1a25f5714e192bturnover df6f71c91d55ede56c0d515ab30eb46e 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 を使用してください。これらのステートメントはサーバーによって実行され、どのフルテキスト パラメーター値がより適切であるかを認識します。