MySQL正規表示式REGEXP使用詳解

2022-09-20 22:03:46

LIKE 和 REGEXP之間的重要差別

LIKE 匹配整個列，如果被匹配的文字在列值中出現，LIKE 將不會找到它，相應的行也不會被返回（除非使用萬用字元）。而 REGEXP 在列值內進行匹配，如果被匹配的文字在列值中出現，REGEXP 將會找到它，相應的行將被返回，並且 REGEXP 能匹配整個列值（與 LIKE 相同的作用）。

正規表示式匹配不區分大小寫

MySQL 的正規表示式匹配（自3.23.4版本後）不區分大小寫（即大寫和小寫都匹配）。為區分大小寫，可以使用 BINARY 關鍵字，例如：WHHERE name REGEXP BINARY 'Hern .000'。

簡單的正規表示式測試

可以在不使用資料庫表的情況下用 SELECT 語句來測試正規表示式，REGEXP 檢查總是返回0（沒有匹配）或1（匹配）。可以用帶文字串的 REGEXP 來測試表示式，並試驗它們。

例如：檢測 'Hern' 中是否含有數位

SELECT 'Hern' REGEXP '[0-9]';

結果將返回0（因為 ‘Hern’ 中沒有數位）

空白元字元（特殊含義的字元）

用雙反斜槓（\）來參照元字元（具有特殊含義的字元）。常用參照元字元：

字元	其它資訊
[ 和 ]	左右方括號用於指定字元類。字元類是進行匹配時所要依據的一組字元。除連字元 (-) 和脫字元 (^) 外，在字元類中指定的元字元和量詞（如 * 和 {m}，分別為元字元和量詞）沒有特殊意義，可當作實際字元進行運算。
*	星號可用於與字元匹配 0 次或多次。例如，`REGEXP '.*abc'` 匹配的字串以 abc 結尾並以任何字首開頭。因此，aabc、xyzabc 和 abc 匹配，但 bc 和 abcc 則不匹配。
?	問號可用於與字元匹配 0 次或 1 次。例如，`'colou?r'` 匹配 color 和 colour。
+	加號可用於與字元匹配 1 次或多次。例如，`'bre+'` 匹配 bre 和 bree，但不匹配 br。
-	可以在字元類中使用一個連字元來表示一個範圍。例如，`REGEXP '[a-e]'` 匹配 a、b、c、d 和 e。
%	百分號可與 SIMILAR TO 配合使用來匹配任意數目的字元。不將百分號視為 REGEXP 和 REGEXP_SUBSTR 所使用的元字元。當指定時，它匹配百分號 (%)。
_（下劃線字元）	可將下劃線與 SIMILAR TO 配合使用來匹配單個字元。不將下劃線視為 REGEXP 和 REGEXP_SUBSTR 所使用的元字元。當指定時，它匹配下劃線 (_)。
\|	管道符號用於指定匹配字串時要使用的替代模式。在由豎線分隔的一行模式中，豎線被解釋為 OR，匹配過程從最左側的模式開始，在找到第一個匹配項時停止。因此，您應按優先順序的降序順序列出模式。您可以指定任意數量的替代模式。
( 和 )	當左括號和右括號用於正規表示式的各個組合部分時，它們為元字元。例如，`(ab)*` 匹配零個或多個 ab 的重複項。與使用數學表示式一樣，您使用組合來控制正規表示式各部分的計算順序。
{ 和 }	當左大括號和右大括號用於指定量詞時，它們為元字元。量詞指定一個模式要構成某個匹配所必須重複的次數。例如： {m} 匹配某個字元正好 m 次。例如，`'519-[0-9]{3}-[0-9]{4}'` 匹配 519 地區號中的一個電話號碼（假定資料按語法中定義的方式進行格式設定）。 {m,} 匹配某個字元至少 m 次。例如，`'[0-9]{5,}'` 匹配任何含有五個或更多數位的字串。 {m,n} 匹配某個字元至少 m 次，但不超過 n 次。例如，`SIMILAR TO '_{5,10}'` 匹配任何含有 5 到 10（含 5 和 10）個字元的字串。
	反斜線被用作元字元的跳脫字元。它也可被用於跳脫非元字元。
^	匹配輸入字串的開始位置。如果設定了 RegExp 物件的 Multiline 屬性，^ 也匹配 'n' 或 'r' 之後的位置。
$	匹配輸入字串的結束位置。如果設定了RegExp 物件的 Multiline 屬性，$ 也匹配 'n' 或 'r' 之前的位置。
.	匹配除 "n" 之外的任何單個字元。要匹配包括 'n' 在內的任何字元，請使用象 '[.n]' 的模式。
:	在字元集中使用冒號來指定子字元類。例如，`'[[:alnum:]]'`。

