日日摸夜夜添夜夜添高潮喷水,国产精品1024在线永久免费

概述

上一章查詢的過濾條件，我們了解了MySQL可以通過 like % 通配符來進(jìn)行模糊匹配。同樣的，它也支持其他正則表達(dá)式的匹配，我們在MySQL中使用 REGEXP 操作符來進(jìn)行正則表達(dá)式匹配。用法和like相

似，但又強(qiáng)大很多，能夠?qū)崿F(xiàn)一些很特殊的、復(fù)雜的規(guī)則匹配。正則表達(dá)式使用REGEXP命令進(jìn)行匹配時(shí)，如果符合返回1，不符合返回0。如果默認(rèn)不加任何匹配規(guī)則REGEXP相當(dāng)于like '%%'。在前面加上NOT（NOT REGEXP）相當(dāng)于NOT LIKE。

匹配模式分析

下面有個(gè)表格，羅列了可應(yīng)用于 REGEXP 操作符中正則匹配模式，描述相對比較詳細(xì)了，后面我們一個(gè)一個(gè)來測試。

匹配模式	描述
^	匹配輸入字符串的開始位置。如果設(shè)置了 REGEXP 對象的 Multiline 屬性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配輸入字符串的結(jié)束位置。如果設(shè)置了REGEXP 對象的 Multiline 屬性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何單個(gè)字符。要匹配包括 '\n' 在內(nèi)的任何字符，請使用 '[.\n]' 的模式。
[….]	字符集合。匹配所包含的任意一個(gè)字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	非匹配字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
[n-m]	匹配m到n之間的任意單個(gè)字符，例如[0-9],[a-z],[A-Z]
*	匹配前面的子表達(dá)式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等價(jià)于{0,}。
+	匹配前面的子表達(dá)式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等價(jià)于 {1,}。
?	匹配前面的子表達(dá)式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等價(jià)于 {0,1}。
a1\| a2\|a3	匹配 a1 或 a2 或 a3。例如，'z\|food' 能匹配 "z" 或 "food"。'(z\|f)ood' 則匹配 "zood" 或 "food"。
{n}	n 是一個(gè)非負(fù)整數(shù)。匹配確定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的兩個(gè) o。
{n,}	匹配前面的子表達(dá)式n次到多次。例如，'o{2,}' 不僅能匹配 "food" ，也能匹配 "foood"。
{n,m}	n 和 m 均為非負(fù)整數(shù)，其中n = m。最少匹配 n 次且最多匹配 m 次。
{,m}	匹配前面的子表達(dá)式0次到m次
(….)	元素組合，即將模式元素組成單一元素，例如（do）*意思是匹配0個(gè)多或多個(gè)do

匹配模式^

從字符串首部分進(jìn)行匹配，這邊匹配s開頭的，匹配符合返回1，不符合返回0。應(yīng)用到表中，既符合返回匹配到的數(shù)據(jù)。

mysql> select 'selina' REGEXP '^s';
+----------------------+
| 'selina' REGEXP '^s' |
+----------------------+
|   1 |
+----------------------+
1 row in set

mysql> select 'aelina' REGEXP '^s';
+----------------------+
| 'aelina' REGEXP '^s' |
+----------------------+
|   0 |
+----------------------+
1 row in set

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '^s';
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+---------+-----+
2 rows in set

匹配模式$

從字符串尾部進(jìn)行匹配，這邊匹配名稱以d結(jié)尾的數(shù)據(jù)。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'd$';
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
+----+-------+-----+---------+-----+
1 row in set

匹配模式.

. 是匹配任意單個(gè)字符，下面腳本匹配 n并且后面帶一個(gè)任意字符的條件

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'n.';
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+---------+-----+
3 rows in set

匹配模式[...]

指匹配括號內(nèi)的任意單個(gè)字符，只要有一個(gè)字符符合條件即可。下面例子能匹配到b、w、z的只有brand、weng 兩個(gè)名稱。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP [bwz];
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
mysql> select * from user2 where name REGEXP '[bwz]';
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+---------+-----+
2 rows in set

匹配模式[^...]

[^...]取反的意思，指匹配未包含的任意字符。例如， '[^brand]' 可以匹配 "helen" 中的'h'，"sol" 的 "s"，"weng" 的 "w"，"selina" 的 "s"，但無法匹配"brand"，所以被過濾了。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '[^brand]';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
4 rows in set

匹配模式[n-m]

匹配m到n之間的任意單個(gè)字符，例如[0-9],[a-z],[A-Z]，下方代碼中，任何元素不在a - e之間的"sol" 被過濾了。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '[a-e]';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
4 rows in set

匹配模式 *

匹配前面的子表達(dá)式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等價(jià)于{0,}。下面的 "e*g" 可以匹配的只有 "weng" 這個(gè)名稱。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'e*g';
+----+------+-----+---------+-----+
| id | name | age | address | sex |
+----+------+-----+---------+-----+
| 4 | weng | 33 | guizhou | 1 |
+----+------+-----+---------+-----+
1 row in set

