MariaDB使用正则匹配查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串.例如从一个文本文件中提取电话号码,查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等,这些地方都可以使用正则表达式,正则表达式强大且灵活,可以应用于非常复杂的查询,MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式,先来看一下下表常用的正则规则.

通配符	说明信息	匹配例子
^	匹配文本的开头字符	‘^b’匹配开头是b的字符串
$	匹配文本的结束字符	‘st$’匹配结尾是st的字符
.	匹配任意单个字符	‘b.t’匹配任意b和t之间有1个字符
*	匹配0个或多个任意字符	‘f*n’匹配字符n前面任意个字符f
+	匹配前面字符1次或多次	‘ba+’匹配以b开头后面紧跟至少1个a
[^]	匹配不在括号中的任何字符	‘[^ab]’匹配开头不包括,a或b字幕的
<字符串>	匹配包含指定字符串的文本	‘aaa’匹配字符串aaa
[字符集合]	匹配字符集合中任意1个字符	‘[xz]’匹配x或者z
字符串{n,}	匹配前面字符至少出现n次	‘b{2}’匹配2个或多个b
字符串{n,m}	匹配前面字符至少出现n次不大于m次	‘b{2,3}’匹配最少2个,最多3个b

以上就是MariaDB所支持的所有匹配通配符,这里内容虽然较少,但是还是要独立出来一个章节,正则在数据的批量遍历中能起到至关重要的作用,应该认真掌握.

字符(^):匹配以特定字符或者字符串开头的文本

1.在lyshark表中,查询Name字段以字母b开头的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP '^b';

+-----+-----+------------+-------+
| Uid | Gid | Name       | Price |
+-----+-----+------------+-------+
| b1  | 101 | blackberry | 10.20 |
| b2  | 104 | berry      |  7.60 |
| t1  | 102 | bannana    | 10.30 |
+-----+-----+------------+-------+
3 rows in set (0.01 sec)

2.在lyshark表中,查询Name字段以字母be开头的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP '^be';

+-----+-----+-------+-------+
| Uid | Gid | Name  | Price |
+-----+-----+-------+-------+
| b2  | 104 | berry |  7.60 |
+-----+-----+-------+-------+
1 row in set (0.00 sec)

字符($):匹配以特定字符或者字符串结尾的文本

1.在lyshark表中,查询Name字段以字母y结尾的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'y$';

+-----+-----+------------+-------+
| Uid | Gid | Name       | Price |
+-----+-----+------------+-------+
| b1  | 101 | blackberry | 10.20 |
| b2  | 104 | berry      |  7.60 |
| c0  | 101 | cherry     |  3.20 |
| m2  | 105 | xbabay     |  2.60 |
+-----+-----+------------+-------+
4 rows in set (0.00 sec)

2.在lyshark表中,查询Name字段以字母bay结尾的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'bay$';

+-----+-----+--------+-------+
| Uid | Gid | Name   | Price |
+-----+-----+--------+-------+
| m2  | 105 | xbabay |  2.60 |
+-----+-----+--------+-------+
1 row in set (0.00 sec)

字符(.):匹配任意一个字符

1.在lyshark表中,查询Name字段值包含字母a与g且两个字母之间只有一个字母的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'a.g';

+-----+-----+--------+-------+
| Uid | Gid | Name   | Price |
+-----+-----+--------+-------+
| bs1 | 102 | orange | 11.20 |
| m1  | 106 | mango  | 15.70 |
+-----+-----+--------+-------+
2 rows in set (0.00 sec)

字符(*):匹配前面的字符任意多次,包括0次

1.在lyshark表中,查询Name字段以字母b开头并且b后面出现字母a的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP '^ba*';

+-----+-----+------------+-------+
| Uid | Gid | Name       | Price |
+-----+-----+------------+-------+
| b1  | 101 | blackberry | 10.20 |
| b2  | 104 | berry      |  7.60 |
| t1  | 102 | bannana    | 10.30 |
+-----+-----+------------+-------+
3 rows in set (0.00 sec)

字符(+):匹配前面的字符至少一次

1.在lyshark表中,查询Name字段以字母b开头,且b后面出现字母a至少一次的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP '^ba+';

