mysql字符串比较函数regexp 以及concat

问题描述

比如table1中有两条记录
name no
a 2,9
b 8,10

然后有一串字符串,是0,1,2,3,4

然后通过一条sql,找出no为2,9的记录来```

因为字符串中有2,数据中也有2

详细解释

表的字段就是
name no
a 2,9
b 8,10
字符串是str="0,1,2,3,4"
接下来就是查 no字段里跟str里有交集的记录
查询的结果就是name=a的,no=2,9的记录。

select * from table1 where   
concat(',',no,',') regexp concat(',0,|,1,|,2,|,3,|,4,');

或者:

select * from table1 where 
concat(',',no,',') regexp concat(',(',replace('0,1,2,3,4',',','|'),'),');

由于某些原因，有时候我们没有按照范式的设计准则而把一些属性放到同一个字符串字段中。比如个人兴趣，有时候我们设计表为
create table members (uid int primary key,uname varchar(20),hobby varchar(100));

表中内容如下

如果我们现在想查找一个与某个用户X (阅读,交友,围棋,足球,滑雪)有着相同爱好的会员记录如果来操作呢？

在其它数据库中，我们能只通过程序来或者存储过程来分解这个 "阅读,交友,围棋,足球,滑雪" 字符串为单独的爱好项目，然后一个一个进行 like '%xxxx%' 来查询。但在MySQL中我们可以直接利用这个regexp正规表达式来构造SQL语句来实现。

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

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

如上语句我们可以通过一句SQL得到所有hobby包含 '阅读,交友,围棋,足球,滑雪' 任一项的记录。

但上述的语句中还有一点小的缺陷，那就是把 '下围棋' 这一条也选择了出来，如果精确匹配的话这条记录不应该被选中。为了避免这种情况,我们对SQL语句做如下改进。

把正则式改为 ',(阅读|交友|围棋|足球|滑雪),' 也就是要求匹配项前后必须有一个界定符","

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

这样避免了第5条记录被选中。

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