MySQL正则表达式

DB version： 5.7.25-log 和 8.0.18

一、MySQL5.7中正则表达式

1.RLIKE() 为REGEXP同义词，见下

2.REGEXP() 完整表达式 expr REGEXP pat
> 如果字符串expr与模式pat指定的正则表达式匹配，则返回1，否则返回0。
> 如果expr或pat为NULL，则返回值为NULL。
> 正则表达式规则明细及使用案例见下方。

3.NOT REGEXP() 完整表达式 expr NOT REGEXP pat 或者expr NOT RLIKE pat。
与NOT (expr REGEXP pat)相同。

正则表达式规则

^ 匹配字符串的开始
mysql> SELECT 'fo\nfo' REGEXP '^fo','ffo\nfo' REGEXP '^fo'; 
+-----------------------+------------------------+
| 'fo\nfo' REGEXP '^fo' | 'ffo\nfo' REGEXP '^fo' |
+-----------------------+------------------------+
|                     1 |                      0 |
+-----------------------+------------------------+

$ 匹配字符串的结尾
mysql> SELECT 'fo\nfoo' REGEXP 'fo$','ffo\nfo' REGEXP 'fo$'; 
+------------------------+------------------------+
| 'fo\nfoo' REGEXP 'fo$' | 'ffo\nfo' REGEXP 'fo$' |
+------------------------+------------------------+
|                      0 |                      1 |
+------------------------+------------------------+

. 匹配任何字符（包括回车和换行符）
mysql> SELECT 'fo\nfo' REGEXP 'fo.f','fo\nfo' REGEXP 'fo.o';
+------------------------+------------------------+
| 'fo\nfo' REGEXP 'fo.f' | 'fo\nfo' REGEXP 'fo.o' |
+------------------------+------------------------+
|                      1 |                      0 |
+------------------------+------------------------+

o* 匹配字符o出现0次或多次
mysql> SELECT 'fo\nfo' REGEXP 'fo*','fo\nfo' REGEXP 'fo*f','fo\nfo' REGEXP 'fo*o';
+-----------------------+------------------------+------------------------+
| 'fo\nfo' REGEXP 'fo*' | 'fo\nfo' REGEXP 'fo*f' | 'fo\nfo' REGEXP 'fo*o' |
+-----------------------+------------------------+------------------------+
|                     1 |                      0 |                      1 |
+-----------------------+------------------------+------------------------+

o+ 匹配字符o出现1次或多次
mysql> SELECT 'fo\nfo' REGEXP 'fo+','fo\nfo' REGEXP 'fo+f','fo\nfo' REGEXP 'fo+o';
+----------------------+------------------------+------------------------+
| 'fo\nfo' REGEXP 'fo+' | 'fo\nfo' REGEXP 'fo+f' | 'fo\nfo' REGEXP 'fo+o' |
+----------------------+------------------------+------------------------+
|                    1 |                      0 |                      0 |
+----------------------+------------------------+------------------------+

o? 匹配字符o出现0次或1次
mysql> SELECT 'fo\nfo' REGEXP 'fo?','fo\nfo' REGEXP 'fo?f','fo\nfo' REGEXP 'fo?o';
+-----------------------+------------------------+------------------------+
| 'fo\nfo' REGEXP 'fo?' | 'fo\nfo' REGEXP 'fo?f' | 'fo\nfo' REGEXP 'fo?o' |
+-----------------------+------------------------+------------------------+
|                     1 |                      0 |                      1 |
+-----------------------+------------------------+------------------------+

de|abc 匹配字符串 de 或者 abc，可结合上面的 ^ $ * + ? 等使用
mysql> SELECT 'finded' REGEXP 'de|abc','dest' REGEXP '^(de|abc)','dabcabc' REGEXP '(de|abc)?','rest' REGEXP 'de|abc';   
+--------------------------+---------------------------+------------------------------+------------------------+
| 'finded' REGEXP 'de|abc' | 'dest' REGEXP '^(de|abc)' | 'dabcabc' REGEXP '(de|abc)?' | 'rest' REGEXP 'de|abc' |
+--------------------------+---------------------------+------------------------------+------------------------+
|                        1 |                         1 |                            1 |                      0 |
+--------------------------+---------------------------+------------------------------+------------------------+

(abc)* 匹配字符串abc出现0次或多次，同上 o*
mysql> SELECT 'abcabc' REGEXP '(abc)*','abcab' REGEXP '(abc)*','e' REGEXP '(abcd)*'; 
+--------------------------+-------------------------+----------------------+
| 'abcabc' REGEXP '(abc)*' | 'abcab' REGEXP '(abc)*' | 'e' REGEXP '(abcd)*' |
+--------------------------+-------------------------+----------------------+
|                        1 |                       1 |                    1 |
+--------------------------+-------------------------+----------------------+

{1}, {2,3}   {n}和{m,n}符号提供了一种更通用的方式来编写正则表达式，以匹配模式的前一个原子(或“块”)的多次出现。m和n是整数，在0 到 255 之间，且 m <= n 。
如以下形式：
o*  可以被改写 成 o{0,}
o+ 可以被改写 成 o{1,}
o? 可以被改写 成 o{0,1}

mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e' , 'abcde' REGEXP 'a[bcd]{3}e' ,'abbbe' REGEXP 'a[bcd]{3}e' , 'abcde' REGEXP 'a[bcd]{1,5}e','abcde' REGEXP 'a[bcd]{4,5}e' ; 
+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-------------------------------+
| 'abcde' REGEXP 'a[bcd]{2}e' | 'abcde' REGEXP 'a[bcd]{3}e' | 'abbbe' REGEXP 'a[bcd]{3}e' | 'abcde' REGEXP 'a[bcd]{1,5}e' | 'abcde' REGEXP 'a[bcd]{4,5}e' |
+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-------------------------------+
|                           0 |                           1 |                           1 |                             1 |                             0 |
+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-------------------------------+
[abc]{3}  表示匹配 a、b、c三个字符任意一个或多个出现三次，如 aaa、aab、abc、ccc等均符合。
[bcd]{1,5} 表示匹配 a、b、c三个字符任意一个或多个出现1次到5次，如 a、ab、aaa、aab、abcc、aaaaa、bbccd等均符合。

[0-9X]     匹配0-9的数字或X；
[a-dX]     匹配a-d的所有字母（即 a,b,c,d）或X；
[^a-dX]    匹配非a-d、非X的字母。

mysql> SELECT  'aXbc' REGEXP '[a-dXYZ]','aXbc' REGEXP '^[a-dXYZ]$','aXbc' REGEXP '^[a-dXYZ]{4}$','aXbc' REGEXP '^[a-dXYZ]+$','aXbc' REGEXP '^[^a-dXYZ]+$','google' REGEXP '^[^a-dXYZ]+$' ; 
+--------------------------+----------------------------+-------------------------------+-----------------------------+------------------------------+--------------------------------+
| 'aXbc' REGEXP '[a-dXYZ]' | 'aXbc' REGEXP '^[a-dXYZ]$' | 'aXbc' REGEXP '^[a-dXYZ]{4}$' | 'aXbc' REGEXP '^[a-dXYZ]+$' | 'aXbc' REGEXP '^[^a-dXYZ]+$' | 'google' REGEXP '^[^a-dXYZ]+$' |
+--------------------------+----------------------------+-------------------------------+-----------------------------+------------------------------+--------------------------------+
|                        1 |                          0 |                             1 |                           1 |                            0 |                              1 |
+--------------------------+----------------------------+-------------------------------+-----------------------------+------------------------------+--------------------------------+

[.characters.]  在方括号表达式（使用 '[' 和 ']' 编写）中，匹配该整理元素的字符序列。 字符可以是单个字符，也可以是字符名称，例如newline。
附录1 列出了允许的字符名称。

mysql> SELECT '~' REGEXP '[[.~.]]','~' REGEXP '[[.tilde.]]';
+----------------------+--------------------------+
| '~' REGEXP '[[.~.]]' | '~' REGEXP '[[.tilde.]]' |
+----------------------+--------------------------+
|                    1 |                        1 |
+----------------------+--------------------------+

[=character_class=]  在方括号表达式（使用 '[' 和 ']' 编写）中，[= character_class =]表示等价类。 
它匹配所有具有相同归类值的字符，包括自身。 
例如，如果 o 和（+）是等价类的成员，则[[= o =]]，[[=（+）=]]和[o（+）]都是同义词。 
等效类不能用作范围的末尾。

[:character_class:]  在方括号表达式（使用 '[' 和 ']' 编写）中，[:character_class:]表示与属于该类的所有字符匹配的字符类。 
附录2 列出了允许的字符类名称。

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+' , '012345' REGEXP '[[:digit:]]+' , 'abc' REGEXP '[[:digit:]]+'; 
+------------------------------------+--------------------------------+-----------------------------+
| 'justalnums' REGEXP '[[:alnum:]]+' | '012345' REGEXP '[[:digit:]]+' | 'abc' REGEXP '[[:digit:]]+' |
+------------------------------------+--------------------------------+-----------------------------+
|                                  1 |                              1 |                           0 |
+------------------------------------+--------------------------------+-----------------------------+

[[:<:]], [[:>:]]   这些标记代表单词边界，它们分别匹配单词的开头和结尾。
mysql> SELECT 'asd  asd  word dasda ads ' REGEXP '[[:<:]]word[[:>:]]','我是 一句 完整的 话' REGEXP '[[:<:]]完整的[[:>:]]','我是 一句 完整的 话' REGEXP '[[:<:]]完整[[:>:]]'; 
+---------------------------------------------------------+-----------------------------------------------------+---------------------------------------------------+
| 'asd  asd  word dasda ads ' REGEXP '[[:<:]]word[[:>:]]' | '我是 一句 完整的 话' REGEXP '[[:<:]]完整的[[:>:]]' | '我是 一句 完整的 话' REGEXP '[[:<:]]完整[[:>:]]' |
+---------------------------------------------------------+-----------------------------------------------------+---------------------------------------------------+
|                                                       1 |                                                   1 |                                                 0 |
+---------------------------------------------------------+-----------------------------------------------------+---------------------------------------------------+

