1. 模糊查询
模糊查询是模式匹配的一种,MySQL中有两种模糊查询方式:
1. 标准SQL模式匹配(关键字LIKE)
2. 扩展正则表达式(关键字REGEXP/RLIKE)
2. 标准SQL模式匹配
_ 匹配任意单字符
% 匹配任意多个字符
标准SQL模式匹配使用关键字LIKE,默认不区分大小写
3. 扩展正则表达式
.
匹配任意单字符
[...]
匹配方括号(集合)里面的任意单字符,比如[abc]匹配a,b或者c;[a-z]匹配任意单字母;[0-9]匹配任意单数字
*
匹配零个或者多个它前面的实例,比如x*匹配任意数量的x;[0-9]*匹配任意数量的数字;.*匹配任意数量的任何实例
扩展正则表达式使用关键字REGEXP/RLIKE,以^开始,以$结束
默认不区分大小写,使用关键字BINARY强制区分大小写
4. 查询举例
1. 找到name以'b'开始的记录
mysql> SELECT * FROM PET WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM PET WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM PET WHERE name REGEXP BINARY '^b';
Empty set (0.01 sec)
2. 找到name以'fy'结束的记录
mysql> SELECT * FROM PET WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM PET WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
3. 找到name包含'w'的记录
mysql> SELECT * FROM PET WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM PET WHERE name REGEXP '.w';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM PET WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
4. 找到name由5个字符组成且第4个字符为w的记录
mysql> SELECT * FROM PET WHERE name LIKE '___W_';
+-------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+-------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM PET WHERE name REGEXP '^...w.$';
+-------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+-------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
-- You could also write the previous query using the {n} (“repeat-n-times”) operator
mysql> SELECT * FROM PET WHERE name REGEXP '^.{3}w.$';
+-------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+-------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
注:有时候使用NOT REGEXP更简单
更多正则表达式语法,点我直达。