注意：

1、多數正規表示式實現使用單個反斜槓跳脫特殊字元，一遍能使用這些字元本身，但是MySQL要求兩個反斜槓（MySQL自己解釋一個，正規表示式庫解釋另一個）。

2、^ 符號的雙重用途： ^在集合中（用 [ ] 定義）時用它來否定該集合，否則用來指定串的開始處。

匹配字元類

子字元類	其它資訊
[:alpha:]	匹配當前歸類中的大寫和小寫字母字元。例如，'[0-9]{3}[[:alpha:]]{2}' 匹配三個數位，後跟兩個字母。
[:alnum:]	匹配當前歸類中的數位、大寫和小寫字母字元。例如，'[[:alnum:]]+' 匹配含有一個或多個字母和數位的字串。
[:digit:]	匹配當前歸類中的數位。例如，'[[:digit:]-]+' 匹配含有一個或多個數位或橫線的字串。同樣，'[^[:digit:]-]+' 匹配含有一個或多個不是數位或橫線的字元的字串。
[:lower:]	匹配當前歸類中的小寫字母字元。例如，'[[:lower:]]' 不匹配 A，因為 A 為大寫。
[:space:]	匹配單個空格 (' ')。例如，搜尋 Contacts.City 以查詢任何名稱為兩個詞的城市： SELECT City FROM Contacts WHERE City REGEXP '.[[:space:]].';
[:upper:]	匹配當前歸類中的大寫字母字元。例如，`'[[:upper:]ab]'` 與以下其中一項匹配：任何大寫字母、a 或 b。
[:whitespace:]	匹配一個空白字元，例如，空格、製表符、換頁符和回車符。
[:ascii:]	匹配任何七位的 ASCII 字元（0 到 127 之間的順序值）。
[:blank:]	匹配一個空白區或水平製表符。 `[[:blank:]]` 等效於 `[ t]`。
[:cntrl:]	匹配順序值小於 32 或字元值為 127 的 ASCII 字元（控制字元）。控制字元包括換行符、換頁符、退格符，等等。
[:graph:]	匹配列印字元。 `[[:graph:]]` 等效於 `[[:alnum:][:punct:]]`。
[:print:]	匹配列印字元和空格。 `[[:print:]]` 等效於 `[[:graph:][:whitespace:]]`。
[:punct:]	匹配其中一個字元： !"#$%&'()*+,-./:;<=>?@[]^_`{\|}~. `[:punct:]` 子字元類不能包括當前歸類中可用的非 ASCII 標點字元。
[:word:]	匹配當前歸類中的字母、數位或下劃線字元。 `[[:word:]]` 等效於 `[[:alnum:]_]`。
[:xdigit:]	匹配字元類 [0-9A-Fa-f] 中的字元。

正規表示式支援的其它語法約定