二、MySQL8 新增正则表达式

1.REGEXP_LIKE() REGEXP 和 RLIKE 是 REGEXP_LIKE() 的同义词。
使用明细见上面5.7版本 '正则表达式规则'

2.REGEXP_INSTR() 完整表达式 REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
> 返回与模式 pat 指定的正则表达式匹配的字符串 expr 的子字符串的起始索引，如果没有匹配则为0。
> 如果 expr 或 pat 为NULL，则返回值为NULL。字符索引从1开始。
完整表达式各参数含义：
pos: 从expr的pos位置开始搜索，默认为1；
occurrence: 返回第occurrence个匹配的子字符串，默认为1；
return_option: 返回类型，如果为0，则返回匹配到的子字符串的第一个字符的位置，如果为1，则返回匹配到的子字符串最后一个字符的位置+1，默认为0；
match_type: 匹配类型，包含以下值，可同时使用一个或多个；
如果在match_type中指定了矛盾选项的字符，则以最右边的字符为准。

c: 匹配时区分大小写
i: 匹配时不区分大小写
m: 多行模式。在字符串内识别行结束符。默认行为是只在字符串表达式的开始和结束处匹配行结束符。
n: 字符.匹配行结束，默认.匹配到行结尾停止。
u: 仅使用unix上的行结束符。只有换行符被识别为行结束，能够被 . ^和$ 匹配。
m n u 三个参数不知如何举例。

mysql> SELECT REGEXP_INSTR('dog cat dog pig', 'dog'),REGEXP_INSTR('dog cat dog pig', 'dog',2,1,0),REGEXP_INSTR('dog cat dog pig', 'dog',2,1,1),REGEXP_INSTR('dog cat dog pig', 'Dog',2,1,1,'c'),REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+----------------------------------------+----------------------------------------------+----------------------------------------------+--------------------------------------------------+-------------------------------------+
| REGEXP_INSTR('dog cat dog pig', 'dog') | REGEXP_INSTR('dog cat dog pig', 'dog',2,1,0) | REGEXP_INSTR('dog cat dog pig', 'dog',2,1,1) | REGEXP_INSTR('dog cat dog pig', 'Dog',2,1,1,'c') | REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+----------------------------------------+----------------------------------------------+----------------------------------------------+--------------------------------------------------+-------------------------------------+
|                                      1 |                                            9 |                                           12 |                                                0 |                                   8 |
+----------------------------------------+----------------------------------------------+----------------------------------------------+--------------------------------------------------+-------------------------------------+

3.REGEXP_REPLACE() 完整表达式 REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
> 用替换字符串repl替换字符串expr中与模式pat指定的正则表达式匹配的匹配项，并返回结果字符串。
> 如果expr，pat或repl为NULL，则返回值为NULL
pos,occurrence,match_type 参数含义见REGEXP_INSTR()

mysql>  SELECT REGEXP_REPLACE('a b c', 'b', 'X'),REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3), REGEXP_REPLACE('abc def ghi', 'def', 'X', 2,1);
+-----------------------------------+----------------------------------------------------+------------------------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') | REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) | REGEXP_REPLACE('abc def ghi', 'def', 'X', 2,1) |
+-----------------------------------+----------------------------------------------------+------------------------------------------------+
| a X c                             | abc def X                                          | abc X ghi                                      |
+-----------------------------------+----------------------------------------------------+------------------------------------------------+

4.REGEXP_SUBSTR() 完整表达式 REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])
> 返回与模式pat指定的正则表达式匹配的字符串expr的子字符串；如果不匹配，则返回NULL。
> 如果expr或pat为NULL，则返回值为NULL。
pos,occurrence,match_type 参数含义见REGEXP_INSTR()


mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+'),REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3),REGEXP_SUBSTR('abc def ghi', '[a-z]+', 4, 2);
+----------------------------------------+----------------------------------------------+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') | REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) | REGEXP_SUBSTR('abc def ghi', '[a-z]+', 4, 2) |
+----------------------------------------+----------------------------------------------+----------------------------------------------+
| abc                                    | ghi                                          | ghi                                          |
+----------------------------------------+----------------------------------------------+----------------------------------------------+

附录1：

附录1.png

附录2：

附录2.png

最后编辑于：2021.02.03 18:49:04

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,839评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,543评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,116评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,371评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,384评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,111评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,416评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,053评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,558评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,007评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,117评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,756评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,324评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,315评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,539评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,578评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,877评论 2赞 345

MySQL正则表达式

一、MySQL5.7中正则表达式

正则表达式规则

二、MySQL8 新增正则表达式

附录1： 附录1.png

附录2： 附录2.png

推荐阅读更多精彩内容

附录1：

附录1.png

附录2：

附录2.png