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

• > fb0fed43b83fb0652d5f29eaa4ee0614 操作符增强其影响，而 c338afddb723986043c602cf4e852541turnover 74a44946b82439a0487cdae172590e90 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中文网（www.php.cn）！