SELECT with LIKE
suggest changeCREATE TABLE stack
( id int AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL
);
INSERT stack(username) VALUES
('admin'),('k admin'),('adm'),('a adm b'),('b XadmY c'), ('adm now'), ('not here');
“adm” anywhere:
SELECT * FROM stack WHERE username LIKE "%adm%";
+----+-----------+
| id | username |
+----+-----------+
| 1 | admin |
| 2 | k admin |
| 3 | adm |
| 4 | a adm b |
| 5 | b XadmY c |
| 6 | adm now |
+----+-----------+
Begins with “adm”:
SELECT * FROM stack WHERE username LIKE "adm%";
+----+----------+
| id | username |
+----+----------+
| 1 | admin |
| 3 | adm |
| 6 | adm now |
+----+----------+
Ends with “adm”:
SELECT * FROM stack WHERE username LIKE "%adm";
+----+----------+
| id | username |
+----+----------+
| 3 | adm |
+----+----------+
Just as the %
character in a LIKE
clause matches any number of characters, the \_
character matches just one character. For example,
SELECT * FROM stack WHERE username LIKE "adm_n";
+----+----------+
| id | username |
+----+----------+
| 1 | admin |
+----+----------+
Performance Notes If there is an index on username
, then
LIKE 'adm'
performs the same as `= ‘adm’LIKE 'adm%
is a “range”, similar toBETWEEN..AND..
It can make good use of an index on the column.LIKE '%adm'
(or any variant with a leading wildcard) cannot use any index. Therefore it will be slow. On tables with many rows, it is likely to be so slow it is useless.RLIKE
(REGEXP
) tends to be slower thanLIKE
, but has more capabilities.- While MySQL offers
FULLTEXT
indexing on many types of table and column, thoseFULLTEXT
indexes are not used to fulfill queries usingLIKE
.
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents