[+/-]
MATCH
(
col1
,col2
,...)
AGAINST (expr
[IN BOOLEAN MODE |
WITH QUERY EXPANSION])
MySQL unterstützt die Volltextindizierung und -suche. Ein
Volltextindex ist in MySQL ein Index des Typs
FULLTEXT
.
FULLTEXT
-Indizes können nur bei
MyISAM
-Tabellen eingesetzt werden. Sie
lassen sich für CHAR
-,
VARCHAR
- und
TEXT
-Spalten mit CREATE
TABLE
erstellen oder mit ALTER
TABLE
oder CREATE INDEX
im
Nachhinein hinzufügen. Bei größeren Datenmengen erfolgt das
Laden Ihrer Daten in eine Tabelle ohne
FULLTEXT
-Index und das nachfolgende
Erstellen des Indexes wesentlich schneller als das Einladen in
eine Tabelle mit vorhandenem
FULLTEXT
-Index.
Hinweise zu Einschränkungen bei der Volltextsuche finden Sie in Abschnitt 12.7.4, „Beschränkungen der Volltextsuche“.
Die Volltextsuche wird mit der Funktion MATCH()
ausgeführt.
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 (title,body) VALUES
->('MySQL Tutorial','DBMS stands for DataBase ...'),
->('How To Use MySQL Well','After you went through a ...'),
->('Optimizing MySQL','In this tutorial we will show ...'),
->('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
->('MySQL vs. YourSQL','In the following database comparison ...'),
->('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)
Die Funktion MATCH()
führt eine
natursprachliche Suche nach einem String in einer
Textsammlung durch. Eine Sammlung ist eine
Menge von einer oder mehreren Spalten, die Bestandteil eines
FULLTEXT
-Indexes sind. Der Such-String wird als
Argument an AGAINST()
übergeben. Für jeden
Datensatz in der Tabelle gibt MATCH()
einen
Relevanzwert zurück, d. h. eine Maßangabe für die Ähnlichkeit
zwischen dem Such-String und dem Text in den in
MATCH()
aufgelisteten Spalten dieses
Datensatzes.
Standardmäßig wird die Suche ohne Unterscheidung der
Groß-/Kleinschreibung durchgeführt. Sie können jedoch auch eine
Suche unter Berücksichtigung der Groß-/Kleinschreibung
durchführen, indem Sie eine binäre Sortierung für die
indizierten Spalten verwenden. So können Sie etwa einer Spalte,
die den Zeichensatz latin1
verwendet, die
Sortierung latin1_bin
zuweisen, damit bei der
Volltextsuche die Groß-/Kleinschreibung unterschieden wird.
Wenn MATCH()
in einer
WHERE
-Klausel verwendet wird (vgl. obiges
Beispiel), dann werden die zurückgegebenen Datensätze
automatisch nach absteigender Relevanz sortiert. Relevanzwerte
sind nichtnegative Fließkommazahlen. Nullrelevanz bezeichnet
keinerlei Ähnlichkeit. Die Relevanz wird auf der Basis der Anzahl
der Wörter im Datensatz, der Anzahl eindeutiger Wörter im
Datensatz, der Gesamtanzahl der Wörter in der Sammlung und der
Anzahl der Dokumente (Datensätze) berechnet, die ein bestimmtes
Wort enthalten.
Bei der natursprachlichen Volltextsuche ist es erforderlich, dass
die in der MATCH()
-Funktion genannten Spalten
dieselben Spalten sind, die in einem in Ihrer Tabelle vorhandenen
FULLTEXT
-Index enthalten sind. Beachten Sie bei
der obigen Abfrage, dass die in der
MATCH()
-Funktion aufgeführten Spalten
(title
und body
) dieselben
sind, die in der Definition des
FULLTEXT
-Indexes der Tabelle
article
genannt sind. Sollten Sie nun
title
oder body
separat
durchsuchen wollen, dann müssten Sie für jede Spalte separate
FULLTEXT
-Indizes erstellen.
Es ist auch möglich, eine boolesche Suche oder eine Suche mit Abfrageerweiterung durchzuführen. Diese Suchtypen sind in Abschnitt 12.7.1, „Boolesche Volltextsuche“, und Abschnitt 12.7.2, „Volltextsuche mit Abfragenerweiterung“, beschrieben.
Das obige Beispiel ist eine einfache Veranschaulichung, die zeigt,
wie man die Funktion MATCH()
verwendet, wobei
Datensätze nach absteigender Relevanz sortiert zurückgegeben
werden. Das nächste Beispiel zeigt, wie man die Relevanzwerte
explizit abruft. Zurückgegebene Datensätze werden nicht
sortiert, weil die SELECT
-Anweisung weder eine
WHERE
- noch eine ORDER
BY
-Klausel enthält:
mysql>SELECT id, MATCH (title,body) AGAINST ('Tutorial')
->FROM articles;
+----+-----------------------------------------+ | id | MATCH (title,body) AGAINST ('Tutorial') | +----+-----------------------------------------+ | 1 | 0.65545833110809 | | 2 | 0 | | 3 | 0.66266459226608 | | 4 | 0 | | 5 | 0 | | 6 | 0 | +----+-----------------------------------------+ 6 rows in set (0.00 sec)
Das folgende Beispiel ist komplexer. Die Abfrage gibt die
Relevanzwerte zurück und sortiert die Datensätze zudem nach
absteigender Relevanz. Um dieses Ergebnis zu erhalten, sollten Sie
MATCH()
zweimal angeben: einmal in der
SELECT
-Liste und ein weiteres Mal in der
WHERE
-Klausel. Hierdurch wird die
Systembelastung nicht erhöht, da der MySQL-Optimierer bemerkt,
dass die beiden MATCH()
-Aufrufe identisch sind,
und den Code für die Volltextsuche insofern nur einmal aufruft.
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.5219271183014 | | 6 | When configured properly, MySQL ... | 1.3114095926285 | +----+-------------------------------------+-----------------+ 2 rows in set (0.00 sec)
Die FULLTEXT
-Implementierung von MySQL
betrachtet alle Folgen echter Wortzeichen (Buchstaben, Ziffern und
Unterstriche) als Wort. Die Folge kann auch Apostrophe
('
) enthalten, aber nur eines je Datensatz. Das
bedeutet, dass aaa'bbb
als ein Wort betrachtet
wird, aaa''bbb
hingegen als zwei Wörter.
Apostrophe am Anfang oder Ende eines Wortes werden vom
FULLTEXT
-Parser entfernt, d. h., er
verarbeitet 'aaa'bbb'
als
aaa'bbb
.
Der FULLTEXT
-Parser bestimmt anhand bestimmter
Trennzeichen wie ‘
’
(Leerzeichen), ‘,
’ (Komma), und
‘.
’ (Punkt), wo Wörter anfangen
und enden. Wenn Wörter nicht durch Trennzeichen getrennt werden
(wie es beispielsweise im Chinesischen der Fall ist), kann der
FULLTEXT
-Parser nicht ermitteln, wo ein Wort
beginnt oder endet. Damit Wörter oder andere indizierte Begriffe
in solchen Sprachen einem FULLTEXT
-Index
hinzugefügt werden können, müssen Sie eine Vorabverarbeitung
durchführen und sie so durch ein beliebiges Trennzeichen (z. B.
‘"
’) voneinander abtrennen.
In MySQL 5.1 ist es möglich, ein Plug-In zu
schreiben, das den integrierten Volltext-Parser ersetzt.
Detaillierte Informationen finden Sie in
Abschnitt 26.2, „Die MySQL-Plug-In-Schnittstelle“. Ein Beispiel für den Quellcode
eines solchen Parser-Plug-Ins finden Sie im Verzeichnis
plugin/fulltext
einer
MySQL-Quelldistribution.
Einige Wörter werden bei der Volltextsuche ignoriert:
Ignoriert werden alle Wörter, die zu kurz sind. Die standardmäßige Mindestlänge von Wörtern, die von der Volltextsuche gefunden werden, beträgt vier Zeichen.
Auch Wörter, die auf der Liste der Stoppwörter stehen, werden ignoriert. Ein Stoppwort ist ein Wort wie „the“ oder „some“, das so verbreitet ist, dass sein semantischer Wert als vernachlässigbar betrachtet wird. Es gibt eine bereits vorhandene Liste mit Stoppwörtern, die aber mit einer benutzerdefinierten Liste überschrieben werden kann.
Die Standardliste mit den Stoppwörtern finden Sie in Abschnitt 12.7.3, „Stoppwörter in der Volltextsuche“. Mindestwortlänge und Stoppwortliste lassen sich wie in Abschnitt 12.7.5, „MySQL-Volltextsuche feineinstellen“, beschrieben ändern.
Jedes korrekte Wort in der Sammlung und in der Abfrage wird entsprechend seiner Bedeutung in der Sammlung oder Abfrage gewichtet. Hieraus ergibt sich, dass ein Wort, dass in vielen Dokumenten vorhanden ist, ein niedrigeres Gewicht (oder sogar ein Nullgewicht) hat, weil sein semantischer Wert in dieser speziellen Sammlung geringer ist. Umgekehrt erhält ein Wort, das selten vorkommt, ein höheres Gewicht. Die Gewichtungen der Wörter werden zusammengefasst, und auf dieser Basis wird die Relevanz des Datensatzes berechnet.
Eine solche Methode funktioniert am besten mit großen Sammlungen
(und sie wurde auch speziell auf diesen Zweck hin sorgfältig
optimiert). Bei sehr kleinen Tabellen spiegelt die Wortverteilung
ihren jeweiligen semantischen Wert nicht adäquat wider. So ist
etwa das Wort „MySQL“ in jedem Datensatz der oben
gezeigten Tabelle articles
vorhanden, d. h.,
eine Suche nach diesem Wort führt zu keinem Ergebnis:
mysql>SELECT * FROM articles
->WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)
Das Suchergebnis ist leer, weil das Wort „MySQL“ in mindestens 50 Prozent der Datensätze vorhanden ist. Insofern wird es letztendlich als Stoppwort behandelt. Bei großen Datenmengen ist dieses Verhalten wünschenswert: Eine natursprachliche Abfrage sollte keinesfalls jeden zweiten Datensatz aus einer 1 Gbyte großen Tabelle zurückgeben. Bei kleinen Datenmengen hingegen ist der Ansatz nicht sehr praktisch.
Ein Wort, das in der Hälfte der Datensätze in einer Tabelle auftritt, scheint wenig geeignet, relevante Dokumente zu finden. Tatsächlich werden in einem solchen Fall viele irrelevante Dokumente gefunden. Wie wir alle wissen, passiert dies viel zu häufig, etwa wenn wir versuchen, mit einer Suchmaschine etwas im Internet zu finden. Hieraus ist zu schließen, dass Datensätzen, die das Wort enthalten, ein niedriger semantischer Wert für die spezielle Datenmenge, in der sie auftreten, zugewiesen wird. Denn ein Wort kann die 50-Prozent-Marke in einer Datenmenge überschreiten, in einer anderen jedoch nicht.
Diese Marke hat eine große Bedeutung, wenn Sie die Volltextsuche zum ersten Mal ausprobieren, um zu sehen, wie sie funktioniert: Wenn Sie eine Tabelle erstellen und nur einen oder zwei Datensätze mit Text einfügen, tritt jedes Wort im Text in mindestens 50 Prozent aller Datensätze auf. Die Suche gibt also in keinem Fall Ergebnisse zurück. Sie sollten also mindestens drei (und am besten noch mehr) Datensätze einfügen. Benutzer, die die 50-Prozent-Grenze umgehen wollen, können den booleschen Suchmodus verwenden. Siehe auch Abschnitt 12.7.1, „Boolesche Volltextsuche“.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.