在查询数据时,通配符可以替代指定位置的一个(下划线:_)或多个字符(百分号:%)。
MySQL 可使用如下所示的通配符:
通配符 | 描述 |
---|---|
百分号( % ) | 表示在百分号(%)位置可以有任意个字符,字符内容不限 |
下划线( _ ) | 表示在下划线( _ )位置有且只有一个字符,字符内容不限 |
说明:
(1)如果条件中使用了通配符,比较运算符必须使用 LIKE,而不能使用 =、>、< 等运算符。如果比较运算符不使用 LIKE,则 MySQL 认为百分号和下划线就是普通字符,而不是通配符。
(2)如果条件中使用了通配符,查询时如果通配符放在最左边,则不会使用索引。
查询时表示在该位置可以是任意个(0 - n 个)字符,字符的内容不限。
例如:
(1)查询姓【刘】的学生信息。
mysql> select * from student where s_name like '刘%';
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 |
| S2016 | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 |
+-------+-----------+---------------------+-------------+-----------+
4 rows in set (0.00 sec)
-- 此查询使用了索引 idx_name
mysql> explain select * from student where s_name like '刘%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: range
possible_keys: idx_name
key: idx_name
key_len: 61
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
-- 通配符使用 =,没有查询结果,除非学生姓名为【刘%】才会被查询到。
mysql> select * from student where s_name = '刘%';
Empty set (0.00 sec)
(2)查询最后一个字是【刚】的学生信息。
mysql> select * from student where s_name like '%刚';
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 |
+-------+-----------+---------------------+-------------+-----------+
1 row in set (0.00 sec)
-- 此查询不会使用索引
mysql> explain select * from student where s_name like '%刚'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
(3)查询姓名中包含【华】字的学生信息。
mysql> select * from student where s_name like '%华%';
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 |
| S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |
+-------+-----------+---------------------+-------------+-----------+
2 rows in set (0.00 sec)
-- 此查询不会使用索引
mysql> explain select * from student where s_name like '%华%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
查询时表示在该位置有且只有一个字符,字符的内容不限。必须和 LIKE 运算符一起使用。
例如:
(1)查询姓名中第二字是【华】的学生的信息。
mysql> select * from student where s_name like '_华%';
+-------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+-----------+---------------------+-------------+-----------+
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 |
+-------+-----------+---------------------+-------------+-----------+
1 row in set (0.00 sec)
-- 此查询不会使用索引
mysql> explain select * from student where s_name like '_华%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
(2)查询姓【刘】并且姓名一共两个字的学生信息。
mysql> select * from student where s_name like '刘_';
+-------+--------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+--------+---------------------+-------------+-----------+
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |
+-------+--------+---------------------+-------------+-----------+
2 rows in set (0.00 sec)
-- 此查询使用了索引 idx_name
mysql> explain select * from student where s_name like '刘_'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: range
possible_keys: idx_name
key: idx_name
key_len: 61
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
(3)查询 phone 第四到第七个数字是【2373】的学生信息
-- 开头一共 3 个下划线
mysql> select * from student where phone like '___2373%';
+-------+--------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | addr |
+-------+--------+---------------------+-------------+-----------+
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |
+-------+--------+---------------------+-------------+-----------+
1 row in set (0.00 sec)