mysql 正则匹配字符串的例子分享

发布时间:2020-01-10编辑:脚本学堂
本文介绍下,mysql中用正则表达式匹配字符串的例子,有需要的朋友参考下吧。

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条记录被选中。
当然也可以利用这种正则式 ',阅读,|,交友,|,围棋,|,足球,|,滑雪,',但效率显然不如 ',(阅读|交友|围棋|足球|滑雪),' 这种了。

其实有个函数很好用,它就是:FIND_IN_SET(9,字段名) 。

就介绍这些吧,希望有助于大家理解与掌握mysql查询中正则表达式的用法。