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.
There are 3 common ways to do search queries in MySQL
- Exact match search (get all records that match the search term) –
SELECT * FROM `TABLE` WHERE `COLUMN` = "SEARCH"
- A “must contain” search (get all records that contain the search term) –
SELECT * FROM `TABLE` WHERE `COLUMN` LIKE "%SEARCH%"
- Fuzzy search (get all records that are similar to the search term) –
SELECT * FROM `TABLE` WHERE MATCH(`COLUMN`) AGAINST("SEARCH" IN NATURAL LANGUAGE MODE);
That covers the quick basics, let us walk through more examples in this guide – Read on!
TABLE OF CONTENTS
DOWNLOAD & NOTES
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
EXAMPLE CODE DOWNLOAD
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
MYSQL SEARCH QUERY
All right, let us now get into the examples of search queries in MySQL to better explain the difference.
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, whileutf8_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
.
EXTRAS
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
- SELECT Statement – MYSQL
- Full-Text Search Functions – MYSQL
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!