MySQL正則表達式REGEXP使用詳解

更新時間：2022年09月19日 10:55:54 作者：Hern（宋兆恒）

MySQL中正則表達式通常被用來檢索或替換符合某個模式的文本內(nèi)容,根據(jù)指定的匹配模式匹配文中符合要求的特殊字符串,下面這篇文章主要給大家介紹了關于MySQL正則表達式REGEXP使用的相關資料,需要的朋友可以參考下

LIKE 和 REGEXP之間的重要差別

LIKE 匹配整個列，如果被匹配的文本在列值中出現(xiàn)，LIKE 將不會找到它，相應的行也不會被返回（除非使用通配符）。而 REGEXP 在列值內(nèi)進行匹配，如果被匹配的文本在列值中出現(xiàn)，REGEXP 將會找到它，相應的行將被返回，并且 REGEXP 能匹配整個列值（與 LIKE 相同的作用）。

正則表達式匹配不區(qū)分大小寫

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

簡單的正則表達式測試

可以在不使用數(shù)據(jù)庫表的情況下用 SELECT 語句來測試正則表達式，REGEXP 檢查總是返回0（沒有匹配）或1（匹配）?？梢杂脦淖执?REGEXP 來測試表達式，并試驗它們。

例如：檢測 'Hern' 中是否含有數(shù)字

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

結果將返回0（因為 ‘Hern’ 中沒有數(shù)字）

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

用雙反斜杠（\\）來引用元字符（具有特殊含義的字符）。常用引用元字符：

字符	其它信息
[ 和 ]	左右方括號用于指定字符類。字符類是進行匹配時所要依據(jù)的一組字符。除連字符 (-) 和脫字符 (^) 外，在字符類中指定的元字符和量詞（如 * 和 {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 配合使用來匹配任意數(shù)目的字符。不將百分號視為 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。當指定時，它匹配百分號 (%)。
_（下劃線字符）	可將下劃線與 SIMILAR TO 配合使用來匹配單個字符。不將下劃線視為 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。當指定時，它匹配下劃線 (_)。
\|	管道符號用于指定匹配字符串時要使用的替代模式。在由豎線分隔的一行模式中，豎線被解釋為 OR，匹配過程從最左側的模式開始，在找到第一個匹配項時停止。因此，您應按優(yōu)先級的降序順序列出模式。您可以指定任意數(shù)量的替代模式。
( 和 )	當左括號和右括號用于正則表達式的各個組合部分時，它們?yōu)樵址?。例如?code>(ab)* 匹配零個或多個 ab 的重復項。與使用數(shù)學表達式一樣，您使用組合來控制正則表達式各部分的計算順序。
{ 和 }	當左大括號和右大括號用于指定量詞時，它們?yōu)樵址?。量詞指定一個模式要構成某個匹配所必須重復的次數(shù)。例如： {m} 匹配某個字符正好 m 次。例如，`'519-[0-9]{3}-[0-9]{4}'` 匹配 519 地區(qū)號中的一個電話號碼（假定數(shù)據(jù)按語法中定義的方式進行格式設置）。 {m,} 匹配某個字符至少 m 次。例如，`'[0-9]{5,}'` 匹配任何含有五個或更多數(shù)字的字符串。 {m,n} 匹配某個字符至少 m 次，但不超過 n 次。例如，`SIMILAR TO '_{5,10}'` 匹配任何含有 5 到 10（含 5 和 10）個字符的字符串。
\	反斜線被用作元字符的轉義字符。它也可被用于轉義非元字符。
^	匹配輸入字符串的開始位置。如果設置了 RegExp 對象的 Multiline 屬性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配輸入字符串的結束位置。如果設置了RegExp 對象的 Multiline 屬性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何單個字符。要匹配包括 '\n' 在內(nèi)的任何字符，請使用象 '[.\n]' 的模式。
:	在字符集中使用冒號來指定子字符類。例如，`'[[:alnum:]]'`。

注意：

1、多數(shù)正則表達式實現(xiàn)使用單個反斜杠轉義特殊字符，一遍能使用這些字符本身，但是MySQL要求兩個反斜杠（MySQL自己解釋一個，正則表達式庫解釋另一個）。

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

匹配字符類

子字符類	其它信息
[:alpha:]	匹配當前歸類中的大寫和小寫字母字符。例如，'[0-9]{3}[[:alpha:]]{2}' 匹配三個數(shù)字，后跟兩個字母。
[:alnum:]	匹配當前歸類中的數(shù)字、大寫和小寫字母字符。例如，'[[:alnum:]]+' 匹配含有一個或多個字母和數(shù)字的字符串。
[:digit:]	匹配當前歸類中的數(shù)字。例如，'[[:digit:]-]+' 匹配含有一個或多個數(shù)字或橫線的字符串。同樣，'[^[:digit:]-]+' 匹配含有一個或多個不是數(shù)字或橫線的字符的字符串。
[: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:]	匹配一個空白區(qū)或水平制表符。 `[[:blank:]]` 等效于 `[ \t]`。
[:cntrl:]	匹配順序值小于 32 或字符值為 127 的 ASCII 字符（控制字符）?？刂谱址〒Q行符、換頁符、退格符，等等。
[:graph:]	匹配打印字符。 `[[:graph:]]` 等效于 `[[:alnum:][:punct:]]`。
[:print:]	匹配打印字符和空格。 `[[:print:]]` 等效于 `[[:graph:][:whitespace:]]`。
[:punct:]	匹配其中一個字符： !"#$%&'()*+,-./:;<=>?@[\]^_`{\|}~. `[:punct:]` 子字符類不能包括當前歸類中可用的非 ASCII 標點字符。
[:word:]	匹配當前歸類中的字母、數(shù)字或下劃線字符。 `[[:word:]]` 等效于 `[[:alnum:]_]`。
[:xdigit:]	匹配字符類 [0-9A-Fa-f] 中的字符。

