mysql 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语句来实现。 



首先我们把 '阅读,交友,围棋,足球,滑雪' 转换成为正则式 为 '阅读|交友|围棋|足球|滑雪' ,  | 在正则表达式中为 '或' 的意思 

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条记录被选中。 



当然也可以利用这种正则式 ',阅读,|,交友,|,围棋,|,足球,|,滑雪,', 但效率显然不如 ',(阅读|交友|围棋|足球|滑雪),' 这种了。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值