用正規表達式進行搜尋
使用MySQL正規表達式
基本字元比對
下面的語句檢索列prod_name包含文本1000的所有行:
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
+--------------+
分析
出關鍵字LIKE被REGEXP替代外,這條語句看上去非常像LIKE的語句。它告訴MySQL:REGEXP後所跟的東西作為正規表達式(與文字正文比對1000比對的一個正規表達式)處理。
在上面的例子中,正規表達式确實沒有帶來太多好處(可能還會降低性能),不過,請考慮下面的例子:
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
分析
這裡使用了正規表達式**.000。**
.
是正規表達式語言中一個特殊的字元。它表示比對任意一個字元。
當然,這個特殊的例子也可以用LIKE和通配符來完成。
比對不區分大小寫 MySQL中的正規表達式比對(自版本3.23.4後)不區分大小寫(即,大寫和小寫都比對)。為區分大小寫,可使用BINARY關鍵字,如WHERE prod_name REGEXP BINARY ‘JetPack .000’。
進行OR比對
為搜尋兩個串之一(或者為這個串,或者為另一個串),使用
|
,如下所示:
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
分析
語句中使用了正規表達式1000|2000。
|
為正規表達式的OR操作符。它表示比對其中之一。
兩個以上的OR條件 可以給出兩個以上的OR條件。例如,'1000|2000|3000’将比對1000或2000或3000。
比對幾個字元之一
比對任何單一字元。可通過指定一組用
[
和
]
括起來的字元來完成,如下所示:
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
輸出
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
分析
[123]定義一組字元,它的意思是比對1或2或3。
正如所見,[]是另一種形式的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;
輸出
+---------------+
| prod_name |
+---------------+
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
+---------------+
分析
這裡并不是期望的輸出。兩個要求的行被檢索出來,但還檢索出了另外3行。之是以這樣是由于MySQL假定你的意思是’1’或’2’或’3 Ton’。除非把字元
|
括在一個集合中,否則它将應用于整個串。
字元集也可以被否定,即,它們将比對除指定字元外的任何東西。為否定一個字元集,在集合的開始處放置一個
^
即可。是以,盡管[123]比對字元1、2或3,但
[^123]
卻比對除這些字元外的任何東西。
範圍比對
集合可用來定義要比對的一個或多個字元。例如,下面的集合将比對數字0到9:
[0123456789]
為簡化這種類型的集合,可使用
-
來定義一個範圍。下面的式子功能上等同于上述數字清單:
[0-9]
範圍不限于完整的集合。此外,範圍不一定隻是數值的,[a-z]比對任意字母字元。
舉一個例子:
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
分析
這裡使用正規表達式[1-5] Ton。[1-5]定義了一個範圍,這個表達式意思是比對1-5,是以傳回3個比對行。由于5 ton比對,是以傳回. 5 ton。
比對特殊字元
輸入
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '.'
ORDER BY vend_name;
輸出
+----------------+
| vend_name |
+----------------+
| ACME |
| Anvils R Us |
| Furball Inc. |
| Jet Set |
| Jouets Et Ours |
| LT Supplies |
+----------------+
分析
這并不是期望的輸出,
.
比對任意字元,是以每個行都被檢索出來。
為了比對特殊字元,必須用
\\
為前導。
\\-
表示查找
-
,
\\.
表示查找
.
。
輸入
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
輸出
+--------------+
| vend_name |
+--------------+
| Furball Inc. |
+--------------+
分析
這才是期望的輸出。
\\.
比對
.
,是以隻檢索出一行。這種處理就是所謂的轉義(escaping),正規表達式内具有特殊意義的所有字元都必須以這種方式轉義。
\\
也用來引用元字元(具有特殊含義的字元),如下表所列。
元字元 | 說明 |
---|---|
\\f | 換頁 |
\\n | 換行 |
\\r | 回車 |
\\t | 制表 |
\\v | 縱向制表 |
比對\ 為了比對反斜杠字元本身,需要使用
\
。
\\\
\或\\? 多數正規表達式實作使用單個反斜杠轉義特殊字元,以便能使用這些字元本身。但MySQL要求兩個反斜杠(MySQL自己解釋一個,正規表達式庫解釋另一個)。
比對字元類
為更友善工作,可以使用預定義的字元集,稱為字元類(character class)。下表列出字元類以及它們的含義。
類 | 說明 |
---|---|
[:alnum:] | 任意字母和數字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字元(同[a-zA-Z]) |
[:blank:] | 空格和制表符(同[\t]) |
[:cntrl:] | ASCII控制字元(ASCII 0到31和127) |
[:digit:] | 任意數字(同[0-9]) |
[:graph:] | 與[:print:]相同,但不包括空格 |
[:lower:] | 任意小寫字母(同[a-z]) |
[:print:] | 任意可列印字元 |
[:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字元 |
[:space:] | 包括空格在内的任意空白字元(同[\\f\\n\\r\\t\\v]) |
[:unnper:] | 任意大寫字母(同[A-Z]) |
[:xdigit:] | 任意十六進制數字(同[a-fA-F0-9]) |
比對多個執行個體
表重複元字元
元字元 | 說明 |
---|---|
* | 0個或多個比對 |
+ | 1個或多個比對(等于 {1,}) |
? | 0個或1個比對(等于{0,1}) |
{n} | 指定數目的比對 |
{n, } | 不少于指定數目的比對 |
{n, m} | 比對數目的範圍(m不超過255) |
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
輸出
+----------------+
| prod_name |
+----------------+
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
分析
\\(
比對
(
,[0-9]比對任意數字,sticks?比對stick和sticks(s後的?使s可選,因為?比對它前面的任何字元的0次或1次出現),
\\)
比對
)
。
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
分析
[: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;
定位符
為了比對特殊位置的文本,需要使用下表列出的定位符。
元字元 | 說明 |
---|---|
^ | 文本的開始 |
$ | 文本的結尾 |
[[:<:]] | 詞的開始 |
[[:>:]] | 詞的結尾 |
輸入
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
輸出
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
分析
^
比對串的開始。是以,
^[0-9\\.]
隻在
.
或任意數字為串中第一個字元時才比對它們。
^的雙重用途 ^有兩種用法。在集合中(用[和]定義),用它來否定該集合,否則,用來指串的開始處。
使REGEXP起類似LIKE的作用 LIKE和REGEXP不同在于,LIKE比對整個串而REGEXP比對子串。利用定位符,通過用^開始每個表達式,用$結束每個表達式,可以使REGEXP的作用與LIKE一樣。
簡單的正規表達式測試 可以在不使用資料庫表的情況下用SELECT來測試正規表達式。REGEXP檢查總是傳回0(沒有比對)或1(比對),可以用帶文字串的REGEXP來測試表達式,并試驗它們,相應的文法如下:這個例子顯然将傳回0。SELECT 'hello' REGEXP '[0-9]';