MySQL in a Nutshell, 2nd Edition

Searching Data

Once our database is loaded with large amounts of data, it can be cumbersome to locate data simply by scrolling through the results of SELECT statements. Also, sometimes we don’t have the exact or complete text for a column we’re examining. For these situations, we can use the LIKE operator. Suppose that our books table now has thousands of entries. Suppose further that a customer says he’s looking for a specific book. He can’t remember the name of the author or the title of the book, but he does remember that the words traveler and winter are in the title. We could enter this SQL statement to search the database based on this minimal information:

SELECT book_id, title,
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE title LIKE '%traveler%'
AND title LIKE '%winter%';
   
+---------+-----------------------------------+---------------+
| book_id | title                             | author        |
+---------+-----------------------------------+---------------+
|    1400 | If on a winter's night a traveler | Italo Calvino |
+---------+-----------------------------------+---------------+

With the LIKE operator, we use the percent sign wildcard twice to indicate that we’re searching for all rows in which the title column’s data contains the string traveler with zero or more characters before it (the preceding percent sign), and zero or more characters after it (the terminating percent sign). Put another way, the word traveler must be contained somewhere in the column’s ...