MySQL查询正则表达式

1、_正则表达式（ regex) 是定义复杂查询的一个强有力的工具。这里是一个简单的资料，它忽略了一些详细的信息。正则表达式定义了一个字符串的规则。最简单的正则表达式不包含任何保留字。例如，正则表达式hello 只和字符串 “ hello ” 匹配。一般的正则表达式使用了某些特殊的结构，所以它能匹配更多的字符串。例如，正则表达式hello|word既能匹配字符串“ hello ” 也能匹配字符串“ word ” 。举一个更复杂一点的例子，正则表达式Ban*s 可以匹配字符串“ Bananas ” 、 “Baaaaas ” 、 “ Bs” 以及其他任何以B 开头以 s 结尾的字符串，中间可以包括任意

2、个a 和任意个n 的组合。一个正则表达式中的可以使用以下保留字所匹配的字符串以后面的字符串开头mysql> select "fonfo" REGEXP "fo$" -> 0（表示不匹配）mysql> select "fofo" REGEXP "fo" -> 1（表示匹配）$所匹配的字符串以前面的字符串结尾mysql> select "fono" REGEXP "fono$" -> 1（表示匹配）精品资料_mysql> select &

3、quot;fono" REGEXP "fo$" -> 0（表示不匹配）.匹配任何字符（包括新行）mysql> select "fofo" REGEXP "f.*" -> 1（表示匹配）mysql> select "fonfo" REGEXP "f.*" -> 1（表示匹配）a*匹配任意多个a （包括空串 )mysql> select "Ban" REGEXP "Ba*n" -> 1（表示匹配）mysql&

4、gt; select "Baaan" REGEXP "Ba*n" -> 1（表示匹配）mysql> select "Bn" REGEXP "Ba*n" -> 1（表示匹配）a+匹配任意多个a （不包括空串)mysql> select "Ban" REGEXP "Ba+n" -> 1（表示匹配）mysql> select "Bn" REGEXP "Ba+n" -> 0（表示不匹配）a?精品资料_匹

5、配一个或零个amysql> select "Bn" REGEXP "Ba?n" -> 1（表示匹配）mysql> select "Ban" REGEXP "Ba?n" -> 1（表示匹配）mysql> select "Baan" REGEXP "Ba?n" -> 0（表示不匹配）de|abc匹配 de 或 abcmysql> select "pi" REGEXP "pi|apa" -> 1

6、（表示匹配）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（表示匹配）

7、mysql> select "pix" REGEXP "(pi|apa)$" -> 0（表示不匹配）(abc)*匹配任意多个abc （包括空串 )mysql> select "pi" REGEXP "(pi)*$" -> 1（表示匹配）精品资料_mysql> select "pip" REGEXP "(pi)*$" -> 0（表示不匹配）mysql> select "pipi" REGEXP "(pi)*

8、$" -> 1（表示匹配）12,3这是一个更全面的方法，它可以实现前面好几种保留字的功能a*可以写成a0,a+可以写成a1,a?可以写成a0,1在 内只有一个整型参数i，表示字符只能出现i 次；在 内有一个整型参数i，后面跟一个“，” ，表示字符可以出现i 次或 i 次以上； 在 内只有一个整型参数i，后面跟一个 “，” ，再跟一个整型参数j,表示字符只能出现i 次以上， j 次以下（包括i 次和 j 次）。其中的整型参数必须大于等于0，小于等于 RE_DUP_MAX（默认是255 ）。如果有两个参数，第二个必须大于等于第一个a-dX精品资料_匹配 “ a”、 “ b” 、 “

9、 c” 、 “ d ” 或“ X ”a-dX匹配除 “ a” 、 “ b” 、 “ c” 、 “ d ”、 “ X” 以外的任何字符。“ ” 、 “ ” 必须成对使用mysql> select "aXbc" REGEXP "a-dXYZ" -> 1（表示匹配）mysql> select "aXbc" REGEXP "a-dXYZ$" -> 0（表示不匹配）mysql> select "aXbc" REGEXP "a-dXYZ+$" -> 1

10、（表示匹配）mysql> select "aXbc" REGEXP "a-dXYZ+$" -> 0（表示不匹配）mysql> select "gheis" REGEXP "a-dXYZ+$" -> 1（表示匹配）mysql> select "gheisa" REGEXP "a-dXYZ+$" -> 0（表示不匹配）-.characters.表示比较元素的顺序。在括号内的字符顺序是唯一的。但是括号中可以包含通配符, 所以他能匹配更多的字符。举

11、例来说：正则表达式.ch.*c匹配 chchcc 的前五个字符。=character_class=表示相等的类，可以代替类中其他相等的元素，包括它自己。例如，如果o 和 (+) 是一个相等的类的成员，那么=o= 、 =(+)= 和 o(+) 是完全等价的。精品资料_:character_class:在括号里面，在: 和 :中间是字符类的名字，可以代表属于这个类的所有字符。字符类的名字有: alnum 、digit 、punct 、alpha 、graph 、space 、blank 、lower 、upper 、cntrl 、print 和 xdigitmysql> select &qu

12、ot;justalnums" REGEXP ":alnum:+" -> 1（表示匹配）mysql> select "!" REGEXP ":alnum:+" -> 0（表示不匹配）:分别匹配一个单词开头和结尾的空的字符串，这个单词开头和结尾都不是包含在alnum 中的字符也不能是下划线。mysql> select "a word a" REGEXP ":" -> 1（表示匹配）mysql> select "a xword a" R