正則表達式支持的其它語法約定

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

正則表達式斷言

斷言測試條件是否為真，并影響字符串中開始匹配的位置。斷言不返回字符；最終匹配中不包括斷言模式。REGEXP 搜索條件和REGEXP_SUBSTR 函數(shù)支持斷言模式。

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

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

另一個示例：假定您想要使用正則表達式來驗證口令是否符合某些規(guī)則?？梢允褂妙愃朴谙旅鎯?nèi)容的零寬度斷言：

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 至少有一位數(shù)（零寬度肯定斷言 [[:digit:]]）當滿足以下條件時，口令有效：

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

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

語法	含義
(?=pattern)	肯定的 lookahead 零寬度斷言查看字符串中的當前位置是否緊跟著出現(xiàn)了 pattern，而 pattern 不會成為匹配字符串的一部分。`'A(?=B)'` 匹配后面跟有 B 的 A，但不使 B 成為匹配的一部分。例如，`SELECT REGEXP_SUBSTR( 'in new york city', 'new(?=\\syork)');` 會返回子串 new，因為它后面緊跟著 ' york'（請注意 york 前面的空格）。
(?!pattern)	否定的 lookahead 零寬度斷言查看字符串中的當前位置是否沒有緊跟著出現(xiàn) pattern，而 pattern 不會成為匹配字符串的一部分。所以，`'A(?!B)'` 匹配后面未跟著 B 的 A。例如，`SELECT REGEXP_SUBSTR('new jersey', 'new(?!\\syork)');` 會返回子串 new。
(?<=pattern)	肯定的 lookbehind 零寬度斷言查看字符串中的當前位置是否前面緊挨著出現(xiàn)了 pattern，而 pattern 不會成為匹配字符串的一部分。所以，`'(?<=A)B'` 匹配前面緊挨著 A 的 B，但不使 A 成為匹配的一部分。例如，`SELECT REGEXP_SUBSTR('new york', '(?<=new\\s)york');` 會返回子串 york。
(?<!pattern)	否定的 lookbehind 零寬度斷言查看字符串中的當前位置的前面是否沒有緊挨著出現(xiàn) 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，是為實現(xiàn)兼容性而提供。例如，在 `'bb' REGEXP '(?:b)b'` 中，`(?:b)` 匹配（并消耗）bb。但是，與所屬關系局部子表達式不同，bb 中的最后一個 b 會被放棄，以允許整個匹配成功（即，允許與在非捕獲塊的外部找到的 b 匹配）。同樣，`'a(?:bc\|b)c'` 匹配 abcc 和 abc。在匹配 abc 時，bc 中最后面的 c 會發(fā)生回溯，以便可以使用組外的 c 來使匹配成功。
(?#text)	用于注釋。text 的內(nèi)容會被忽略。

語法實例

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列含有（）、數(shù)字0~9、‘Hern’的所有信息

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

注意：問號（？）匹配‘Hern’后面出現(xiàn)任何字符的0次或1次。

（2）、例如搜索city表中name列含有連在一起的4位數(shù)字的所有信息

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位數(shù)字的所有信息

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

總結

到此這篇關于MySQL正則表達式REGEXP使用的文章就介紹到這了,更多相關MySQL正則表達式REGEXP內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MYSQL事務教程之Yii2.0商戶提現(xiàn)功能
這篇文章主要給大家介紹了關于MYSQL事務教程之Yii2.0商戶提現(xiàn)功能的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
2019-07-07
將MySQL的臨時目錄建立在內(nèi)存中的教程
這篇文章主要介紹了將MySQL的臨時目錄建立在內(nèi)存中的教程,以獲得不關機情況下的高性能使用,需要的朋友可以參考下
2015-04-04
MySQL load語句詳細介紹
這篇文章主要介紹了MySQL load語句詳細介紹,本文講解了load的基本語法、文件的路徑、配置選項、STARTING LINES選項、TERMINATED LINES選項等內(nèi)容,需要的朋友可以參考下
2014-12-12
MySql 修改密碼后的錯誤快速解決方法
今天在MySql5.6操作時報錯：You must SET PASSWORD before executing this statement解決方法，需要的朋友可以參考下
2016-11-11
基于explain性能詳細分析
這篇文章主要介紹了基于explain性能詳細分析,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2023-12-12
MySQL常用登錄命令小結
本文主要介紹了MySQL常用登錄命令小結，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-05-05
Windows中Mysql啟動失敗的完美解決方案
這篇文章主要介紹了Windows中Mysql啟動失敗解決方案,mysql服務啟動失敗分為2種情況給大家詳細介紹，針對每一種給大家詳細解決，需要的朋友可以參考下
2022-10-10
MySQL 處理插入過程中的主鍵唯一鍵重復值的解決方法
本篇文章主要介紹在插入數(shù)據(jù)到表中遇到鍵重復避免插入重復值的處理方法，主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE的相關知識，感興趣的朋友一起學習吧
2016-04-04
MySQL存儲表情符號小技巧
這篇文章主要為大家介紹了MySQL如何存儲表情符號的小技巧,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪
2024-01-01
mysql部分替換sql語句分享
有時候需要對mysql中的內(nèi)容進行部分替換，那么可以參考下面的文章。
2011-11-11