Recherche en texte intégral (Full-text) dans MySQL

 La recherche donc sur un texte en entier est effectuée par la fonction MATCH() .  on va donc creer une table articles afin de tester cette fonction sur notre base MySQL :

 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 VALUES
    -> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
    -> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
    -> (NULL,'Optimising MySQL','In this tutorial we will show ...'),
    -> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
    -> (NULL,'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 la recherche d'une chaîne de caractères dans une liste de textes (et dans un groupe d'une ou plusieurs colonnes utilisées pour l'index FULLTEXT ). La chaîne recherchée est donnée en argument à AGAINST() . La recherche est sans distinguer les majuscules des minuscules. Pour chaque ligne de la table, MATCH() retourne une valeur de pertinence, qui est une mesure de la ressemblance entre le chaîne recherchée et le texte de la ligne dans le colonne donnée dans la liste de MATCH() .Quand MATCH() est utilisé comme condition de WHERE (voir l'exemple suivant) les lignes retournées sont automatiquement organisées avec la pertinence la plus élevé en premier. Ces pertinences sont des nombres non négatifs en virgule flottante. Une pertinence de zéro signifie qu'il n'y a pas de similarité. La pertinence est calculé en fonction du nombre de mots dans la ligne, du nombre de mots uniques dans cette ligne, du nombre total de mots dans la liste, et du nombre de documents (lignes) qui contiennent un mot en particulier.

Il est aussi possible d'exécuter une recherche en mode booléen. Ceci est décrit dans une section ultérieure. L'exemple précédent est une illustration élémentaire qui montre comment on utilise la fonctionMATCH() . Les lignes sont retournées par ordre décroissant de pertinence. L'exemple suivant montre comment récupérer la valeur de pertinence explicitement. Comme il n'y a pas de condition WHERE ni de condition ORDER BY les lignes retournées ne sont pas ordonnées.

 mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.64840710366884 |
|  2 |                                       0 |
|  3 |                        0.66266459031789 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec) 

L'exemple suivant est plus complexe. La requête retourne la valeur de pertinence et organise les lignes par ordre décroissant de pertinence. Pour obtenir ce résultat, il faut spécifier MATCH() deux fois. Cela ne cause pas de surcharge car l'optimisateur de MySQL remarquera que les deux appels à MATCH() sont identiques et appellent le code de recherche sur texte plein 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.5055546709332 |
|  6 | When configured properly, MySQL ... |   1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec) 

MySQL utilise un filtre très simple pour séparer le texte en mots. Un "mot" est n'importe quelle chaîne de caractères constituée de lettres, chiffres, ''' et '_' . Tout "mot" présent dans la liste des mots à ignorés ou qui est trop court (3 caractères ou moins) est ignoré.Tous les mots corrects dans la liste et dans la requête sont pondérés en fonction de leur importance dans la liste ou la requête. de cette façon, un mot présent dans de nombreux documents aura un poids faible (et peut être même un poids nul), car il a peut d'importance dans cette requête particuliète. Au contraire, si le mot est rare, il recevra un poids fort. Le poids des mots sont alors rassemblés pour calculer la pertinence de la ligne. Une telle technique fonctionne plus efficacement sur de grands volumes de données (en fait, elle est optimisée pour cela). Avec des toutes petites tables, la distribution des mots ne reflète par correctement leur valeur sémantique et ce modèle peut parfois produire des résultats étranges.

 mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec) 

La recherche du mot MySQL ne donne aucun résultat dans l'exemple précédent, car il est présent dans plus de la moitié des lignes. Ainsi, il est considéré comme un mot à ignorer (un mot avec une valeur sémantique nulle). C'est le comportement le plus optimal -- un langage de requêtes ne doit pas retourner chaque ligne d'une table de 1 GB. Un mot qui est trouvé dans la moitié des enregistrements d'une table n'est pas efficace pour trouver les document appropriés. En fait, il trouvera sûrement beaucoup de documents inappropriés à la recherche. On sait tous que cela arrive souvent lorsqu'on recherche quelque chose sur internet en utilisant un moteur de recherche. C'est en suivant ce raisonnement que ces lignes se sont vues attribuer une valeur sémantique très basse dans ce cas particulier . Avec la version 4.0.1, MySQL peut aussi performer des recherches booléennes en utilisant le modificateur IN BOOLEAN MODE .

 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 Efficiently | After you went through a ...        |
|  3 | Optimising 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 ... |
+----+------------------------------+-------------------------------------+ 

Cette requête a retourné toutes les lignes qui contiennent le mot MySQL (note : la barrière des 50% n'est pas utilisée), mais ne contiennent pas le mot YourSQL . Notez qu'une recherche en mode booléen ne trie pas automatiquement les lignes par ordre de pertinence. Vous pouvez le voir dans le résultat de la requête précédente, où la ligne avec la plus haute pertinence (celui qui contient MySQL deux fois) est listé en dernier. Une recherche booléenne peu aussi fonctionner sans un index FULLTEXT , même si cela risque de ralentir le processus.La recherche booléenne supporte les opérateurs suivants :

+

Un signe plus avant un mot indique que celui-ci doit être présent dans chaque ligne retournée.

-

Un signe moins avant un mot indique que celui-ci ne doit pas être présent dans chaque ligne retournée.

Par défaut (quand ni moins ni plus n'est spécifié) le mot est optionnel,

mais la ligne qui le contient sera mise en valeur. Ce fonctionnement est celui deMATCH() ... AGAINST() dans le paramètre IN BOOLEAN MODE .

< >

Ces deux opérateurs servent à faire varier la contribution d'un mot à la pertinence associée à une ligne. L'opérateur < diminue la contribution alors que > l'augmente. Voir l'exemple ci-dessous.

( )

Les parenthèses sont utilisées pour grouper des mots dans une sous-expression.

~

Un tilde avant un mot opère comme un opérateur de négation, ce qui rend la contribution du mot à la pertinence négative. C'est utile pour se débarrasser des mots inutiles. Une ligne contenant un mot comme cela sera moins mise en avant que les autres, mais ne sera pas exclue, ce qui serait le cas avec l'opérateur - .

*

C'est l'opérateur de tronquage. Contrairement aux autres opérateurs, il doit suivrele mot, non le précéder.

"

La phrase, protégée par des guillemets " , ne trouvera que les lignes contenant cette phrase littéralement, comme elle a été entrée .

et voici quelques exemples :

apple banana

trouve les lignes qui contiennent au moins l'un de ces mots.

+apple +juice

... les deux mots.

+apple macintosh

... le mot ''apple'', mais elles sont plus valorisées si elles contiennent aussi ''macintosh''.

+apple -macintosh

... le mot ''apple'' mais pas le mot ''macintosh''.

+apple +(>pie <strudel)

... ''apple'' et ''pie'', ou ''apple'' et ''strudel'' (l'ordre n'importe pas), mais valorise plus ''apple pie'' que ''apple strudel''.

apple*

... ''apple'', ''apples'', ''applesauce'', et ''applet''.

"some words"

... ''some words of wisdom'', mais pas ''some noise words''.