MySQL中的正则表达式

正则表达式（Regular Expression）是一种强大的文本处理工具，在很多开发和应用环境都提供了支持，本文将介绍正则表达式在MySQL数据库的具体应用。

正则表达式的应用，关键在于模式（Pattern）的定义。模式中可以定义匹配的内容、匹配的数量、匹配的位置等要素。在MySQL数据库中，需要通过正则表达式模式作为文本查询条件时，可以使用regexp或rlike运算符，其基本应用格式如下：

MySQL

<字段> regexp '<模式>'

代码中需要判断文本内容是否符合指定的模式时可以使用regexp_like(s,p)函数，其中，s为文本内容，p为指定的模式。当s符合p模式函数返回1，否则返回0；如果s或p为null则函数返回null。下面介绍模式的定义，并主要使用regexp_like()函数进行测试。

匹配内容

首先，定义模式时会使用一些特殊字符，如圆点（.）、星号（*）、问号（?）、加号（+）、中括号（[和]）、花括号（{和}）等，这些符号在模式定义有特殊的含义，称为正则表达式的元字符。在模式中如果需要匹配这些特殊字符，需要进行转义。比如，匹配加号时需要使用'\\+'定义；请注意，这里有两个外斜线（\\），实际进行了两次转义，第一个外斜线是MySQL文本转义符，用于对第二个外斜线进行转义，即'\\'在MySQL文本中表示一个外斜线；第一次转义后会得到'\+'，此时才是模式的转义字符，最终转义为一个加号。简单点儿说，在MySQL中定义正则表达式模式时，转义字符需要使用两个外斜线。

在模式中，圆点（.）匹配任何一个字符，如下面的代码会返回1。

MySQL

select regexp_like('abc', '.');

如果需要判断文本内容中是否包含圆点，则需要使用转义，如下面的代码会返回0。

MySQL

select regexp_like('abc', '\\.');

这里可以修改regexp_like()函数第一个参数内容来观察执行结果。

使用中括号[和]可以定义字符集，用于匹配字符集中的任意一个字符，如下面的代码。

MySQL

select regexp_like('Yes','[yn]');

代码会返回1，并且，只要文本中包含y、n、Y、N中的任何一个都会返回1。请注意，默认情况下模式中不区分字母大小写。本例，如果将'Yes'修改为不包含y、n、Y、N的内容则会返回0。

如果需要匹配不包含字符集内容的字符，可以在字符集中使用^开始，如下面的代码。

MySQL

select regexp_like('Y','[^yn]');

本例，文本中只包含一个字母'Y'，而模式定义为不包含y或n，执行代码会返回0。请注意，这里参数1的文本内容只要包含任何一个不是y或n的字符，执行代码都是返回1。

在集合中，还可以通过连接号（-）指定范围，如[0-9]表示数字、[a-z]表示英文小写字母、[A-Z]表示英文大写字母。如下面的代码。

MySQL

select regexp_like('abc', '[0-9]');

执行代码会返回0，因为参数1的文本中没有包含数字0到9的任何一个字符。如果将模式修改为[^0-9]则表示任何一个非数字字符，执行结果会返回1。

需要匹配某类字符时，可以使用以下一些转义字符：

\\d，数字（0到9）。\\D，非数字，与\\d相反。
\\w，数字、大小写字母和下画线。\\W，与\\w相反。
\\s，空白字符，包括空格、换页符（\\f）、换行符（\\n）、水平制表符（\\t）、垂直制表符（\\v）、回车符（\\r）。\\S，非空白字符，与\\s相反。

使用|定义多个规则，类似或（or）关系，如下面的代码。

MySQL

select regexp_like('abc', '\d|[a-fA-F]');

本例模式用于匹配数字和字母a到f的大小写形式，即十六进制的字符，执行查询会显示1。这里，如果将模式中的'|'符号删除，则模式的含义就变为以1个数字开始，a到f字母在后的内容，文本'abc'中没有数字开始，则执行结果会返回0。

如果模式定义比较复杂，还可以使用圆括号定义为组，如下面的代码。

MySQL

select regexp_like('abc', '(\d)|([a-fA-F])');

匹配数量

除了指定匹配的内容，还可以在匹配内容的后面定义匹配的次数，设置方法如下：

星号（*），0个或多个。
加号（+），1个或多个。
问号（?），0个或1个。
{n}，n个。
{n,}，最少n个。
{m,n}，m个到n个，最多255个。

如下面的代码可以匹配11位数字。

MySQL

select regexp_like('12345678910', '\\d{11}');

执行代码会返回1。可以修改参数1的文本内容，并观察执行结果。

匹配位置

模式中定义匹配的位置可以使用如下字符：

\\b，匹配边界，如单词的开始和结束位置。\\B非边界，与\\b相反。
^，使用在模式的开始，表示下一规则应是文本的开始部分。
$，使用在模式的结尾，表示上一规则应是文本的结束部分。

下面的代码通过\\b转义字符匹配完整的Jim。

MySQL

select regexp_like('His Name is Jimmy.', '\\bJim\\b');

执行代码会显示0。模式'\\bJim\\b'会匹配完整的Jim，将模式修改为'\\bJimmy\\b'后会匹配参数1中的Jimmy，执行代码会返回1。

下面的代码演示^和$的应用。

MySQL

select regexp_like('12345678910', '^1\\d{10}$');

模式中包含了2个部分，其中'^1'表示以数字1开始，'\\d{10}$'表示以10个数字结束。本例可修改参数1的文本内容并观察运行结果。

正则表达式处理函数

MySQL内置的正则表达式处理函数包括：

regexp_like(expr, pat[, match_type])
regexp_instr(expr, pat[, pos[, occurrence[, return_option[,match_type]]]])
regexp_replace(expr, pat, repl[, pos[, occurrence[, match_type]]])
regexp_substr(expr, pat[, pos[, occurrence[, match_type]]])

先来看各个参数的功能，如下表所示。

参数	说明
expr	指定需要查询的原始文本。
pat	指定模式字符串。
match_type	指定匹配选项，使用字符串定义，选项包括： c，区分字母大小字。 i，不区分字母的大小写。 m，多行模式，文本中的每一行单独判断开始和结束位置，会对^和$的匹配产生影响。 n，圆点可匹配换行符（\\n）。 u，仅支持Unix系统中的换行符，圆点（.）、^和$可匹配。
pos	指定在expr中开始搜索的位置，默认为第1个字符。
occurrence	指定返回第几个匹配结果，默认为1。
return_option	默认为0，返回匹配内容第一个字符位置；设置为1时返回匹配内容第一个字符的后一个位置。
repl	在regexp_replace()函数中用于指定匹配内容替换成的新内容。

下面来看几个函数的应用实例。

regexp_like()函数已经使用多次，用于判断文本是否匹配指定的模式，下面来看match_type参数的应用。

MySQL

select regexp_like('ABCD', 'a', 'c');

代码中指定match_type参数为'c'，表示要区分字母大小写，此时文本中没有包含小写字母a，所以会返回0。删除'c'参数则不区分字母大小写，执行查询会返回1。

regexp_instr()函数可以判断匹配内容出现的位置，如下面的代码。

MySQL

select regexp_instr('aaaa', 'a');

执行查询会返回1，即字母a在第一个位置出现。

下面的代码，指定从第3个字符开始匹配字母a。

MySQL

select regexp_instr('aaaa', 'a',3);

执行查询会显示3。

下面的代码，指定从第一个位置开始匹配字母a，并返回第3次匹配结果的位置。

MySQL

select regexp_instr('aaaa', 'a',1,3);

执行查询同样返回3。

regexp_replace()函数用于将文本中匹配的结果替换为新的内容，如下面的代码。

MySQL

select regexp_replace('abcdefg', 'bcd', '***');

执行查询，会将参数1中的bcd替换为'***'，结果返回'a***efg'。

regexp_substr()函数可以返回匹配的内容，用于观察匹配内容非常有效。下面的代码会返回bcd和1个数字的匹配结果。

MySQL

select regexp_substr('abcd1abcd2abcd3', 'bcd\\d');

执行查询会返回bcd1。

下面的代码指定从第6个字符开始匹配，结果会显示bcd2。

MySQL

select regexp_substr('abcd1abcd2abcd3', 'bcd\\d', 6);

下面的代码，指定从第1个字符开始匹配，并返回第3个匹配结果。

MySQL

select regexp_substr('abcd1abcd2abcd3', 'bcd\\d', 1, 3);

执行查询会显示bcd3。

小结

本文介绍了在MySQL数据库中使用正则表达式的基础知识，包括模式的定义、regexp_like()、regexp_instr()、regexp_replace()和regexp_substr()函数的使用等。

模式中可以定义匹配的内容、数量和位置等要素。人们常说，正则表达式的模式最非常难以阅读和理解，在一定程度上是有道理的；一个复杂的模式往往比较难以解读，正是因为这样，才需要更加熟练掌握模式各个要素的定义方法，只有通过大量的练习和测试才能写出符合要求的模式。