Database Reference
In-Depth Information
Another useful special character in Boolean searches is
*
; when appended to a search
word, it acts as a wildcard operator. The following statement finds rows containing not
only
whirl
, but also words such as
whirls
,
whirleth
, and
whirlwind
:
mysql>
SELECT COUNT(*) FROM kjv
->
WHERE MATCH(vtext) AGAINST('whirl*' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 28 |
+----------+
For the complete list of Boolean full-text operators, see the
MySQL Reference Manual
.
5.15. Performing Full-Text Phrase Searches
Problem
You want to perform a full-text search for a phrase; that is, for words that occur adjacent
to each other and in a specific order.
Solution
Use the full-text phrase-search capability.
Discussion
To find rows that contain a particular phrase, a simple full-text search doesn't work:
mysql>
SELECT COUNT(*) FROM kjv
->
WHERE MATCH(vtext) AGAINST('still small voice');
+----------+
| COUNT(*) |
+----------+
| 548 |
+----------+
The query returns a result, but not the one you're looking for. A full-text search computes
a relevance ranking based on the presence of each word individually, no matter where
it occurs within the
vtext
column, and the ranking is nonzero as long as any of the
words are present. Consequently, that kind of statement tends to find too many rows.
Instead, use full-text Boolean mode, which supports phrase searching. Enclose the
phrase in double quotes within the search string:
