Which regular expression functions are currently supported by MySQL8.0

本文主要给大家介绍MySQL8.0目前支持哪几种正则表达式函数，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下MySQL8.0目前支持哪几种正则表达式函数吧。

NameDescriptionNOT REGEXPNegation of REGEXPREGEXPWhether string matches regular expressionREGEXP_INSTR()Starting index of substring matching regular expressionREGEXP_LIKE()Whether string matches regular expressionREGEXP_REPLACE()Replace substrings matching regular expressionREGEXP_SUBSTR()Return substring matching regular expressionRLIKEWhether string matches regular expression

regexp、rlike、regexp_like()三者功能相同，只是写法不同

not regexp是否定形式

mysql> select 'abc' regexp '^a';+-------------------+| 'abc' regexp '^a' |+-------------------+| 1 |+-------------------+1 row in set (0.00 sec)mysql> select 'abc' rlike '^a';+------------------+| 'abc' rlike '^a' |+------------------+| 1 |+------------------+1 row in set (0.00 sec)mysql> select regexp_like('abc','^a');+-------------------------+| regexp_like('abc','^a') |+-------------------------+| 1 |+-------------------------+1 row in set (0.00 sec)mysql> select 'abc' not regexp '^a';+-----------------------+| 'abc' not regexp '^a' |+-----------------------+| 0 |+-----------------------+1 row in set (0.00 sec)mysql> select not regexp_like('abc','^a');+-----------------------------+| not regexp_like('abc','^a') |+-----------------------------+| 0 |+-----------------------------+1 row in set (0.00 sec)

regexp_replace()替代函数

mysql> select regexp_replace('a1,b2,c3','[a-z]{1}','b');+-------------------------------------------+| regexp_replace('a1,b2,c3','[a-z]{1}','b') |+-------------------------------------------+| b1,b2,b3 |+-------------------------------------------+1 row in set (0.00 sec)mysql> select regexp_replace('aaa,b2,c3','[a-z]{2}','d');+--------------------------------------------+| regexp_replace('aaa,b2,c3','[a-z]{2}','d') |+--------------------------------------------+| da,b2,c3 |+--------------------------------------------+1 row in set (0.00 sec)

regexp_substr() 截断字符串

mysql> select regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,1);+-----------------------------------------------------+| regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,1) |+-----------------------------------------------------+| a1 |+-----------------------------------------------------+1 row in set (0.00 sec)mysql> select regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,2);+-----------------------------------------------------+| regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,2) |+-----------------------------------------------------+| b1 |+-----------------------------------------------------+1 row in set (0.00 sec)mysql> select regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,3);+-----------------------------------------------------+| regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,3) |+-----------------------------------------------------+| c1 |+-----------------------------------------------------+1 row in set (0.00 sec)mysql> select regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,4);+-----------------------------------------------------+| regexp_substr('a1,b1,c1,ddds','[a-z 0-9]{1,9}',1,4) |+-----------------------------------------------------+| ddds |+-----------------------------------------------------+1 row in set (0.00 sec)

regexp_instr() 返回匹配的字符串开始位置index.

mysql> select regexp_instr('dogcatdog','dog',1);+-----------------------------------+| regexp_instr('dogcatdog','dog',1) |+-----------------------------------+| 1 |+-----------------------------------+1 row in set (0.00 sec)mysql> select regexp_instr('dogcatdog','dog',2);+-----------------------------------+| regexp_instr('dogcatdog','dog',2) |+-----------------------------------+| 7 |+-----------------------------------+1 row in set (0.00 sec)mysql> select regexp_instr('a aa aaa aaaa','a{3}',1);+----------------------------------------+| regexp_instr('a aa aaa aaaa','a{3}',1) |+----------------------------------------+| 6 |+----------------------------------------+1 row in set (0.00 sec)

看完以上关于MySQL8.0目前支持哪几种正则表达式函数，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。