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:
Operator | Description |
---|---|
+ | 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 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 theAGAINST()
function to perform boolean full-text searches. - Combine the Boolean operators to perform Boolean text searches.