MySQL8.0目前支持哪几种正则表达式函数

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

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目前支持哪几种正则表达式函数，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。