Boolean Full-Text Searches

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier.

mysql> SELECT * FROM articles WHERE MATCH (title,body)
 -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title   | body    |
+----+-----------------------+-------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

This query retrieves all the rows that contain the word ``MySQL'' but that do not contain the word ``YourSQL''.

Boolean full-text searches have these characteristics:

• They do not use the 50% threshold.
• They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains ``MySQL'' twice, but it is listed last, not first.
• They can work even without a FULLTEXT index, although this would be slow.
• The minimum and maximum word length full-text parameters apply.
• The stopword list applies.

The boolean full-text search capability supports the following operators:

+

A leading plus sign indicates that this word must be present in every row returned.

-

A leading minus sign indicates that this word must not be present in any row returned.

(no operator)

By default (when neither + nor - is specified) the word is optional, but the rows that contain it will be rated higher. This mimics the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.

> <

These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it. See the example below.

( )

Parentheses are used to group words into subexpressions. Parenthesized groups can be nested.

~

A leading tilde acts as a negation operator, causing the word's contribution to the row relevance to be negative. It's useful for marking noise words. A row that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the - operator.

*

An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word.

"

A phrase that is enclosed within double quote (`"') characters matches only rows that contain the phrase literally, as it was typed.

The following examples demonstrate some search strings that use boolean full-text operators:

'apple banana'

Find rows that contain at least one of the two words.

'+apple +juice'

Find rows that contain both words.

'+apple macintosh'

Find rows that contain the word ``apple'', but rank rows higher if they also contain ``macintosh''.

'+apple -macintosh'

Find rows that contain the word ``apple'' but not ``macintosh''.

'+apple +(>turnover <strudel)'

Find rows that contain the words ``apple'' and ``turnover'', or ``apple'' and ``strudel'' (in any order), but rank ``apple turnover'' higher than ``apple strudel''.

'apple*'

Find rows that contain words such as ``apple'', ``apples'', ``applesauce'', or ``applet''.

'"some words"'

Find rows that contain the exact phrase ``some words'' (for example, rows that contain ``some words of wisdom'' but not ``some noise words''). Note that the `"' characters that surround the phrase are operator characters that delimit the phrase. They are not the quotes that surround the search string itself.