13、EGEXP ":" -> 0（表示不匹配）mysql> select "weeknights" REGEXP "(wee|week)(knights|nights)$" -> 1（表示匹配）精品资料_Description of MySQL regular expression syntaxA regular expression (regex) is a powerful way of specifying a complex search.MySQL uses regular Henry Spencer'

14、s inplementation of regular expressions.And that is aimed to conform to POSIX 1003.2.MySQL uses the extendedversion.This is a simplistic reference that skips the details. To get more exactinformation, see Henry Spencer's regex(7) manual page that is included in thesource distribution. See sectio

15、n C Contributors to MySQL.精品资料_A regular expression describes a set of strings. The simplest regexp is one thathas no special characters in it. For example, the regexp hello matches helloand nothing else.Nontrivial regular expressions use certain special constructs so that they canmatch more than on

16、e string. For example, the regexp hello|word matcheseither the string hello or the string word.As a more complex example, the regexp Ban*s matches any of the stringsBananas, Baaaaas, Bs and any other string starting with a B, ending with an s,and containing any number of a or n characters in between

17、.A regular expression may use any of the following specialcharacters/constructs:Match the beginning of a string.mysql> select "fonfo" REGEXP "fo$"-> 0mysql> select "fofo" REGEXP "fo"-> 1$Match the end of a string.mysql> select "fono" R

18、EGEXP "fono$"-> 1mysql> select "fono" REGEXP "fo$"-> 0.精品资料_Match any character (including newline).mysql> select "fofo" REGEXP "f.*"-> 1mysql> select "fonfo" REGEXP "f.*"-> 1a*Match any sequence of zero or mo

19、re a characters.mysql> select "Ban" REGEXP "Ba*n"-> 1mysql> select "Baaan" REGEXP "Ba*n"-> 1mysql> select "Bn" REGEXP "Ba*n"-> 1a+Match any sequence of one or more a characters.mysql> select "Ban" REGEXP "

20、;Ba+n"-> 1mysql> select "Bn" REGEXP "Ba+n"-> 0a?Match either zero or one a character.mysql> select "Bn" REGEXP "Ba?n"-> 1mysql> select "Ban" REGEXP "Ba?n"-> 1mysql> select "Baan" REGEXP "Ba?n"

21、;-> 0de|abcMatch either of the sequences de or abc.mysql> select "pi" REGEXP "pi|apa"-> 1mysql> select "axe" REGEXP "pi|apa"-> 0mysql> select "apa" REGEXP "pi|apa"-> 1精品资料_mysql> select "apa" REGEXP "(

22、pi|apa)$"-> 1mysql> select "pi" REGEXP "(pi|apa)$"-> 1mysql> select "pix" REGEXP "(pi|apa)$"-> 0(abc)*Match zero or more instances of the sequence abc.mysql> select "pi" REGEXP "(pi)*$"-> 1mysql> select "pi

23、p" REGEXP "(pi)*$"-> 0mysql> select "pipi" REGEXP "(pi)*$"-> 112,3The is a more general way of writing regexps that match manyoccurrences of the previous atom.a*Can be written as a0,.a+Can be written as a1,.a?Can be written as a0,1.To be more precise, an a

24、tom followed by a bound containing oneinteger i and no comma matches a sequence of exactly i matches ofthe atom. An atom followed by a bound containing one integer i and a精品资料_comma matches a sequence of i or more matches of the atom. Anatom followed by a bound containing two integers i and j matche

25、s asequence of i through j (inclusive) matches of the atom. Botharguments must 0 >= value <= RE_DUP_MAX (default 255). If thereare two arguments, the second must be greater than or equal to thefirst.a-dXa-dXMatches any character which is (or is not, if is used) either a, b, c, dor X. To includ

26、e a literal character, it must immediately follow theopening bracket . To include a literal - character, it must be writtenfirst or last. So 0-9 matches any decimal digit. Any character thatdoes not have a defined meaning inside a pair has no specialmeaning and matches only itself.mysql> select &

27、quot;aXbc" REGEXP "a-dXYZ"-> 1mysql> select "aXbc" REGEXP "a-dXYZ$"-> 0mysql> select "aXbc" REGEXP "a-dXYZ+$"-> 1mysql> select "aXbc" REGEXP "a-dXYZ+$"-> 0mysql> select "gheis" REGEXP "a

28、-dXYZ+$"-> 1mysql> select "gheisa" REGEXP "a-dXYZ+$"-> 0.characters.精品资料_The sequence of characters of that collating element. The sequenceis a single element of the bracket expression's list. A bracketexpression containing a multi-character collating element ca

29、n thusmatch more than one character, e.g., if the collating sequenceincludes a ch collating element, then the regular expression .ch.*cmatches the first five characters of chchcc.=character_class=An equivalence class, standing for the sequences of characters of allcollating elements equivalent to th

30、at one, including itself. For example,if o and (+) are the members of an equivalence class, then =o=,=(+)=, and o(+) are all synonymous. An equivalence class maynot be an endpoint of a range.:character_class:Within a bracket expression, the name of a character class enclosedin : and : stands for the list of all characters