MySQL Volltextsuchfunktionen

Einführung

In diesem Lab erkunden Sie die Volltextsuchfunktionen von MySQL. Sie beginnen mit der Erstellung einer Datenbank und einer Tabelle namens articles mit den Spalten id, title und content und füllen diese dann mit Beispieldaten.

Als Nächstes fügen Sie der Tabelle articles einen Volltextindex namens article_index hinzu, der speziell auf die Spalten title und content abzielt, indem Sie die Anweisung ALTER TABLE verwenden. Abschließend überprüfen Sie die Indexerstellung mit dem Befehl SHOW INDEXES und stellen sicher, dass der Index_type FULLTEXT ist. Diese Einrichtung bereitet Sie auf effiziente Textsuchvorgänge in Ihrer MySQL-Datenbank vor.

Während dieses Labs arbeiten Sie innerhalb der MySQL-Shell. Sie müssen die MySQL-Shell nur zu Beginn aufrufen und am Ende beenden. Alle SQL-Befehle in den folgenden Schritten sollten innerhalb derselben MySQL-Sitzung ausgeführt werden.

Datenbank und Tabelle erstellen

In diesem Schritt erstellen Sie eine MySQL-Datenbank und eine Tabelle zur Speicherung von Artikeln. Diese Tabelle wird verwendet, um die Volltextsuchfunktionen zu demonstrieren.

Öffnen Sie zunächst Ihr Terminal und verbinden Sie sich als Root-Benutzer mit dem MySQL-Server. Dies können Sie mit dem folgenden Befehl tun:

sudo mysql -u root

Dieser Befehl stellt eine Verbindung zum MySQL-Server unter Verwendung des Benutzers root her. Da Sie sudo verwenden, werden Sie nicht nach einem Passwort gefragt.

Sobald Sie mit der MySQL-Shell verbunden sind, sehen Sie die Eingabeaufforderung mysql>. Bleiben Sie in der MySQL-Shell für alle nachfolgenden Schritte bis zum Ende des Labs.

Erstellen wir nun eine Datenbank namens search_db. Diese Datenbank wird unsere Tabelle articles enthalten. Führen Sie den folgenden SQL-Befehl aus:

CREATE DATABASE IF NOT EXISTS search_db;

Die Klausel IF NOT EXISTS stellt sicher, dass die Datenbank nur erstellt wird, wenn sie noch nicht existiert.

Wechseln Sie als Nächstes mit dem Befehl USE zur neu erstellten Datenbank:

USE search_db;

Sie sollten eine Meldung sehen, die anzeigt, dass die Datenbank geändert wurde.

Erstellen Sie nun die Tabelle articles. Diese Tabelle wird drei Spalten haben: id, title und content.

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

Lassen Sie uns diesen Befehl aufschlüsseln:

• CREATE TABLE articles: Diese Anweisung erstellt eine neue Tabelle namens articles.
• id INT AUTO_INCREMENT PRIMARY KEY: Dies definiert eine Integer-Spalte namens id, die für jede neue Zeile automatisch inkrementiert wird und als Primärschlüssel für die Tabelle dient.
• title VARCHAR(255) NOT NULL: Dies definiert eine String-Spalte namens title mit einer maximalen Länge von 255 Zeichen. NOT NULL bedeutet, dass diese Spalte nicht leer sein darf.
• content TEXT: Dies definiert eine Textspalte namens content, die größere Zeichenketten speichern kann.

Nachdem Sie diesen Befehl ausgeführt haben, wird die Tabelle articles in der Datenbank search_db erstellt.

Sie können die Tabellenerstellung überprüfen, indem Sie die Tabellen in der aktuellen Datenbank auflisten:

SHOW TABLES;

Sie sollten articles in der Ausgabe sehen.

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

Nachdem Sie nun die Datenbank und die Tabelle erstellt haben, sind Sie bereit, diese im nächsten Schritt mit einigen Beispieldaten zu füllen.

Beispieldaten einfügen und Volltextindex hinzufügen

In diesem Schritt fügen Sie Beispieldaten in die Tabelle articles ein und fügen dann einen Volltextindex hinzu, um eine effiziente Textsuche zu ermöglichen.

Stellen Sie sicher, dass Sie sich immer noch in der MySQL-Shell befinden und die Datenbank search_db verwenden. Wenn nicht, wählen Sie die Datenbank aus:

USE search_db;

Fügen wir nun einige Beispieldaten in die Tabelle articles ein. Wir fügen drei Zeilen mit unterschiedlichen Titeln und Inhalten hinzu:

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

Diese INSERT-Anweisung fügt mehrere Zeilen in die Tabelle articles ein. Jeder Klammersatz nach VALUES stellt eine neue Zeile dar, wobei die Werte den Spalten title und content entsprechen.

