TUTO : MYSQL FULLTEXT IN BOOLEAN MODE

Dans ce tuto avant tout destiné aux développeurs, nous vous proposons une solution pour implémenter un système de recherche avec MySQL & FULLTEXT en mode BOOLEAN avec InnoDB.

Nous vous invitons à consulter le précédent article, qui offre une première partie avec des conseils web-marketing relatifs à la recherche interne des sites web, intitulé "Recherche & Pertinence avec MySQL & FULLTEXT". Ces généralités et conseils utiles sont suivis d'une deuxième partie destinée aux développeurs, sous la forme d'un tuto avec du code, ainsi que des explications détaillées nécessaires à la compréhension de ce qui va suivre...

Généralités sur MySQL FULLTEXT IN BOOLEAN MODE

Contrairement au mode NATURAL vu dans le précédent article, un des avantages du mode BOOLEAN est de pouvoir utiliser les "operators" suivants + - > < ( ) ~ " @ * permettant une recherche avancée.

Notre choix est de n'utiliser (proposer aux internautes) que seulement 4 de ces operators (vous trouverez des explications complètes dans la doc officielle MySQL).

operator +
Le mot doit obligatoirement être présent. La requête 'fauteuil +cuir' sortira des résultats uniquement si le mot 'cuir' est présent ('fauteuil en cuir', 'canapé cuir', etc.)

operator -
Le mot ne doit pas être présent. La requête 'teeshirt -rouge' sortira des résultats qui "match" avec 'teeshirt', mais uniquement si le mot 'rouge' est absent.

operator *
Wildcard : n'importe quel caractère après le mot. La requête 'table*' sortira des résultats pour : 'table', 'tables déco', mais également pour 'tableau noir', etc.

operators ""
Pour effectuer une recherche exacte. Exemple : "canapé cuir" => les deux mots doivent être présents ET dans l'ordre ('canapé d'angle en cuir' ne "match" pas).

Le code PHP

Le code PHP ci-dessous n'est pas directement exploitable, car il doit être adapté à vos propres méthodes, votre code gérant la recherche, etc.

Il est commenté et il contient des sections de A) à E) qui feront l'objet d'explications plus détaillées dans cet article.

Comme on dit dans le jargon du métier des mécanos du web, mettons sans plus attendre "les mains dans l'cambouis" :

<?php /* Testé avec MySQL v5.7 et PHP 7.0 */

/* A) Initialisation */
$sRecherche = 'article machin'; // les mots-clés de la recherche
$bWildcard = false; // concaténation d'un wildcard ?
$iCoefDescCourte = 0.5; // coefficients / scores
$iCoefDescLongue = 0.1;
$oPDO = new PDO('dsn', 'username', 'password'); // Connexion à la base de données

/* B) Récup. des mots-clés
Filtrage des espaces
*/
$aMots = preg_split('/\s+/', $sRecherche);
$sMots = '';

foreach ($aMots as $sElem) {
 /* C) Filtre anti-erreurs SQL
 limitation des operators Fulltext à [ + - * "]
 */
 if (!preg_match('/^(?:[\+\-\"\*])?[^\+\-><\(\)~*\"@]+(?:\-[^\+\-><\(\)~*\"@]+)?(?:[\"*])?$/', $sElem)) {
  continue;
 }

 /* D) Ajout ou non d'un widcard *
 suivant config (true/false)
 */
 if ($bWildcard && !(strrpos($sElem, '*') || strrpos($sElem, '"'))) {
  $sMots .= $sElem.'* ';
 } else {
  $sMots .= $sElem.' ';
 }

 //print_r('<br><br>Mot : '.$sElem); // visu de chaque mot-clé
}

/* suppression du dernier espace */
$sMots = rtrim($sMots);

//print_r('<br><br>Requête : |'.$sMots.'|'); // visu requête complète

/* sécurisation avec PDO */
$sMots = $oPDO->quote($sMots);

/* F) Une partie de la requête SQL */

$sRequete = '
SELECT
nom,
description_courte,
description_longue,
MATCH(nom) AGAINST('.$sMots.' IN BOOLEAN MODE)  AS  score_nom,
MATCH(description_courte) AGAINST('.$sMots.' IN BOOLEAN MODE) AS score_description_courte,
MATCH(description_longue) AGAINST('.$sMots.' IN BOOLEAN MODE) AS score_description_longue
FROM produits
WHERE
MATCH(nom) AGAINST('.$sMots.' IN BOOLEAN MODE) OR
MATCH(description_courte) AGAINST('.$sMots.' IN BOOLEAN MODE) OR
MATCH(description_longue) AGAINST('.$sMots.' IN BOOLEAN MODE)
ORDER BY (score_nom+score_description_courte*'.$iCoefDescCourte.'+score_description_longue*'.$iCoefDescLongue.') DESC;
';

/* Debug */
// print_r($oPDO->query($sRequete)->fetchAll(PDO::FETCH_ASSOC));

Quelques explications sur le code section par section

A) Initialisation

$sRecherche
Récupération des mots-clés (saisis dans le champ du formulaire de recherche).

$bWildcard
Booléen : si sa valeur est true on ajoutera un wildcard, une * à la fin de chacun des mots recherchés de façon à ce qu'une recherche sur "lunette" au singulier "match" également avec son pluriel "lunettes", etc.

$iCoefDescCourte
$iCoefDescLongue
Affecte un coefficient au score trouvé par MySQL par rapport à chaque description (courte et longue) qui "match" afin de moduler l'importance qu'à le champ dans la recherche. Plus d'explication dans le précédent article.
Plage recommandée : 0 < coefficient 1

B) Traitement des mots-clés (avant le foreach)

La fonction preg_split génère un tableau (array) contenant chaque mot-clé de la requête. On gère du même coup les éventuels espaces en doublon.

C) Filtre anti-erreurs SQL

La regex (de la mort) dans le preg_match filtre chaque mot (boucle foreach) qui ne correspond pas au pattern autorisé. Sans cela, le fait de pouvoir passer à la requête SQL un mot contenant des operators placés au mauvais endroit ou doublonnés comme : '+++machin', 'rouge-', etc. ferait planter la requête SQL. Essayez sans et vous verrez !
Note : je n'ai pas trouvé plus simple alors si vous avez mieux...

D) Ajout ou non d'un widcard *

Si vous testez avec $bWildcard = true; (voir A) une * sera concaténée à la fin de chaque mot.
Cela permet une recherche plus étendue, exemple : 'table*' "matchera" avec 'tables' ou 'tableau'. Grâce aux strrpos on évitera de polluer la jolie requête SQL avec des * ou des " doublonnés.

E) Une partie de la requête SQL

Mis à part l'ajout de "IN BOOLEAN MODE", nous n'avons quasi pas de différences du point de vue du code. Je vous invite donc une fois de plus à consulter le précédent article qui contient quelques explications relatives à cette requête SQL. En apparence peu de changement, mais dans l'exécution c'est tout autre chose...

Conclusion

Pour nos clients, nous avons jugé bon de leur permettre de paramétrer via leur admin/config le mode de recherche FULLTEXT comme ils le souhaitent :

  • 2 petits champs "input" pour saisir les coefficients à affecter aux descriptions (voir : A) Initialisation). Un 0 (zéro) leur permet de zapper totalement une des descriptions ou les deux (recherche uniquement sur le nom du produit). De notre côté nous conditionnons simplement la requête SQL en fonction de cela.
  • Deux petites cases à cocher : l'une pour sélectionner soit le mode "NATURAL" soit le "BOOLEAN" et l'autre correspondant au $bWildcard (voir : A) Initialisation).

N'hésitez pas à nous faire part de vos remarques ou suggestions afin que tous ensemble nous fassions avancer la recherche ; )


De Mathieu Chartier Internet-Formation :
Moteur de recherche PHP complet (pagination, surlignage, fulltext)


