• MYSQL正则表达式函数详解和实战

    正则表达式是一种方便的方式来匹配一系列的字符串，其能够将复杂的匹配表达式转换为一种简单的匹配模式，并且能够支持大多数的搜索关键字。因此，使用正则表达式来优化 MySQL 查询可以减小搜索范围的方式来实现。

    虽然MySQL正则表达式非常强大，但在处理大量数据时可能会影响性能。在某种场景下不推荐使用正则表达式。

    目录

    一、REGEXP操作符

    二、NOT REGEXP操作符

    三、RLIKE操作符

    四、NOT RLIKE操作符

    五、REGEXP_LIKE函数

    六、REGEXP_REPLACE函数

    七、REGEXP_INSTR函数

    八、REGEXP_SUBSTR函数

    MySQL支持正则表达式，并提供了几个函数和操作符来使用它们。以下是一些常用的MySQL正则表达式函数和操作符。

    一、REGEXP操作符

    在 MySQL 中， REGEXP 操作符检查一个字符串和一个正则表达式是否匹配。
    REGEXP 操作符等同于 RLIKE 操作符和 REGEXP_LIKE() 函数。REGEXP 不区分大小写。
    NOT REGEXP 操作符是 REGEXP 操作符的否定操作。

    语法：

    str REGEXP regexp

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。

    返回值：

    • 若字符串 str 和正则表达式 regexp 匹配，返回 1，否则返回 0。
    • 若任意一个参数为 NULL，返回 NULL。

    这里列出了几个常见的 REGEXP示例：

    1. SELECT
    2.     'hello' REGEXP '^[a-z]+$',
    3.     'hello' REGEXP '^[A-Z]+$',
    4.     '12345' REGEXP '[0-9]+$',
    5.     '12345' REGEXP '^\\d+$',
    6.     '123ab' REGEXP '^\\d*$',
    7.     '123ab' REGEXP '^.*$'

    输出如下：

    1. 'hello' REGEXP '^[a-z]+$': 1
    2. 'hello' REGEXP '^[A-Z]+$': 1
    3.  '12345' REGEXP '[0-9]+$': 1
    4.   '12345' REGEXP '^\\d+$': 1
    5.   '123ab' REGEXP '^\\d*$': 0
    6.     '123ab' REGEXP '^.*$': 1

    二、NOT REGEXP操作符

    在 MySQL 中， NOT REGEXP 操作符检查一个字符串和一个正则表达式是否不匹配。
    NOT REGEXP 操作符是 REGEXP 操作符的否定操作。NOT REGEXP 不区分大小写。

    语法：

    str NOT REGEXP regexp

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。

    返回值：

    • 若字符串 str 和正则表达式 regexp 不匹配，返回 1，否则返回 0。
    • 若任意一个参数为 NULL，返回 NULL。

    这里列出了几个常见的 NOT REGEXP 示例：

    1. SELECT
    2.     'hello' NOT REGEXP '^[a-z]+$',
    3.     'hello' NOT REGEXP '^[A-Z]+$',
    4.     '12345' NOT REGEXP '[0-9]+$',
    5.     '12345' NOT REGEXP '^\\d+$',
    6.     '123ab' NOT REGEXP '^\\d*$',
    7.     '123ab' NOT REGEXP '^.*$'

    输出如下：

    1. 'hello' NOT REGEXP '^[a-z]+$': 0
    2. 'hello' NOT REGEXP '^[A-Z]+$': 0
    3.  '12345' NOT REGEXP '[0-9]+$': 0
    4.   '12345' NOT REGEXP '^\\d+$': 0
    5.   '123ab' NOT REGEXP '^\\d*$': 1
    6.     '123ab' NOT REGEXP '^.*$': 0

    三、RLIKE操作符

    在 MySQL 中， RLIKE 操作符检查一个字符串和一个正则表达式是否匹配。
    RLIKE 操作符等同于 REGEXP 操作符和 RLIKE_LIKE() 函数。RLIKE 不区分大小写。

    语法：

    str RLIKE regexp

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。

    返回值：

    • 若字符串 str 和正则表达式 regexp 匹配，返回 1，返回 0。
    • 若任意一个参数为 NULL，返回NULL。

    这里列出了几个常见的 RLIKE 示例：

    1. SELECT
    2.     'hello' RLIKE '^[a-z]+$',
    3.     'hello' RLIKE '^[A-Z]+$',
    4.     '12345' RLIKE '[0-9]+$',
    5.     '12345' RLIKE '^\\d+$',
    6.     '123ab' RLIKE '^\\d*$',
    7.     '123ab' RLIKE '^.*$'

    输出：

    1. 'hello' RLIKE '^[a-z]+$': 1
    2. 'hello' RLIKE '^[A-Z]+$': 1
    3.  '12345' RLIKE '[0-9]+$': 1
    4.   '12345' RLIKE '^\\d+$': 1
    5.   '123ab' RLIKE '^\\d*$': 0
    6.     '123ab' RLIKE '^.*$': 1

    四、NOT RLIKE操作符

    在 MySQL 中， NOT RLIKE 操作符检查一个字符串是否和一个正则表达式不匹配。
    NOT RLIKE 操作符是 RLIKE 操作符的否定操作。

    语法：

    str NOT RLIKE regexp

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。

    返回值：

    • 若字符串 str 和正则表达式 regexp 不匹配，返回 1，否则返回 0。
    • 若任意一个参数为 NULL，NULL。

    这里列出了几个常见的 NOT RLIKE 示例：

    1. SELECT
    2.     'hello' NOT RLIKE '^[a-z]+$',
    3.     'hello' NOT RLIKE '^[A-Z]+$',
    4.     '12345' NOT RLIKE '[0-9]+$',
    5.     '12345' NOT RLIKE '^\\d+$',
    6.     '123ab' NOT RLIKE '^\\d*$',
    7.     '123ab' NOT RLIKE '^.*$'

    输出：

    1. 'hello' NOT RLIKE '^[a-z]+$': 0
    2. 'hello' NOT RLIKE '^[A-Z]+$': 0
    3.  '12345' NOT RLIKE '[0-9]+$': 0
    4.   '12345' NOT RLIKE '^\\d+$': 0
    5.   '123ab' NOT RLIKE '^\\d*$': 1
    6.     '123ab' NOT RLIKE '^.*$': 0

    五、REGEXP_LIKE函数

    MySQL REGEXP_LIKE() 函数检查一个字符串是否和一个正则表达式是否匹配。
    REGEXP 和 RLIKE 操作符等同于 REGEXP_LIKE() 函数。
    默认情况下， REGEXP_LIKE() 函数执行不区分大小写的匹配。

    注：MYSQL8支持此函数，MYSQL5版本不支持此函数。

    语法：

    1. REGEXP_LIKE(str, regexp)
    2. REGEXP_LIKE(str, regexp, mode)

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。
    • mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
      • c: 区分大小写
      • i: 不区分大小写
      • m: 多行匹配模式
      • n: 点 . 可以匹配行结束
      • u: 仅限 unix 行结尾
    • 如果匹配模式存在矛盾的选项，则以最右边的为准。

    返回值：

    • 若字符串 str 和正则表达式 regexp 匹配，返回 1，否则返回 0。
    • 若 str 或者 regexp 为 NULL，返回 NULL。

    这里列出了几个常见的 REGEXP_LIKE() 示例：

    1. SELECT
    2.     REGEXP_LIKE('hello', '^[a-z]+$'),
    3.     REGEXP_LIKE('hello', '^[A-Z]+$'),
    4.     REGEXP_LIKE('12345', '[0-9]+$'),
    5.     REGEXP_LIKE('12345', '^\\d+$'),
    6.     REGEXP_LIKE('123ab', '^\\d*$'),
    7.     REGEXP_LIKE('123ab', '^.*$')

    输出：

    1.      REGEXP_LIKE('hello', '^[a-z]+$'): 1
    2.      REGEXP_LIKE('hello', '^[A-Z]+$'): 1
    3.       REGEXP_LIKE('12345', '[0-9]+$'): 1
    4.        REGEXP_LIKE('12345', '^\\d+$'): 1
    5.        REGEXP_LIKE('123ab', '^\\d*$'): 0
    6.          REGEXP_LIKE('123ab', '^.*$'): 1

     可以将匹配模式设置为 'c' 来启用匹配时区分大小写（不区分大小写是 i ）：

    SELECT REGEXP_LIKE('hello', '^[A-Z]+$', 'c');

    输出：

    1. +---------------------------------------+
    2. | REGEXP_LIKE('hello', '^[A-Z]+$', 'c') |
    3. +---------------------------------------+
    4. |                                     0 |
    5. +---------------------------------------+

    六、REGEXP_REPLACE函数

    MySQL REGEXP_REPLACE() 函数在一个字符串中使用新内容替换一个和指定的正则表达式匹配的内容。
    默认情况下， REGEXP_REPLACE() 函数执行不区分大小写的匹配。

    注：MYSQL8支持此函数，MYSQL5版本不支持此函数。

    语法：

    1. REGEXP_REPLACE(str, regexp, replacement)
    2. REGEXP_REPLACE(str, regexp, replacement, position)
    3. REGEXP_REPLACE(str, regexp, replacement, position, occurrence)
    4. REGEXP_REPLACE(str, regexp, replacement, position, occurrence, mode)

    参数说明：

    • str：必需的。一个字符串。
    • egexp：必需的。正则表达式。
    • replacement：必需的。用来替换的字符串。
    • position：可选的。开始搜索的起始位置。默认为从头开始。
    • occurrence：可选的。替换第几次匹配的内容。默认为替换所有匹配的内容。
    • mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
      • c: 区分大小写
      • i: 不区分大小写
      • m: 多行匹配模式
      • n: 点 . 可以匹配行结束
      • u: 仅限 unix 行结尾
    • 如果匹配模式存在矛盾的选项，则以最右边的为准。

    返回值：

    • 在 str 中使用 replacement 替换和正则表达式 regexp 匹配的内容，并返回替换后的结果。
    • 若str, regexp 或者 replacement 为 NULL，返回 NULL。

    这里列出了几个常见的 REGEXP_REPLACE() 示例：

    1. +-------------------------------------------------------+
    2. | SELECT REGEXP_REPLACE('123 abc 456 def', '\\s+', '-') |
    3. +-------------------------------------------------------+
    4. | 123-abc-456-def                                       |
    5. +-------------------------------------------------------+
    1. +-------------------------------------------------------------+
    2. | SELECT REGEXP_REPLACE('123 abc 456 def', '\\d+', 'X', 1, 2) |
    3. +-------------------------------------------------------------+
    4. | 123 abc X def                                               |
    5. +-------------------------------------------------------------+

    七、REGEXP_INSTR函数

    MySQL REGEXP_INSTR() 函数从一个字符串中搜索和指定模式匹配的子串并返回的子串的索引（注：第一个位置是1，不是0）。
    默认情况下， REGEXP_INSTR() 函数执行不区分大小写的匹配。

    注：MYSQL8支持此函数，MYSQL5版本不支持此函数。
    语法：

    1. REGEXP_INSTR(str, regexp)
    2. REGEXP_INSTR(str, regexp, position)
    3. REGEXP_INSTR(str, regexp, position, occurrence)
    4. REGEXP_INSTR(str, regexp, position, occurrence, return_option)
    5. REGEXP_INSTR(str, regexp, position, occurrence, return_option, mode)

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。
    • position：可选的。开始搜索的起始位置。默认为 1。
    • occurrence：可选的。第几次匹配的内容。默认为 1。
    • return_option：可选的。指定返回哪一种位置索引。如果为 0，返回匹配的子串的第一个字符的位置索引；如果为 1，返回匹配的子串的后面的位置索引。默认为 0.
    • mode：可选的。执行匹配采用的模式。可使用以下其中一个或多个：
      • c: 区分大小写
      • i: 不区分大小写
      • m: 多行匹配模式
      • n: 点 . 可以匹配行结束
      • u: 仅限 unix 行结尾
    • 如果匹配模式存在矛盾的选项，则以最右边的为准。

    返回值：

    • 从一个字符串 str 中搜索和指定模式 regexp 匹配的子串并返回的子串的索引。如果没有匹配到，则返回 NULL。
    • 若str 或者 regexp 为 NULL，返回 NULL。

    这里列出了几个常见的 REGEXP_INSTR() 示例：

    • 匹配第一个数字（或者连续数字）的索引：
    1. +------------------------------------------------+
    2. | SELECT REGEXP_INSTR('123 abc 456 def', '\\d+') |
    3. +------------------------------------------------+
    4. |                                              1 |
    5. +------------------------------------------------+
    • 匹配第二个数字（或者连续数字）的索引：（ 第一个处为连续数字）
    1. +------------------------------------------------------+
    2. | SELECT REGEXP_INSTR('123 abc 456 def', '\\d+', 1, 2) |
    3. +------------------------------------------------------+
    4. |                                                    9 |
    5. +------------------------------------------------------+
    • 匹配第一个数字（或连续数字）后的索引：
    1. +---------------------------------------------------------+
    2. | SELECT REGEXP_INSTR('123 abc 456 def', '\\d+', 1, 1, 1) |
    3. +---------------------------------------------------------+
    4. |                                                       4 |
    5. +---------------------------------------------------------+
    • 执行采用模式匹配：
    1. -- 区分大小写
    2. +--------------------------------------------------------------+
    3. | SELECT REGEXP_INSTR('123 aaa 456 aaa', 'A{3}', 1, 1, 0, 'c') |
    4. +--------------------------------------------------------------+
    5. |                                                            0 |
    6. +--------------------------------------------------------------+
    7. -- 不区分大小写
    8. +--------------------------------------------------------------+
    9. | SELECT REGEXP_INSTR('123 aaa 456 aaa', 'A{3}', 1, 1, 0, 'i') |
    10. +--------------------------------------------------------------+
    11. |                                                            5 |
    12. +--------------------------------------------------------------+

    八、REGEXP_SUBSTR函数

    MySQL REGEXP_SUBSTR() 函数从一个字符串获取和指定模式匹配的子串并返回。
    默认情况下， REGEXP_SUBSTR() 函数执行不区分大小写的匹配。

    注：MYSQL8支持此函数，MYSQL5版本不支持此函数。

    语法：

    1. REGEXP_SUBSTR(str, regexp)
    2. REGEXP_SUBSTR(str, regexp, position)
    3. REGEXP_SUBSTR(str, regexp, position, occurrence)
    4. REGEXP_SUBSTR(str, regexp, position, occurrence, mode)

    参数说明：

    • str：必需的。一个字符串。
    • regexp：必需的。正则表达式。
    • position：可选的。开始搜索的起始位置。默认为 1。
    • occurrence：可选的。第几次匹配的内容。默认为 1。
    • mode：可选的。执行匹配采用的模式。。可使用以下其中一个或多个：
      • c: 区分大小写
      • i: 不区分大小写
      • m: 多行匹配模式
      • n: 点 . 可以匹配行结束
      • u: 仅限 unix 行结尾
    • 如果匹配模式存在矛盾的选项，则以最右边的为准。

    返回值：

    • 在 str 中搜索和正则表达式 regexp 匹配的子字符串并返回。如果没有匹配到，则返回 NULL。
    • 若 str 或者 regexp 为 NULL，返回 NULL。

    这里列出了几个常见的 REGEXP_INSTR() 示例：

    • 以下语句用来找出字符串 '123 abc 456 def' 中的第一组连续的数字：
    1. +-------------------------------------------------+
    2. | SELECT REGEXP_SUBSTR('123 abc 456 def', '\\d+') |
    3. +-------------------------------------------------+
    4. | 123                                             |
    5. +-------------------------------------------------+
    • 如果想找出字符串 '123 abc 456 def' 中的第二组连续的数字，请使用下面的语句：
    1. +--------------------------------------------------------+
    2. | SELECT REGEXP_SUBSTR('123 abc 456 def', '\\d+', 1, 2); |
    3. +--------------------------------------------------------+
    4. | 456                                                    |
    5. +--------------------------------------------------------+
    • 执行采用模式匹配：
    1. -- 区分大小写
    2. +------------------------------------------------------------+
    3. | SELECT REGEXP_SUBSTR('123 aaa 456 aaa', 'A{3}', 1, 1, 'c') |
    4. +------------------------------------------------------------+
    5. |                                                            |
    6. +------------------------------------------------------------+
    7. -- 不区分大小写
    8. +------------------------------------------------------------+
    9. | SELECT REGEXP_SUBSTR('123 aaa 456 aaa', 'A{3}', 1, 1, 'i') |
    10. +------------------------------------------------------------+
    11. | aaa                                                        |
    12. +------------------------------------------------------------+

    PS：本次测试MYSQL版本分别为 MYSQL5.7.36 和 MYSQL8.0.32

  • 相关阅读:
    VulnHubBreach1.0[渗透测试]新手必看
    Spring修炼之旅（3）自动装配与注解开发
    WebSocket网络协议
    轨迹纠偏解决方法
    前端js读取本地md或txt文件内容
    Web前端—CSEO、Favicon、小兔鲜儿电商网站顶部设计
    刷题 | 迷宫求解
    图片接口文档怎么写
    26.Xaml Menu控件----＞菜单控件 （手动创建菜单，命令，Icon，快捷键）
    如何将超大文件传输给别人，超大文件如何传输
  • 原文地址：https://blog.csdn.net/imwucx/article/details/133953728