使用MySQL之用正規表示式進行搜尋

hisun9發表於2024-10-20
MySql

1. 正規表示式介紹

正規表示式是用來匹配文字的特殊的串（字符集合）。

如果你想從一個文字檔案中提取電話號碼，可以使用正規表示式。

如果你需要查詢名字中間有數字的所有檔案，可以使用一個正規表示式。

如果你想在一個文字塊中找到所有重複的單詞，可以使用一個正規表示式。

如果你想替換一個頁面中的所有URL為這些URL的實際HTML連結，也可以使用一個正規表示式（對於最後這個例子，或者是兩個正規表示式）。

與任意語言一樣，正規表示式具有你必須學習的特殊的語法和指令。

補充：

學習更多內容：

完全覆蓋正規表示式的內容超出了本書的範圍。雖然基礎知識都在這裡做了介紹，但對正規表示式更為透徹的介紹可能還需要參閱《正規表示式必知必會》這本書。

2. 使用MySQL正規表示式

正規表示式的作用是匹配文字，將一個模式（正規表示式）與一個文字串進行比較。

MySQL用WHERE子句對正規表示式提供了初步的支援，允許你指定正規表示式，過濾SELECT檢索出的資料。

補充：

僅為正規表示式語言的一個子集：

如果你熟悉正規表示式，需要注意：MySQL僅支援多數正規表示式實現的一個很小的子集。

2.1 基本字元匹配

下面的語句檢索列prod_name包含文字1000的所有行：

select prod_name from products
where prod_name regexp '1000'
order by prod_name;

輸出如下：

img

除關鍵字LIKE被REGEXP替代外，這條語句看上去非常像使用LIKE的語句。它告訴MySQL：REGEXP後所跟的東西作為正規表示式（與文字正文1000匹配的一個正規表示式）處理。

為什麼要費力地使用正規表示式？在剛才的例子中，正規表示式確實沒有帶來太多好處（可能還會降低效能），不過，請考慮下面的例子：

select prod_name from products
where prod_name regexp '.000'
order by prod_name;

輸出如下：

img

這裡使用了正規表示式 .000 。 . 是正規表示式語言中一個特殊的字元。它表示匹配任意一個字元，因此，1000和2000都匹配且返回。

當然，這個特殊的例子也可以用LIKE和萬用字元來完成。

select prod_name from products
where prod_name like '%000'
order by prod_name;

輸出如下：

img

補充：

  • LIKE 與 REGEXP：

    在LIKE和REGEXP之間有一個重要的差別。請看以下兩條語句：

    select prod_name from products
where prod_name like '1000'
order by prod_name;

    select prod_name from products
where prod_name regexp '1000'
order by prod_name;

    如果執行上述兩條語句，會發現第一條語句不返回資料，而第二條語句返回一行。為什麼？

    • LIKE匹配整個列。如果被匹配的文字在列值中出現，LIKE將不會找到它，相應的行也不被返回（除非使用萬用字元）。

    • 而REGEXP在列值內進行匹配，如果被匹配的文字在列值中出現，REGEXP將會找到它，相應的行將被返回。這是一個非常重要的差別。

    • 那麼，REGEXP能不能用來匹配整個列值（從而起與LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可，後面會介紹。

  • 匹配不區分大小寫：

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

2.2 進行OR匹配

為搜尋兩個串之一（或者為這個串，或者為另一個串），使用 | 。

比如：

select prod_name from products 
where prod_name regexp '1000|2000'
order by prod_name;

輸出如下：

img

語句中使用了正規表示式 1000|2000 。|為正規表示式的OR運算子。它表示匹配其中之一，因此1000和2000都匹配並返回。

插一句題外話：

當我把程式碼寫成這樣：

select prod_name from products 
where prod_name regexp '1000 | 2000'
order by prod_name;

即在 | 的兩邊各加一個空格時，輸出結果是這樣的：

img

只檢索出了一條資料，說明正規表示式對空格是敏感的。

迴歸正題

使用 | 從功能上類似於在SELECT語句中使用OR語句，多個OR條件可併入單個正規表示式。

補充：

兩個以上的OR條件：

可以給出兩個以上的OR條件。例如，'1000 | 2000 | 3000' 將匹配1000或2000或3000。

2.3 匹配幾個字元之一

匹配任何單一字元。但是，如果你只想匹配特定的字元，怎麼辦？

可透過指定一組用 [ 和 ] 括起來的字元來完成。

