Mysql语句的RegExp的用法与案例

一、RegExp基本语法

1. JavaScript RegExp 参考手册
2. MySQL REGEXP：正则表达式查询
3. MySQL中REGEXP正则表达式使用大全

字符	描述
^	从开始行开始匹配
$	从末端行开始匹配
.	匹配任意单个字符除了换行符
[ ]	[ ] 内为字符集，匹配 [ ] 内任一字符
[^ ]	匹配除了方括号里的任意字符
*	匹配零个或多个在它前面的字 符
+	匹配前面的字符 1 次或多次
?	匹配 0 或 1 个 ？号前的字符。
{n,m}	匹配前面的字符串至少 n 次， 至多 m 次
(xyz)	匹配与 xyz 完全相等的字符串
\s	匹配任意单个字符除了换行符
\	转义字符,匹配一些保留的字符 [ ] ( ) { } . * + ? ^ $ \

[.characters.]  在括号表达式中（使用[和]）,匹配用于校对元素的字符序列，字符为单个字符或新行等字符名

mysql> SELECT ‘~’ REGEXP ‘[[.~.]]’;                                                        -> 1

mysql> SELECT ‘~’ REGEXP ‘[[.tilde.]]’;                                                 -> 1

 

[=character_class=]

在括号表达式中（使用[和]），[=character_class=]表示等同类。它与具有相同校对值的所有字符匹配，包括它本身，

[[=a=]]              等同于[a(+)],[a+],[a{1,}]

 

[:character_class:]

在括号表达式中（使用[和]），[:character_class:]表示与术语类的所有字符匹配的字符类。

标准的类名称是：

alnum 		文字数字字符

alpha		文字字符

blank		空白字符

cntrl		控制字符

digit		数字字符

graph		图形字符

lower		小写文字字符

print		图形或空格字符

punct		标点字符

space		空格、制表符、新行、和回车

upper		大写文字字符

xdigit		十六进制数字字符

[[:<:]], [[:>:]]		
这些标记表示word边界,它们分别与word的开始和结束匹配
word是一系列字字符，其前面和后面均没有字字符。字符是alnum类中的字母数字字符或下划线(_)

---

二、Mysql使用RegExp案例

案例1：在json数据里面，找到class_detail_students_id这个字段，并且数据包含学生编号“58888828”

思路：
在这个数据 -> {"class_id":2,"class_detail_students_id":",58888881,58888828,58888881,"}
需要查找 -> class_detail_students_id 这个字段
并查看 -> ,58888881,58888828,58888881, 里面是否包含58888828
注意点：数据里面有双引号，这是regexp数据可以用单引号包围，里面的双引号用\\转义

匹配规则regexp ==>'(\\"class_detail_students_id\\")(:\\")[(0-9)|,]*(,58888828,)[(0-9)|,]*(\\")'


解答完整的sql:
select * from mole_class_detail_change where 
current_value regexp '(\\"class_detail_students_id\\")(:\\")[(0-9)|,]*(,58888828,)[(0-9)|,]*(\\")'

案例2：匹配000000002 _ _ _ _ _ _ _ _ 1900 和 000000003 _ _ _ _ _ _ _ _1700，其中"__" 这是8个空位，只能是数字

思路：
这是两个字符串，都空出中间8为数字，可以用"()"
(000000002) -> 匹配  "000000002"
[[:digit:]] -> 匹配  数字字符
{8}			-> 匹配前一个括号里面8次
|			-> 或的意思


解答完整的sql:
select * from mole_class_detail_change 
where class_detail_code regexp '((000000002)([[:digit:]]{8})(1900))|((000000003)([[:digit:]]{8})(1700))'

案例3：每个用户有多个身份权限，查询用户同时有a、b 、 c 权限的用户

上方截图展示了表数据 和分组查询的结果
.	->	匹配任意单个字符除了换行符
*	->	匹配零个或多个在它前面的字符
+	->	匹配1个或多个在它前面的字符
[abc]	->	匹配a/b/c里面的一个字符
[,|,.+,]	-> 	匹配','(逗号) 或者 ',abcfdg等等,'(逗号包围的数据)


匹配规则regexp	==>	 '.*,[abc][,|(,.+,)][abc][,|(,.+,)][abc][,|(,.+,)]'


解答完整的sql:
select user_id,concat(',',GROUP_CONCAT(DISTINCT role_id),',') as group_role from test GROUP BY user_id 
having group_role regexp '.*,[abc][,|(,.+,)][abc][,|(,.+,)][abc][,|(,.+,)]'