Boolean Text Searches in MySQL
Summary: In this tutorial, you will learn how to perform boolean text searches in MySQL.
Beside natural language search, MySQL supports an additional form of full-text searching which is known as Boolean mode. In Boolean mode, MySQL searches for words instead of ‘concept’ like natural language search. You can perform a very complex query in Boolean mode along with Boolean operators therefore full-text search in Boolean mode is suitable for experience users.
To perform full-text search in Boolean mode you use the keyword IN BOOLEAN MODE. For example, see the following query:
SELECT productName, productline
FROM products
WHERE MATCH(productName)
AGAINST('Truck' IN BOOLEAN MODE )
+------------------------+------------------+
| productName | productline |
+------------------------+------------------+
| 1940 Ford Pickup Truck | Trucks and Buses |
| 1940s Ford truck | Trucks and Buses |
+------------------------+------------------+
2 rows in set (0.00 sec)
Exactly two rows which product name has word ‘Truck’ are returned.
To match the rows that contain ‘Truck’ but not any word beginning with ‘Pickup’, you can use boolean operator - which excludes the word in the results. Here is the query:
SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE )
+------------------+------------------+
| productName | productline |
+------------------+------------------+
| 1940s Ford truck | Trucks and Buses |
+------------------+------------------+
1 row in set (0.00 sec)
Here is the list of full-text Boolean operators and their meanings:
|
+
|
Include, word must be present.
|
|
-
|
Exclude, word must not be present.
|
|
>
|
Include, and increase ranking value.
|
|
<
|
Include, and decrease ranking value.
|
|
()
|
Group words into sub expressions (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).
|