MySQL之正規表達式

一.介紹

正規表達式用來描述或者比對符合規則的字元串。它的用法和like比較相似，但是它又比like更強大，能夠實作一些很特殊的規則比對；正規表達式需要使用REGEXP指令，比對上傳回"1"比對不上傳回"0"，預設不加條件REGEXP相當于like '%%'。在前面加上NOT相當于NOT LIKE。

指令	說明
^	在字元的開啟處進行比對
$	在字元的末尾處進行比對
.	比對任何字元（包括回車和新行）
[….]	比對括号内的任意單個字元
[m-n]	比對m到n之間的任意單個字元，例如[0-9],[a-z],[A-Z]
[^..]	不能比對括号内的任意單個字元
a*	比對0個或多個a,包括空,可以作為占位符使用.
a+	比對一個或多個a,不包括空
a?	比對一個或0個a
a1| a2	比對a1或a2
a{m}	比對m個a
a{m,}	比對m個或者更多個a
a{m,n}	比對m到n個a
a{,n}	比對0到n個a
(….)	将模式元素組成單一進制素，例如（do）*意思是比對0個多或多個do

二 使用測試

在字元串開始處進行比對

1 2 3 4 5 6 7

mysql> SELECT   'abc' REGEXP  '^a' ; +-------------------+ |  'abc' REGEXP  '^a' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

在字元串末尾開始比對

8 9 10 11 12 13 14 15

mysql> SELECT   'abc' REGEXP  'a$' ; +-------------------+ |  'abc' REGEXP  'a$' | +-------------------+ |                 0 | +-------------------+ row  in set (0.01 sec) mysql> SELECT   'abc' REGEXP  'c$' ; +-------------------+ |  'abc' REGEXP  'c$' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

比對任意字元

16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> SELECT   'abc' REGEXP  '.a' ; +-------------------+ |  'abc' REGEXP  '.a' | +-------------------+ |                 0 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '.b' ; +-------------------+ |  'abc' REGEXP  '.b' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '.c' ; +-------------------+ |  'abc' REGEXP  '.c' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  'a.' ; +-------------------+ |  'abc' REGEXP  'a.' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

　　

[...]

mysql> SELECT   'abc' REGEXP  '[xyz]' ; +----------------------+ |  'abc' REGEXP  '[xyz]' | +----------------------+ |                    0 | +----------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '[xaz]' ; +----------------------+ |  'abc' REGEXP  '[xaz]' | +----------------------+ |                    1 | +----------------------+ row  in set (0.00 sec)

[^...]

注意^符合隻有在[]内才是取反的意思，在别的地方都是表示開始處比對

 注意：REGEXP 前的比對字元作為一個整體

mysql> SELECT   'a' REGEXP  '[^abc]' ; +---------------------+ |  'a' REGEXP  '[^abc]' | +---------------------+ |                   0 | +---------------------+ row  in set (0.00 sec) mysql> SELECT   'x' REGEXP  '[^abc]' ; +---------------------+ |  'x' REGEXP  '[^abc]' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '[^a]' ; +---------------------+ |  'abc' REGEXP  '[^a]' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec)

　注意:　'abc'作為一個整體，是以它比對不了a

　

比對0個或多個a,包括空字元串。 可以作為占位符使用.有沒有指定字元都可以比對到資料

mysql> SELECT  'stab' REGEXP  '.ta*b' ; +-----------------------+ |  'stab' REGEXP  '.ta*b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'stb' REGEXP  '.ta*b' ; +----------------------+ |  'stb' REGEXP  '.ta*b' | +----------------------+ |                    1 | +----------------------+ mysql> SELECT  '' REGEXP  'a*' ; +----------------+ |  '' REGEXP  'a*' | +----------------+ |              1 | +----------------+ 1 row  in set (0.00 sec)

比對1個或者多個a,但是不包括空字元

mysql> SELECT  'stab' REGEXP  '.ta+b' ; +-----------------------+ |  'stab' REGEXP  '.ta+b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'stb' REGEXP  '.ta+b' ; +----------------------+ |  'stb' REGEXP  '.ta+b' | +----------------------+ |                    0 | +----------------------+ row  in set (0.00 sec)

比對0個或者1個a

mysql> SELECT  'stb' REGEXP  '.ta?b' ; +----------------------+ |  'stb' REGEXP  '.ta?b' | +----------------------+ |                    1 | +----------------------+ row  in set (0.00 sec) mysql> SELECT  'stab' REGEXP  '.ta?b' ; +-----------------------+ |  'stab' REGEXP  '.ta?b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'staab' REGEXP  '.ta?b' ; +------------------------+ |  'staab' REGEXP  '.ta?b' | +------------------------+ |                      0 | +------------------------+ row  in set (0.00 sec)

a1|a2

比對a1或者a2，

32 33 34 35 36 37 38 39

