Capacités de recherche plein texte de MySQL

Introduction

Dans ce laboratoire, vous allez explorer les capacités de recherche en texte intégral (full-text search) de MySQL. Vous commencerez par créer une base de données et une table nommée articles avec des colonnes pour id, title et content, puis vous la remplirez avec des données d'exemple.

Ensuite, vous ajouterez un index de texte intégral nommé article_index à la table articles, spécifiquement sur les colonnes title et content, en utilisant l'instruction ALTER TABLE. Enfin, vous vérifierez la création de l'index à l'aide de la commande SHOW INDEXES, en confirmant que le Index_type est FULLTEXT. Cette configuration vous préparera à des recherches textuelles efficaces dans votre base de données MySQL.

Tout au long de ce laboratoire, vous travaillerez dans le shell MySQL. Vous n'avez besoin d'entrer dans le shell MySQL qu'au début et d'en sortir à la fin. Toutes les commandes SQL des étapes suivantes doivent être exécutées dans la même session MySQL.

Créer la base de données et la table

Dans cette étape, vous allez créer une base de données MySQL et une table pour stocker des articles. Cette table sera utilisée pour démontrer les capacités de recherche en texte intégral.

Tout d'abord, ouvrez votre terminal et connectez-vous au serveur MySQL en tant qu'utilisateur root. Vous pouvez le faire en utilisant la commande suivante :

sudo mysql -u root

Cette commande se connecte au serveur MySQL en utilisant l'utilisateur root. Comme vous utilisez sudo, il ne vous sera pas demandé de mot de passe.

Une fois connecté au shell MySQL, vous verrez l'invite mysql>. Restez dans le shell MySQL pour toutes les étapes suivantes jusqu'à la fin du laboratoire.

Maintenant, créons une base de données nommée search_db. Cette base de données contiendra notre table articles. Exécutez la commande SQL suivante :

CREATE DATABASE IF NOT EXISTS search_db;

La clause IF NOT EXISTS garantit que la base de données n'est créée que si elle n'existe pas déjà.

Ensuite, basculez vers la base de données nouvellement créée en utilisant la commande USE :

USE search_db;

Vous devriez voir un message indiquant que la base de données a été modifiée.

Maintenant, créez la table articles. Cette table aura trois colonnes : id, title et content.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Analysons cette commande :

• CREATE TABLE articles : Cette instruction crée une nouvelle table nommée articles.
• id INT AUTO_INCREMENT PRIMARY KEY : Ceci définit une colonne entière nommée id qui s'incrémente automatiquement pour chaque nouvelle ligne et sert de clé primaire pour la table.
• title VARCHAR(255) NOT NULL : Ceci définit une colonne de chaîne nommée title avec une longueur maximale de 255 caractères. NOT NULL signifie que cette colonne ne peut pas être vide.
• content TEXT : Ceci définit une colonne de texte nommée content qui peut stocker des chaînes plus longues.

Après avoir exécuté cette commande, la table articles sera créée dans la base de données search_db.

Vous pouvez vérifier la création de la table en listant les tables de la base de données actuelle :

SHOW TABLES;

Vous devriez voir articles listé dans la sortie.

+-----------------------+
| Tables_in_search_db   |
+-----------------------+
| articles              |
+-----------------------+
1 row in set (0.00 sec)

Maintenant que vous avez créé la base de données et la table, vous êtes prêt à les remplir avec des données d'exemple dans la prochaine étape.

Insérer des données d'exemple et ajouter un index plein texte

Dans cette étape, vous allez insérer des données d'exemple dans la table articles, puis ajouter un index de texte intégral pour permettre des recherches textuelles efficaces.

Assurez-vous que vous êtes toujours dans le shell MySQL et que vous utilisez la base de données search_db. Sinon, sélectionnez la base de données :

USE search_db;

Maintenant, insérons des données d'exemple dans la table articles. Nous allons ajouter trois lignes avec des titres et des contenus différents :

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

Cette instruction INSERT ajoute plusieurs lignes à la table articles. Chaque ensemble de parenthèses après VALUES représente une nouvelle ligne, les valeurs correspondant aux colonnes title et content.