正規表示式語法	名稱和含義
xxx	匹配值為 xxx 的字元，其中 xxx 是任何八進位制數位序列，0 是零。例如，`134` 匹配反斜線。
a	匹配報警字元。
A	用在字元集外部以便匹配字串的開頭。等效於在字元集外部使用的 `^`。
b	匹配退格字元。
B	匹配反斜線字元 ()。
cX	匹配已命名的控制字元。例如，cZ 代表 ctrl-Z。
d	匹配當前歸類中的一個數位。例如，以下語句搜尋 Contacts.Phone 以查詢以 00 結尾的所有電話號碼： SELECT Surname, Surname, City, Phone FROM Contacts WHERE Phone REGEXP '\d{8}00'; d 既可用在字元類的內部也可用在字元類的外部，等效於 `[[:digit:]]`。
D	匹配數位以外的任何字元。它的作用與 d 正好相反。 D 既可用在字元類的內部也可用在字元類的外部，等效於 `[^[:digit:]]`。在方括號內使用取非速記時請務必謹慎。`[DS]` 與 `[^ds]` 並不相同。後者匹配數位或空格以外的任何字元。所以它匹配 x，但不匹配 8。而前者匹配不是數位或不是空格（滿足兩個條件之一）的任何字元。因為數位不是空格，空格也不是數位，所以 `[DS]` 可以匹配任何字元、數位、空格或其它字元。
e	匹配跳脫字元。
E	將由 Q 啟動的將元字元視為非元字元這一功能停止。
f	匹配換頁符。
n	匹配換行符。
Q	將所有元字元視為非元字元，直到遇到 E。例如，`Q[$E` 等效於 `[$`。
r	匹配回車符。
s	匹配一個被視為白空格的空格或字元。例如，從 Products.ProductName 中返回名稱中至少有一個空格的所有產品名： SELECT Name FROM Products WHERE Name REGEXP '.\s.'; s 既可用在字元類的內部也可用在字元類的外部，等效於 `[[:whitespace:]]`。
S	匹配非白空格字元。它的作用與 d 正好相反，而等效於 `[^[:whitespace:]]`。 S 既可用在字元類的內部也可用在字元類的外部。在方括號內使用取非速記時請務必謹慎。`[DS]` 與 `[^ds]` 並不相同。後者匹配數位或空格以外的任何字元。所以它匹配 x，但不匹配 8。而前者匹配不是數位或不是空格（滿足兩個條件之一）的任何字元。因為數位不是空格，空格也不是數位，所以 `[DS]` 可以匹配任何字元、數位、空格或其它字元。
t	匹配水平製表符。
v	匹配垂直製表符。
w	匹配當前歸類中的字母字元、數位或下劃線。例如，從 Contacts.Surname 返回長度正好為七個字母數位字元的所有姓： SELECT Surname FROM Contacts WHERE Surname REGEXP '\w{7}'; w 既可用在字元類的內部也可用在字元類的外部。等效於 `[[:alnum:]_].`。
W	匹配當前歸類中字母字元、數位或下劃線以外的任何字元。它的作用與 w 正好相反，而等效於 `[^[:alnum:]_]`。在字元類的內部和外部都可使用此正規表示式。
xhh	匹配值為 0xhh 的字元，其中 hh 最多為兩個十六進位制數位。例如，`x2D` 等效於一個連字元。等效於 x{hh}。
x{hhh}	匹配值為 0xhhh 的字元，其中 hhh 最多為三個十六進位制數位。
z 和 Z	匹配字串結尾處的位置（而非字元）。等效於 `$`。

正規表示式斷言

斷言測試條件是否為真，並影響字串中開始匹配的位置。斷言不返回字元；最終匹配中不包括斷言模式。REGEXP 搜尋條件和REGEXP_SUBSTR 函數支援斷言模式。

在嘗試拆分字串時，lookahead 和 lookbehind 斷言對於 REGEXP_SUBSTR 將非常有用。例如，可以通過執行以下語句返回 Customers 表的 Address 列中街道名稱（不帶街道編號）的列表：

SELECT REGEXP_SUBSTR( Street, '(?<=^\S+\s+).*$' ) 
FROM Customers;

另一個範例：假定您想要使用正規表示式來驗證口令是否符合某些規則。可以使用類似於下面內容的零寬度斷言：

IF password REGEXP '(?=.*[[:digit:]])(?=.*[[:alpha:]].*[[:alpha:]])[[:word:]]{4,12}' 
   MESSAGE 'Password conforms' TO CLIENT;
ELSE
   MESSAGE 'Password does not conform' TO CLIENT;
END IF

password 至少有一位數（零寬度肯定斷言 [[:digit:]]）當滿足以下條件時，口令有效：

password 至少有兩個字母字元（零寬度肯定斷言 [[:alpha:]].*[[:alpha:]]）
password 只含有字母數位字元或下劃線字元 ([[:word:]])
password 最少含有 4 個字元，最多含有 12 個字元 ({4,12})

下表包含 SQL Anywhere 支援的斷言：