+-----+-----+---------+-------+
| Uid | Gid | Name    | Price |
+-----+-----+---------+-------+
| t1  | 102 | bannana | 10.30 |
+-----+-----+---------+-------+
1 row in set (0.00 sec)

字符(|):匹配指定字符

1.在lyshark表中,查询Name字段包括字符串on的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'on';

+-----+-----+---------+-------+
| Uid | Gid | Name    | Price |
+-----+-----+---------+-------+
| bs2 | 105 | melon   |  8.20 |
| l2  | 104 | lemon   |  6.40 |
| o2  | 103 | coconut |  9.20 |
+-----+-----+---------+-------+
3 rows in set (0.00 sec)

2.在lyshark表中,查询Name字段包括字符串on或者ap的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'on|ap';

+-----+-----+---------+-------+
| Uid | Gid | Name    | Price |
+-----+-----+---------+-------+
| a1  | 101 | apple   |  5.20 |
| a2  | 103 | apricot |  2.20 |
| bs2 | 105 | melon   |  8.20 |
| l2  | 104 | lemon   |  6.40 |
| o2  | 103 | coconut |  9.20 |
| t2  | 102 | grape   |  5.30 |
+-----+-----+---------+-------+
6 rows in set (0.00 sec)

3.在lyshark表中,查询Name字段使用LIKE关键字匹配on的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name LIKE 'on';
Empty set (0.00 sec)

#结果匹配不到,这也是这两个匹配方法的不同之处.

字符([]):匹配指定字符中任意一个

1.在lyshark表中,查询Name字段包含字母o或者t的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP '[ot]';

+-----+-----+---------+-------+
| Uid | Gid | Name    | Price |
+-----+-----+---------+-------+
| a2  | 103 | apricot |  2.20 |
| bs1 | 102 | orange  | 11.20 |
| bs2 | 105 | melon   |  8.20 |
| l2  | 104 | lemon   |  6.40 |
| m1  | 106 | mango   | 15.70 |
| m3  | 105 | xxtt    | 11.60 |
| o2  | 103 | coconut |  9.20 |
+-----+-----+---------+-------+
7 rows in set (0.01 sec)

字符([^字符集]):匹配不在指定集合中的任何字符

1.在lyshark表中,查询Uid字段包含字母a-e和1-2以外字符的集合,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Uid REGEXP '[^a-e1-2]';

+---------+-----+----------+--------+
| Uid     | Gid | Name     | Price  |
+---------+-----+----------+--------+
| b5      | 107 | xxxx     |   3.60 |
| bs1     | 102 | orange   |  11.20 |
| bs2     | 105 | melon    |   8.20 |
| c0      | 101 | cherry   |   3.20 |
| l2      | 104 | lemon    |   6.40 |
| lyshark | 999 | lysharks | 999.00 |
| m1      | 106 | mango    |  15.70 |
| m2      | 105 | xbabay   |   2.60 |
| m3      | 105 | xxtt     |  11.60 |
| o2      | 103 | coconut  |   9.20 |
| t1      | 102 | bannana  |  10.30 |
| t2      | 102 | grape    |   5.30 |
| t4      | 107 | xbababa  |   3.60 |
+---------+-----+----------+--------+
13 rows in set (0.00 sec)

字符({n,}):匹配前一个字符至少匹配n次的

1.在lyshark表中,查询Name字段,匹配x字符至少出现2次的行,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'x{2,}';

+-----+-----+------+-------+
| Uid | Gid | Name | Price |
+-----+-----+------+-------+
| b5  | 107 | xxxx |  3.60 |
| m3  | 105 | xxtt | 11.60 |
+-----+-----+------+-------+
2 rows in set (0.00 sec)

字符({n,m}):匹配前一个字符至少匹配n次,且不大于m次的记录

1.在lyshark表中,查询Name字段,匹配ba字符至少出现2次且不大于3次的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark where Name REGEXP 'ba{1,3}';

+-----+-----+---------+-------+
| Uid | Gid | Name    | Price |
+-----+-----+---------+-------+
| m2  | 105 | xbabay  |  2.60 |
| t1  | 102 | bannana | 10.30 |
| t4  | 107 | xbababa |  3.60 |
+-----+-----+---------+-------+
3 rows in set (0.00 sec)