比如：

select prod_name from products 
where prod_name regexp '[123] Ton'
order by prod_name;

輸出如下：

img

這裡，使用了正規表示式[123] Ton。[123]定義一組字元，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（沒有3 ton）。

正如所見， [] 是另一種形式的OR語句。

事實上，正規表示式 [123] Ton 為 [1|2|3] Ton 的縮寫，也可以使用後者。但是，需要用[]來定義OR語句查詢什麼。

為更好地理解這一點，請看下面的例子：

select prod_name from products
where prod_name regexp '1|2|3 Ton'
order by prod_name;

輸出如下：

img

這並不是期望的輸出。兩個要求的行被檢索出來，但還檢索出了另外3行。之所以這樣是由於MySQL假定你的意思是'1'或'2'或'3 ton'。除非把字元|括在一個集合中，否則它將應用於整個串。

字符集合也可以被否定，即，它們將匹配除指定字元外的任何東西。

為否定一個字符集，在集合的開始處放置一個 ^ 即可。因此，儘管[123]匹配字元1、2或3，但[^123]卻匹配除這些字元外的任何東西。

2.4 匹配範圍

集合可用來定義要匹配的一個或多個字元。

例如，下面的集合將匹配數字0到9：

[0123456789]

為簡化這種型別的集合，可使用 - 來定義一個範圍。下面的式子功能上等同於上述數字列表：

[0-9]

範圍不限於完整的集合，[1-3] 和 [6-9] 也是合法的範圍。此外，範圍不一定只是數值的，[a-z] 匹配任意字母字元。

比如：

select prod_name from products
where prod_name regexp '[1-5] Ton'
order by prod_name;

輸出如下：

img

這裡使用正規表示式 [1-5] Ton。[1-5] 定義了一個範圍，這個表示式意思是匹配1到5，因此返回3個匹配行。由於5 ton匹配，所以返回.5 ton。

2.5 匹配特殊字元

正規表示式語言由具有特定含義的特殊字元構成。我們已經看到 . 、[] 、| 和 - 等，還有其他一些字元。

請問，如果你需要匹配這些字元，應該怎麼辦呢？例如，如果要找出包含.字元的值，怎樣搜尋？請看下面的例子：

select vend_id, vend_name from vendors
where vend_name regexp '.'
order by vend_name;

輸出如下：

img

這並不是期望的輸出，. 匹配任意字元，因此每個行都被檢索出來。

為了匹配特殊字元，必須用 \\ 為前導。\\- 表示查詢 -，\\.表示查詢 . 。

比如：

select vend_name from vendors
where vend_name regexp '\\.'
order by vend_name;

輸出如下：

img

這才是期望的輸出。\\. 匹配 .，所以只檢索出一行。這種處理就是所謂的轉義（escaping），正規表示式內具有特殊意義的所有字元都必須以這種方式轉義。這包括 .、|、[]以及迄今為止使用過的其他特殊字元。

\\也用來引用元字元（具有特殊含義的字元），如表9-1所列。

img

補充：

  • 匹配 \：

    為了匹配反斜槓（\）字元本身，需要使用\\\。

  • \或 \\?

    多數正規表示式實現使用單個反斜槓轉義特殊字元，以便能使用這些字元本身。但MySQL要求兩個反斜槓（MySQL自己解釋一個，正規表示式庫解釋另一個），具體來說，MySQL會將第一個反斜槓視為轉義符，處理後會留下一個反斜槓，然後再傳遞給正規表示式庫進行處理。

    例如，如果你想在MySQL中匹配一個點（.）字元，你需要使用\\.。這是因為MySQL會首先將\\解釋為一個反斜槓，然後將\.傳遞給正規表示式庫，該庫會將其解釋為一個點字元的字面意義。

2.6 匹配字元類

存在找出你自己經常使用的數字、所有字母字元或所有數字字母字元等的匹配。為更方便工作，可以使用預定義的字符集，稱為字元類（character class）。表9-2列出字元類以及它們的含義。

img

補充：

