MySQL Volltextsuche - Funktionen

Einführung

In diesem Lab werden Sie die Volltextsucheinstellungen von MySQL erkunden. Sie beginnen damit, eine Datenbank und eine Tabelle namens articles mit Spalten für id, title und content zu erstellen und diese dann mit Beispieldaten zu füllen.

Als Nächstes werden Sie einen Volltextindex namens article_index für die Tabelle articles hinzufügen, speziell für die Spalten title und content, indem Sie die ALTER TABLE-Anweisung verwenden. Abschließend werden Sie die Erstellung des Indexes mit dem Befehl SHOW INDEXES überprüfen und sicherstellen, dass der Index_type FULLTEXT ist. Diese Einrichtung bereitet Sie auf effiziente Textsuchen in Ihrer MySQL-Datenbank vor.

Skills Graph

Hinzufügen eines Volltextindexes zu einer Tabelle

In diesem Schritt werden wir einem Tabellen in einer MySQL-Datenbank einen Volltextindex hinzufügen. Volltextindizes werden verwendet, um effiziente Textsuchen in zeichenbasierten Daten durchzuführen.

Bevor wir beginnen, erstellen wir eine einfache Tabelle namens articles, mit der wir arbeiten können. Öffnen Sie Ihr Terminal und verbinden Sie sich mit dem MySQL-Server mit dem folgenden Befehl:

mysql -u root -p

Sie werden nach dem Root-Passwort gefragt. Geben Sie es ein, um sich zu verbinden.

Jetzt erstellen wir eine Datenbank namens search_db und wechseln zu ihr:

CREATE DATABASE IF NOT EXISTS search_db;
USE search_db;

Als Nächstes erstellen wir die Tabelle articles mit Spalten für id, title und content:

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

Jetzt fügen wir einige Beispieldaten in die Tabelle articles ein:

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.');

Um einem Volltextindex zur Tabelle articles hinzuzufügen, verwenden wir die ALTER TABLE-Anweisung. Wir erstellen einen Volltextindex für die Spalten title und content. Führen Sie den folgenden SQL-Befehl aus:

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

Dieser Befehl erstellt einen Volltextindex namens article_index, der die Spalten title und content umfasst. MySQL kann nun diese Spalten effizient mit Hilfe der Volltextsucheinstellungen durchsuchen.

Sie können überprüfen, ob der Index erstellt wurde, indem Sie den Befehl SHOW INDEXES verwenden:

SHOW INDEXES FROM articles;

Die Ausgabe sollte eine Zeile für den Volltextindex article_index enthalten. Die Spalte Index_type für diesen Index sollte FULLTEXT anzeigen.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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)

Sie haben nun erfolgreich einen Volltextindex zu Ihrer Tabelle hinzugefügt. In den nächsten Schritten werden Sie lernen, wie Sie diesen Index verwenden können, um Volltextsuchen durchzuführen.

Suchen mit MATCH AGAINST

In diesem Schritt werden wir die MATCH AGAINST-Klausel verwenden, um Volltextsuchen in der Tabelle articles durchzuführen, die wir im vorherigen Schritt erstellt haben.

Stellen Sie zunächst sicher, dass Sie mit dem MySQL-Server verbunden sind und die Datenbank search_db verwenden. Wenn Sie die Verbindung geschlossen haben, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie das Root-Passwort ein, wenn Sie dazu aufgefordert werden. Wählen Sie dann die Datenbank aus:

USE search_db;

Die MATCH AGAINST-Klausel wird in der WHERE-Klausel einer SELECT-Anweisung verwendet, um die Volltextsuche durchzuführen. Die grundlegende Syntax lautet:

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

Hierbei sind column1, column2 usw. die Spalten, die Teil des Volltextindexes sind, und 'search_term' ist der Text, nach dem Sie suchen möchten.

Führen wir eine einfache Suche nach Artikeln durch, die das Wort "MySQL" enthalten:

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

Diese Abfrage gibt alle Zeilen aus der Tabelle articles zurück, in denen entweder die Spalte title oder die Spalte content das Wort "MySQL" enthält.

Sie sollten die folgende Ausgabe sehen:

+----+--------------------------+---------------------------------------------------------------------+
| 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)

Beachten Sie, dass alle drei Artikel zurückgegeben werden, da alle das Wort "MySQL" entweder im Titel oder im Inhalt enthalten.

Die MATCH AGAINST-Klausel kann auch verwendet werden, um die Ergebnisse nach Relevanz zu bewerten. MySQL weist jeder Zeile eine Relevanzbewertung zu, die darauf basiert, wie gut sie dem Suchbegriff entspricht. Sie können diese Bewertung mithilfe der MATCH AGAINST-Klausel in der SELECT-Liste abrufen:

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

Diese Abfrage gibt die id, den title, den content und eine relevance-Bewertung für jeden Artikel zurück. Je höher die Relevanzbewertung, desto besser ist die Übereinstimmung.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 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)

Sie können die Ergebnisse dann nach Relevanz sortieren, um die besten Übereinstimmungen zuerst anzuzeigen:

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

Diese Abfrage gibt die gleichen Ergebnisse wie zuvor zurück, aber sortiert nach der relevance-Bewertung in absteigender Reihenfolge.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 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)

In diesem Schritt haben Sie gelernt, wie Sie die MATCH AGAINST-Klausel verwenden, um grundlegende Volltextsuchen durchzuführen und wie Sie die Ergebnisse nach Relevanz bewerten können. Im nächsten Schritt werden Sie lernen, wie Sie den booleschen Modus für erweiterte Suchfunktionen nutzen können.

Verwenden des booleschen Modus für erweiterte Suche

In diesem Schritt werden wir den booleschen Modus der MATCH AGAINST-Klausel erkunden, der präzisere und flexiblere Volltextsuchen ermöglicht. Der boolesche Modus bietet Operatoren, um erforderliche, verbotene und optionale Wörter in der Suchanfrage anzugeben.

Zunächst stellen Sie sicher, dass Sie mit dem MySQL-Server verbunden sind und die Datenbank search_db verwenden. Wenn Sie die Verbindung geschlossen haben, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie das Root-Passwort ein, wenn Sie dazu aufgefordert werden. Wählen Sie dann die Datenbank aus:

USE search_db;

Um den booleschen Modus zu verwenden, fügen Sie die IN BOOLEAN MODE-Klausel zur MATCH AGAINST-Anweisung hinzu. Die Syntax lautet:

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

Hier sind einige der im booleschen Modus verfügbaren Operatoren:

• +: Das Wort muss in jeder zurückgegebenen Zeile vorhanden sein.
• -: Das Wort darf in keiner zurückgegebenen Zeile vorhanden sein.
• >: Das Wort trägt zur Relevanz der Zeile bei.
• <: Das Wort verringert die Relevanz der Zeile.
• ~: Das Wort trägt negativ zur Relevanz der Zeile bei.
• *: Der Platzhalteroperator.
• "": Definiert eine Phrase.

Beginnen wir mit einem einfachen Beispiel. Angenommen, wir möchten Artikel finden, die das Wort "MySQL" müssen enthalten und das Wort "optimizing" nicht enthalten dürfen. Wir können die folgende Abfrage verwenden:

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

Diese Abfrage gibt Zeilen zurück, die "MySQL" enthalten, aber nicht "optimizing".

Sie sollten die folgende Ausgabe sehen:

+----+------------------------+---------------------------------------------------------------------+
| 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)

Beachten Sie, dass der Artikel mit dem Titel "Optimizing MySQL Queries" nicht zurückgegeben wird, da er das Wort "optimizing" enthält.

Jetzt versuchen wir eine Abfrage, die "MySQL" erfordert und "indexing" bevorzugt:

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

Im booleschen Modus werden Wörter ohne Operatoren als optional behandelt. Diese Abfrage erfordert "MySQL" und enthält optional "indexing".

Die Ausgabe wird sein:

+----+--------------------------+---------------------------------------------------------------------+
| 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)

Alle drei Artikel werden zurückgegeben, da alle "MySQL" enthalten. Das Vorhandensein von "indexing" ist optional.

Verwenden wir den >-Operator, um die Relevanz von Zeilen, die "indexing" enthalten, zu erhöhen:

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

Diese Abfrage erfordert immer noch "MySQL", aber jetzt werden Zeilen, die "indexing" enthalten, höher bewertet (obwohl wir in diesem Beispiel nicht nach Relevanz sortieren). Die Ausgabe bleibt die gleiche, aber die internen Relevanzbewertungen werden beeinflusst.

Schließlich suchen wir nach einer Phrase mit doppelten Anführungszeichen:

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

Diese Abfrage sucht nach der genauen Phrase "full-text search".

Die Ausgabe wird sein:

+----+------------------------+---------------------------------------------------------------------+
| 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)

