MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Full Text Indexes / Question No: 180

How to use boolean full-text search in MySQL?

I want to use MySQL fulltext feature. Can you explain, how to use boolean full-text search in MySQL?

Answer No: 180

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).

Related MySQL FAQs to the Above FAQ

Main-features-and-basic-information-about-full-text-search Main features and basic information about full-text search

What-are-full-text-restrictions-in-MySQL What are full-text restrictions in MySQL?

On-what-data-types-fulltext-index-can-be-created On what data types fulltext index can be created?

How-to-create-full-text-index-in-MySQL How to create full-text index in MySQL?

How-to-create-multi-columns-full-text-index-in-MySQL How to create multi columns full-text index in MySQL?

What-are-natural-language-and-boolean-and-query-expansion-full-text-searches What are natural language and boolean and query expansion full-text searches?

How-to-use-full-text-searches-with-query-expansion-in-MySQL How to use full-text searches with query expansion in MySQL?

How-to-use-natural-language-full-text-search-in-MySQL How to use natural language full-text search in MySQL?

What-are-stopwords-in-MySQL What are stopwords in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.