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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/lilom/article/details/77092744

介绍

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

演示数据

演示数据

MySQL 标准的SQL模式匹配

使用 LIKENOT 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)

演示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)

演示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)
  • (%)通配符不匹配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)

演示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)

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)

MySQL扩展的正规表达式匹配

使用 REGEXPNOT REGEXP 操作符,或者 RLIKENOT 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)

我们可以看到,正则表达式返回所有en_name 字段中包含m 子串的记录
再来看使用SQL标准模式是否能匹配en_name 字段含 m 字串的记录?

mysql> SELECT * FROM animal WHERE en_name LIKE 'm';
Empty set (0.00 sec)

结果显示是不行的,因为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)
  • 如果是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)

上面这条SQL语句中正则表达式使用了3个 . 符号,假如我们只用1个 . 符号,看看能否查出记录

mysql> SELECT * FROM animal WHERE ch_name REGEXP '^.马';
Empty set (0.00 sec)

结果显示是不行的

  • 如果是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)

可以看到上面的SQL语句中的正则表达式用了2个. 符号,查询出了记录。

3. [] 方括号元字符

  • 方括号表示一个集合,表示此集合中的任意一个字符。如[abc],可以匹配字符’a’、’b’、’c’。
  • [a-z] 匹配所有字母
  • [0-9] 匹配所有数字

演示4
查询animal 表中 en_name 字段中以字符hp 开头的记录

mysql> SELECT * FROM animal WHERE en_name REGEXP '^[hp]';
+----+--------------+---------+
| id | en_name      | ch_name |
+----+--------------+---------+
|  1 | horse        | 马      |
|  2 | hippopotamus | 河马    |
|  3 | pony         | 矮马    |
|  7 | pig          | 猪      |
+----+--------------+---------+
4 rows in set (0.00 sec)

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. SQL标准模式匹配整个字符串;而扩展正规表达式 ,匹配整个字符串或者其字串。
  2. SQL标准模式使用 LIKENOT LIKE 操作符;正则表达式使用 REGEXPNOT REGEXP 或者 RLIKENOT RLIKE 操作符
  3. MySQL中的匹配模式不区分大小写
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页