語法	含義
(?=pattern)	肯定的 lookahead 零寬度斷言檢視字串中的當前位置是否緊跟著出現了 pattern，而 pattern 不會成為匹配字串的一部分。`'A(?=B)'` 匹配後面跟有 B 的 A，但不使 B 成為匹配的一部分。例如，`SELECT REGEXP_SUBSTR( 'in new york city', 'new(?=\syork)');` 會返回子串 new，因為它後面緊跟著 ' york'（請注意 york 前面的空格）。
(?!pattern)	否定的 lookahead 零寬度斷言檢視字串中的當前位置是否沒有緊跟著出現 pattern，而 pattern 不會成為匹配字串的一部分。所以，`'A(?!B)'` 匹配後面未跟著 B 的 A。例如，`SELECT REGEXP_SUBSTR('new jersey', 'new(?!\syork)');` 會返回子串 new。
(?<=pattern)	肯定的 lookbehind 零寬度斷言檢視字串中的當前位置是否前面緊挨著出現了 pattern，而 pattern 不會成為匹配字串的一部分。所以，`'(?<=A)B'` 匹配前面緊挨著 A 的 B，但不使 A 成為匹配的一部分。例如，`SELECT REGEXP_SUBSTR('new york', '(?<=new\s)york');` 會返回子串 york。
(?<!pattern)	否定的 lookbehind 零寬度斷言檢視字串中的當前位置的前面是否沒有緊挨著出現 pattern，而 pattern 不會成為匹配字串的一部分。例如，`SELECT REGEXP_SUBSTR('about york', '(?<!new\s)york');` 會返回子串 york。
(?>pattern)	所屬關係區域性子表示式僅匹配與 pattern 匹配的剩餘字串的最大字首。例如，在 `'aa' REGEXP '(?>a)a'` 中，`(?>a)` 匹配（並消耗）aa，而決不僅僅是前導 a。因此，`'aa' REGEXP '(?>a*)a'` 的計算結果為 false。
(?:pattern)	非捕獲塊該語法在功能上就等效於 pattern，是為實現相容性而提供。例如，在 `'bb' REGEXP '(?:b)b'` 中，`(?:b)` 匹配（並消耗）bb。但是，與所屬關係區域性子表示式不同，bb 中的最後一個 b 會被放棄，以允許整個匹配成功（即，允許與在非捕獲塊的外部找到的 b 匹配）。同樣，`'a(?:bc\|b)c'` 匹配 abcc 和 abc。在匹配 abc 時，bc 中最後面的 c 會發生回溯，以便可以使用組外的 c 來使匹配成功。
(?#text)	用於註釋。text 的內容會被忽略。

語法範例

1、基本字元匹配

例如搜尋city表中 Population 列包含'1000'的所有資訊，並按照 ID 升序排序。

SELECT *
FROM city
WHERE Population REGEXP '1000'
ORDER BY ID;

搜尋city表中 Population 列包含'000'的所有資訊，並按照 ID 升序排序。

SELECT *
FROM city
WHERE Population REGEXP '.000'
ORDER BY ID;

注意：這裡的點（.）表示匹配任意一個字元，例如，1000,2000,3000等都匹配且返回。

2、進行 OR （使用豎線（|）符號表示）匹配（為搜尋多個串之一）

例如搜尋city表中 Population 列包含 '1000' 或 '2000' 的所有資訊，並按照ID升序排序。

SELECT *
FROM city
WHERE Population REGEXP '1000|2000'
ORDER BY ID;

注意：多個 OR 條件可以併入單個正規表示式，例如 '1000|2000|3000' 將匹配1000或2000或3000.

3、匹配幾個字元之一（匹配任何單一字元，使用符號中括號（[ ]））

例如搜尋city表中 Population 列包含 '1000' 或 '2000' 或 '3000'的所有資訊，並按照ID升序排序。

SELECT *
FROM city
WHERE Population REGEXP '[123]000'
ORDER BY ID;

注意：[ ] 是另一種形式的 OR 語句，事實上，[123]000 為 [1|2|3]000 的縮寫（與 '1000|2000|3000'要一個意思）。

例如搜尋city表中 Population 列除 '1000' 或 '2000' 或 '3000'以外的所有資訊，並按照ID升序排序。

SELECT *
FROM city
WHERE Population REGEXP '[^123]000'
ORDER BY ID;

注意：使用 ^ 符號匹配除中括號（[ ]）之外的任意值，並返回。

4、匹配範圍（使用 - 符號）

例如搜尋city表中 Population 列 '1000' - '5000'的所有資訊，並按照ID升序排序。

SELECT *
FROM city
WHERE Population REGEXP '[1-5]000'
ORDER BY ID;
或
SELECT *
FROM city
WHERE Population REGEXP '[12345]000'
ORDER BY ID;

5、匹配特殊字元（使用 \ 符號）

例如搜尋city表中 name 列中包含 ' . ' 字元值的所有資訊。

SELECT *
FROM city
WHERE name REGEXP '\.';

6、匹配多個範例

（1）、例如搜尋city表中name列含有（）、數位0~9、‘Hern’的所有資訊

SELECT *
FROM city
WHERE name REGEXP '\([0-9] Hern?\)';

注意：問號（？）匹配‘Hern’後面出現任何字元的0次或1次。

（2）、例如搜尋city表中name列含有連在一起的4位元數位的所有資訊

SELECT *
FROM city
WHERE name REGEXP '[[:digit:]]{4}';
或者
SELECT *
FROM city
WHERE name REGEXP '[0-9][0-9][0-9][0-9]';

7、定位符

例如搜尋city表中name列開始位置含有連在一起的4位元數位的所有資訊

SELECT *
FROM city
WHERE name REGEXP '^[0-9]{4}';

總結

到此這篇關於MySQL正規表示式REGEXP使用的文章就介紹到這了,更多相關MySQL正規表示式REGEXP內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！