MYSQL-通配符与正则表达式的使用

转载地址 https://blog.youkuaiyun.com/lilom/article/details/77092744
介绍

通配符与正则表示的使用都是用来匹配一个字符串，可以用于匹配数据表中的某个字段的值，或者用于其它的SQL命令中的模糊查询。
通配符与正则表达式都将作为SQL中where 子句的内容，前者通过使用 LIKE 和 NOT LIKE 操作符,后者通过使用 REGEXP 和 NOT REGEXP 或者 RLIKE 和 NOT RLIKE 操作符。

演示数据

MySQL 标准的SQL模式匹配

使用 LIKE 和 NOT LIKE 操作符

1. % 百分号通配符

表示任意多种字符且可以出现任意次数
演示1 ：
查询animal 表中 en_name 字段中以 h 开头的记录

mysql> SELECT * FROM animal WHERE en_name LIKE ‘h%’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 1 | horse | 马 |
| 2 | hippopotamus | 河马 |
±—±-------------±--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
演示2 ：
查询animal 表中 en_name 字段中不以 h 开头的记录

mysql> SELECT * FROM animal WHERE en_name NOT LIKE ‘h%’;
±—±-------------±----------+
| id | en_name | ch_name |
±—±-------------±----------+
| 3 | pony | 矮马 |
| 4 | thoroughbred | 纯种马 |
| 5 | mule | 骡 |
| 6 | ox | 牛 |
| 7 | pig | 猪 |
| 8 | sheep | 羊 |
| 9 | deer | 鹿 |
| 10 | elephant | 象 |
| 11 | tomcat | 公猫 |
| 12 | squirrel | 松鼠 |
| 13 | mouse | 家鼠 |
±—±-------------±----------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
演示3 ：
查询animal 表中 en_name 字段中以 t 结尾的记录

mysql> SELECT * FROM animal WHERE en_name LIKE ‘%t’;
±—±---------±--------+
| id | en_name | ch_name |
±—±---------±--------+
| 10 | elephant | 象 |
| 11 | tomcat | 公猫 |
±—±---------±--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
（%）通配符不匹配NULL
2. _ 下划线通配符

表示任意一个字符(无论是字母还是汉字)
如果想匹配两个或多个字符，那就使用两次或多次_下划线通配符
演示1 ：
查询animal 表中 en_name 字段中字符（字母）长度为2的记录

mysql> SELECT * FROM animal WHERE en_name LIKE ‘__’;
±—±--------±--------+
| id | en_name | ch_name |
±—±--------±--------+
| 6 | ox | 牛 |
±—±--------±--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
演示2 ：
查询animal 表中 cn_name 字段中字符（汉字）长度为2的记录

mysql> SELECT * FROM animal WHERE ch_name LIKE ‘__’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 2 | hippopotamus | 河马 |
| 3 | pony | 矮马 |
| 11 | tomcat | 公猫 |
| 12 | squirrel | 松鼠 |
| 13 | mouse | 家鼠 |
±—±-------------±--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
3. 通配符可以灵活的搭配使用

演示1 ：
查询animal 表中 en_name 字段值为tomcat 的记录

mysql> SELECT * FROM animal WHERE en_name LIKE ‘__m%’;
±—±--------±--------+
| id | en_name | ch_name |
±—±--------±--------+
| 11 | tomcat | 公猫 |
±—±--------±--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
MySQL扩展的正规表达式匹配

使用 REGEXP 和 NOT REGEXP 操作符，或者 RLIKE 和NOT RLIKE，这两套操作符同义。

1. ^ 表示模式匹配的开始，$ 表示模式匹配的结尾

正规表达式匹配的是目标字符串的子串，也就是说只要目标字符串中有一部分符合模式（即正则表达），那么就匹配成功
不同于SQL标准的模式匹配。SQL标准的模式匹配将匹配整个目标字符串，也就是说只有当目标字符串完全符合模式（子串符合模式不算），那么才匹配成功
由于正则表达式将匹配目标字符串或其字串，当我们想要用正则表达式像SQL标准模式一样只匹配整个目标字符串时，就需要用到 ^ 字符来表示目标字符串的开头，$ 字符表示目标字符串的结尾
演示1 ：
使用REGEXP 查询animal 表中 en_name 字段包含字母m记录

