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