MySQL 一个字段,用’,'隔开,存储多个id,关联查询
因为朋友过来问我,一个商品表的一个颜色的关联字段,里面放着多个颜色的id,这些id用逗号隔开,然后想要查出一条商品记录上显示出所有颜色。实现效果如下:
商品表:goods
select * from goods;
- 1
id | name | color |
---|---|---|
1 | 中性笔 | 2,3,4 |
2 | 圆珠笔 | 1,3 |
颜色表:color
select * from color;
- 1
id | name |
---|---|
1 | 红色 |
2 | 白色 |
3 | 黑色 |
4 | 蓝色 |
查询后的结果:
select g.id as id,g.name as name,GROUP_CONCAT(c.color_name) as color_name from goods g
inner join color c on FIND_IN_SET(c.id,g.color)
group by g.id;
- 1
- 2
- 3
id | name | color_name |
---|---|---|
1 | 中性笔 | 白色,黑色,蓝色 |
2 | 圆珠笔 | 红色,黑色 |
这里面涉及到两个函数,GROUP_CONCAT 和 FIND_IN_SET
1、GROUP_CONCAT
官方文档:https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
帖子详解:https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc
2、FIND_IN_SET
官方文档:https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set
示例:
SELECT FIND_IN_SET( 'b' , 'a,b,c,d' ); --返回值为2,即第2个值
- 1
语法:FIND_IN_SET(str,strlist)
定义:
-
假如字符串str在由N子链组成的字符串列表strlist中,则返回值的范围在1到N之间。
-
一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。
-
如果第一个参数是一个常数字符串,而第二个是typeSET列,则FIND_IN_SET()函数被优化,使用比特计算。
-
如果str不在strlist或strlist为空字符串,则返回值为0。
-
如任意一个参数为NULL,则返回值为NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。
strlist:一个由英文逗号“,”链接的字符串,例如:“a,b,c,d”,该字符串形式上类似于SET类型的值被逗号给链接起来。
速度方面的话,IN > FIND_IN_SET,具体文档可参照关于 find_in_set的性能问题,不过在表数据量不大的情况,为了节省表数量,可以使用该方式
另外,在翻译的字段里做筛选可以使用以下方式
select * from
(
select g.id as id,g.name as name,GROUP_CONCAT(c.color) as color_name from t_good g
inner join t_color c on FIND_IN_SET(c.id,g.color)
group by g.id
) tmp
where instr(tmp.color_name,'红')>0;
INSTR(STR,SUBSTR) 在一个字符串(STR)中搜索指定的字符(SUBSTR),返回发现指定的字符的位置(INDEX);
STR 被搜索的字符串
SUBSTR 希望搜索的字符串
结论:在字符串STR里面,字符串SUBSTR出现的第一个位置(INDEX),INDEX是从1开始计算,如果没有找到就直接返回0,没有返回负数的情况。