匹配模式 +

匹配前面的子表達(dá)式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等價(jià)于 {1,}。如下方的腳本，符合條件的是1到多個(gè)的n加上一個(gè)d的組合，只有 "brand" 和 "annd" 符合。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'n+d';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
2 rows in set

匹配模式 ?

匹配前面的子表達(dá)式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等價(jià)于 {0,1}。e為1個(gè)或者0個(gè)，后面再用 l 限制，所以符合的只有三個(gè)。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'e?l';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
3 rows in set

匹配模式 a1| a2|a3

匹配 a1 或 a2 或 a3。例如下方，'nn|en' 能分別匹配到 "anny" 、"annd" 和 "helen"、"weng"。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'nn|en';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
4 rows in set

匹配模式 {n} {n,} {n,m} {,m}

n 和 m 均為非負(fù)整數(shù)，其中n = m。最少匹配 n 次且最多匹配 m 次。m為空代表>=n的任意數(shù)，n為空代表0。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'n{2}';
+----+------+-----+----------+-----+
| id | name | age | address | sex |
+----+------+-----+----------+-----+
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+------+-----+----------+-----+
2 rows in set

mysql> select * from user2 where name REGEXP 'n{1,2}';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
6 rows in set

mysql> select * from user2 where name REGEXP 'l{1,}';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL  | 0 |
+----+--------+-----+----------+-----+
3 rows in set

匹配模式(...)

假設(shè)括號內(nèi)容為abc，則是將abc作為一個(gè)整體去匹配，符合這個(gè)規(guī)則的數(shù)據(jù)被過濾出來。下面以an為例子，配合上面學(xué)過的知識。

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL  | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP '(an)+';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> select * from user2 where name REGEXP '(ann)+';
+----+------+-----+----------+-----+
| id | name | age | address | sex |
+----+------+-----+----------+-----+
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+------+-----+----------+-----+
2 rows in set

mysql> select * from user2 where name REGEXP '(an).*d{1,2}';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
2 rows in set

匹配特殊字符 \\

正則表達(dá)式語言由具有特定含義的特殊字符構(gòu)成。我們已經(jīng)看到.、 []、|、*、+ 等，那我們是怎么匹配這些字符的。如下示例，我們使用 \\ 來匹配特殊字符，\\為前導(dǎo)， \\-表示查找-， \\.表示查找.。

mysql> select * from user3;
+----+------+-------+
| id | age | name |
+----+------+-------+
| 1 | 20 | brand |
| 2 | 22 | sol |
| 3 | 20 | helen |
| 4 | 19.5 | diny |
+----+------+-------+
4 rows in set

mysql> select * from user3 where age REGEXP '[0-9]+\\.[0-9]+';
+----+------+------+
| id | age | name |
+----+------+------+
| 4 | 19.5 | diny |
+----+------+------+
1 row in set

總結(jié)

1.當(dāng)我們需要用正則匹配數(shù)據(jù)的時(shí)候，可以使用REGEXP和NOT REGEXP操作符(類似LIKE和NOT LIKE)；

2.REGEXP默認(rèn)不區(qū)分大小寫，可以使用BINARY關(guān)鍵詞強(qiáng)制區(qū)分大小寫； WHERE NAME REGEXP BINARY ‘^[A-Z]';

3.REGEXP默認(rèn)是部分匹配原則，即有一個(gè)匹配上則返回真。例如：SELECT 'A123' REGEXP BINARY '[A-Z]'，返回的是1;

4、如果使用 () 進(jìn)行匹配，則是將括號內(nèi)部的內(nèi)容當(dāng)作整體去匹配，比如 (ABC)，則需要匹配整個(gè)ABC。

5、這邊只是看介紹了正則的基礎(chǔ)知識，想要更為透徹的了解可以參考正則教程，我覺得寫的不錯(cuò)。

到此這篇關(guān)于MySQL全面瓦解之查詢的正則匹配詳解的文章就介紹到這了,更多相關(guān)MySQL查詢的正則匹配內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

mysql中如何使用正則表達(dá)式查詢
mysql 正則表達(dá)式查詢含有非數(shù)字和字符的記錄
MySql中使用正則表達(dá)式查詢的方法
詳解MySql基本查詢、連接查詢、子查詢、正則表達(dá)查詢
MySQL使用正則表達(dá)式進(jìn)行查詢操作經(jīng)典實(shí)例總結(jié)
MySql官方手冊學(xué)習(xí)筆記2 MySql的模糊查詢和正則表達(dá)式

標(biāo)簽：合肥惠州呼和浩特公主嶺沈陽牡丹江天津阿里

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL全面瓦解之查詢的正則匹配詳解》，本文關(guān)鍵詞 MySQL,全面,瓦解,之,查詢,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。