MySQL Boolean Full-Text Searches

Summary: in this tutorial, you will learn how to perform MySQL Boolean full-text searches, utilizing 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, as in the natural language search.

MySQL allows you to perform a full-text search based on highly complex queries in Boolean mode, leveraging Boolean operators. This is why the full-text search in Boolean mode is best suited for experienced users.

Let’s create a sample table and perform a full-text search using the boolean mode to understand how it works.

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contents TEXT,
    FULLTEXT ( contents )
);

INSERT INTO documents(contents)
VALUES
('MySQL Database'),
('MySQL'),
('Database'),
('SQL'),
('A fork of MySQL'),
('SQLite');Code language: SQL (Structured Query Language) (sql)

The following example shows you how to search for documents whose contents have the word “MySQL”:

SELECT 
  id, 
  contents 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('mysql' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------------+
| id | contents        |
+----+-----------------+
|  1 | MySQL Database  |
|  2 | MySQL           |
|  5 | A fork of MySQL |
+----+-----------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

In this example, we use the Boolean mode with the IN BOOLEAN MODE modifier in the AGAINST() function to perform boolean full-text searches.

To find the documents that have the word MySQL but do not have the word databases, you can use the exclude Boolean operator ( - ) as follows:

SELECT 
  id, 
  contents 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('mysql -database' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------------+
| id | contents        |
+----+-----------------+
|  2 | MySQL           |
|  5 | A fork of MySQL |
+----+-----------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The search result shows only documents with the keyword MySQL excluding the documents with the word databases.

MySQL Boolean full-text search operators

The following table illustrates the full-text search Boolean operators and their meanings:

The following examples illustrate how to use the boolean full-text operator + to search for rows that have the word “database”:

SELECT 
  id, 
  contents 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('+database' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------+
| id | contents       |
+----+----------------+
|  1 | MySQL Database |
|  3 | Database       |
+----+----------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

To search for rows that contain the word “mysql”, but put the higher rank for the rows that contain “database”:

SELECT 
  id, 
  contents, 
  MATCH (contents) AGAINST ('mysql +database' IN BOOLEAN MODE) relevancy 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('mysql +database' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------+---------------------+
| id | contents       | relevancy           |
+----+----------------+---------------------+
|  1 | MySQL Database |  0.3182637691497803 |
|  3 | Database       | 0.22764469683170319 |
+----+----------------+---------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

To find the rows that contain words starting with “sql” such as “sql”, “sqlite”, etc., you use the following:

SELECT 
  id, 
  contents
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('sql*' IN BOOLEAN MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+
| id | contents |
+----+----------+
|  4 | SQL      |
|  6 | SQLite   |
+----+----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the IN BOOLEAN MODE option explicitly in the AGAINST() function to perform boolean full-text searches.
• Combine the Boolean operators to perform Boolean text searches.