MySQL Boolean Full-Text Searches

MySQL provides us with multiple methods for searching functionalities. The “LIKE” clause is supposed as the best searching method for easy and less complex use cases. For complex business logic, there are other alternative methods that are very effective, suitable, and easy to implement.

In this tutorial, we will learn about the boolean full-text search which is provided by MySQL along with natural language full-text search. Along with the theoretical explanation, we will also see some examples of boolean full-text searches.

What is Boolean Full-text Search?

MySQL boolean search is one of the types of a full-text search. Unlike natural language full-text search where the concept is used, the words are used in the boolean full-text search. It enables us to conduct a search using extremely complex queries that include Boolean operators such as the less than () and greater than (>) operators, the plus (+) and minus (-) signs, subexpressions (“(” and “)”), double quotes (“”), an operator that reduces the value’s contribution to the results (~), and wildcards.

Syntax of Boolean Full-text Search

We can perform a boolean full-text search using the IN BOOLEAN MODE modifier with the AGAINST clause. The following syntax shows how to use the boolean full-text search.

SELECT col_name FROM table_name
WHERE MATCH(target_col_names) 
AGAINST(expression IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Where,

• target_col_names – Names of the column that you want to search the keyword in.
• expression – List of keywords with valid boolean operators.

You might have understood the basic concept of the boolean full-text search method. Let’s take some examples of it so that you can understand it at best. Before taking examples, we must know which operators are supported in the boolean search method and what they indicate. Look at the following table for boolean search operators.

Before Proceeding to Boolean Full-text Search

When particular indexes are used in MySQL, full-text searches are done, and such indexes have numerous unique characteristics, including the following:

• The index must be of the FULLTEXT type in order to be classified as a full-text index.
• Only tables using the InnoDB or MyISAM storage engines can use FULLTEXT indexes.
• Only CHAR, VARCHAR, and TEXT columns can have FULLTEXT indexes.
• When the MATCH() AGAINST() clause is used, FULLTEXT indexes are utilized.

You can easily create a full-text index on a single column as well as on multiple columns. Here is the StackOverflow thread for creating a full-text index on a single column and multiple columns.

Examples of Boolean Full-text Search

For the demonstration of the boolean full-text search method, we will create a table and insert some data into it.

CREATE TABLE bftSearch(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
body VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO bftSearch(title,body)
VALUES("mysql tutorial", "this is a mysql tutorial"),
("ml tutorial", "not a mysql tutorial"),
("android tut", "use firebase"),
("system programing", "8086 microprocessor"),
("datastructure & myDB", "mysql with DS"),
("advanced mysql", "data mining and graph database");Code language: SQL (Structured Query Language) (sql)

Let’s display the table data to check everything is inserted without an error.

SELECT * FROM bftSearch;Code language: SQL (Structured Query Language) (sql)

Perfect! Now let’s write a simple query demonstrating the boolean search method.

SELECT * FROM bftSearch
WHERE MATCH(title, body)
AGAINST('mysql' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Here, we try to display all records where there is a ‘mysql’ keyword present in the title and body column. Note that, the keyword should be present in at least one column from the mentioned column list.

As you can see, the record of id 5 does not contain the keyword ‘mysql’ in the title column but it is present in the body column. Hence, we get that record in the output.

Now, let’s take another example where we try to display the data where records contain the keyword “mysql” but not “tutorial”.

SELECT * FROM bftSearch
WHERE MATCH(title, body)
AGAINST('+mysql -tutorial' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Here, we are using include(+) and exclude(-) operators to specify which keywords should be present in the output result and which shouldn’t be.

As you can see, none of the output records contains the keyword “tutorial” but does contain “mysql”.

Now, the following are the problem statements along with expressions to be used for boolean full-text searches in your queries.

To find entries that contain at least one of the two words: mysql or tutorial,

‘mysql tutorial’Code language: SQL (Structured Query Language) (sql)

To find entries that contain both the phrases: mysql and tutorial

‘+mysql +tutorial’Code language: SQL (Structured Query Language) (sql)

To find rows that contain the term “mysql,” but give the rows that have “tutorial” a higher ranking:

‘+mysql tutorial’Code language: SQL (Structured Query Language) (sql)

To find rows that contain the term “mysql” but not “tutorial,”

+mysql -tutorial’Code language: SQL (Structured Query Language) (sql)

To find rows containing the term “mysql” and rank them lower if they contain the word “tutorial.”

‘+mysql ~tutorial’Code language: SQL (Structured Query Language) (sql)

To find rows containing the phrases “mysql” and “tutorial,” or “mysql” and “training,” in any sequence, but with “mysql tutorial” appearing higher than “mysql training.”

‘+mysql +(>tutorial <training)’Code language: SQL (Structured Query Language) (sql)

To discover rows containing terms beginning with “my,” such as “mysql,” “mydatabase,” and so on, use the following expression:

‘my*’Code language: SQL (Structured Query Language) (sql)

Conclusion

In this tutorial, we have learned what is boolean full-text search, how it works and how to implement it through practical examples. We have also learned how to create a full-text index for a full-text search. I hope this tutorial helped you learn the boolean full-text search. We recommend you implement it in your projects for better filtering and sorting outputs. Stay tuned to mysqlcode.com for more interesting tutorials!

References

MySQL official documentation on boolean full-text search.