mysql字符串比较函数:concat和regexp

问题描述

比如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的记录。

1
2

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

或者:

1
2

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));

表中内容如下

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

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

时间: 2024-11-02 16:09:44

mysql字符串比较函数:concat和regexp的相关文章

mysql 字符串比较函数

字符串函数 字符串比较函数 假如结果的长度大于 max_allowed_packet 系统变量的最大值时,字符串值函数的返回值为NULL.请参见7.5.2节,"调节服务器参数". 对于在字符串位置操作的函数,第一个位置的编号为 1. ASCII(str) 返回值为字符串str 的最左字符的数值.假如str为空字符串,则返回值为 0 .假如str 为NULL,则返回值为 NULL. ASCII()用于带有从 0到255的数值的字符. mysql> SELECT ASCII('2')

MySQL字符串与字符串比较函数学习

1:字符串连接函数  代码如下 复制代码       CONCAT(str1,str2,...)       返回结果为连接参数产生的字符串.如有任何一个参数为NULL ,则返回值为 NULL.或许有一个或多个参数. 如果所有参数均为非二进制字符串,则结果为非二进制字符串. 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串.一个数字参数被转化为与之相等的二进制字符串格式:若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS C

MySQL字符串中抽取数值的方法

MySQL的字符串函数非常多,以至于有时候我不知道该如何灵活的使用这些函数. 字符串基本信息函数     collation  convert,char_length等 加密函数   password(x),encode,  aes_encrypt 字符串连接函数   concat(x1,x2,-.) 修剪函数  trim,ltrim,rtrim 子字符串操作函数 substring(x,start,length),mid(x,start,length) 字符串复制函数  repeat,spac

MySQL中的CONCAT函数使用教程

  这篇文章主要介绍了MySQL中的CONCAT函数使用教程,是Python入门学习中的基础知识,需要的朋友可以参考下 使用MySQL CONCAT()函数将两个字符串连接起来,形成一个单一的字符串.试试下面的例子: ? 1 2 3 4 5 6 7 mysql> SELECT CONCAT('FIRST ', 'SECOND'); +----------------------------+ | CONCAT('FIRST ', 'SECOND') | +--------------------

Mysql字符串处理函数详细介绍、总结

 这篇文章主要介绍了Mysql字符串处理函数详细介绍.总结,需要的朋友可以参考下 一.简明总结 ASCII(char) 返回字符的ASCII码值 BIT_LENGTH(str) 返回字符串的比特长度 CONCAT(s1,s2-,sn)  将s1,s2-,sn连接成字符串 CONCAT_WS(sep,s1,s2-,sn) 将s1,s2-,sn连接成字符串,并用sep字符间隔 INSERT(str,x,y,instr) 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果

mysql字符串函数总结

()函数可以获得一个字符串的长度: select length('abcdefg'),length('0123456789'); /* +-------------------+----------------------+ | length('abcdefg') | length('0123456789') | +-------------------+----------------------+ |                 7 |                   10 | +-

MySQL字符串函数详解(推荐)_Mysql

一.ASCII ASCII(str) 返回字符串str的最左面字符的ASCII代码值.如果str是空字符串,返回0.如果str是NULL,返回NULL. 二.ORD ORD(str) 如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码.如果最左面的字符不是一个多字节字符.返回与A

mysql模糊查询like和regexp小结_Mysql

在mysql中实现模糊查询的有like和regexp. ------------------------ like的用法许多人都是知道的,最为常用的情况就是select * from a where name like '%a%'; 其中'%'代表的是任意个字符,它的效果像是正则表达式里的'*',它有几种用法:'a%','%a%','%a',分别表示以什么开头,存在什么以及以什么结尾. 另外也可以使用'_'字符,这表示一个任意字符.效果类似正则表达式里面的'.'. like是对这个字段里面的所有

mysql 字符串函数收集比较全_Mysql

对于针对字符串位置的操作,第一个位置被标记为1. ASCII(str) 返回字符串str的 最左面字符的ASCII代码值.如果str是空字符串, 返回0.如果str是NULL,返回NULL. mysql> select ASCII('2'); -> 50mysql> select ASCII(2); -> 50mysql> select ASCII('dx'); -> 100 也可参见ORD()函数. ORD(str) 如果字符串str最左面字符是一个多字节字符,通过以