MySQL中的模式匹配
2013-04-04 08:15:35 
阿炯
MySQL中主要有两种模式匹配可供选择，一种标准的SQL模式匹配，另一种基于象Unix实用程序如vim、grep的扩展正则表达式模式匹配的格式。MySQL采用Henry Spencer的正则表达式实现，其目标是符合POSIX 1003.2。MySQL采用了扩展的版本，以支持在SQL语句中与REGEXP操作符一起使用的模式匹配操作。标准的SQL模式匹配
SQL的模式匹配允许你使用"_"匹配任何单个字符，而"%"匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子，注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。总结为以下三点：
1.使用LIKE和NOT LIKE比较操作符(注意不能使用=或!=);
2.模式默认是忽略大小写的；
3.允许使用"_"匹配任何单个字符，"%"匹配任意数目字符(包括零字符)；
找出以"b"开头的名字：
mysql> SELECT * FROM freeoa_tab WHERE name LIKE "b%";
找出以"fy"结尾的名字：
mysql> SELECT * FROM freeoa_tab WHERE name LIKE "%fy";
找出包含一个"w"的名字：
mysql> SELECT * FROM freeoa_tab WHERE name LIKE "%w%";
找出包含正好5个字符的名字，使用"_"模式字符：
mysql> SELECT * FROM freeoa_tab WHERE name LIKE "_____";
扩展正则表达式模式匹配
由MySQL提供的模式匹配的其他类型是使用扩展正则表达式，当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。
扩展正则表达式的一些字符是：
"."匹配任何单个的字符。
一个字符类"[...]"匹配在方括号内的任何字符。例如，"[abc]"匹配"a"、"b"或"c"。为了命名字符的一个范围，使用一个"-"。
"[a-z]"匹配任何小写字母，而"[0-9]"匹配任何数字。
" * "匹配零个或多个在它前面的东西。例如，"x*"匹配任何数量的"x"字符，"[0-9]*"匹配的任何数量的数字，而".*"匹配任何数量的任何东西。
正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，"[aA]"匹配小写或大写的"a"而"[a-zA-Z]"匹配两种写法的任何字母。
如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。
为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用"^"或在模式的结尾用"$"。
总结为以下四点：
1.使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，他们是同义词)；
2.REGEXP模式匹配与被匹配字符的任何地方匹配，则匹配成功(即只要被匹配字符包含或者可以等于所定义的模式，就匹配成功)；
不同于LIKE模式匹配，只有和整个值匹配，才匹配成功(即只有被匹配字符完全和所定义的模式匹配，才匹配成功)
3.REGEXP默认也是不区分大小写，可以使用BINARY关键词强制区分大小写；
如：SELECT * FROM freeoa_tab WHERE name REGEXP BINARY '^B';
4.正则表达式为一个表达式，它能够描述一组字符串，REGEXP操作符完成MySQL的扩展正则表达式匹配。REGEXP实现的功能是如果被匹配字符中部分或完全符合所定义的表达式描述的字符，则表示匹配成功。
最简单的正则表达式是不含任何特殊字符的正则表达式，如freeoa。
SELECT * FROM freeoa_tab WHERE name REGEXP 'freeoa';
表示的意思是如果name这列的某一行包含'freeoa'这个单词，则匹配就成功了。(与LIKE不同，LIKE要求name这列的某一行必须完全等于freeoa，才匹配成功)。
下面就介绍一些关于它的高级应用：扩展正则表达式的特殊字符。
1) '.'匹配任何单个字符；
2) […]匹配在方括号内的任何字符，可以使用'-'表示范围，如[a-z],[0-9]，而且可以混合[a-dXYZ]表示匹配a,b,c,d,X,Y,Z中的任何一个；(注意使用括号以及'|'的方法也可以达到相同的效果，如(a|b|c)匹配a,b,c中的任何一个)；此外可以使用'^'表示否定，如[^a-z]表示不含有a-z中间的任何一个字符；
3) '*'表示匹配0个或多个在它前面的字符。如x*表示0个或多个x字符，.*表示匹配任何数量的任何字符；
4) 可以将模式定位必须匹配被匹配字符的开始或结尾，在匹配模式前加"^"：表示匹配从被匹配字符的最开头开始，在匹配模式后加"$"：表示匹配要进行到被匹配字符的最末尾。
5) '+'表示匹配1个或多个在它前面的字符。如a+表示1个或多个a字符。
6) '?'表示匹配0个或1个在它前面的字符。如a?表示0个或1个a字符。
7) '|'如de|abc表示匹配序列de或者abc。注意虽然[…]也可以表示匹配中的某一个，但是每次仅仅能表示单个字符及[a-bXYZ]实际每一次只代表了一个字符。
8) ()括号可以应用在表达式中，使得更容易理解。
9) a{5}表示匹配共5个a，a{2,8}表示匹配2～8个a。
a*可以写成a{0, } 第二个参数省略表示没有上界；a+可以写成a{1,}；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。
10) 标准类别[:character_class:]：
常用的一些标准类别，一般在[]中使用，由于用在[]中故和[a-z]类似，每一次只能顶替一个字符。(这个有点类似perl里面定义的常用的一些标准类别：\w表示一个单词字符即[a-zA-Z0-9];\W一个非单词字符与\w相反; \d一个数字即[0-9];\D一个非数字;\s一个白空间字符即[\t\f\r\n];\f为换页符;\S一个非白空间字符)
标准的类别名称：
alnum 文字数字字符
alpha 文字字符
blank 空白字符
cntrl 控制字符
digit 数字字符
graph 图形字符
lower 小写文字字符
print 图形或空格字符
punct 标点字符
space 空格、制表符、新行、和回车
upper 大写文字字符
xdigit 十六进制数字字符
举列如下：
SELECT 'justalnums' REGEXP '[[:alnum:]]+';
解释其中[[:alnum:]]由于[:alnum:]表示文字数字字符，它又用在[]中，故[[:alnum:]]代表一个字符它为一个文字或者数字。后面的+号表示1个或多个这样的文字或数字。
上述语句返回1.那是因为justalnums中是由字母组成的。
11) 字边界：[[:<:]]表示开始，[[:>:]]表示结束：
其定义了一个单词的开始和结束边界，这个单词为字字符，这样[[:<:]]代表这个字字符前面的部分，[[:>:]]代表这个字字符后面的部分。字字符为alnum类的字母数字字符或下划线(_)；因此[[:<:]], [[:>:]]均代表不是字字符的字符，即只要不是字母数字字符以及下划线(_)即可。因此其可以为什么都不是。因此[[:<:]]word[[:>:]]能够匹配如下的所有情况：
即word单词本身，word*** 解释***代表不是字母数字以及_的任何字符(如,word-net)；***word(如,micorsoft word)；***word***(如，this is a word program.)
举例：[[:<:]]word[[:>:]]:
SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; 结果为真
最后注意的注意：
要在正则表达式中使用特殊字符，需要在这些字符前面添加2个反斜杠'/'，
举例：
SELECT '1+2' REGEXP '1+2'; 结果为0
SELECT '1+2' REGEXP '1\+2'; 结果为0
SELECT '1+2' REGEXP '1\\+2'; 结果为1
解释：
这是因为MySQL解析程序解析该SQL语句时：
首先将字符串'1\\+2'最终解析为1\+2；
然后把1\+2当作正则表达式，由正则表达式库来解析，它代表1+2，因此需要加上2个反斜杠。要想在正则表达式中使用特殊字符的文字实例，应在其前面加上2个反斜杠"\"字符。MySQL解析程序负责解释其中一个，正则表达式库负责解释另一个。
不要经常犯加一个反斜杠的错误，加一个反斜杠会莫名其妙：
如SELECT '1t2' REGEXP '1\t2';
结果会返回1
本来的意思是匹配1制表符\t以及2，但是由于只添加了一个/所以，解析以后编程了1t2，所以匹配成功。
12) [.characters.]和[=character_class=]
在括号表达式中(使用[和])，匹配用于校对元素的字符序列。字符为单个字符或诸如新行等字符名。在文件regexp/cname.h中，可找到字符名称的完整列表。
在括号表达式中(使用[和])，[=character_class=]表示等同类。它与具有相同校对值的所有字符匹配，包括它本身，例如，如果o和(+)均是等同类的成员，那么[[=o=]]、[[=(+)=]]和[o(+)]是同义词。等同类不得用作范围的端点。
^
匹配字符串的开始部分。
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|apa'; -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa'; -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa'; -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$'; -> 0
(abc)*
匹配序列abc的0个或多个实例。
mysql> SELECT 'pi' REGEXP '^(pi)*$'; -> 1
mysql> SELECT 'pip' REGEXP '^(pi)*$'; -> 0
mysql> SELECT 'pipi' REGEXP '^(pi)*$'; -> 1
{1}, {2,3}
{n}或{m,n}符号提供了编写正则表达式的更通用方式，能够匹配模式的很多前述原子(或"部分")。m和n均为整数。
a*
可被写入为a{0,}。
a+
可被写入为a{1,}。
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:]表示与术语类的所有字符匹配的字符类。
它们代表在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
为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写。
找出以"b"开头的名字，使用"^"匹配名字的开始并且"[bB]"匹配小写或大写的"b"：
mysql> SELECT * FROM freeoa_tab WHERE name REGEXP "^[bB]";
找出以"fy"结尾的名字，使用"$"匹配名字的结尾：
mysql> SELECT * FROM freeoa_tab WHERE name REGEXP "fy$";
找出包含一个"w"的名字，使用"[wW]"匹配小写或大写的"w"：
mysql> SELECT * FROM freeoa_tab WHERE name REGEXP "[wW]";
为了找出包含正好5个字符的名字，使用"^"和"$"匹配名字的开始和结尾，和5个"."实例在两者之间：
mysql> SELECT * FROM freeoa_tab WHERE name REGEXP "^.....$";
你也可以使用"{n}""重复n次"操作符重写先前的查询：
mysql> SELECT * FROM freeoa_tab WHERE name REGEXP "^{5}$";
现在我要找出只有一个'+'的值:
select id,name,createtime from roleInfo where name regexp '^[^+]\\+[^+]$';
select id,name,createtime from roleInfo where name like '%+' and name not like '%++';
找出至少有一个'+'的值:
select id,name,createtime from roleInfo where name regexp '^[^+]+[+]?$';
参考资料：
MySQL的模式匹配
MySQL的正则表达式匹配