Example
CREATE 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
FULLTEXTindexing on many types of table and column, thoseFULLTEXTindexes are not used to fulfill queries usingLIKE.