Sie können überprüfen, ob die Daten eingefügt wurden, indem Sie alle Zeilen aus der Tabelle auswählen:

SELECT * FROM articles;

Sie sollten die drei eingefügten Zeilen in der 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)

Nachdem die Tabelle nun Daten enthält, fügen wir einen Volltextindex hinzu. Ein Volltextindex ermöglicht es MySQL, schnelle und relevante Suchen in Textdaten durchzuführen. Wir fügen einen Volltextindex sowohl für die Spalten title als auch content hinzu.

Verwenden Sie die Anweisung ALTER TABLE, um den Index hinzuzufügen:

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

Lassen Sie uns diesen Befehl aufschlüsseln:

• ALTER TABLE articles: Dies zeigt an, dass wir die Tabelle articles ändern.
• ADD FULLTEXT INDEX article_index: Dies fügt einen neuen Index vom Typ FULLTEXT hinzu und benennt ihn article_index.
• (title, content): Dies gibt die Spalten an, die in den Volltextindex aufgenommen werden.

Nachdem Sie diesen Befehl ausgeführt haben, erstellt MySQL den Volltextindex für die angegebenen Spalten.

Um zu überprüfen, ob der Volltextindex erfolgreich erstellt wurde, können Sie den Befehl SHOW INDEXES verwenden:

SHOW INDEXES FROM articles;

Die Ausgabe zeigt alle Indizes der Tabelle articles. Sie sollten einen Eintrag für article_index mit FULLTEXT in der Spalte Index_type sehen.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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 Daten eingefügt und einen Volltextindex zu Ihrer Tabelle hinzugefügt. Im nächsten Schritt lernen Sie, wie Sie diesen Index für Volltextsuchanfragen verwenden.

Einfache Volltextsuche durchführen

In diesem Schritt führen Sie grundlegende Volltextsuchanfragen in der Tabelle articles mithilfe der Klausel MATCH AGAINST durch. Dies ist die grundlegende Methode, um den von Ihnen erstellten Volltextindex zu nutzen.

Stellen Sie sicher, dass Sie sich immer noch in der MySQL-Shell befinden und die Datenbank search_db verwenden. Wenn nicht, wählen Sie die Datenbank aus:

USE search_db;

Die Klausel MATCH AGAINST wird innerhalb der WHERE-Klausel einer SELECT-Anweisung verwendet. Die grundlegende Syntax lautet:

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

Hier ist column_list eine durch Kommas getrennte Liste der Spalten, die in den Volltextindex aufgenommen wurden (in unserem Fall title und content), und 'search_term' ist das Wort oder die Phrase, nach der Sie suchen möchten.

Suchen wir nach Artikeln, die das Wort "MySQL" enthalten:

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

Diese Abfrage wählt die Spalten id, title und content aus der Tabelle articles aus, bei denen die Spalten title oder content mit dem Begriff "MySQL" übereinstimmen.

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)

Alle drei Artikel werden zurückgegeben, da sie alle das Wort "MySQL" enthalten.

Die Volltextsuche liefert auch einen Relevanz-Score, der angibt, wie gut jede Zeile mit dem Suchbegriff übereinstimmt. Sie können diesen Score in Ihre SELECT-Anweisung aufnehmen:

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

Diese Abfrage fügt eine Spalte namens relevance hinzu, die den Score für jede Zeile anzeigt.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 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 nach Relevanz sortieren, um die besten Treffer zuerst zu sehen:

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

Diese Abfrage sortiert die Ergebnisse basierend auf dem relevance-Score 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 grundlegende Volltextsuchanfragen mit MATCH AGAINST durchführen und wie Sie Ergebnisse nach Relevanz abrufen und sortieren. Im nächsten Schritt werden Sie fortgeschrittenere Suchoptionen mithilfe des booleschen Modus untersuchen.

Booleschen Modus für erweiterte Suche verwenden

In diesem Schritt verwenden Sie den booleschen Modus der Klausel MATCH AGAINST, um präzisere und flexiblere Volltextsuchanfragen durchzuführen. Der boolesche Modus ermöglicht die Verwendung von Operatoren, um zu steuern, welche Wörter in den Ergebnissen vorhanden sein müssen oder nicht dürfen.

Stellen Sie sicher, dass Sie sich immer noch in der MySQL-Shell befinden und die Datenbank search_db verwenden. Wenn nicht, wählen Sie die Datenbank aus:

USE search_db;

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

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

Hier sind einige gängige Operatoren, die im booleschen Modus verwendet werden:

• +: Erfordert, dass das Wort im Ergebnis vorhanden ist.
• -: Schließt Zeilen aus, die das Wort enthalten.
• "": Sucht nach einer exakten Phrase.
• *: Platzhalter-Operator (am Ende eines Wortes).

