Database Reference
In-Depth Information
Note
IN BOOLEAN MODE
Behaves Differently Although the results in this example are
the same as they would be without
IN BOOLEAN MODE
, there is an important dif-
ference in behavior (even if it did not manifest itself in this particular example). I point
these out in the “Full-Text Search Usage Notes” section later in this chapter.
To match the rows that contain
heavy
but not any word beginning with
rope
, the following can be used:
▼
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
▼
Output
+---------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------+
| Customer complaint: Not heavy enough to generate flying stars around head |
| of victim. If being purchased for dropping, recommend ANV02 or ANV03 |
| instead. |
+---------------------------------------------------------------------------+
▼
Analysis
This time only one row is returned. Again, the word
heavy
is matched, but
this time
-rope*
instructs MariaDB to explicitly exclude any row that contains
rope*
(any word beginning with
rope
, including
ropes
, which is why one
of the rows was excluded).
You have now seen two full-text search boolean operators:
-
excludes a word
and
*
is the truncation operator (think of it as a wildcard used at the end of a
word). Table 18.1 lists all the supported boolean operators.
Table 18.1
Full-Text Boolean Operators
Privilege
Description
+
Include, word must be present.
-
Exclude, word must not be present.
>
Include, and increase ranking value.
<
Include, and decrease ranking value.
()
Group words into subexpressions (allowing them to be included,
excluded, ranked, and so forth as a group).
~
Negate a word's ranking value.
*
Wildcard at end of word.
""
Defines a phrase. (As opposed to a list of individual words,
the entire phrase is matched for inclusion or exclusion.)
