Summary: in this tutorial, you will learn how to perform MySQL Boolean full-text searches. In addition, you will learn how to use Boolean operators to form very complex search queries.
Introduction to MySQL Boolean full-text searches
Besides the natural language full-text search, MySQL supports an additional form of full-text search that is called Boolean full-text search. In the Boolean mode, MySQL searches for words instead of the concept like in the natural language search.
MySQL allows you to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators. This is why the full-text search in Boolean mode is suitable for experienced users.
To perform a full-text search in the Boolean mode, you use the
IN BOOLEAN MODE modifier in the
AGAINST expression. The following example shows you how to search for a product whose product name contains the
SELECT productName, productline
AGAINST('Truck' IN BOOLEAN MODE )
Two products whose product names contain the
Truck word are returned.
To find the product whose product names contain the
Truck word but not any rows that contain
Pickup , you can use the exclude Boolean operator (
- ), which returns the result that excludes the
Pickup keyword as the following query:
SELECT productName, productline
WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE )
MySQL Boolean full-text search operators
The following table illustrates the full-text search Boolean operators and their meanings:
|+||Include, the word must be present.|
|–||Exclude, the word must not be present.|
|>||Include, and increase ranking value.|
|<||Include, and decrease the 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 the end of the word.|
|“”||Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).|
The following examples illustrate how to use boolean full-text operators in search query:
To search for rows that contain at least one of the two words: mysql or tutorial
To search for rows that contain both words: mysql and tutorial
To search for rows that contain the word “mysql”, but put the higher rank for the rows that contain “tutorial”:
To search for rows that contain the word “mysql” but not “tutorial”
To search for rows that contain word “mysql” and rank the row lower if it contains the word “tutorial”.
To search for rows that contain the words “mysql” and “tutorial”, or “mysql” and “training” in whatever order, but put the rows that contain “mysql tutorial” higher than “mysql training”.
‘+mysql +(>tutorial <training)’
To find rows that contain words starting with “my” such as “mysql”, “myyahoo”, etc., you use the following:
MySQL boolean full-text search main features
- MySQL does not automatically sort rows in the order of decreasing relevance in Boolean full-text search.
- To perform Boolean queries, InnoDB tables require all columns of the
MATCHexpression has a
FULLTEXTindex. Notice that MyISAM tables do not require this, although the search is quite slow.
- MySQL does not support multiple Boolean operators on a search query on InnoDB tables e.g., ‘++mysql’. MySQL will return an error if you do so. However, MyISAM behaves differently. It ignores other operators and uses the operator that is closest to the search word, for example, ‘+-mysql’ will become ‘-mysql’.
- InnoDB full-text search does not support trailing plus (+) or minus (-) sign. It only supports leading plus or minus sign. MySQL will report an error if you search word is ‘mysql+’ or ‘mysql-‘. In addition, the following leading plus or minus with wildcard are invalid: +*, +-
- The 50% threshold is not applied. By the way, 50% threshold means if a word appears in more than 50% of the rows, MySQL will ignore it in the search result.
In this tutorial, we have shown you how to perform MySQL Boolean full-text searches with many useful Boolean operators.