MySQL 正则表达式

一、MySQL 正则表达式 介绍

正则表达式是描述搜索模式的特殊字符串。它是一个强大的工具，可以提供一种简洁灵活的方式来识别基于模式的字符和单词等文本字符串。它使用自己的语法，可以由正则表达式处理器解释。正则表达式被广泛用于几乎所有平台，从编程语言到数据库，包括 MySQL。

正则表达式使用反斜杠作为转义字符，如果使用了双反斜杠，则应在模式匹配中考虑该转义字符。正则表达式不区分大小写。在 MySQL 中缩写为 REGEX 或 REGEXP。

使用正则表达式的好处是，我们不限于搜索基于固定模式的字符串，在 LIKE 运算符中带有百分号 (%) 和下划线 (_)。正则表达式有更多的元字符，在执行模式匹配时允许更多的灵活性和控制。

我们之前学习过通配符，它​​可以让我们得到与正则表达式类似的结果。所以我们可能会问，如果我们能得到与通配符相同的结果，为什么还要学习正则表达式。这是因为与通配符相比，正则表达式允许我们以更复杂的方式搜索匹配的数据。

二、MySQL 正则表达式 语法

MySQL 采用了Henry Spencer实现的正则表达式。MySQL允许我们使用 REGEXP 运算符在 SQL 语句中匹配模式。以下是说明在 MySQL 中使用正则表达式的基本语法：

SELECT column_lists FROM table_name WHERE field_name REGEXP 'pattern';  

在此语法中，column_list指示在结果集中返回的列名。table_name是使用模式检索数据的表的名称。WHERE field_name表示对其执行正则表达式的列名。REGEXP是正则表达式操作符，pattern是REGEXP要匹配的搜索条件。我们还可以使用RLIKE运算符，它是 REGEXP 的同义词，它给出与 REGEXP 相同的结果。我们可以通过使用 REGEXP 而不是 LIKE 来避免将此语句与 LIKE 运算符一起使用的混淆。

如果 WHERE field_name 中的值与模式匹配，则此语句返回true 。否则，它返回false。如果 field_name 或 pattern 为NULL，则结果始终为 NULL。REGEXP 运算符的否定形式不是 REGEXP。

三、正则表达式元字符

下表显示了正则表达式中最常用的元字符和结构：

元字符	说明
^	插入符号 (^) 字符用于在搜索字符串的开头开始匹配。
$	美元 ($) 字符用于在搜索字符串的末尾开始匹配。
.	点 (.) 字符匹配除新行之外的任何单个字符。
[ABC]	它用于匹配方括号中的任何字符。
[^abc]	它用于匹配方括号中未指定的任何字符。
*	星号 (*) 字符匹配前面字符串的零 (0) 个或多个实例。
+	加号 (+) 字符匹配前面字符串的一个或多个实例。
{n}	它用于匹配前面元素的 n 个实例。
{m, n}	它用于将 m 匹配到前一个元素的 n 个实例。
p1|p2	它用于隔离匹配任何模式 p1 或 p2 的替代方案。
?	问号 (?) 字符匹配零 (0) 或前面字符串的一个实例。
[AZ]	它用于匹配任何大写字符。
[a-z]	它用于匹配任何小写字符。
[0-9]	它用于匹配从 0 到 9 的数字。
[[:<:]]	它匹配单词的开头。
[[:>:]]	它匹配单词的结尾。
[:class:]	它用于匹配一个字符类，即[:alpha:]匹配字母，[:space:]匹配空格，[:punct:]匹配标点符号，[:upper:]匹配上级字母。

三、MySQL 正则表达式 示例

让我们使用下面给出的实际示例来理解正则表达式：

假设我们有一个名为student_info的表，其中包含以下数据。我们将根据此表数据演示各种示例。

如果我们想搜索名字以“A or B”开头的学生，我们可以使用正则表达式和元字符，如下所示：

mysql> SELECT * FROM student_info WHERE stud_name REGEXP '^[ab]';  

 执行语句，我们会得到想要的结果。请参阅以下输出：

如果我们想获取名字以 k 结尾的学生信息，我们可以使用 'k$' 元字符来匹配字符串的结尾，如下所示：

mysql> SELECT * FROM student_info WHERE stud_name REGEXP 'k$';  

执行语句，我们会得到想要的结果。请参阅以下输出：

如果我们想获取科目包含“i”字符的学生信息，我们可以使用以下查询来做到这一点：

mysql> SELECT * FROM student_info WHERE subject REGEXP 'i';  

执行语句，我们会得到想要的结果。请参阅以下输出：

四、正则表达式函数和运算符

以下是 MySQL 中常规函数和运算符的列表：

