Simple MySQL Search Query Examples – Exact, Like, Fuzzy

Welcome to a tutorial 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.

There are 3 common ways to do search queries in MySQL:

  1. For an exact match – SELECT * FROM `TABLE` WHERE `COLUMN` = 'SEARCH'
  2. To search for entries that contain a certain value – SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%SEARCH%'
  3. 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.

 

 

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

 

 

 

THE DUMMY TABLES

For this example, we will be working with these 2 dummy article and content tables.

ARTICLES

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

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

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

INSERT INTO `articles` (`article_id`, `article_title`, `article_desc`) VALUES
(1, 'Disconsolate Resin Crumbs', 'A scandal obliges the anomaly downstairs underneath an agenda.'),
(2, 'Cowardly Falcon Hush', 'The sophisticated anomaly leaps under my stamped diagnosis.');

Just a simple table with 3 fields here – ID, title, and description. Take extra note that the title and description are indexed for better search performance.

CONTENTS

0-dummy.sql
-- (B) CONTENTS
CREATE TABLE `contents` (
  `article_id` int(11) NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `contents` (`article_id`, `content`) VALUES
(1, 'The elephant posts the gathered volunteer. Why can\'t a conference swallow before the blasting bucket? The pump tunes the directive. The unstable litter damages the mathematical burst.'),
(2, 'The elephant angles the animal. Against its commentary hesitates the camera. Outside the flag freezes a proven secretary.');

ALTER TABLE `contents`
  ADD PRIMARY KEY (`article_id`);
ALTER TABLE `contents` ADD FULLTEXT KEY `content` (`content`);

Same here – This is just a simple table with id and the content itself. Take note that the content is a FULLTEXT indexed field.

 

 

1) EXACT MATCH SEARCH

1-exact.sql
-- (A) CAPTAIN OBVIOUS
SELECT * FROM `articles` WHERE `article_id`=1;

-- (B) CASE INSENSITIVE
SELECT * FROM `articles` WHERE `article_title` = 'cowardly falcon hush';

-- (C) 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 'cowardly falcon hush';
SELECT * FROM `articles` WHERE `article_title` = BINARY 'Cowardly Falcon Hush';

This first example should be Captain Obvious enough, all beginners should already know how to use WHERE `COLUMN` = SEARCH to do an exact match search. But take note it this is case insensitive by default – A quick trick to do case sensitive search to just prepend the search term with BINARY.

 

2) CONTAINS SEARCH

2-contains.sql
-- (A) SO LONG AS DESCRIPTION CONTAINS THE WORD "ANOMALY" ANYWHERE
SELECT * FROM `articles` WHERE `article_desc` LIKE '%anomaly%';

-- (B) DESCRIPTION MUST START wITH "A SCANDAL"
SELECT * FROM `articles` WHERE `article_desc` LIKE 'A scandal%';

-- (C) DESCRIPTION MUST NED WITH wITH "STAMPED DIAGNOSIS"
SELECT * FROM `articles` WHERE `article_desc` LIKE '%stamped diagnosis.';
  • 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%'.

Extra – WHERE `COLUMN` LIKE 'SEARCH' acts the same as WHERE `COLUMN` = 'SEARCH'.

 

 

3) FUZZY SEARCH

3-fuzzy.sql
SELECT * FROM `contents` WHERE MATCH(`content`)
AGAINST('flag outside' IN NATURAL LANGUAGE MODE);

Now, the fuzzy search is an interesting one. A “contains” search must in the specified search order, but a fuzzy search returns results that are “close enough”.

 

4) SEARCH MULTIPLE TABLES

4-join.sql
SELECT * FROM `articles` JOIN `contents` USING (`article_id`)
WHERE `article_id` LIKE '%elephant%' OR `content` LIKE '%elephant%'

How do we do a search across multiple tables? JOIN them together and search as usual.

 

 

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.

 

INFOGRAPHIC CHEAT SHEET

FOO (click to enlarge)

 

LINKS & REFERENCES

 

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 *