MySQL 正则表达式
我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
正则表达式是为复杂搜索指定模式的强大方式。
正则表达式描述了一组字符串。最简单的正则表达式是不含任何特殊字符的正则表达式。例如，正则表达式hello匹配hello。
非平凡的正则表达式采用了特殊的特定结构，从而使得它们能够与1个以上的字符串匹配。例如，正则表达式hello|word匹配字符串hello或字符串word。
作为一个更为复杂的示例，正则表达式B[an]*s匹配下述字符串中的任何一个：Bananas，Baaaaas，Bs，以及以B开始、以s结束、并在其中包含任意数目a或n字符的任何其他字符串。
sql语法
select * from table where column regexp '正则表达式'
特殊字符
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
示例
- ^
| 匹配字符串的开始部分。 |
|---|
| mysql> SELECT 'fo\nfo' REGEXP '^fo$'; -> 0 |
| mysql> SELECT 'fofo' REGEXP '^fo'; -> 1 |
- $
| 匹配字符串的结束部分。 |
|---|
| mysql> SELECT 'fo\no' REGEXP '^fo\no$'; -> 1 |
| mysql> SELECT 'fo\no' REGEXP '^fo$'; -> 0 |
- .
| 匹配任何字符（包括回车和新行）。 |
|---|
| mysql> SELECT 'fofo' REGEXP '^f.*$'; -> 1 |
| mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$'; -> 1 |
- a*
| 匹配0或多个a字符的任何序列。 |
|---|
| mysql> SELECT 'Ban' REGEXP '^Ba*n'; -> 1 |
| mysql> SELECT 'Baaan' REGEXP '^Ba*n'; -> 1 |
| mysql> SELECT 'Bn' REGEXP '^Ba*n'; -> 1 |
- a+ |
| 匹配1个或多个a字符的任何序列。 |
|---|
| mysql> SELECT 'Ban' REGEXP '^Ba+n'; -> 1 |
| mysql> SELECT 'Bn' REGEXP '^Ba+n'; -> 0 |
- a? |
| 匹配0个或1个a字符。 |
|---|
| mysql> SELECT 'Bn' REGEXP '^Ba?n'; -> 1 |
| mysql> SELECT 'Ban' REGEXP '^Ba?n'; -> 1 |
| mysql> SELECT 'Baan' REGEXP '^Ba?n'; -> 0 |
- de|abc |
| 匹配序列de或abc。 |
|---|
| mysql> SELECT 'pi' REGEXP 'pi |
| mysql> SELECT 'axe' REGEXP 'pi |
| mysql> SELECT 'apa' REGEXP 'pi |
| mysql> SELECT 'apa' REGEXP '^(pi |
| mysql> SELECT 'pi' REGEXP '^(pi |
| mysql> SELECT 'pix' REGEXP '^(pi |
- (abc)* |
| 匹配序列abc的0个或多个实例。 |
|---|
| mysql> SELECT 'pi' REGEXP '^(pi)*$'; -> 1 |
| mysql> SELECT 'pip' REGEXP '^(pi)*$'; -> 0 |
| mysql> SELECT 'pipi' REGEXP '^(pi)*$'; -> 1 |
- {1},
{n}或{m,n}符号提供了编写正则表达式的更通用方式，能够匹配模式的很多前述原子（或“部分”）。m和n均为整数。
o a*
可被写入为a{0,}。
o a+
可被写入为a{1,}。
o a?
可被写入为a{0,1}。
更准确地讲，a{n}与a的n个实例准确匹配。a{n,}匹配a的n个或更多实例。a{m,n}匹配a的m～n个实例，包含m和n。
| m和n必须位于0～RE_DUP_MAX（默认为255）的范围内，包含0和RE_DUP_MAX。如果同时给定了m和n，m必须小于或等于n。 |
|---|
| mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e'; -> 0 |
| mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e'; -> 1 |
| mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e'; -> 1 |
- [a-dX], [^a-dX] |
| 匹配任何是（或不是，如果使用^的话）a、b、c、d或X的字符。两个其他字符之间的“-”字符构成一个范围，与从第1个字符开始到第2个字符之间的所有字符匹配。例如，[0-9]匹配任何十进制数字 。要想包含文字字符“]”，它必须紧跟在开括号“[”之后。要想包含文字字符“-”，它必须首先或最后写入。对于[]对内未定义任何特殊含义的任何字符，仅与其本身匹配。 |
|---|
mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]'; -> 1 |
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$'; -> 0 |
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$'; -> 1 |
mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$'; -> 0 |
mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$'; -> 1 |
mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$'; -> 0 |
- [.characters.]
| 在括号表达式中（使用[和]），匹配用于校对元素的字符序列。字符为单个字符或诸如新行等字符名。在文件regexp/cname.h中，可找到字符名称的完整列表。 |
|---|
| mysql> SELECT '~' REGEXP '[[.~.]]'; -> 1 |
| mysql> SELECT '~' REGEXP '[[.tilde.]]'; -> 1 |
- [=character_class=] |
| 在括号表达式中（使用[和]），[=character_class=]表示等同类。它与具有相同校对值的所有字符匹配，包括它本身，例如，如果o和(+)均是等同类的成员，那么[[=o=]]、[[=(+)=]]和[o(+)]是同义词。等同类不得用作范围的端点。 |
|---|
- [:character_class:]
在括号表达式中（使用[和]），[:character_class:]表示与术语类的所有字符匹配的字符类。标准的类名称是：
|
alnum |
文字数字字符 |
|
alpha |
文字字符 |
|
blank |
空白字符 |
|
cntrl |
控制字符 |
|
digit |
数字字符 |
|
graph |
图形字符 |
|
lower |
小写文字字符 |
|
|
图形或空格字符 |
|
punct |
标点字符 |
|
space |
空格、制表符、新行、和回车 |
|
upper |
大写文字字符 |
|
xdigit |
十六进制数字字符 |
| 它们代表在ctype(3)手册页面中定义的字符类。特定地区可能会提供其他类名。字符类不得用作范围的端点。 |
|---|
| mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+'; -> 1 |
| mysql> SELECT '!!' REGEXP '[[:alnum:]]+'; -> 0 |
- [[:<:]], [[:>:]]
| 这些标记表示word边界。它们分别与word的开始和结束匹配。word是一系列字字符，其前面和后面均没有字字符。字字符是alnum类中的字母数字字符或下划线(_)。 |
|---|
| mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1 |
| mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -> 0 |
| 要想在正则表达式中使用特殊字符的文字实例，应在其前面加上2个反斜杠“\”字符。MySQL解析程序负责解释其中一个，正则表达式库负责解释另一个。例如，要想与包含特殊字符“+”的字符串“1+2”匹配，在下面的正则表达式中，只有最后一个是正确的： |
| mysql> SELECT '1+2' REGEXP '1+2'; -> 0 |
| mysql> SELECT '1+2' REGEXP '1+2'; -> 0 |
| mysql> SELECT '1+2' REGEXP '1\+2'; -> 1 |
常用正则
校验数字的表达式
数字：
^[0-9]*$
n位的数字：
^\d{n}$
正数、负数、和小数：
^(\-|\+)?\d+(\.\d+)?$
正浮点数：
^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$ 或 ^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$
负浮点数：
^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$ 或 ^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$
浮点数：
^(-?\d+)(\.\d+)?$ 或 ^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$
特殊需求表达式
残疾人证号：
[\d]{17}[\dX][1-7][1-4]/ 或 /^\\d{22}$)|(^\\d{20}$
身份证号：
^\d{15}$)|(^\d{18}$)|(^\d{17}(\d|X|x)$
手机号码：
/^1(3\d|4[5-9]|5[0-35-9]|6[567]|7[0-8]|8\d|9[0-35-9])\d{8}$/
或
^(13[0-9]|14[5|7]|15[0-9]|16[5|6|7]|17[0-9]|18[0-9])[0-9]{8}$
电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：
^(\(\d{3,4}-)|\d{3.4}-)?\d{7,8}$
国内电话号码(0511-4405222、021-87888822)：
\d{3}-\d{8}|\d{4}-\d{7}
电话号码正则表达式（支持手机号码，3-4位区号，7-8位直播号码，1－4位分机号）【可能不是最新】:
((\d{11})|^((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))$)
Email地址：
^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$
域名：
[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(\.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+\.?
帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：
^[a-zA-Z][a-zA-Z0-9_]{4,15}$
密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：
^[a-zA-Z]\w{5,17}$
强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在 8-10 之间)：
^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])[a-zA-Z0-9]{8,10}$
强密码(必须包含大小写字母和数字的组合，可以使用特殊字符，长度在8-10之间)：
^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$
日期格式：
^\d{4}-\d{1,2}-\d{1,2}
xml文件：
^([a-zA-Z]+-?)+[a-zA-Z0-9]+\\.[x|X][m|M][l|L]$
中文字符的正则表达式：
[\u4e00-\u9fa5]
双字节字符：
[^\x00-\xff]
(包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))
空白行的正则表达式：
\n\s*\r
(可以用来删除空白行)
HTML标记的正则表达式：
<(\S*?)[^>]*>.*?|<.*? />
( 首尾空白字符的正则表达式：^\s*|\s*$或(^\s*)|(\s*$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)
腾讯QQ号：
[1-9][0-9]{4,}
(腾讯QQ号从10000开始)
中国邮政编码：
[1-9]\d{5}(?!\d)
(中国邮政编码为6位数字)
IPv4地址：
((2(5[0-5]|[0-4]\d))|[0-1]?\d{1,2})(\.((2(5[0-5]|[0-4]\d))|[0-1]?\d{1,2})){3}
注意：
mysql 不支持\d、\w等，需要转换\d为[0-9]
浙公网安备 33010602011771号