技術文章 » MySQL REGEXP：正規表示式查詢

MySQL REGEXP：正規表示式查詢

2020-07-16 10:05:22
MySQL中正式表示式通常被用來檢索或替換符合某個模式的文字內容，根據指定的匹配模式匹配文中符合要求的特殊字串。

例如，從一個檔案中提取電話號碼，查詢一篇文章中重複的單詞或替換使用者輸入的敏感語彙等，這些地方都可以使用正規表示式。正規表示式強大而且靈活，常用於複雜的查詢。

MySQL 中使用 REGEXP 關鍵字指定正規表示式的字元匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表。

選項 說明 例子 匹配值範例
^ 匹配文字的開始字元 '^b' 匹配以字母 b 開頭 的字串 book、big、banana、 bike
$ 匹配文字的結束字元 'st$’ 匹配以 st 結尾的字 符串 test、resist、persist
. 匹配任何單個字元 'b.t’ 匹配任何 b 和 t 之間有一個字元 bit、bat、but、bite
* 匹配零個或多個在它前面的字 符 'f*n’ 匹配字元 n 前面有 任意個字元 f fn、fan、faan、abcn
+ 匹配前面的字元 1 次或多次 'ba+’ 匹配以 b 開頭，後 面至少緊跟一個 a ba、bay、bare、battle
<字串> 匹配包含指定字元的文字 'fa’ fan、afa、faad
[字元集合] 匹配字元集合中的任何一個字 符 '[xz]'匹配 x 或者 z dizzy、zebra、x-ray、 extra
[^] 匹配不在括號中的任何字元 '[^abc]’ 匹配任何不包 含 a、b 或 c 的字串 desk、fox、f8ke
字串{n,} 匹配前面的字串至少 n 次 b{2} 匹配 2 個或更多 的 b bbb、 bbbb、 bbbbbbb
字串
{n,m}		 匹配前面的字串至少 n 次， 至多 m 次 b{2,4} 匹配最少 2 個， 最多 4 個 b bbb、 bbbb

查詢以特定字元或字串開頭的記錄

字元“^”匹配以特定字元或者字串開頭的文字。

【範例 1】在 tb_departments 表中，查詢 dept_name 欄位以字母“C”開頭的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^C';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.05 sec)
在 tb_departments 表中有兩條記錄的 dept_name 欄位值是以字母 C 開頭的，返回結果有 2 條記錄。

【範例 2】在 tb_departments 表中，查詢 dept_name 欄位以“Ch”開頭的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.03 sec)
只有 Chinese 是以“Ch”開頭的，所以查詢結果中只有 1 條記錄。

查詢以特定字元或字串結尾的記錄

字元“$”匹配以特定字元或者字串結尾的文字。

【範例 3】在 tb_departments 表中，查詢 dept_name 欄位以字母“y”結尾的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'y$';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
在 tb_departments 表中有兩條記錄的 dept_name 欄位值是以字母 y 結尾的，返回結果有 2 條記錄。

【範例 4】在 tb_departments 表中，查詢 dept_name 欄位以“my”結尾的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'my$';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)
只有 Economy 是以“my”結尾的，所以查詢結果中只有 1 條記錄。

用符號“.”代替字串中的任意一個字元

【範例 5】在 tb_departments 表中，查詢 dept_name 欄位值包含字母“o”與字母“y”，且兩個字母之間只有一個字母的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'o.y';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
查詢語句中“o.y”指定匹配字元中要有字母 o 和 y，且兩個字母之間包含單個字元，並不限定匹配的字元的位置和所在查詢字串的總長度，因此 Economy 和 History 都符合匹配條件。

使用“*”和“+”來匹配多個字元

星號“*”匹配前面的字元任意多次，包括 0 次。加號“+”匹配前面的字元至少一次。

【範例 6】在 tb_departments 表中，查詢 dept_name 欄位值包含字母“C”，且“C”後面出現字母“h”的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch*';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
星號“*”可以匹配任意多個字元，Computer 中字母 C 後面並沒有出現字母 h，但是也滿足匹配條件。

【範例 7】在 tb_departments 表中，查詢 dept_name 欄位值包含字母“C”，且“C”後面出現字母“h”至少一次的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch+';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)
“h+”匹配字母“h”至少一次，只有 Chinese 滿足匹配條件。

匹配指定字串

正規表示式可以匹配指定字串，只要這個字串在查詢文字中即可，若要匹配多個字串，則多個字串之間使用分隔符“|”隔開。

【範例 8】在 tb_departments 表中，查詢 dept_name 欄位值包含字串“in”的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'in';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)
可以看到，dept_name 欄位的 Chinese 中包含字串“in”，滿足匹配條件。

【範例 9】在 tb_departments 表中，查詢 dept_name 欄位值包含字串“in”或者“on”的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'in|on';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
|       4 | Economy   | 44444     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
可以看到，dept_name 欄位的 Chinese 中包含字串“in”，Economy 中包含字串“on”，滿足匹配條件。

提示：LIKE 運算子也可以匹配指定的字串，但與 REGEXP 不同，LIKE 匹配的字串如果在文字中間出現，就找不到它，相應的行也不會返回。而 REGEXP 在文字內進行匹配，如果被匹配的字串在文字中出現，REGEXP 將會找到它，相應的行也會被返回。

匹配指定字串中的任意一個

方括號“[]”指定一個字元集合，只匹配其中任何一個字元，即為所查詢的文字。

【範例 10】在 tb_departments 表中，查詢 dept_name 欄位值包含字母“o”或者“e”的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '[io]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
4 rows in set (0.00 sec)
從查詢結果可以看到，所有返回的記錄的 dept_name 欄位的值中都包含字母 o 或者 e，或者兩個都有。

方括號“[]”還可以指定數值集合。

【範例 11】在 tb_departments 表中，查詢 dept_call 欄位值中包含 1、2 或者 3 的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_call REGEXP '[123]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       2 | Math      | 22222     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
查詢結果中，dept_call 欄位值中有 1、2、3 三個數位中的一個即為匹配記錄欄位。

匹配集合“[123]”也可以寫成“[1-3]”，即指定集合區間。例如，“[a-z]”表示集合區間為a~z的字母，“[0-9]”表示集合區間為所有數位。

匹配指定字元以外的字元

“[^字元集合]”匹配不在指定集合中的任何字元。

【範例 12】在 tb_departments 表中，查詢 dept_name 欄位值包含字母 a~t 以外的字元的記錄，輸入的 SQL 語句和執行結果如下所示。 
mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '[^a-t]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
返回記錄中的 dept_name 欄位值中包含了指定字母和數位以外的值，如 u、y 等，這些字母均不在 a～t 中，滿足匹配條件。