概述
上一章 查詢的過濾條件，我們瞭解了MySQL可以通過 like % 萬用字元來進行模糊匹配。同樣的，它也支援其他正規表示式的匹配，我們在MySQL中使用 REGEXP 操作符來進行正規表示式匹配。用法和like相
似，但又強大很多，能夠實現一些很特殊的、複雜的規則匹配。正規表示式使用REGEXP命令進行匹配時，如果符合返回1，不符合返回0。如果 預設不加任何匹配規則REGEXP相當於like '%%'。在前面加上NOT（NOT REGEXP）相當於NOT LIKE。
匹配模式分析
下面有個表格 ，羅列了可應用於 REGEXP 操作符中正則匹配模式，描述相對比較詳細了，後面我們一個一個來測試。
|
匹配模式 |
描述 |
|
^ |
匹配輸入字串的開始位置。如果設定了 REGEXP 物件的 Multiline 屬性，^ 也匹配 '\n' 或 '\r' 之後的位置。 |
|
$ |
匹配輸入字串的結束位置。如果設定了REGEXP 物件的 Multiline 屬性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
|
. |
匹配除 "\n" 之外的任何單個字元。要匹配包括 '\n' 在內的任何字元，請使用 '[.\n]' 的模式。 |
|
[….] |
字符集合。匹配所包含的任意一個字元。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
|
[^...] |
非匹配字符集合。匹配未包含的任意字元。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
|
[n-m] |
匹配m到n之間的任意單個字元，例如[0-9],[a-z],[A-Z] |
|
* |
匹配前面的子表示式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等價於{0,}。 |
|
+ |
匹配前面的子表示式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等價於 {1,}。 |
|
? |
匹配前面的子表示式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等價於 {0,1}。 |
|
a1| a2|a3 |
匹配 a1 或 a2 或 a3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 則匹配 "zood" 或 "food"。 |
|
{n} |
n 是一個非負整數。匹配確定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的兩個 o。 |
|
{n,} |
匹配前面的子表示式n次到多次。例如，'o{2,}' 不僅能匹配 "food" ，也能匹配 "foood"。 |
|
{n,m} |
n 和 m 均為非負整數，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
|
{,m} |
匹配前面的子表示式0次到m次 |
|
(….) |
元素組合，即將模式元素組成單一元素，例如（do）*意思是匹配0個多或多個do |
匹配模式^
從字串首部分進行匹配，這邊匹配s開頭的，匹配符合返回1，不符合返回0。應用到表中，既符合返回匹配到的資料。
1 mysql> select 'selina' REGEXP '^s'; 2 +----------------------+ 3 | 'selina' REGEXP '^s' | 4 +----------------------+ 5 | 1 | 6 +----------------------+ 7 1 row in set 8 9 mysql> select 'aelina' REGEXP '^s'; 10 +----------------------+ 11 | 'aelina' REGEXP '^s' | 12 +----------------------+ 13 | 0 | 14 +----------------------+ 15 1 row in set
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP '^s'; 14 +----+--------+-----+---------+-----+ 15 | id | name | age | address | sex | 16 +----+--------+-----+---------+-----+ 17 | 3 | sol | 21 | xiamen | 0 | 18 | 5 | selina | 25 | NULL | 0 | 19 +----+--------+-----+---------+-----+ 20 2 rows in set
匹配模式$
從字串尾部進行匹配，這邊匹配名稱以d結尾的資料。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP 'd$'; 14 +----+-------+-----+---------+-----+ 15 | id | name | age | address | sex | 16 +----+-------+-----+---------+-----+ 17 | 1 | brand | 21 | fuzhou | 1 | 18 +----+-------+-----+---------+-----+ 19 1 row in set
匹配模式.
. 是匹配任意單個字元，下面指令碼匹配 n並且後面帶一個任意字元的條件
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP 'n.'; 14 +----+--------+-----+---------+-----+ 15 | id | name | age | address | sex | 16 +----+--------+-----+---------+-----+ 17 | 1 | brand | 21 | fuzhou | 1 | 18 | 4 | weng | 33 | guizhou | 1 | 19 | 5 | selina | 25 | NULL | 0 | 20 +----+--------+-----+---------+-----+ 21 3 rows in set
匹配模式[...]
指匹配括號內的任意單個字元，只要有一個字元符合條件即可。下面例子能匹配到b、w、z的 只有brand、weng 兩個名稱。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP [bwz]; 14 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[bwz]' at line 1 15 mysql> select * from user2 where name REGEXP '[bwz]'; 16 +----+-------+-----+---------+-----+ 17 | id | name | age | address | sex | 18 +----+-------+-----+---------+-----+ 19 | 1 | brand | 21 | fuzhou | 1 | 20 | 4 | weng | 33 | guizhou | 1 | 21 +----+-------+-----+---------+-----+ 22 2 rows in set
匹配模式[^...]
[^...]取反的意思，指匹配未包含的任意字元。例如， '[^brand]' 可以匹配 "helen" 中的'h'，"sol" 的 "s"，"weng" 的 "w"，"selina" 的 "s"，但無法匹配"brand"，所以被過濾了。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP '[^brand]'; 14 +----+--------+-----+----------+-----+ 15 | id | name | age | address | sex | 16 +----+--------+-----+----------+-----+ 17 | 2 | helen | 20 | quanzhou | 0 | 18 | 3 | sol | 21 | xiamen | 0 | 19 | 4 | weng | 33 | guizhou | 1 | 20 | 5 | selina | 25 | NULL | 0 | 21 +----+--------+-----+----------+-----+ 22 4 rows in set
匹配模式[n-m]
匹配m到n之間的任意單個字元，例如[0-9],[a-z],[A-Z]，下方程式碼中，任何元素不在a - e之間的"sol" 被過濾了。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP '[a-e]'; 14 +----+--------+-----+----------+-----+ 15 | id | name | age | address | sex | 16 +----+--------+-----+----------+-----+ 17 | 1 | brand | 21 | fuzhou | 1 | 18 | 2 | helen | 20 | quanzhou | 0 | 19 | 4 | weng | 33 | guizhou | 1 | 20 | 5 | selina | 25 | NULL | 0 | 21 +----+--------+-----+----------+-----+ 22 4 rows in set
匹配模式 *
匹配前面的子表示式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等價於{0,}。 下面的 "e*g" 可以匹配的只有 "weng" 這個名稱。1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 +----+--------+-----+----------+-----+ 11 5 rows in set 12 13 mysql> select * from user2 where name REGEXP 'e*g'; 14 +----+------+-----+---------+-----+ 15 | id | name | age | address | sex | 16 +----+------+-----+---------+-----+ 17 | 4 | weng | 33 | guizhou | 1 | 18 +----+------+-----+---------+-----+ 19 1 row in set
匹配模式 +
匹配前面的子表示式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等價於 {1,}。如下方的指令碼，符合條件的是1到多個的n加上一個d的組合，只有 "brand" 和 "annd" 符合。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 | 6 | anny | 23 | shanghai | 0 | 11 | 7 | annd | 24 | shanghai | 1 | 12 +----+--------+-----+----------+-----+ 13 7 rows in set 14 15 mysql> select * from user2 where name REGEXP 'n+d'; 16 +----+-------+-----+----------+-----+ 17 | id | name | age | address | sex | 18 +----+-------+-----+----------+-----+ 19 | 1 | brand | 21 | fuzhou | 1 | 20 | 7 | annd | 24 | shanghai | 1 | 21 +----+-------+-----+----------+-----+ 22 2 rows in set
匹配模式 ?
匹配前面的子表示式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等價於 {0,1}。e為1個或者0個，後面再用 l 限制，所以符合的只有三個。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 | 6 | anny | 23 | shanghai | 0 | 11 | 7 | annd | 24 | shanghai | 1 | 12 +----+--------+-----+----------+-----+ 13 7 rows in set 14 15 mysql> select * from user2 where name REGEXP 'e?l'; 16 +----+--------+-----+----------+-----+ 17 | id | name | age | address | sex | 18 +----+--------+-----+----------+-----+ 19 | 2 | helen | 20 | quanzhou | 0 | 20 | 3 | sol | 21 | xiamen | 0 | 21 | 5 | selina | 25 | NULL | 0 | 22 +----+--------+-----+----------+-----+ 23 3 rows in set
匹配模式 a1| a2|a3
匹配 a1 或 a2 或 a3。例如下方，'nn|en' 能分別匹配到 "anny" 、"annd" 和 "helen"、"weng"。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 | 6 | anny | 23 | shanghai | 0 | 11 | 7 | annd | 24 | shanghai | 1 | 12 +----+--------+-----+----------+-----+ 13 7 rows in set 14 15 mysql> select * from user2 where name REGEXP 'nn|en'; 16 +----+-------+-----+----------+-----+ 17 | id | name | age | address | sex | 18 +----+-------+-----+----------+-----+ 19 | 2 | helen | 20 | quanzhou | 0 | 20 | 4 | weng | 33 | guizhou | 1 | 21 | 6 | anny | 23 | shanghai | 0 | 22 | 7 | annd | 24 | shanghai | 1 | 23 +----+-------+-----+----------+-----+ 24 4 rows in set
匹配模式 {n} {n,} {n,m} {,m}
n 和 m 均為非負整數，其中n <= m。最少匹配 n 次且最多匹配 m 次。m為空代表>=n的任意數，n為空代表0。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 | 6 | anny | 23 | shanghai | 0 | 11 | 7 | annd | 24 | shanghai | 1 | 12 +----+--------+-----+----------+-----+ 13 7 rows in set 14 15 mysql> select * from user2 where name REGEXP 'n{2}'; 16 +----+------+-----+----------+-----+ 17 | id | name | age | address | sex | 18 +----+------+-----+----------+-----+ 19 | 6 | anny | 23 | shanghai | 0 | 20 | 7 | annd | 24 | shanghai | 1 | 21 +----+------+-----+----------+-----+ 22 2 rows in set 23 24 mysql> select * from user2 where name REGEXP 'n{1,2}'; 25 +----+--------+-----+----------+-----+ 26 | id | name | age | address | sex | 27 +----+--------+-----+----------+-----+ 28 | 1 | brand | 21 | fuzhou | 1 | 29 | 2 | helen | 20 | quanzhou | 0 | 30 | 4 | weng | 33 | guizhou | 1 | 31 | 5 | selina | 25 | NULL | 0 | 32 | 6 | anny | 23 | shanghai | 0 | 33 | 7 | annd | 24 | shanghai | 1 | 34 +----+--------+-----+----------+-----+ 35 6 rows in set 36 37 mysql> select * from user2 where name REGEXP 'l{1,}'; 38 +----+--------+-----+----------+-----+ 39 | id | name | age | address | sex | 40 +----+--------+-----+----------+-----+ 41 | 2 | helen | 20 | quanzhou | 0 | 42 | 3 | sol | 21 | xiamen | 0 | 43 | 5 | selina | 25 | NULL | 0 | 44 +----+--------+-----+----------+-----+ 45 3 rows in set
匹配模式(...)
假設括號內容為abc，則是將abc作為一個整體去匹配，符合這個規則的資料被過濾出來。下面以an為例子，配合上面學過的知識。
1 mysql> select * from user2; 2 +----+--------+-----+----------+-----+ 3 | id | name | age | address | sex | 4 +----+--------+-----+----------+-----+ 5 | 1 | brand | 21 | fuzhou | 1 | 6 | 2 | helen | 20 | quanzhou | 0 | 7 | 3 | sol | 21 | xiamen | 0 | 8 | 4 | weng | 33 | guizhou | 1 | 9 | 5 | selina | 25 | NULL | 0 | 10 | 6 | anny | 23 | shanghai | 0 | 11 | 7 | annd | 24 | shanghai | 1 | 12 +----+--------+-----+----------+-----+ 13 7 rows in set 14 15 mysql> select * from user2 where name REGEXP '(an)+'; 16 +----+-------+-----+----------+-----+ 17 | id | name | age | address | sex | 18 +----+-------+-----+----------+-----+ 19 | 1 | brand | 21 | fuzhou | 1 | 20 | 6 | anny | 23 | shanghai | 0 | 21 | 7 | annd | 24 | shanghai | 1 | 22 +----+-------+-----+----------+-----+ 23 3 rows in set 24 25 mysql> select * from user2 where name REGEXP '(ann)+'; 26 +----+------+-----+----------+-----+ 27 | id | name | age | address | sex | 28 +----+------+-----+----------+-----+ 29 | 6 | anny | 23 | shanghai | 0 | 30 | 7 | annd | 24 | shanghai | 1 | 31 +----+------+-----+----------+-----+ 32 2 rows in set 33 34 mysql> select * from user2 where name REGEXP '(an).*d{1,2}'; 35 +----+-------+-----+----------+-----+ 36 | id | name | age | address | sex | 37 +----+-------+-----+----------+-----+ 38 | 1 | brand | 21 | fuzhou | 1 | 39 | 7 | annd | 24 | shanghai | 1 | 40 +----+-------+-----+----------+-----+ 41 2 rows in set
匹配特殊字元 \\
正規表示式語言由具有特定含義的特殊字元構成。我們已經看到.、 []、|、*、+ 等， 那我們是怎麼匹配這些字元的。如下示例，我們使用 \\ 來匹配特殊字元，\\為前導， \\-表示查詢-， \\.表示查詢.。
1 mysql> select * from user3; 2 +----+------+-------+ 3 | id | age | name | 4 +----+------+-------+ 5 | 1 | 20 | brand | 6 | 2 | 22 | sol | 7 | 3 | 20 | helen | 8 | 4 | 19.5 | diny | 9 +----+------+-------+ 10 4 rows in set 11 12 mysql> select * from user3 where age REGEXP '[0-9]+\\.[0-9]+'; 13 +----+------+------+ 14 | id | age | name | 15 +----+------+------+ 16 | 4 | 19.5 | diny | 17 +----+------+------+ 18 1 row in set
總結
1.當我們需要用正則匹配資料的時候，可以使用REGEXP和NOT REGEXP操作符(類似LIKE和NOT LIKE)；
2.REGEXP預設不區分大小寫，可以使用BINARY關鍵詞強制區分大小寫； WHERE NAME REGEXP BINARY ‘^[A-Z]’;
3.REGEXP預設是部分匹配原則，即有一個匹配上則返回真。例如：SELECT 'A123' REGEXP BINARY '[A-Z]'，返回的是1;
4、如果使用 () 進行匹配，則是將括號內部的內容當作整體去匹配，比如 (ABC)，則需要匹配整個ABC。
5、這邊只是看介紹了正則的基礎知識，想要更為透徹的瞭解可以參考 正則教程 ，我覺得寫的不錯。