(je recevrai un mail quand un article est publié (no spam)

4 thoughts on “TUTO : MYSQL FULLTEXT IN BOOLEAN MODE

  1. Christophe Maggi

    Hello,
    J'ai l'impression qu'il y a un gros micmac dans vos explications.
    Les champs noms et descriptions courtes par définition sont courts (par exemple varchar 100, varchar 250). il vaut mieux indexer correctement les colonnes et effectuer une recherche de type LIKE%% dessus qu'une recherche FULLTEXT.
    La recherche FULLTEXT doit être conservée si nécessaire pour des type TEXT ou équivalent.
    Pour que votre article soit complet, il faudrait parler des indices de relevances qui permettraient de déterminer les meilleurs résultats, des indexations de colonnes et des vitesses d'exécution des requêtes.
    Sur un site e-commerce la meilleure solution est d'avoir une colonne avec des mots clés (5-10) séparés par des connecteurs (on ne cherche dedans que si nécessaire) , les champs courts, de type Nom, Description courte etc... on fait des LIKE%% si nécessaire et ne conserver la recherche en FULLTEXT naturelle ou booléenne que sur les champs longs ou très longs. On utilise une stopword liste et on trie le tout par indice de pertinence, à la limite filtrer encore les résultats avant de les sortir et limiter la taille de sortie.

  2. jm Auteur de l’article

    @Christophe :
    L'objectif de ce tuto (et du précédent) est d'introduire la notion de FULLTEXT sans faire trop compliqué et à la mesure de mes compétences.
    Grace à votre contribution et celles à venir celui-ci s'enrichira peu à peu...
    Dans notre cas d'école ainsi que sur notre Système, la description courte n'est pas un VARCHAR, mais un "text" : voir la requête CREATE TABLE `produits` dans le tuto précédent. Pour nous autres SEO/SEA la description courte contient au moins un petit paragraphe avec éventuellement des liens, du bold, des images, etc.
    Nous avons en effet un VARCHAR pour la description des extraits-produits (page catégorie), mais on ne cherche tout simplement pas dedans.
    Votre proposition de faire un LIKE%% sur le 'nom' est OK, mais cela dépend de ce qu'on souhaite faire. Exemple : si je veux que les operators (+ - *) de FULLTEXT iN BOOLEAN MODE fonctionnent sur le nom, que j'ai le choix entre une recherche paramétrable, stricte ou plus étendue (wildcard ou pas), etc.

    @Tous
    Suite aux échanges avec Mathieu Chartier sur le précédent article et notamment à propos de son Moteur de recherche PHP complet (pagination, surlignage, fulltext), le fil de discussion continu ici.

  3. Chartier Mathieu

    @jm J'ai bossé sur mon moteur de recherche PHP en 2013, j'ai bien évolué dans ma manière de programmer depuis ce temps. D'ailleurs, j'ai toujours prévu de le réécrire complètement avec des classes et méthodes plus adaptées, plus optimisées, etc. Disons que ce moteur, aussi fonctionnel et complet soit-il, est davantage un brouillon qu'autre chose. 🙂

    Ta regex est longue et complexe, mais elle semble fait ce que tu cherches, à savoir supprimer les caractères un peu "particulier". En général, j'ai tendance à vouloir épurer davantage les résultats, et mon moteur ne le fait pas assez à mon goût. Je supprime les stop words par exemple, mais il faudrait aussi supprimer toute ponctuation, etc.
    Avec du recul, je pense que l'idéal serait de récupérer un contenu que l'on doit traiter. Lors du traitement, on passe tout en minuscule (recherche sans casse), on supprime toute forme de ponctuation qui suit un mot (il ne faut pas supprimer les tirets des mots composés par exemple), on supprime bien entendu tout code HTML ou XML avec strip_tags() par exemple, etc. Ainsi, on a un contenu épuré dans lequel chercher. Il ne reste alors qu'à traiter la requête comme tu l'as fait avec ta regex.

    P.S. : mon moteur est différent car je suis parti du constat que plusieurs méthodes existent et que toutes peuvent être utiles. Comme la même classe permet de faire des recherches LIKE, REGEXP ou FULLTEXT, j'ai dû adapter des choses selon le type de recherche. Si je réécris le moteur, je ferai différemment (des méthodes communes et des classes par type de recherche, etc.).

  4. jm Auteur de l’article

    @Mathieu
    J'ai mis un lien au bas de cet article vers ce que tu appelles ton "brouillon" ; )
    Il n'y a pas grand-chose sur le net sur MySQL&FULLTEXT encore moins en Boolean Mode...
    Le coup des operators qui plantent la requête m'a fait galérer un moment et devant le peu d'info, j'ai dû chercher ma propre soluce.

    Entre ton tuto, tes commentaires, et ce qu'il y a ici, on peut d'ores et déjà donner quelques pistes et espérer partager avec d'autres développeurs d'utiles informations sur le sujet...

    Quant à ton futur prochain moteur de recherche, pense à nous annoncer la bonne nouvelle ici même.

Partagez sur :

Les commentaires sont fermés.