概述
上一章 查詢的過濾條件，我們了解了MySQL可以通過 like % 通配符來進行模糊比對。同樣的，它也支援其他正規表達式的比對，我們在MySQL中使用 REGEXP 操作符來進行正規表達式比對。用法和like相
似，但又強大很多，能夠實作一些很特殊的、複雜的規則比對。正規表達式使用REGEXP指令進行比對時，如果符合傳回1，不符合傳回0。如果 預設不加任何比對規則REGEXP相當于like '%%'。在前面加上NOT（NOT REGEXP）相當于NOT LIKE。
比對模式分析
下面有個表格 ，羅列了可應用于 REGEXP 操作符中正則比對模式，描述相對比較詳細了，後面我們一個一個來測試。
比對模式^
從字元串首部分進行比對，這邊比對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#39;;
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、這邊隻是看介紹了正則的基礎知識，想要更為透徹的了解可以參考 正則教程 ，我覺得寫的不錯。
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司，特别制作了這個專輯——這一次整體放出。
大緻内容包括了： Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧！
歡迎大家關注公衆号【Java爛豬皮】，回複【666】，擷取以上最新Java後端架構VIP學習資料以及視訊學習教程，然後一起學習，一文在手，面試我有。
每一個專欄都是大家非常關心，和非常有價值的話題，如果我的文章對你有所幫助，還請幫忙點贊、好評、轉發一下，你的支援會激勵我輸出更高品質的文章，非常感謝！