在MySQL中，正規表示式語法要求字元類必須用雙括號包裹起來。

  1. 匹配任何字母或數字（[:alnum:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:alnum:]]';

    這個查詢會匹配 your_column 中包含至少一個字母或數字的行。

  2. 匹配所有字母字元（[:alpha:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:alpha:]]';

    這個查詢會選出 your_column 中包含至少一個字母（無論大小寫）的行。

  3. 匹配所有空格或製表符（[:blank:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:blank:]]';

    這個查詢會選出包含空格或製表符的行。

  4. 匹配所有控制字元（[:cntrl:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:cntrl:]]';

    這個查詢會匹配包含ASCII控制字元的行（ASCII 0到31和127之間的字元）

  5. 匹配所有數字（[:digit:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:digit:]]';

    這個查詢會選出 your_column 中包含數字的行，效果與 [0-9] 相同。

  6. 匹配所有小寫字母（[:lower:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:lower:]]';

    這個查詢會選出 your_column 中包含至少一個小寫字母的行。

  7. 匹配所有大寫字母（[:upper:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:upper:]]';

    這個查詢會選出 your_column 中包含至少一個大寫字母的行。

  8. 匹配所有可列印字元（[:print:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:print:]]';

    這個查詢會選出包含可列印字元的行，包括空格。

  9. 匹配所有標點符號（[:punct:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:punct:]]';

    這個查詢會選出包含標點符號的行，比如 !、.、? 等。

  10. 匹配所有十六進位制數字（[:xdigit:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:xdigit:]]';

    這個查詢會選出 your_column 中包含十六進位制數字的行（即0-9和a-f/A-F）。

2.7 匹配多個例項

目前為止使用的所有正規表示式都試圖匹配單次出現。如果存在一個匹配，該行被檢索出來，如果不存在，檢索不出任何行。但有時需要對匹配的數目進行更強的控制。例如，你可能需要尋找所有的數，不管數中包含多少數字，或者你可能想尋找一個單詞並且還能夠適應一個尾隨的s（如果存在），等等。

這可以用表9-3列出的正規表示式重複元字元來完成。

img

舉幾個例子來進行說明：

  • 第一個例子

    select prod_name from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;

    輸出如下：

    img

    正規表示式 \\([0-9] sticks?\\) 需要解說一下。

    \\( 匹配 (，[0-9]匹配任意數字（這個例子中為1和5），sticks?匹配stick和sticks（s後的?使s可選，因為 ? 匹配它前面的任何字元的0次或1次出現），\\) 匹配 )。沒有?，匹配stick和sticks會非常困難。

    注意：在正規表示式中，括號 () 是特殊字元，通常用於分組（即捕獲組）。如果你想匹配字面上的括號，而不是使用它們的分組功能，則需要對它們進行轉義。

  • 另一個例子，這次打算匹配連在一起的4位數字：

    select prod_name from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;

    輸出如下：

    img

    如前所述，[:digit:] 匹配任意數字，因而它為數字的一個集合。{4}確切地要求它前面的字元（任意數字）出現4次，所以[[:digit:]]{4} 匹配連在一起的任意4位數字。

  • 需要注意的是，在使用正規表示式時，編寫某個特殊的表示式幾乎總是有不止一種方法。上面的例子也可以如下編寫：

    select prod_name from products
where prod_name regexp '[0-9][0-9][0-9][0-9]'
order by prod_name;

    輸出如下：

    img

2.8 定位符

目前為止的所有例子都是匹配一個串中任意位置的文字。為了匹配特定位置的文字，需要使用表9-4列出的定位符。

img

例如，如果你想找出以一個數（包括以小數點開始的數）開始的所有產品，怎麼辦？簡單搜尋 [0-9\\.]（或 [[:digit:]\\.] ）不行，因為它將在文字內任意位置查詢匹配。解決辦法是使用 ^ 定位符，如下所示：

select prod_name from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;

輸出如下：

img

^ 匹配串的開始。因此，^[0-9\\.] 只在.或任意數字為串中第一個字元時才匹配它們。沒有 ^，則還要多檢索出4個別的行（那些中間有數字的行）。

補充：

  • ^的雙重用途：

    ^ 有兩種用法。在集合中（用[ 和 ] 定義），用它來否定該集合，否則，用來指串的開始處。

  • 使REGEXP起類似LIKE的作用：

    前面說過，LIKE和REGEXP的不同在於，LIKE匹配整個串而REGEXP匹配子串。利用定位符，透過用^開始每個表示式，用$結束每個表示式，可以使REGEXP的作用與LIKE一樣。

  • 簡單的正規表示式測試：

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

    select 'hello' regexp '[0-9]';

    輸出如下：

    img

    這個例子顯然將返回0（因為文字hello中沒有數字）。

相關文章