As of version 4.0.1, MySQL provides IN BOOLEAN MODE modifier to perform boolean full-text searches. The following is an example that shows, how to use boolean full-text search.
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title | body |
+----+-----------------------+-------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+
The + operator in the AGAINST function indicates that a word is required to be present and - operator indicates that a word is required to be absent for a match to occur. Thus, this query retrieves all the rows that contain the word "MySQL" but that do not contain the word "YourSQL". An important thing to note that in implementing this feature, MySQL uses what is sometimes referred to as implied boolean logic, in which + stands for AND, - stands for NOT, and when no operator is supplied, OR is considered.
Characteristics of Boolean Full-Text Searches
- They do not use the 50% threshold.
- They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains "MySQL" twice, but it is listed last, not first.
- They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.
- The minimum and maximum word length full-text parameters apply.
- The stopword list applies
Boolean Full-Text Search Operators
The Boolean Full-Text search capability supports the following operators:
| + |
The word is mandatory in all rows returned. Used at the start of a word, i.e. +meet, meat all reports would have to contain the word meet, but meat is optional. |
| - |
The word cannot appear in any row returned. Used at the start of a word, i.e. -meet, +meat all reports would NOT contain the word meet, but have to contain the word meat. |
| < |
The word that follows has a lower relevance than other words, although rows containing it will still match. |
| > |
The word that follows has a higher relevance than other words. |
| () |
Used to group words into sub expressions. So, +(lizard cat dog) would return records with at least one of the words in the brackets. |
| * |
The wildcard, indicating zero or more characters. It can only appear at the end of a word, i.e. Me* would return words like meet, meeting, meetings, mean, and meat etc. |
| " |
Anything enclosed in the double quotes is taken as a whole (so you can match phrases). |
