How To Do Search Queries In MYSQL – Exact, Like, 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.

  • To do an exact match search – SELECT * FROM `TABLE` WHERE `COLUMN` = 'SEARCH'
  • To do a “must contain” search – SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%SEARCH%'
  • For a case-sensitive search – SELECT * FROM `TABLE` WHERE `COLUMN` LIKE BINARY '%SEARCH%'
  • To do a fuzzy search – SELECT * FROM `TABLE` WHERE MATCH(`COLUMN`) AGAINST('SEARCH' IN NATURAL LANGUAGE MODE);

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

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes MySQL Search Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

 

DUMMY ARTICLE TABLE

0-dummy.sql
CREATE TABLE `articles` (
  `article_id` int(11) NOT NULL,
  `article_title` varchar(255) NOT NULL,
  `article_text` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

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

ALTER TABLE `articles`
  MODIFY `article_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

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

1-exact.sql
-- (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";
  • The first example should be easy enough, all beginners should already know how to use WHERE `COLUMN` = SEARCH to do an exact match search.
  • To keep things simple, depending on the collation of the column, searches may be case-sensitive or insensitive by default.
  • To “enforce” a case-sensitive search, we can prepend a BINARY to the search term.

 

EXAMPLE 2) LIKE SEARCH (MUST CONTAIN)

2-like.sql
-- (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%";
  • 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

3-fuzzy.sql
-- (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 still contain the search term in the specified order, 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 “relevance search score” with it, which is a number from 0 to 1. The higher the score, the more relevant it is.

 

EXAMPLE 4) SEARCH MULTIPLE FIELDS

4-multiple.sql
-- (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.

 

 

USEFUL BITS & LINKS

That’s all for the main tutorial, and here is a small section on some extras and links that may be useful to you.

 

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

 

INFOGRAPHIC CHEAT SHEET

FOO (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *