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:
- For an exact match –
SELECT * FROM `TABLE` WHERE `COLUMN` = 'SEARCH'
- To search for entries that contain a certain value –
SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%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.
REAL QUICK SLIDES
TABLE OF CONTENTS
|Download & Notes||MySQL Search||Useful Bits & Links|
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.
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.
MYSQL SEARCH QUERY
All right, let us now get into the examples of search queries in MySQL.
THE DUMMY TABLES
For this example, we will be working with these 2 dummy article and content tables.
-- (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.
-- (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
-- (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
2) CONTAINS SEARCH
-- (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%'.
WHERE `COLUMN` LIKE 'SEARCH' acts the same as
WHERE `COLUMN` = 'SEARCH'.
3) FUZZY SEARCH
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
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
LINKS & REFERENCES
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!