13 Fonctions à utiliser dans les clauses SELECT et WHERE
Manuel de Référence MySQL 4.1 : Version Française
. Opérateurs et fonctions tous types
. Les fonctions de contrôle
. Fonctions de chaînes de caractères
. Fonctions numériques
. Fonctions de dates et d'heures
->Recherche en texte intégral ( Full-text ) dans MySQL
. Fonctions de transtypage
. Autres fonctions
. Fonctions et options à utiliser dans les clauses GROUP BY
|
13.6 Recherche en texte intégral ( Full-text ) dans MySQL
-
MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])
- Depuis la version 3.23.23,
MySQL
propose l'indexation et la recherche sur
l'ensemble d'un champ
TEXT
(
full-text
). Les index en texte intégral de
MySQL
sont des index de type
FULLTEXT
. Les index
FULLTEXT
sont
utilisés avec les tables
MyISAM
et peuvent être
créés depuis des colonnes de types
CHAR
,
VARCHAR
,
ou
TEXT
au moment de
CREATE TABLE
ou plus tard avec
ALTER TABLE
ou
CREATE INDEX
. Pour les enregistrements les plus grands, il sera plus rapide
de charger les donnés dans une table qui n'a pas d'index
FULLTEXT
,
et ensuite de créer l'index avec
ALTER TABLE
(ou
CREATE
INDEX
). L'enregistrement de données dans une table qui a déjà
des index
FULLTEXT
sera plus lent.
Les contraintes sur la recherche en texte intégral sont listées dans
la section Restrictions sur les recherches en texte intégral .
La recherche en texte intégral est effectuée par 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 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. La pertinence est un nombre décimal positif. La 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.
Pour les recherches en texte intégral et en langage naturel, la technique impose
que les colonnes utilisées avec la fonction
MATCH()
doivent être les mêmes que
les colonnes utilisées dans un index
FULLTEXT
. Dans la requête précédente, notez
que les colonnes nommées dans la fonction
MATCH()
(
title
et
body
) sont les mêmes que celles de la définition de la table
article
et son index
FULLTEXT
. Si vous voulez rechercher le titre
title
ou le corps
body
séparément, vous devrez créer un index
FULLTEXT
pour chaque colonne.
Il est aussi possible d'exécuter une recherche en mode booléen. Ceci est
décrit dans les sections Opérateurs booléens de recherche en texte intégral et
Recherches en texte intégral avec extension .
L'exemple précédent est une illustration élémentaire
qui montre comment on utilise la fonction
MATCH()
. 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'optimiseur de
MySQL
remarquera que
les deux appels à
MATCH()
sont identiques et appellent le code
de recherche sur texte intégral 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é.
-
Un mot trop court est ignoré. La taille minimale pour un mot dans les
recherches est de 4 lettres.
-
Les mots de la liste sont ignorés. Un mot banni est par exemple
``
the
'' ou ``
some
'', ``un'' or ``les'' en français, qui sont considérés comme
trop communs pour avoir une valeur intrinsèque. Il y a une liste
de mots bannis en anglais par défaut.
La taille minimale des mots et la liste de mots à ignorer sont décrites
dans la section Optimisation des recherches en texte intégral .
Tous les mots corrects de la liste et de 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 nombreuses lignes aura un poids faible (et peut être
même un poids nul), car il a peu d'importance dans cette requête particulière.
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 les 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 Go.
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
.
Le seuil de 50% a un impact significatif lorsque vous commencez à comprendre
comment fonctionne l'index : si vous créez une table et insérez une ou deux lignes,
chaque mot apparaîtra dans 50% des lignes. Résultat, la recherche ne trouvera rien.
Assurez-vous d'insérer au moins trois lignes, et même plus.
Sommaire :
|