MySQL全面瓦解8:查询的正则匹配

发布于 2022-8-1 18:52
浏览
0收藏

概述
上一章 查询的过滤条件,我们了解了MySQL可以通过 like % 通配符来进行模糊匹配。同样的,它也支持其他正则表达式的匹配,我们在MySQL中使用 REGEXP 操作符来进行正则表达式匹配。用法和like相

似,但又强大很多,能够实现一些很特殊的、复杂的规则匹配。正则表达式使用REGEXP命令进行匹配时,如果符合返回1,不符合返回0。如果 默认不加任何匹配规则REGEXP相当于like '%%'。在前面加上NOT(NOT REGEXP)相当于NOT LIKE。

匹配模式分析
下面有个表格 ,罗列了可应用于 REGEXP 操作符中正则匹配模式,描述相对比较详细了,后面我们一个一个来测试。

MySQL全面瓦解8:查询的正则匹配-开源基础软件社区

MySQL全面瓦解8:查询的正则匹配-开源基础软件社区

匹配模式^
从字符串首部分进行匹配,这边匹配s开头的,匹配符合返回1,不符合返回0。应用到表中,既符合返回匹配到的数据。

 mysql> select 'selina' REGEXP '^s';
 +----------------------+
 | 'selina' REGEXP '^s' |
 +----------------------+
 |                    1 |
 +----------------------+
 1 row in set
 
 mysql> select 'aelina' REGEXP '^s';
 +----------------------+
 | 'aelina' REGEXP '^s' |
 +----------------------+
 |                    0 |
 +----------------------+
 1 row in set
mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set 
 mysql> select * from user2 where name REGEXP '^s';
 +----+--------+-----+---------+-----+
 | id | name   | age | address | sex |
 +----+--------+-----+---------+-----+
 |  3 | sol    |  21 | xiamen  |   0 |
 |  5 | selina |  25 | NULL    |   0 |
 +----+--------+-----+---------+-----+

2 rows in set

匹配模式$
从字符串尾部进行匹配,这边匹配名称以d结尾的数据。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'd$';
 +----+-------+-----+---------+-----+
 | id | name  | age | address | sex |
 +----+-------+-----+---------+-----+
 |  1 | brand |  21 | fuzhou  |   1 |
 +----+-------+-----+---------+-----+
 1 row in set 

匹配模式.

. 是匹配任意单个字符,下面脚本匹配 n并且后面带一个任意字符的条件

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'n.';
 +----+--------+-----+---------+-----+
 | id | name   | age | address | sex |
 +----+--------+-----+---------+-----+
 |  1 | brand  |  21 | fuzhou  |   1 |
 |  4 | weng   |  33 | guizhou |   1 |
 |  5 | selina |  25 | NULL    |   0 |
 +----+--------+-----+---------+-----+
 3 rows in set

匹配模式[...]
指匹配括号内的任意单个字符,只要有一个字符符合条件即可。下面例子能匹配到b、w、z的 只有brand、weng 两个名称。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP [bwz];
 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL rver version for the right syntax to use near '[bwz]' at line 1
 mysql> select * from user2 where name REGEXP '[bwz]';
 +----+-------+-----+---------+-----+
 | id | name  | age | address | sex |
 +----+-------+-----+---------+-----+
 |  1 | brand |  21 | fuzhou  |   1 |
 |  4 | weng  |  33 | guizhou |   1 |
 +----+-------+-----+---------+-----+
 2 rows in set 

匹配模式[^...] 

[^...]取反的意思,指匹配未包含的任意字符。例如, '[^brand]' 可以匹配 "helen" 中的'h',"sol" 的 "s","weng" 的 "w","selina" 的 "s",但无法匹配"brand",所以被过滤了。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP '[^brand]';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 4 rows in set

匹配模式[n-m]
匹配m到n之间的任意单个字符,例如[0-9],[a-z],[A-Z],下方代码中,任何元素不在a - e之间的"sol" 被过滤了。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP '[a-e]';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 4 rows in set

匹配模式 *

匹配前面的子表达式零次或多次。例如,a* 能匹配 "a" 以及 "ab"。* 等价于{0,}。下面的 "e*g" 可以匹配的只有 "weng" 这个名称。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'e*g';
 +----+------+-----+---------+-----+
 | id | name | age | address | sex |
 +----+------+-----+---------+-----+
 |  4 | weng |  33 | guizhou |   1 |
 +----+------+-----+---------+-----+
 1 row in set 