Vous pouvez vérifier que les données ont été insérées en sélectionnant toutes les lignes de la table :

SELECT * FROM articles;

Vous devriez voir les trois lignes insérées dans la sortie :

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Maintenant que la table contient des données, ajoutons un index de texte intégral. Un index de texte intégral permet à MySQL d'effectuer des recherches rapides et pertinentes sur les données textuelles. Nous allons ajouter un index de texte intégral sur les colonnes title et content.

Utilisez l'instruction ALTER TABLE pour ajouter l'index :

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

Analysons cette commande :

• ALTER TABLE articles : Ceci indique que nous modifions la table articles.
• ADD FULLTEXT INDEX article_index : Ceci ajoute un nouvel index de type FULLTEXT et le nomme article_index.
• (title, content) : Ceci spécifie les colonnes qui seront incluses dans l'index de texte intégral.

Après avoir exécuté cette commande, MySQL construira l'index de texte intégral sur les colonnes spécifiées.

Pour vérifier que l'index de texte intégral a été créé avec succès, vous pouvez utiliser la commande SHOW INDEXES :

SHOW INDEXES FROM articles;

La sortie affichera tous les index de la table articles. Vous devriez voir une entrée pour article_index avec FULLTEXT dans la colonne Index_type.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Vous avez maintenant inséré avec succès des données et ajouté un index de texte intégral à votre table. Dans la prochaine étape, vous apprendrez à utiliser cet index pour effectuer des recherches en texte intégral.

Effectuer une recherche plein texte basique

Dans cette étape, vous allez effectuer des recherches de texte intégral de base sur la table articles en utilisant la clause MATCH AGAINST. C'est la manière fondamentale d'utiliser l'index de texte intégral que vous avez créé.

Assurez-vous que vous êtes toujours dans le shell MySQL et que vous utilisez la base de données search_db. Sinon, sélectionnez la base de données :

USE search_db;

La clause MATCH AGAINST est utilisée dans la clause WHERE d'une instruction SELECT. La syntaxe de base est :

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');

Ici, column_list est une liste séparée par des virgules des colonnes incluses dans l'index de texte intégral (dans notre cas, title et content), et 'search_term' est le mot ou la phrase que vous recherchez.

Recherchons les articles qui contiennent le mot "MySQL" :

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

Cette requête sélectionne les colonnes id, title et content de la table articles où les colonnes title ou content correspondent au terme "MySQL".

Vous devriez voir la sortie suivante :

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Les trois articles sont retournés car ils contiennent tous le mot "MySQL".

La recherche de texte intégral fournit également un score de pertinence, indiquant à quel point chaque ligne correspond au terme de recherche. Vous pouvez inclure ce score dans votre instruction SELECT :

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

Cette requête ajoute une colonne nommée relevance qui affiche le score pour chaque ligne.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Vous pouvez trier les résultats par pertinence pour voir les meilleures correspondances en premier :

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

Cette requête trie les résultats en fonction du score de relevance par ordre décroissant.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Dans cette étape, vous avez appris à effectuer des recherches de texte intégral de base en utilisant MATCH AGAINST et comment récupérer et trier les résultats par pertinence. Dans la prochaine étape, vous explorerez des options de recherche plus avancées en utilisant le mode booléen.

Utiliser le mode booléen pour la recherche avancée

Dans cette étape, vous utiliserez le mode booléen de la clause MATCH AGAINST pour effectuer des recherches de texte intégral plus précises et flexibles. Le mode booléen vous permet d'utiliser des opérateurs pour contrôler quels mots doivent ou ne doivent pas être présents dans les résultats.

Assurez-vous que vous êtes toujours dans le shell MySQL et que vous utilisez la base de données search_db. Sinon, sélectionnez la base de données :

USE search_db;

Pour utiliser le mode booléen, vous ajoutez la clause IN BOOLEAN MODE à l'instruction MATCH AGAINST. La syntaxe est :

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);

Voici quelques opérateurs courants utilisés en mode booléen :