mysql> SELECT  'a' REGEXP  'a|b' ; +------------------+ |  'a' REGEXP  'a|b' | +------------------+ |                1 | +------------------+ row  in set (0.00 sec) mysql> SELECT  'b' REGEXP  'a|b' ; +------------------+ |  'b' REGEXP  'a|b' | +------------------+ |                1 | +------------------+ row  in set (0.00 sec) mysql> SELECT  'b' REGEXP  '^(a|b)' ; +---------------------+ |  'b' REGEXP  '^(a|b)' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT  'a' REGEXP  '^(a|b)' ; +---------------------+ |  'a' REGEXP  '^(a|b)' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT  'c' REGEXP  '^(a|b)' ; +---------------------+ |  'c' REGEXP  '^(a|b)' | +---------------------+ |                   0 | +---------------------+ row  in set (0.00 sec)

　注意：^符合隻有在[]内才是取反的意思，在别的地方都是表示開始處比對　

mysql> SELECT  'auuuuc' REGEXP  'au{4}c' ; +--------------------------+ |  'auuuuc' REGEXP  'au{4}c' | +--------------------------+ |                        1 | +--------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{3}c' ; +--------------------------+ |  'auuuuc' REGEXP  'au{3}c' | +--------------------------+ |                        0 | +--------------------------+ row  in set (0.00 sec)

mysql> SELECT  'auuuuc' REGEXP  'au{3,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{3,}c' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{4,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{4,}c' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{5,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{5,}c' | +---------------------------+ |                         0 | +---------------------------+ row  in set (0.00 sec)

比對m到n個a,包含m和n

mysql> SELECT  'auuuuc' REGEXP  'au{3,5}c' ; +----------------------------+ |  'auuuuc' REGEXP  'au{3,5}c' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{4,5}c' ; +----------------------------+ |  'auuuuc' REGEXP  'au{4,5}c' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{5,10}c' ; +-----------------------------+ |  'auuuuc' REGEXP  'au{5,10}c' | +-----------------------------+ |                           0 | +-----------------------------+ row  in set (0.00 sec)

(abc)

将abc作為一個序列比對，不用括号括起來都是用單個字元去比對，如果要把多個字元作為一個整體去比對就需要用到括号，是以括号适合上面的所有情況。

mysql> SELECT  'xababy' REGEXP  'x(abab)y' ; +----------------------------+ |  'xababy' REGEXP  'x(abab)y' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'xababy' REGEXP  'x(ab)*y' ; +---------------------------+ |  'xababy' REGEXP  'x(ab)*y' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'xababy' REGEXP  'x(ab){1,2}y' ; +-------------------------------+ |  'xababy' REGEXP  'x(ab){1,2}y' | +-------------------------------+ |                             1 | +-------------------------------+ row  in set (0.00 sec)

總結

特别要注意最後的()的指令，如果不使用()那麼所有的都是指單個字元去做比對，如果需要使用多個字元作為一個整體去比對,就需要将多個字元使用（）給括起來

1.使用REGEXP和NOT REGEXP操作符(類似LIKE和NOT LIKE)；

2.REGEXP預設也是不區分大小寫，可以使用BINARY關鍵詞強制區分大小寫； WHERE NAME REGEXP BINARY ‘^[A-Z]’;

3.REGEXP預設是部分比對原則，即有一個比對上則傳回真。例如：SELECT  'Abc' REGEXP BINARY '[A-Z]';

作者：趙盼盼

出處：https://www.cnblogs.com/zhaopanpan/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

⇩ 關注或點個喜歡就行 ^_^

關注我

mysql> SELECT   'abc' REGEXP  '^a' ; +-------------------+ |  'abc' REGEXP  '^a' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

mysql> SELECT   'abc' REGEXP  'a$' ; +-------------------+ |  'abc' REGEXP  'a$' | +-------------------+ |                 0 | +-------------------+ row  in set (0.01 sec) mysql> SELECT   'abc' REGEXP  'c$' ; +-------------------+ |  'abc' REGEXP  'c$' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

mysql> SELECT   'abc' REGEXP  '.a' ; +-------------------+ |  'abc' REGEXP  '.a' | +-------------------+ |                 0 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '.b' ; +-------------------+ |  'abc' REGEXP  '.b' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '.c' ; +-------------------+ |  'abc' REGEXP  '.c' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  'a.' ; +-------------------+ |  'abc' REGEXP  'a.' | +-------------------+ |                 1 | +-------------------+ row  in set (0.00 sec)

mysql> SELECT   'abc' REGEXP  '[xyz]' ; +----------------------+ |  'abc' REGEXP  '[xyz]' | +----------------------+ |                    0 | +----------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '[xaz]' ; +----------------------+ |  'abc' REGEXP  '[xaz]' | +----------------------+ |                    1 | +----------------------+ row  in set (0.00 sec)

mysql> SELECT   'a' REGEXP  '[^abc]' ; +---------------------+ |  'a' REGEXP  '[^abc]' | +---------------------+ |                   0 | +---------------------+ row  in set (0.00 sec) mysql> SELECT   'x' REGEXP  '[^abc]' ; +---------------------+ |  'x' REGEXP  '[^abc]' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT   'abc' REGEXP  '[^a]' ; +---------------------+ |  'abc' REGEXP  '[^a]' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec)

