MySQL LIKE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

= matches a whole value. LIKE matches a pattern, so you can ask for the titles that start with a word, end with one, or have a given shape. Open the client with mysql -u root -p sakila before you start.

How LIKE works

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME LIKE 'PATTERN_TEXT';

Replace PATTERN_TEXT with the pattern to match. A pattern is ordinary text plus 2 special characters:

Character	Matches
`%`	any run of characters, including none at all
`_`	exactly 1 character

Everything else in the pattern matches itself. A pattern with neither % nor _ behaves almost like =. The 2 part company on trailing spaces: under a PAD SPACE collation such as utf8mb4_general_ci, 'a ' = 'a' is true and 'a ' LIKE 'a' is false. Under a NO PAD collation, which every utf8mb4_0900 collation is, both are false.

Examples

1) Starts with

SELECT title FROM film WHERE title LIKE 'ACADEMY%' ORDER BY title;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

2) Ends with

SELECT title FROM film WHERE title LIKE '%GOLDFINGER' ORDER BY title;

+----------------------+
| title                |
+----------------------+
| ACE GOLDFINGER       |
| BREAKFAST GOLDFINGER |
| SILVERADO GOLDFINGER |
+----------------------+
3 rows in set (0.00 sec)

3) A fixed number of characters

3 underscores after the J ask for a name of exactly 4 characters.

SELECT first_name, last_name
FROM actor
WHERE first_name LIKE 'J___'
ORDER BY first_name, last_name;

+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| JADA       | RYDER       |
| JANE       | JACKMAN     |
| JEFF       | SILVERSTONE |
| JOHN       | SUVARI      |
| JUDE       | CRUISE      |
| JUDY       | DEAN        |
+------------+-------------+
6 rows in set (0.00 sec)

JOHNNY is not in the list, because J___ matches 4 characters and no more.

4) Does not match

SELECT title FROM film WHERE title NOT LIKE 'A%' ORDER BY title LIMIT 5;

+---------------------+
| title               |
+---------------------+
| BABY HALL           |
| BACKLASH UNDEFEATED |
| BADMAN DAWN         |
| BAKED CLEOPATRA     |
| BALLOON HOMEWARD    |
+---------------------+
5 rows in set (0.00 sec)

LIKE ignores letter case here

The same pattern in lower case returns the same row.

SELECT title FROM film WHERE title LIKE 'academy%' ORDER BY title;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

That is not a rule about LIKE. It is a rule about the column, whose collation decides whether 2 strings count as equal. Sakila’s is case-insensitive, and so is the MySQL default. The Case sensitivity lesson shows how to check a column and how to ask for a case-sensitive match.

Matching a literal % or _

A % in your search text is a wildcard unless you say otherwise. ESCAPE names a character that turns off the one after it, so a pattern can ask for a real percent sign.

SELECT 'axxb' LIKE 'a%b' AS wildcard, 'axxb' LIKE 'a!%b' ESCAPE '!' AS literal_percent;

+----------+-----------------+
| wildcard | literal_percent |
+----------+-----------------+
|        1 |               0 |
+----------+-----------------+
1 row in set (0.00 sec)

a%b matched axxb because % stood for xx. a!%b asked for a literal percent sign between a and b, which axxb does not have.

SELECT '50%' LIKE '50!%' ESCAPE '!' AS literal_percent;

+-----------------+
| literal_percent |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

The underscore works the same way.

SELECT 'a_b' LIKE 'a!_b' ESCAPE '!' AS underscore, 'axb' LIKE 'a!_b' ESCAPE '!' AS any_char;

+------------+----------+
| underscore | any_char |
+------------+----------+
|          1 |        0 |
+------------+----------+
1 row in set (0.00 sec)

Any character you are sure your data does not contain will do. Without ESCAPE, a backslash escapes the next character, so 'a\%b' means the same thing. That shortcut stops working when the server runs with NO_BACKSLASH_ESCAPES in its sql_mode, and ESCAPE works either way.

A leading % is the expensive one

LIKE 'ACADEMY%' can use an index on the column, because every match starts with the same known text and an index is held in sorted order, so the server jumps to that part of it. LIKE '%GOLDFINGER' gives it no starting point, because a match can begin anywhere, so it reads every value in the column and tests each one. EXPLAIN on the 2 queries shows the difference: 1 estimated row against 1,000. On 1,000 films nobody notices. On a large table it is the difference between a lookup and a full scan. When you need to search inside text at scale, a different tool fits: see Full-text search in MySQL.

Summary

Use LIKE to match a pattern rather than a whole value.
Read % as any run of characters and _ as exactly 1 character.
Name an escape character with ESCAPE to match a literal % or _.
Expect the column’s collation, not LIKE, to decide whether case matters.
Avoid a leading % on a large table, because no index can narrow the search.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How LIKE works

Examples

1) Starts with

2) Ends with

3) A fixed number of characters

4) Does not match

LIKE ignores letter case here

Matching a literal % or _

A leading % is the expensive one

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How LIKE works

​Examples

​1) Starts with

​2) Ends with

​3) A fixed number of characters

​4) Does not match

​LIKE ignores letter case here

​Matching a literal % or _

​A leading % is the expensive one

​Summary

​See also

How LIKE works

Examples

1) Starts with

2) Ends with

3) A fixed number of characters

4) Does not match

LIKE ignores letter case here

Matching a literal % or _

A leading % is the expensive one

Summary

See also