In diesem Schritt haben Sie gelernt, wie Sie den booleschen Modus mit der MATCH AGAINST-Klausel verwenden, um erweiterte und präzise Volltextsuchen mit Operatoren wie +, -, >, < und "" durchzuführen. Im nächsten Schritt werden Sie Ihre Volltextabfragen testen und verbessern.

Testen der Volltextabfrageergebnisse

In diesem Schritt werden wir unsere Volltextabfragen testen und verbessern, um sicherzustellen, dass sie die gewünschten Ergebnisse liefern. Wir werden mit verschiedenen Suchbegriffen und booleschen Operatoren experimentieren, um die bestmögliche Suchgenauigkeit zu erreichen.

Zunächst stellen Sie sicher, dass Sie mit dem MySQL-Server verbunden sind und die Datenbank search_db verwenden. Wenn Sie die Verbindung geschlossen haben, verbinden Sie sich erneut mit:

mysql -u root -p

Geben Sie das Root-Passwort ein, wenn Sie dazu aufgefordert werden. Wählen Sie dann die Datenbank aus:

USE search_db;

Beginnen wir damit, die Daten in unserer Tabelle articles zu überprüfen:

SELECT * FROM articles;

Dies zeigt Ihnen den aktuellen Inhalt der Tabelle:

+----+--------------------------+---------------------------------------------------------------------+
| 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)

Jetzt versuchen wir eine komplexere boolesche Abfrage. Angenommen, wir möchten Artikel finden, die das Wort "MySQL" und entweder "indexing" oder "optimizing" enthalten. Wir können dies erreichen, indem wir Klammern verwenden, um die optionalen Begriffe zu gruppieren:

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

Diese Abfrage erfordert "MySQL" und dann entweder "indexing" oder "optimizing".

Die Ausgabe wird sein:

+----+--------------------------+---------------------------------------------------------------------+
| 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)

Artikel 1 wird ausgeschlossen, weil er nur "MySQL" und nicht "indexing" oder "optimizing" enthält.

Versuchen wir ein weiteres Szenario. Angenommen, wir möchten Artikel finden, die über "MySQL" handeln, aber speziell nicht über "queries".

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

Diese Abfrage erfordert "MySQL" und schließt Artikel aus, die "queries" enthalten.

Die Ausgabe wird sein:

+----+------------------------+---------------------------------------------------------------------+
| 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)

Artikel 3, der über "MySQL Queries" handelt, wird ausgeschlossen.

Jetzt experimentieren wir mit der Bewertung der Ergebnisse. Wir können die Operatoren > und < verwenden, um die Relevanzbewertung zu beeinflussen. Angenommen, wir möchten Artikel über "MySQL" finden, aber Artikel, die auch "indexing" erwähnen, priorisieren:

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;

Diese Abfrage erfordert "MySQL" und erhöht die Relevanz von Artikeln, die "indexing" enthalten. Die Ergebnisse werden dann nach Relevanz in absteigender Reihenfolge sortiert.

Die Ausgabe wird sein:

+----+--------------------------+---------------------------------------------------------------------+-----------+
| 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)

Beachten Sie, dass der Artikel "Indexing in MySQL" jetzt an erster Stelle erscheint, weil er aufgrund des Begriffs >indexing eine höhere Relevanzbewertung hat.

Durch Experimentieren mit verschiedenen Suchbegriffen und booleschen Operatoren können Sie Ihre Volltextabfragen optimieren, um die gewünschten Ergebnisse zu erzielen. Denken Sie immer daran, Ihre Abfragen gründlich zu testen, um sicherzustellen, dass sie wie erwartet funktionieren.

Dies schließt das Lab zur Volltextsuche in MySQL ab. Sie haben gelernt, wie Sie einen Volltextindex hinzufügen, grundlegende Suchen mit MATCH AGAINST durchführen, den booleschen Modus für erweiterte Suchen verwenden und Ihre Abfrageergebnisse testen.

Zusammenfassung

In diesem Lab haben wir zunächst eine Datenbank namens search_db und eine Tabelle articles mit den Spalten id, title und content erstellt. Anschließend wurden Beispielsdaten in die Tabelle eingefügt, um sie mit Artikeln zu füllen.

Der Kern dieses Schritts bestand darin, einen Volltextindex namens article_index für die Tabelle articles hinzuzufügen, speziell für die Spalten title und content, indem die ALTER TABLE-Anweisung verwendet wurde. Dies ermöglicht effiziente Textsuchen. Schließlich haben wir die Erstellung des Indexes mit dem Befehl SHOW INDEXES überprüft und bestätigt, dass der Index_type FULLTEXT ist.