Suchen wir nach Artikeln, die das Wort "MySQL" enthalten müssen und das Wort "optimizing" nicht enthalten dürfen.

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

Diese Abfrage verwendet den Operator +, um "MySQL" zu erfordern, und den Operator -, um "optimizing" auszuschließen.

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)

Der Artikel über "Optimizing MySQL Queries" wird ausgeschlossen, da er "optimizing" enthält.

Suchen wir nun nach der exakten Phrase "full-text search":

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

Die Verwendung von doppelten Anführungszeichen sucht nach den Wörtern als zusammenhängende Phrase.

Die Ausgabe wird sein:

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

Nur der erste Artikel wird zurückgegeben, da er die exakte Phrase "full-text search" enthält.

Versuchen wir, den Platzhalter-Operator * zu verwenden. Angenommen, Sie möchten Artikel finden, die Wörter enthalten, die mit "index" beginnen.

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

Der Begriff index* passt auf Wörter wie "index" und "indexing".

Die Ausgabe wird sein:

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

In diesem Schritt haben Sie gelernt, wie Sie den booleschen Modus mit der Klausel MATCH AGAINST und verschiedenen Operatoren verwenden, um kontrolliertere und spezifischere Volltextsuchanfragen durchzuführen. Im nächsten Schritt üben Sie die Kombination dieser Techniken.

Suchtechniken kombinieren

In diesem Schritt kombinieren Sie die grundlegende MATCH AGAINST-Suche mit booleschen Modus-Operatoren, um komplexere und verfeinerte Volltextabfragen durchzuführen.

Stellen Sie sicher, dass Sie sich immer noch in der MySQL-Shell befinden und die Datenbank search_db verwenden. Wenn nicht, wählen Sie die Datenbank aus:

USE search_db;

Versuchen wir, Artikel zu finden, die das Wort "MySQL" und entweder das Wort "indexing" oder "optimizing" enthalten. Wir können Klammern verwenden, um die optionalen Begriffe im booleschen Modus zu gruppieren.

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

Diese Abfrage erfordert "MySQL" (+MySQL) und mindestens einen der Begriffe in den Klammern (+(indexing 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)

Der erste Artikel wird ausgeschlossen, da er "MySQL" enthält, aber weder "indexing" noch "optimizing".

Sie können den booleschen Modus auch mit Relevanz-Scoring kombinieren. Suchen wir nach Artikeln, die "MySQL" enthalten, und priorisieren Sie diejenigen, die auch "indexing" erwähnen, indem Sie den Operator > verwenden.

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 den Relevanz-Score für Artikel, die "indexing" enthalten. Die Ergebnisse werden dann nach der berechneten Relevanz 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" aufgrund des Operators >indexing am höchsten eingestuft wird.

Durch die Kombination von grundlegender Suche mit booleschen Operatoren und Relevanz-Scoring können Sie leistungsstarke und flexible Volltextsuchanfragen erstellen, die auf Ihre spezifischen Bedürfnisse zugeschnitten sind.

Damit ist das Lab zu den Volltextsuchfunktionen von MySQL abgeschlossen. Sie können die MySQL-Shell nun verlassen, indem Sie Folgendes eingeben:

exit

Sie haben gelernt, wie Sie eine Tabelle mit einem Volltextindex einrichten, grundlegende Suchen durchführen und den booleschen Modus für fortgeschrittenere Abfragen verwenden.

Zusammenfassung

In diesem Lab haben Sie sich mit den Volltextsuchfunktionen von MySQL beschäftigt. Sie haben mit der Erstellung einer Datenbank namens search_db und einer Tabelle namens articles zur Speicherung von Textdaten begonnen. Anschließend haben Sie die Tabelle mit Beispieldatensätzen gefüllt.

Der wichtigste Schritt war das Hinzufügen eines Volltextindexes namens article_index zu den Spalten title und content der Tabelle articles mithilfe der Anweisung ALTER TABLE. Dieser Index ist für eine effiziente Volltextsuche unerlässlich. Sie haben die Indexerstellung mit SHOW INDEXES überprüft.

Schließlich haben Sie untersucht, wie Volltextsuchanfragen mit der Klausel MATCH AGAINST durchgeführt werden. Sie haben gelernt, wie Sie grundlegende Suchen durchführen, Relevanz-Scores abrufen und Ergebnisse nach Relevanz sortieren. Sie haben sich auch mit dem booleschen Modus befasst und Operatoren wie +, -, "" und * verwendet, um präzisere und komplexere Suchanfragen zu erstellen. Durch die Kombination dieser Techniken können Sie relevante Informationen aus Textdaten, die in Ihrer MySQL-Datenbank gespeichert sind, effektiv suchen und abrufen.