mysql> SELECT  'stab' REGEXP  '.ta*b' ; +-----------------------+ |  'stab' REGEXP  '.ta*b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'stb' REGEXP  '.ta*b' ; +----------------------+ |  'stb' REGEXP  '.ta*b' | +----------------------+ |                    1 | +----------------------+ mysql> SELECT  '' REGEXP  'a*' ; +----------------+ |  '' REGEXP  'a*' | +----------------+ |              1 | +----------------+ 1 row  in set (0.00 sec)

mysql> SELECT  'stab' REGEXP  '.ta+b' ; +-----------------------+ |  'stab' REGEXP  '.ta+b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'stb' REGEXP  '.ta+b' ; +----------------------+ |  'stb' REGEXP  '.ta+b' | +----------------------+ |                    0 | +----------------------+ row  in set (0.00 sec)

mysql> SELECT  'stb' REGEXP  '.ta?b' ; +----------------------+ |  'stb' REGEXP  '.ta?b' | +----------------------+ |                    1 | +----------------------+ row  in set (0.00 sec) mysql> SELECT  'stab' REGEXP  '.ta?b' ; +-----------------------+ |  'stab' REGEXP  '.ta?b' | +-----------------------+ |                     1 | +-----------------------+ row  in set (0.00 sec) mysql> SELECT  'staab' REGEXP  '.ta?b' ; +------------------------+ |  'staab' REGEXP  '.ta?b' | +------------------------+ |                      0 | +------------------------+ row  in set (0.00 sec)

mysql> SELECT  'a' REGEXP  'a|b' ; +------------------+ |  'a' REGEXP  'a|b' | +------------------+ |                1 | +------------------+ row  in set (0.00 sec) mysql> SELECT  'b' REGEXP  'a|b' ; +------------------+ |  'b' REGEXP  'a|b' | +------------------+ |                1 | +------------------+ row  in set (0.00 sec) mysql> SELECT  'b' REGEXP  '^(a|b)' ; +---------------------+ |  'b' REGEXP  '^(a|b)' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT  'a' REGEXP  '^(a|b)' ; +---------------------+ |  'a' REGEXP  '^(a|b)' | +---------------------+ |                   1 | +---------------------+ row  in set (0.00 sec) mysql> SELECT  'c' REGEXP  '^(a|b)' ; +---------------------+ |  'c' REGEXP  '^(a|b)' | +---------------------+ |                   0 | +---------------------+ row  in set (0.00 sec)

mysql> SELECT  'auuuuc' REGEXP  'au{4}c' ; +--------------------------+ |  'auuuuc' REGEXP  'au{4}c' | +--------------------------+ |                        1 | +--------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{3}c' ; +--------------------------+ |  'auuuuc' REGEXP  'au{3}c' | +--------------------------+ |                        0 | +--------------------------+ row  in set (0.00 sec)

mysql> SELECT  'auuuuc' REGEXP  'au{3,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{3,}c' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{4,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{4,}c' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{5,}c' ; +---------------------------+ |  'auuuuc' REGEXP  'au{5,}c' | +---------------------------+ |                         0 | +---------------------------+ row  in set (0.00 sec)

mysql> SELECT  'auuuuc' REGEXP  'au{3,5}c' ; +----------------------------+ |  'auuuuc' REGEXP  'au{3,5}c' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{4,5}c' ; +----------------------------+ |  'auuuuc' REGEXP  'au{4,5}c' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'auuuuc' REGEXP  'au{5,10}c' ; +-----------------------------+ |  'auuuuc' REGEXP  'au{5,10}c' | +-----------------------------+ |                           0 | +-----------------------------+ row  in set (0.00 sec)

mysql> SELECT  'xababy' REGEXP  'x(abab)y' ; +----------------------------+ |  'xababy' REGEXP  'x(abab)y' | +----------------------------+ |                          1 | +----------------------------+ row  in set (0.00 sec) mysql> SELECT  'xababy' REGEXP  'x(ab)*y' ; +---------------------------+ |  'xababy' REGEXP  'x(ab)*y' | +---------------------------+ |                         1 | +---------------------------+ row  in set (0.00 sec) mysql> SELECT  'xababy' REGEXP  'x(ab){1,2}y' ; +-------------------------------+ |  'xababy' REGEXP  'x(ab){1,2}y' | +-------------------------------+ |                             1 | +-------------------------------+ row  in set (0.00 sec)