匹配模式 +

匹配前面的子表达式一次或多次。例如,'a+' 能匹配 "ab" 以及 "abc",但不能匹配 "a"。+ 等价于 {1,}。如下方的脚本,符合条件的是1到多个的n加上一个d的组合,只有 "brand" 和 "annd" 符合。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'n+d';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 2 rows in set

匹配模式 ?
匹配前面的子表达式一次或多次。例如,'a?' 能匹配 "ab" 以及 "a"。? 等价于 {0,1}。e为1个或者0个,后面再用 l 限制,所以符合的只有三个。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'e?l';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 3 rows in set 

匹配模式 a1| a2|a3

匹配 a1 或 a2 或 a3。例如下方,'nn|en' 能分别匹配到 "anny" 、"annd" 和 "helen"、"weng"。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'nn|en';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  2 | helen |  20 | quanzhou |   0 |
 |  4 | weng  |  33 | guizhou  |   1 |
 |  6 | anny  |  23 | shanghai |   0 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 4 rows in set

匹配模式 {n} {n,} {n,m} {,m} 
 n 和 m 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。m为空代表>=n的任意数,n为空代表0。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'n{2}';
 +----+------+-----+----------+-----+
 | id | name | age | address  | sex |
 +----+------+-----+----------+-----+
 |  6 | anny |  23 | shanghai |   0 |
 |  7 | annd |  24 | shanghai |   1 |
 +----+------+-----+----------+-----+
 2 rows in set
 
 mysql> select * from user2 where name REGEXP 'n{1,2}';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 6 rows in set
 
 mysql> select * from user2 where name REGEXP 'l{1,}';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 3 rows in set

匹配模式(...) 
假设括号内容为abc,则是将abc作为一个整体去匹配,符合这个规则的数据被过滤出来。下面以an为例子,配合上面学过的知识。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP '(an)+';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  6 | anny  |  23 | shanghai |   0 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 3 rows in set
 
 mysql> select * from user2 where name REGEXP '(ann)+';
 +----+------+-----+----------+-----+
 | id | name | age | address  | sex |
 +----+------+-----+----------+-----+
 |  6 | anny |  23 | shanghai |   0 |
 |  7 | annd |  24 | shanghai |   1 |
 +----+------+-----+----------+-----+
 2 rows in set
 
 mysql> select * from user2 where name REGEXP '(an).*d{1,2}';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 2 rows in set

匹配特殊字符 \\
正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、 []、|、*、+ 等, 那我们是怎么匹配这些字符的。如下示例,我们使用 \\ 来匹配特殊字符,\\为前导, \\-表示查找-, \\.表示查找.。

 mysql> select * from user3;
 +----+------+-------+
 | id | age  | name  |
 +----+------+-------+
 |  1 |   20 | brand |
 |  2 |   22 | sol   |
 |  3 |   20 | helen |
 |  4 | 19.5 | diny  |
 +----+------+-------+
 4 rows in set
 
 mysql> select * from user3 where age REGEXP '[0-9]+\\.[0-9]+';
 +----+------+------+
 | id | age  | name |
 +----+------+------+
 |  4 | 19.5 | diny |
 +----+------+------+
 1 row in set 

总结

1.当我们需要用正则匹配数据的时候,可以使用REGEXP和NOT REGEXP操作符(类似LIKE和NOT LIKE);

2.REGEXP默认不区分大小写,可以使用BINARY关键词强制区分大小写;WHERE NAME REGEXP BINARY ‘^[A-Z]’;

3.REGEXP默认是部分匹配原则,即有一个匹配上则返回真。例如:SELECT  'A123' REGEXP BINARY '[A-Z]',返回的是1;

4、如果使用 () 进行匹配,则是将括号内部的内容当作整体去匹配,比如 (ABC),则需要匹配整个ABC。

5、这边只是看介绍了正则的基础知识,想要更为透彻的了解可以参考 正则教程 ,我觉得写的不错。

 

 

文章转载自公众号:架构与思维

已于2022-8-1 18:52:38修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