MYSQL Search Queries (Exact vs Like vs Fuzzy)

Welcome to a tutorial and examples on how to do MySQL search queries. Yes, this is a very common question and pitfall among beginners. How difficult can a SELECT query be? Not so straightforward, apparently.

That covers the quick basics, let us walk through more examples in this guide – Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

DUMMY ARTICLE TABLE

-- (A) ARTICLES TABLE
CREATE TABLE `articles` (
  `article_id` bigint(20) NOT NULL,
  `article_title` varchar(255) NOT NULL,
  `article_text` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `articles`
  ADD PRIMARY KEY (`article_id`),
  ADD KEY `article_title` (`article_title`),
  ADD FULLTEXT KEY `article_text` (`article_text`);

ALTER TABLE `articles`
  MODIFY `article_id` bigint(20) NOT NULL AUTO_INCREMENT;

-- (B) DUMMY ENTRIES
INSERT INTO `articles` (`article_id`, `article_title`, `article_text`) VALUES
(1, 'Secret of the Voiceless Ringmaster', 'Unpacked reserved sir offering bed friends judgment may and quitting speaking. Is do be improved raptures offering required in replying raillery.'),
(2, 'The Smoke Flame', 'Stairs ladies friend by in mutual a no. Mr hence chief he causes. Whole no doors on hoped. Mile tells if help they ye full name. '),
(3, 'Sign of the Secret Hand', 'May indulgence difficulty ham can put especially. Bringing remember for supplied her why was confined.');

For this example, we will work with a simple articles table.

Field	Description
article_id	Article ID, primary key.
article_title	Title of the article.
article_text	Content of the article.

 

 

EXAMPLE 1) EXACT MATCH SEARCH

-- (A) CAPTAIN OBVIOUS EXACT SEARCH
SELECT * FROM `articles` WHERE `article_id`=1;

-- (B) EXACT MATCH, CASE INSENSITIVE
SELECT * FROM `articles` WHERE `article_title`="the smoke flame";

-- (C) EXACT MATCH, CASE SENSITIVE
-- CREDIT: https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql
SELECT * FROM `articles` WHERE `article_title`= BINARY "The Smoke Flame";

Exact match searches should be self-explanatory. Retrieve records that match the search term precisely, but take note:

• Depending on the collation of the column, searches may be case-sensitive or insensitive by default. For example, utf8_general_ci is case-insensitive, while utf8_bin is case-sensitive.
• To “enforce” a case-sensitive search, we can prepend a BINARY to the search term.

 

EXAMPLE 2) LIKE SEARCH (MUST CONTAIN)

-- (A) CONTAIN "SECRET" ANYWHERE
SELECT * FROM `articles` WHERE `article_title` LIKE "%secret%";

-- (B) MUST START WITH "SECRET"
SELECT * FROM `articles` WHERE `article_title` LIKE "secret%";

-- (C) MUST END WITH wITH "SECRET HAND"
SELECT * FROM `articles` WHERE `article_title` LIKE "%secret hand";

-- (D) CASE SENSITIVE
SELECT * FROM `articles` WHERE `article_title` LIKE BINARY "%Secret%";

A LIKE search query will return records as long as they contain the search query.

• To search for entries that contain a certain value, use WHERE `COLUMN` LIKE "%SEARCH%".
• To search for entries that start with a certain value, use WHERE `COLUMN` LIKE "%SEARCH".
• Lastly, for entries that end with a certain value, use WHERE `COLUMN` LIKE "SEARCH%".
• Of course, we can “enforce” a case-sensitive search using BINARY again.

 

 

EXAMPLE 3) FUZZY SEARCH

-- (A) FUZZY SEARCH
SELECT * FROM `articles` WHERE MATCH(`article_text`)
AGAINST("difficulty ham" IN NATURAL LANGUAGE MODE);
 
-- (B) MATCH SCORE
SELECT `article_title`,
  MATCH(`article_text`)
  AGAINST("difficulty ham" IN NATURAL LANGUAGE MODE)
  AS `score`
FROM `articles`
WHERE
  MATCH(`article_text`)
  AGAINST("difficulty ham" IN NATURAL LANGUAGE MODE);

Lastly, the fuzzy search is an interesting one. A “like search” must contain the search term in one way or another, but a fuzzy search returns all results that are “close enough”.

P.S. Another interesting part about fuzzy search is that we can get a “search relevance score” with it, which is a number from 0 to 1. The higher the score, the more relevant it is.

 

EXAMPLE 4) SEARCH MULTIPLE COLUMNS

-- (A) EITHER TITLE *OR* TEXT MUST CONTAIN "SECRET"
SELECT * FROM `articles` WHERE
  `article_title` LIKE "%secret%"
  OR `article_text` LIKE "%secret%";

-- (B) BOTH TITLE *AND* TEXT MUST CONTAIN "SECRET"
SELECT * FROM `articles` WHERE
  `article_title` LIKE "%secret%"
  AND `article_text` LIKE "%secret%";

How do we do a search across multiple columns? Not going too deep into the can of worms, just use OR or AND.

 

 

 

INDEX THE SEARCHABLE FIELDS!

Notice the ADD KEY `article_title` and ADD FULLTEXT KEY `article_text`? Yes, that is very important for search performance. For the uninitiated, here is a quick analogy:

• The index key is like building a catalog for library books.
• A library sure can function without a catalog, but searches will be very slow as we have to look through each and every book.
• Building a catalog does take up extra disk space, but it vastly improves search performance in the long run.

 

LINKS & REFERENCES

• SELECT Statement – MYSQL
• Full-Text Search Functions – MYSQL