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.'^'的雙重用途：在集合'[]'中用來否定集合，否則，用來指串的開始處