• + : Requiert que le mot soit présent dans le résultat.
• - : Exclut les lignes qui contiennent le mot.
• "" : Recherche une expression exacte.
• * : Opérateur de joker (à la fin d'un mot).

Trouvons les articles qui doivent contenir le mot "MySQL" et ne doivent pas contenir le mot "optimizing".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

Cette requête utilise l'opérateur + pour exiger "MySQL" et l'opérateur - pour exclure "optimizing".

Vous devriez voir la sortie suivante :

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

L'article sur "Optimizing MySQL Queries" est exclu car il contient "optimizing".

Maintenant, recherchons l'expression exacte "full-text search" :

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

L'utilisation de guillemets doubles recherche les mots en tant qu'expression contiguë.

La sortie sera :

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Seul le premier article est retourné car il contient l'expression exacte "full-text search".

Essayons d'utiliser l'opérateur de joker *. Supposons que vous souhaitiez trouver des articles contenant des mots commençant par "index".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);

Le terme index* correspondra à des mots comme "index" et "indexing".

La sortie sera :

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Dans cette étape, vous avez appris à utiliser le mode booléen avec la clause MATCH AGAINST et divers opérateurs pour effectuer des recherches de texte intégral plus contrôlées et spécifiques. Dans la prochaine étape, vous pratiquerez la combinaison de ces techniques.

Combiner les techniques de recherche

Dans cette étape, vous allez combiner la recherche MATCH AGAINST de base avec les opérateurs du mode booléen pour effectuer des requêtes de texte intégral plus complexes et affinées.

Assurez-vous que vous êtes toujours dans le shell MySQL et que vous utilisez la base de données search_db. Sinon, sélectionnez la base de données :

USE search_db;

Essayons de trouver les articles qui contiennent le mot "MySQL" et soit le mot "indexing", soit le mot "optimizing". Nous pouvons utiliser des parenthèses pour regrouper les termes optionnels en mode booléen.

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

Cette requête exige "MySQL" (+MySQL) et exige au moins un des termes entre parenthèses (+(indexing optimizing)).

La sortie sera :

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Le premier article est exclu car il contient "MySQL" mais ni "indexing" ni "optimizing".

Vous pouvez également combiner le mode booléen avec le score de pertinence. Trouvons les articles contenant "MySQL" et donnons la priorité à ceux qui mentionnent également "indexing" en utilisant l'opérateur >.

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

Cette requête exige "MySQL" et augmente le score de pertinence pour les articles contenant "indexing". Les résultats sont ensuite triés par la pertinence calculée.

La sortie sera :

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

Notez que l'article "Indexing in MySQL" est classé le plus haut en raison de l'opérateur >indexing.

En combinant la recherche de base avec les opérateurs booléens et le score de pertinence, vous pouvez créer des requêtes de texte intégral puissantes et flexibles adaptées à vos besoins spécifiques.

Ceci conclut le laboratoire sur les capacités de recherche de texte intégral de MySQL. Vous pouvez maintenant quitter le shell MySQL en tapant :

exit

Vous avez appris à configurer une table avec un index de texte intégral, à effectuer des recherches de base et à utiliser le mode booléen pour des requêtes plus avancées.

Résumé

Dans ce laboratoire, vous avez découvert les capacités de recherche de texte intégral de MySQL. Vous avez commencé par créer une base de données nommée search_db et une table articles pour stocker des données textuelles. Vous avez ensuite rempli la table avec des articles d'exemple.

L'étape clé a été l'ajout d'un index de texte intégral nommé article_index aux colonnes title et content de la table articles à l'aide de l'instruction ALTER TABLE. Cet index est essentiel pour une recherche de texte intégral efficace. Vous avez vérifié la création de l'index en utilisant SHOW INDEXES.

Enfin, vous avez exploré comment effectuer des recherches de texte intégral à l'aide de la clause MATCH AGAINST. Vous avez appris à effectuer des recherches de base, à récupérer des scores de pertinence et à trier les résultats par pertinence. Vous vous êtes également penché sur le mode booléen, en utilisant des opérateurs tels que +, -, "" et * pour créer des requêtes de recherche plus précises et complexes. En combinant ces techniques, vous pouvez rechercher et récupérer efficacement des informations pertinentes à partir des données textuelles stockées dans votre base de données MySQL.