名称	说明
NOT_REGEXP	它是 REGEXP 运算符的否定。
REGEXP	该运算符表示字符串是否匹配正则表达式。
RLIKE	该运算符表示字符串是否匹配正则表达式。
REGEXP_INSTR()	它是一个在子字符串的起始索引与正则表达式匹配时给出结果的函数。
REGEXP_LIKE()	该函数表示字符串是否匹配正则表达式。
REGEXP_REPLACE()	它通过替换与正则表达式匹配的子字符串来给出结果。
REGEXP_SUBSTRING()	此函数返回与正则表达式匹配的子字符串。

五、REGEXP、RLIKE 和 REGEXP_LIKE()

尽管这些函数和运算符返回相同的结果，但 REGEXP_LIKE () 通过可选参数为我们提供了更多功能。我们可以按如下方式使用它们：

expression REGEXP pattern  
expression RLIKE pattern  
REGEXP(expression, pattern[, match_type])  

这些语句给出字符串表达式是否匹配正则表达式模式的输出。如果表达式与模式匹配，我们将得到 1。否则，它们返回 0。下面的例子解释得更清楚。

在下图中，第一个语句返回“1”，因为“B”在 AZ 范围内。第二个语句将模式的范围限制为 BZ。所以'A'不会匹配范围内的任何字符，MySQL 返回 0。这里我们使用了别名match_ 和 not_match_以便返回的列更容易理解。

六、REGEXP_LIKE() 参数

以下是修改函数输出的五个可能参数：

• c：表示区分大小写的匹配。
• i：表示不区分大小写的匹配。
• m：它代表一种多行模式，允许在字符串中使用行终止符。默认情况下，此函数匹配字符串开头和结尾的行终止符。
• n：用于修改 . （点）字符以匹配行终止符。
• u：它表示仅 Unix 的行尾。

示例

在此示例中，我们添加了“c”和“i”作为可选参数，它调用区分大小写和不区分大小写的匹配。第一个查询给出输出 0，因为“a”在“az”范围内，但不在大写字母 AZ 的范围内。由于不区分大小写的特性，第二个查询给出了输出 1。

七、NOT REGEXP & NOT RLIKE

NOT REGEXP & NOT RLIKE是正则表达式运算符，用于比较指定的模式并返回与模式不匹配的结果。如果未找到匹配项，则这些运算符返回 1。否则，它们返回 0。我们可以按如下方式使用这些函数：

SELECT (expr NOT REGEXP pat);  
OR  
SELECT (expr NOT RLIKE pat);  

示例

以下语句返回 0，因为在给定范围内找到了'a' 。

mysql> SELECT ('a' NOT REGEXP '[a-z]') AS not_match;  

输出结果为：

八、REGEXP_INSTR()

REGEXP_INSTR()是一个在子字符串表达式的起始索引与模式匹配时给出结果的函数。如果未找到匹配项，则返回 0。如果表达式或模式为NULL，则返回 NULL。这里索引从 1 开始。

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])  

此函数使用各种可选参数，即 pos、occurrence、return_option、match_type 等。

示例

假设我们要获取 expr (abcdefa) 中子串 'a' 的索引位置。第一个查询返回 1，因为我们没有设置任何可选参数，这是字符串的第一个索引。第二个查询返回 13，因为我们已经修改了带有可选参数出现的查询。

SELECT REGEXP_INSTR('a b c d e f a', 'a', 1, 1, 0) AS start_index_;  
SELECT REGEXP_INSTR('a b c d e f a', 'a', 1, 2, 0) AS a_index_2;  

九、REGEXP_REPLACE()

REGEXP_REPLACE()函数通过匹配字符替换指定的字符串字符，然后返回结果字符串。如果没有找到任何表达式、模式或可替换字符串，它将返回 NULL。该功能可以按如下方式使用：

SELECT REGEXP_REPLACE ('expression', 'character', 'replace_character');  

替换字符使用可选参数，例如 pos、occurrence 和 match_type。

示例

此语句将 'tutorials' 模式替换为 'javat' 模式。

mysql> SELECT REGEXP_REPLACE ('tutorialspoint', 'tutorials', 'javat') AS replaced; 

输出结果为：

十、REGEXP_SUBSTRING()

REGEXP_SUBSTRING()函数返回与指定模式匹配的表达式的子字符串。如果找到表达式或指定的模式，甚至没有找到匹配项，则返回 NULL。该功能可以按如下方式使用：

SELECT REGEXP_SUBSTR ('expr', 'pattern');  

该模式使用可选参数，例如 pos、occurrence 和 match_type。

示例

此语句返回“point”模式，这是给定 range 的第三次出现。

mysql> SELECT REGEXP_SUBSTR ('java t point', '[a-z]+', 2, 3) AS substring;  

输出结果为：