REGEXP 正則的實現兩個字串組的匹配。

最近MySQL版塊中類似問題出現得比較多。總結了一下。

 

由於某些原因，有時候我們沒有按照範式的設計準則而把一些屬性放到同一個字串欄位中。比如個人興趣，有時候我們設計表為
create table members (uid int primary key,uname varchar(20),hobby varchar(100));

表中內容如下

mysql> select * from members;
+-----+-------+---------------------------------+
| uid | uname | hobby                           |
+-----+-------+---------------------------------+
|   1 | AAAA  | 音樂,電影,網路,籃球,閱讀,乒乓球 |
|   2 | BBBB  | 音樂,閱讀,乒乓球,發獃,圍棋,參禪 |
|   3 | CCCC  | 交友,乒乓球                     |
|   4 | DDDD  | 撞球,網路,看書,旅遊             |
|   5 | EEEE  | 音樂,發獃,下圍棋,參禪           |
+-----+-------+---------------------------------+
4 rows in set (0.00 sec)

 

如果我們現在想尋找一個與某個使用者X (閱讀,交友,圍棋,足球,滑雪)有著相同愛好的會員記錄 如果來操作呢？

在其它資料庫中，我們能只通過程式來或者預存程序來分解這個 "閱讀,交友,圍棋,足球,滑雪" 字串為單獨的愛好項目，然後一個一個進行 like '%xxxx%' 來查詢。 但在MySQL中我們可以直接利用這個regexp正規運算式 來構造SQL語句來實現。

 

首先我們把 '閱讀,交友,圍棋,足球,滑雪' 轉換成為正則式 為 '閱讀|交友|圍棋|足球|滑雪' ,  | 在Regex中為 '或' 的意思

mysql> select replace('閱讀,交友,圍棋,足球,滑雪',',','|');
+---------------------------------------------+
| replace('閱讀,交友,圍棋,足球,滑雪',',','|') |
+---------------------------------------------+
| 閱讀|交友|圍棋|足球|滑雪                    |
+---------------------------------------------+
1 row in set (0.00 sec)

 

這樣我們可以用SQL語句如下。
mysql> select * from members where hobby regexp replace('閱讀,交友,圍棋,足球,滑雪',',','|');
+-----+-------+---------------------------------+
| uid | uname | hobby                           |
+-----+-------+---------------------------------+
|   1 | AAAA  | 音樂,電影,網路,籃球,閱讀,乒乓球 |
|   2 | BBBB  | 音樂,閱讀,乒乓球,發獃,圍棋,參禪 |
|   3 | CCCC  | 交友,乒乓球                     |
|   5 | EEEE  | 音樂,發獃,下圍棋,參禪           |
+-----+-------+---------------------------------+
3 rows in set (0.00 sec)

如上語句我們可以通過一句SQL得到所有hobby包含 '閱讀,交友,圍棋,足球,滑雪' 任一項的記錄。

但上述的語句中還有一點小的缺陷，那就是把 '下圍棋' 這一條也選擇了出來，如果精確匹配的話這條記錄不應該被選中。為了避免這種情況,我們對SQL語句做如下改進。

把正則式改為 ',(閱讀|交友|圍棋|足球|滑雪),'  也就是要求匹配項前後必須有一個界定符","

 

mysql> select concat(',(',replace('閱讀,交友,圍棋,足球,滑雪',',','|'),'),');
+---------------------------------------------------------------+
| concat(',(',replace('閱讀,交友,圍棋,足球,滑雪',',','|'),'),') |
+---------------------------------------------------------------+
| ,(閱讀|交友|圍棋|足球|滑雪),                                  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from members
    -> where concat(',',hobby,',') regexp
    ->   concat(',(',replace('閱讀,交友,圍棋,足球,滑雪',',','|'),'),');
+-----+-------+---------------------------------+
| uid | uname | hobby                           |
+-----+-------+---------------------------------+
|   1 | AAAA  | 音樂,電影,網路,籃球,閱讀,乒乓球 |
|   2 | BBBB  | 音樂,閱讀,乒乓球,發獃,圍棋,參禪 |
|   3 | CCCC  | 交友,乒乓球                     |
+-----+-------+---------------------------------+
3 rows in set (0.00 sec)

這樣避免了第5條記錄被選中。

 

當然也可以利用這種正則式 ',閱讀,|,交友,|,圍棋,|,足球,|,滑雪,'， 但效率顯然不如 ',(閱讀|交友|圍棋|足球|滑雪),' 這種了。

 

參考文檔:

 

MySQL 5.1參考手冊 - 12.3.1. 字串比較函數 - REGEXP (RLIKE)

http://dev.mysql.com/doc/refman/5.1/zh/functions.html#string-comparison-functions

 

MySQL 5.1參考手冊 - 附錄G：MySQLRegex

http://dev.mysql.com/doc/refman/5.1/zh/regexp.html