Tutoriel de base de MySQL 10 - Fonction Fonction de recherche en texte intégral

Syntaxe :

• MATCH (col1,col2,...) CONTRE (expr [EN MODE BOOLÉEN | AVEC EXPANSION DE REQUÊTE])

MySQL prend en charge les fonctions d'indexation et de recherche en texte intégral. L'index de texte intégral de type FULLTEXT dans MySQL. Les index FULLTEXT ne sont disponibles que sur les tables MyISAM ; ils peuvent être créés à partir de colonnes CHAR, VARCHAR ou TEXT dans le cadre d'une instruction CREATE TABLE, ou ajoutés ultérieurement à l'aide de ALTER TABLE ou CREATE INDEX. Pour les ensembles de données plus volumineux, il est plus rapide de saisir vos données dans une table qui ne possède pas d'index FULLTEXT, puis de créer l'index, plutôt que de saisir les données dans un index FULLTEXT existant.

La recherche en texte intégral s'effectue avec la fonction 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)

La fonction MATCH() effectue une recherche en langage naturel dans la base de données pour une chaîne. Une base de données est un ensemble de 1 ou 2 colonnes contenues dans FULLTEXT. La chaîne de recherche est donnée en argument à AGAINST(). Pour chaque ligne du tableau, MATCH() renvoie une valeur de corrélation, c'est-à-dire une mesure de similarité entre la chaîne recherchée et le texte de cette ligne dans la colonne spécifiée du tableau MATCH().

Par défaut, les recherches sont effectuées sans tenir compte de la casse. Toutefois, vous pouvez effectuer une recherche en texte intégral sensible à la casse en utilisant un tri binaire sur les colonnes indexées. Par exemple, vous pouvez attribuer une méthode de tri latin1_bin à une colonne qui utilise le jeu de caractères latin1, ce qui rend les recherches en texte intégral sensibles à la casse.

Comme dans l'exemple ci-dessus, lorsque MATCH() est utilisé dans une instruction WHERE, la valeur pertinente est un nombre à virgule flottante non négatif. Une corrélation nulle signifie aucune similitude. Le calcul de pertinence est basé sur le nombre de mots dans la ligne, le nombre d'uniques dans la ligne, le nombre total de mots dans la base de données et le nombre de fichiers (lignes) contenant le mot particulier.

Pour la recherche en texte intégral en langage naturel, il est nécessaire que les colonnes nommées dans la fonction MATCH() soient les mêmes que les colonnes contenues dans certains index FULLTEXT de votre table. Pour la requête ci-dessus, veuillez noter que les colonnes nommées dans la fonction MATCH() (titre et texte intégral) sont les mêmes que les colonnes de l'index FULLTEXT de la table article. Si vous souhaitez rechercher le titre et le texte intégral séparément, vous devez créer un index FULLTEXT sur chaque colonne.

Vous pouvez également exécuter une recherche booléenne ou une recherche en utilisant l'expansion des requêtes.

L'exemple ci-dessus montre essentiellement comment utiliser la fonction MATCH() qui renvoie les lignes dans un ordre décroissant de corrélation. L'exemple suivant montre comment récupérer explicitement la valeur pertinente. L'ordre des lignes renvoyées est incertain car l'instruction SELECT ne contient pas de clause WHERE ou 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)

L'exemple suivant est plus compliqué. La requête renvoie les valeurs pertinentes, en triant les lignes par ordre décroissant de pertinence. Pour obtenir ce résultat, vous devez spécifier MATCH() deux fois : une fois dans la liste SELECT et une fois dans la clause WHERE. Cela n'entraîne aucune maintenance supplémentaire car l'optimiseur MySQL remarque que les deux appels MATCH() sont identiques et n'active le code de recherche en texte intégral qu'une seule fois.

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)

Il y a 2 lignes dans le tableau (0,00 seconde)

L'exécution MySQL FULLTEXT traite toute séquence de prototypes de caractères d'un seul mot (lettres, chiffres et parties de soulignement) comme un mot . Cette séquence peut également contenir des guillemets simples ('), mais il n'y en aura pas plus d'un sur une ligne. Cela signifie que aaa'bbb sera traité comme un seul mot, tandis que aaa''bbb sera traité comme 2 mots. Les guillemets simples avant ou après un mot seront supprimés par l'analyseur FULLTEXT ; « aaa'bbb » deviendra aaa'bbb.

L'analyseur FULLTEXT détermine où commence et se termine un mot en recherchant certains délimiteurs, tels que ' ' (espaceur), , (virgule) et (point). Si les mots ne sont pas séparés par des délimiteurs (comme en chinois), l'analyseur FULLTEXT ne peut pas déterminer les positions de début et de fin d'un mot. Afin de pouvoir ajouter des mots ou d'autres termes indexés à un index FULLTEXT dans une telle langue, vous devez les prétraiter afin qu'ils soient séparés par un délimiteur arbitraire tel que ".

Certains mots seront ignorés dans recherche en texte intégral :

• Tout mot trop court sera ignoré. La longueur minimale par défaut d'un mot trouvé par la recherche en texte intégral est

  . >
Les mots dans les mots vides sont ignorés. Un mot comme « le » ou « certains » est trop courant pour être considéré comme sémantique. Il existe un mot vide intégré. Il peut être remplacé par des listes définies par l'utilisateur
• .

Chaque mot correct dans le vocabulaire et la requête est mesuré en fonction de son importance dans le vocabulaire et la requête. De cette manière, un mot qui apparaît dans de nombreux documents a une importance moindre (et même de nombreux mots l'ont). importance nulle) en raison de sa valeur sémantique inférieure dans ce vocabulaire particulier. Au contraire, si le mot est rare, il obtient alors une importance plus élevée. L'importance des mots est alors combinée et utilisée pour calculer la pertinence de la ligne.

这项技术最适合同大型词库一起使用 (事实上, 此时它经过仔细的调整 )。对于很小的表，单词分布并不能充分反映它们的语义价值， 而这个模式有时可能会产生奇特的结果。例如, 虽然单词 “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 4477962978ce90044e3b592835945f46 REPAIR TABLE tbl_name QUICK;

  Notez que si vous utilisez myisamchk pour effectuer une opération qui modifie un index de table (telle qu'une réparation ou une analyse), reconstruit l'index FULLTEXT en utilisant les valeurs par défaut des paramètres de texte intégral pour la longueur minimale et maximale des mots et les mots vides, sauf indication contraire de votre part. Cela entraînera l'échec de la requête.

  发生这个问题的原因是只有服务器认识这些参数。它们的存储位置不在  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）！