介紹

正則表達式用來描述或者匹配符合規則的字符串。它的用法和like比較相似，但是它又比like更強大，能夠實現一些很特殊的規則匹配；正則表達式需要使用REGEXP命令，匹配上返回"1"匹配不上返回"0"，REGEXP相當於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

 

 ^

在字符串開始處進行匹配

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

$

在字符串末尾開始匹配

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

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

.

匹配任意字符

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

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

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

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

[...]

匹配括號內的任意單個字符

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

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

[^...]:注意^符合只有在[]內才是取反的意思，在別的地方都是表示開始處匹配

 匹配不出括號沒的任意個字符，注意：REGEXP 前的匹配字符作為一個整體

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

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

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

'abc'作為一個整體，所以它匹配不了a

a*

匹配0個或多個a,包括空字符串。

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

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

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

a+

匹配1個或者多個a,但是不包括空字符

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

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

a?

匹配0個或者1個a

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

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

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

a1|a2

匹配a1或者a2，

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

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

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

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

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

注意：^符合只有在[]內才是取反的意思，在別的地方都是表示開始處匹配

a{m}

匹配m個a

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

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

a{m,}

匹配m個或者更多個a

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

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

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

a{m,n}

匹配m到n個a,包含m和n

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

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

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

a{,n}

這個暫時還沒有測出來，意思應該不是匹配0到n個a,如果是表達0到n個a也可以用{0,n}代替

 

(abc)

將abc作為一個序列匹配，不用括號括起來都是用單個字符去匹配，如果要把多個字符作為一個整體去匹配就需要用到括號，所以括號適合上面的所有情況。

mysql> SELECT 'xababy' REGEXP 'x(abab)y';
+----------------------------+
| 'xababy' REGEXP 'x(abab)y' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

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

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

 

總結

 特別要注意最後的()的命令，如果不使用()那麼所有的都是指單個字符去做匹配，如果需要使用多個字符作為一個整體去匹配就需要將多個字符使用（）給括起來