MySQL 中，使用?REGEXP?關(guān)鍵字指定正則表達(dá)式的字符匹配模式，其基本語法格式如下：
屬性名 REGEXP '匹配方式'
其中，“屬性名”表示需要查詢的字段名稱；“匹配方式”表示以哪種方式來匹配查詢。“匹配方式”中有很多的模式匹配字符，它們分別表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。
選項(xiàng)說明例子匹配值示例
^匹配文本的開始字符'^b' 匹配以字母 b 開頭的字符串book、big、banana、bike
$匹配文本的結(jié)束字符'st$' 匹配以 st 結(jié)尾的字符串test、resist、persist
.匹配任何單個(gè)字符'b.t' 匹配任何?b 和 t 之間有一個(gè)字符bit、bat、but、bite
*匹配零個(gè)或多個(gè)在它前面的字符'f*n' 匹配字符 n 前面有任意個(gè)字符 ffn、fan、faan、abcn
+匹配前面的字符 1 次或多次'ba+' 匹配以 b 開頭，后面至少緊跟一個(gè) aba、bay、bare、battle
<字符串>匹配包含指定字符的文本'fa' 匹配包含‘fa’的文本fan、afa、faad
[字符集合]匹配字符集合中的任何一個(gè)字符'[xz]' 匹配 x 或者 zdizzy、zebra、x-ray、extra
[^]匹配不在括號(hào)中的任何字符'[^abc]' 匹配任何不包含 a、b 或 c 的字符串desk、fox、f8ke
字符串{n,}匹配前面的字符串至少 n 次'b{2}' 匹配 2 個(gè)或更多的 bbbb、bbbb、bbbbbbb
字符串
{n,m}
匹配前面的字符串至少 n 次， 至多 m 次'b{2,4}' 匹配最少 2 個(gè)，最多 4 個(gè) bbbb、bbbb
MySQL 中的正則表達(dá)式與 Java 語言、PHP 語言等編程語言中的正則表達(dá)式基本一致。
查詢以特定字符或字符串開頭的記錄
字符^用來匹配以特定字符或字符串開頭的記錄。
例 1
在 tb_students_info 表中，查詢 name 字段以“J”開頭的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '^J';
+----+------+------+------+--------+-----------+
| id | name | age? | sex? | height | course_id |
+----+------+------+------+--------+-----------+
|? 4 | Jane |? 22 | 男? |? ? 162 |? ? ? ? 3 |
|? 5 | Jim? |? 24 | 女? |? ? 175 |? ? ? ? 2 |
|? 6 | John |? 21 | 女? |? ? 172 |? ? ? ? 4 |
+----+------+------+------+--------+-----------+
3 rows in set (0.01 sec)
例 2
在 tb_students_info 表中，查詢 name 字段以“Ji”開頭的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '^Ji';
+----+------+------+------+--------+-----------+
| id | name | age? | sex? | height | course_id |
+----+------+------+------+--------+-----------+
|? 5 | Jim? |? 24 | 女? |? ? 175 |? ? ? ? 2 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)
查詢以特定字符或字符串結(jié)尾的記錄
字符$用來匹配以特定字符或字符串結(jié)尾的記錄。
例 3
在 tb_students_info 表中，查詢 name 字段以“y”結(jié)尾的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP 'y$';
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 1 | Dany? |? 25 | 男? |? ? 160 |? ? ? ? 1 |
|? 3 | Henry |? 23 | 女? |? ? 185 |? ? ? ? 1 |
|? 7 | Lily? |? 22 | 男? |? ? 165 |? ? ? ? 4 |
+----+-------+------+------+--------+-----------+
3 rows in set (0.00 sec)
例 4
在 tb_students_info 表中，查詢 name 字段以“ry”結(jié)尾的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP 'ry$';
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 3 | Henry |? 23 | 女? |? ? 185 |? ? ? ? 1 |
+----+-------+------+------+--------+-----------+
1 row in set (0.00 sec)
替代字符串中的任意一個(gè)字符
字符.用來替代字符串中的任意一個(gè)字符。
例 5
在 tb_students_info 表中，查詢 name 字段值包含“a”和“y”，且兩個(gè)字母之間只有一個(gè)字母的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP 'a.y';
+----+------+------+------+--------+-----------+
| id | name | age? | sex? | height | course_id |
+----+------+------+------+--------+-----------+
|? 1 | Dany |? 25 | 男? |? ? 160 |? ? ? ? 1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)
匹配多個(gè)字符
字符*和+都可以匹配多個(gè)該符號(hào)之前的字符。不同的是，+表示至少一個(gè)字符，而*可以表示 0 個(gè)字符。
例 6
在 tb_students_info 表中，查詢 name 字段值包含字母“T”，且“T”后面出現(xiàn)字母“h”的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '^Th*';
+----+--------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+--------+------+------+--------+-----------+
|? 9 | Thomas |? 22 | 女? |? ? 178 |? ? ? ? 5 |
| 10 | Tom? ? |? 23 | 女? |? ? 165 |? ? ? ? 5 |
+----+--------+------+------+--------+-----------+
2 rows in set (0.00 sec)
例 7
在 tb_students_info 表中，查詢 name 字段值包含字母“T”，且“T”后面至少出現(xiàn)“h”一次的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '^Th+';
+----+--------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+--------+------+------+--------+-----------+
|? 9 | Thomas |? 22 | 女? |? ? 178 |? ? ? ? 5 |
+----+--------+------+------+--------+-----------+
1 row in set (0.00 sec)
匹配指定字符串
正則表達(dá)式可以匹配字符串。當(dāng)表中的記錄包含這個(gè)字符串時(shí)，就可以將該記錄查詢出來。指定多個(gè)字符串時(shí)，需要用|隔開。只要匹配這些字符串中的任意一個(gè)即可。
例 8
在 tb_students_info 表中，查詢 name 字段值包含字符串“an”的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP 'an';
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 1 | Dany? |? 25 | 男? |? ? 160 |? ? ? ? 1 |
|? 4 | Jane? |? 22 | 男? |? ? 162 |? ? ? ? 3 |
|? 8 | Susan |? 23 | 男? |? ? 170 |? ? ? ? 5 |
+----+-------+------+------+--------+-----------+
3 rows in set (0.00 sec)
例 9
在 tb_students_info 表中，查詢 name 字段值包含字符串“an”或“en”的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP 'an|en';
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 1 | Dany? |? 25 | 男? |? ? 160 |? ? ? ? 1 |
|? 2 | Green |? 23 | 男? |? ? 158 |? ? ? ? 2 |
|? 3 | Henry |? 23 | 女? |? ? 185 |? ? ? ? 1 |
|? 4 | Jane? |? 22 | 男? |? ? 162 |? ? ? ? 3 |
|? 8 | Susan |? 23 | 男? |? ? 170 |? ? ? ? 5 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)
注意：字符串與|之間不能有空格。因?yàn)?，查詢過程中，數(shù)據(jù)庫系統(tǒng)會(huì)將空格也當(dāng)作一個(gè)字符，這樣就查詢不出想要的結(jié)果。
匹配指定字符串中的任意一個(gè)
使用方括號(hào)[ ]可以將需要查詢的字符組成一個(gè)字符集合。只要記錄中包含方括號(hào)中的任意字符，該記錄就會(huì)被查詢出來。例如，通過“[abc]”可以查詢包含 a、b 和 c 等 3 個(gè)字母中任意一個(gè)的記錄。
例 10
在 tb_students_info 表中，查詢 name 字段值包含字母“i”或“o”的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '[io]';
+----+--------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+--------+------+------+--------+-----------+
|? 5 | Jim? ? |? 24 | 女? |? ? 175 |? ? ? ? 2 |
|? 6 | John? |? 21 | 女? |? ? 172 |? ? ? ? 4 |
|? 7 | Lily? |? 22 | 男? |? ? 165 |? ? ? ? 4 |
|? 9 | Thomas |? 22 | 女? |? ? 178 |? ? ? ? 5 |
| 10 | Tom? ? |? 23 | 女? |? ? 165 |? ? ? ? 5 |
| 11 | LiMing |? 22 | 男? |? ? 180 |? ? ? ? 7 |
+----+--------+------+------+--------+-----------+
6 rows in set (0.00 sec)
從查詢結(jié)果可以看到，所有返回記錄的 name 字段值都包含字母 i 或 o，或者兩個(gè)都有。
方括號(hào)[ ]還可以指定集合的區(qū)間。例如，“[a-z]”表示從 a~z 的所有字母；“[0-9]”表示從 0~9 的所有數(shù)字；“[a-z0-9]”表示包含所有的小寫字母和數(shù)字；“[a-zA-Z]”表示匹配所有字符。
例 11
在 tb_students_info 表中，查詢 name 字段值中包含 1、2 或 3 的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '[123]';
Empty set (0.00 sec)
匹配集合“[123]”也可以寫成“[1-3]”，即指定集合區(qū)間。
匹配指定字符以外的字符
[^字符集合]用來匹配不在指定集合中的任何字符。
例 12
在 tb_students_info 表中，查詢 name 字段值包含字母 a~t 以外的字符的記錄，SQL 語句和執(zhí)行過程如下。
mysql> SELECT * FROM tb_students_info
? ? -> WHERE name REGEXP '[^a-t]' ;
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 1 | Dany? |? 25 | 男? |? ? 160 |? ? ? ? 1 |
|? 3 | Henry |? 23 | 女? |? ? 185 |? ? ? ? 1 |
|? 7 | Lily? |? 22 | 男? |? ? 165 |? ? ? ? 4 |
|? 8 | Susan |? 23 | 男? |? ? 170 |? ? ? ? 5 |
+----+-------+------+------+--------+-----------+
4 rows in set (0.00 sec)
使用{n,}或者{n,m}來指定字符串連續(xù)出現(xiàn)的次數(shù)
字符串{n,}表示字符串連續(xù)出現(xiàn) n 次；字符串{n,m}表示字符串連續(xù)出現(xiàn)至少 n 次，最多 m 次。
例如，a{2,} 表示字母 a 連續(xù)出現(xiàn)至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 連續(xù)出現(xiàn)最少 2 次，最多不能超過 4 次。
例 13
在 tb_students_info 表中，查詢 name 字段值出現(xiàn)字母‘e’ 至少 2 次的記錄，SQL 語句如下：
mysql> SELECT * FROM tb_students_info WHERE name REGEXP 'e{2,}';
+----+-------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+-------+------+------+--------+-----------+
|? 2 | Green |? 23 | 男? |? ? 158 |? ? ? ? 2 |
+----+-------+------+------+--------+-----------+
1 row in set (0.00 sec)
例 14
在 tb_students_info 表中，查詢 name 字段值出現(xiàn)字符串“i” 最少 1 次，最多 3 次的記錄，SQL 語句如下：
mysql> SELECT * FROM tb_students_info WHERE name REGEXP 'i{1,3}';
+----+--------+------+------+--------+-----------+
| id | name? | age? | sex? | height | course_id |
+----+--------+------+------+--------+-----------+
|? 5 | Jim? ? |? 24 | 女? |? ? 175 |? ? ? ? 2 |
|? 7 | Lily? |? 22 | 男? |? ? 165 |? ? ? ? 4 |
| 11 | LiMing |? 22 | 男? |? ? 180 |? ? ? ? 7 |
+----+--------+------+------+--------+-----------+
3 rows in set (0.00 sec)