mysql> SELECT * FROM animal WHERE en_name REGEXP ‘m’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 2 | hippopotamus | 河马 |
| 5 | mule | 骡 |
| 11 | tomcat | 公猫 |
| 13 | mouse | 家鼠 |
±—±-------------±--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
我们可以看到，正则表达式返回所有en_name 字段中包含m 子串的记录
再来看使用SQL标准模式是否能匹配en_name 字段含 m 字串的记录？

mysql> SELECT * FROM animal WHERE en_name LIKE ‘m’;
Empty set (0.00 sec)
1
2
结果显示是不行的，因为SQL模式只检查整个目标字符串（即整个en_name 字段）是否匹配

2. .点 元字符

表示任意一个字符（注意：虽然官方文档上写着. 符号代表着一个字符character,但是测试表明其代表的是一个字节，因此字母和汉字所用的. 符号数量将会不同）
如果是英文，1个.代表一个字符
演示1 ：
查询animal 表中 en_name 字段中第二个字符为o 的记录

mysql> SELECT * FROM animal WHERE en_name REGEXP ‘^.o’;
±—±--------±--------+
| id | en_name | ch_name |
±—±--------±--------+
| 1 | horse | 马 |
| 3 | pony | 矮马 |
| 11 | tomcat | 公猫 |
| 13 | mouse | 家鼠 |
±—±--------±--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
如果是UTF-8编码，3个.代表一个汉字
演示2 ：
查询animal 表中 ch_name 字段中第二个字符为马的记录

mysql> SELECT * FROM animal WHERE ch_name REGEXP ‘^…马’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 2 | hippopotamus | 河马 |
| 3 | pony | 矮马 |
±—±-------------±--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
上面这条SQL语句中正则表达式使用了3个 . 符号，假如我们只用1个 . 符号，看看能否查出记录

mysql> SELECT * FROM animal WHERE ch_name REGEXP ‘^.马’;
Empty set (0.00 sec)
1
2
结果显示是不行的

如果是GBK编码，2个.代表一个汉字
演示3 ：
下面我们将en_name 这个字段的编码换成GBK后再来查询。
查询animal 表中 ch_name 字段中第二个字符为马的记录

mysql> SELECT * FROM animal WHERE ch_name REGEXP ‘^…马’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 2 | hippopotamus | 河马 |
| 3 | pony | 矮马 |
±—±-------------±--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
可以看到上面的SQL语句中的正则表达式用了2个. 符号，查询出了记录。

3. [] 方括号元字符

方括号表示一个集合，表示此集合中的任意一个字符。如[abc]，可以匹配字符’a’、’b’、’c’。
[a-z] 匹配所有字母
[0-9] 匹配所有数字
演示4 ：
查询animal 表中 en_name 字段中以字符h 或 p 开头的记录

mysql> SELECT * FROM animal WHERE en_name REGEXP ‘¹’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 1 | horse | 马 |
| 2 | hippopotamus | 河马 |
| 3 | pony | 矮马 |
| 7 | pig | 猪 |
±—±-------------±--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
4. * 星号元字符

表示*匹配0个或多个在它之前的模式
.*可以匹配所有长度的所有字符串
5. {n}重复n次操作符

表示前面的通配符重复n次。如.{4} ,表示的意思同 模式 … 一样
演示4 ：
查询animal 表中 en_name 字段包含连续两个p的记录

mysql> SELECT * FROM animal WHERE en_name REGEXP ‘p{2}’;
±—±-------------±--------+
| id | en_name | ch_name |
±—±-------------±--------+
| 2 | hippopotamus | 河马 |
±—±-------------±--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
小结

SQL标准模式匹配整个字符串；而扩展正规表达式 ，匹配整个字符串或者其字串。
SQL标准模式使用 LIKE 和 NOT LIKE 操作符;正则表达式使用 REGEXP 和 NOT REGEXP 或者 RLIKE 和 NOT RLIKE 操作符
MySQL中的匹配模式不区分大小写

作者：lilom
来源：优快云
原文：https://blog.youkuaiyun.com/lilom/article/details/77092744
版权声明：本文为博主原创文章，转载请附上博文链接！

---

1. hp ↩︎