MySQL學習足跡記錄07–數據過濾–用正則表達式進行檢索
本文用到的檢索數據
mysql> SELECT prod_name FROM products -> ORDER BY prod_name; +----------------+ | prod_name | +----------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | | Bird seed | | Carrots | | Detonator | | Fuses | | JetPack 1000 | | JetPack 2000 | | Oil can | | Safe | | Sling | | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 14 rows in set (0.00 sec)
1.基本字符匹配
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '1000' #匹配"1000" -> ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | JetPack 1000 | +--------------+ 1 row in set (0.00 sec) eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '.000' #'.'表示匹配任意一個字符 -> ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec)
2.進行OR匹配
為瞭搜尋N個串之一,使用 ‘|’
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '1000 | 2000' -> ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec)
3.匹配幾個字符之一
*匹配特定的單字符,可以通過指定一組【】括起來的字符來完成
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[123] Ton' -> ORDER BY prod_name; +-------------+ | prod_name | +-------------+ | 1 ton anvil | | 2 ton anvil | +-------------+ 2 rows in set (0.00 sec) 等效於: mysql> 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 | +-------------+ 2 rows in set (0.00 sec)
4.否定一個字符集‘^'
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[^123] Ton' -> ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | .5 ton anvil | +--------------+ 1 row in set (0.00 sec)
5.匹配范圍【n-m】
eg: mysql> 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 | +--------------+ 3 rows in set (0.00 sec)
註:以下操作所用到的表格數據
mysql> SELECT vend_name FROM vendors ORDER BY vend_name; +----------------+ | vend_name | +----------------+ | ACME | | Anvils R Us | | Furball Inc. | | Jet Set | | Jouets Et Ours | | LT Supplies | +----------------+ 6 rows in set (0.00 sec)
6.匹配特殊字符,需用\\為前導,即轉義字符
*MySQL要求兩個反斜杠(MySQL自己解釋一個,正則表達式庫解釋另一個)
匹配'.'
eg: mysql> 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 | +----------------+ 6 rows in set (0.00 sec) 正確的應為: mysql> SELECT vend_name FROM vendors -> WHERE vend_name REGEXP '\\.' -> ORDER BY vend_name; +--------------+ | vend_name | +--------------+ | Furball Inc. | +--------------+ 1 row in set (0.00 sec)
#以下7,8,9,10列出的僅作參考,無需記憶
7.匹配字符類
[:alnum:] ==> [a-zA-Z0-9]
[:alpha:] ==> [a-zA-Z]
[:blank:] ==>空格和制表符[\\t]
[:cntrl:] ==>ASCII控制字符(ASCII0到31和127)
[:digit:] ==>[0-9]
[:graph:] ==>與[:print:]相同,但不包括空格
[:lower:] ==>[a-z]
[:punct:] ==>即不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] ==>包括空格在內的任意空白字符[\\f\\n\\r\\t\\v]
[:upper:] ==>[A-Z]
[:xdigit:]==>任意十六進制數[a-fA-F0-9]
[:print:] ==>任意可打印字符
8.空白元字符
\\f ==>換頁
\\r ==>回車
\\v ==>縱向制表
9.匹配多個實例
重復元字符
* ==> 0個或多個匹配
+ ==> 1個或多個匹配(等於{1,})
? ==> 0個或1個匹配(等於{0,1})
{n} ==> 指定數目的匹配
{n,} ==> 不小於指定數目的匹配
{n,m} ==> 匹配數目的范圍(m<=255)
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '\\([0-9] sticks?\\) ' #'?'匹配它前面的任何字符的0次或1次出現 -> ORDER BY prod_name; +----------------+ | prod_name | +----------------+ | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 2 rows in set (0.00 sec) mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '[[:digit:]]{4}' #匹配連在一起的任意四位數字 -> ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec)
10.定位符
*定位元字符
^ ==> 文本的開始
$ ==> 文本的結尾
[[:<:]] ==> 詞的開始
[[:>:]] ==> 詞的結尾
eg: mysql> SELECT prod_name FROM products -> WHERE prod_name REGEXP '^[0-9\\.]' #'^'定位到串開頭,[0-9\\.]表示隻有在'.'或任一數字為 -> ORDER BY prod_name; #串中的第一個字符,才匹配它 +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.00 sec)
11.'^'的雙重用途:在集合'[]'中用來否定集合,否則,用來指串的開始處