MySQL 处理逗号分隔的数据

前言

在日常开发中会经常从MySQL中获取数据然后做一些处理(业务代码)，其中很常见的一种场景是处理逗号分隔的数据，此篇就介绍两个使用率不高的MySQL函数 GROUP_CONCAT 和 FIND_IN_SET。

person表设计

结构:

名	类型	长度	注释
id	int	10	主键id
hobby_id	varcher	255	关联hobby表主键id 逗号分隔 1:sing 2:running 3:game 4:cooking
name	varcher	255	姓名

数据:

id	hobby_id	name
1	1,2,3,4	Jay
2	2,4	马里奥
3	4	林克

GROUP_CONCAT 函数

场景一

从 person 表中取出所有人的name 返回一个 List。

常规处理

// personService.getList() 中实际执行的SQL是 select * from person
List<Person> list = personService.getList();
List<String> nameList = list.stream(Person::getName).collect(Collectors.toList());

使用 GROUP_CONCAT

// personService.getGroupConactList() 中实际执行的SQL是 SELECT GROUP_CONCAT(`name`) FROM person
// 返回一个逗号分隔的字符串
String names = personService.getGroupConactList();
List<String> nameList = Arrays.asList(names.split(","));

FIND_IN_SET 函数

场景一

找出业余爱好为跑步的person数据(hobbyId=2)

常规处理

// 因为 hobby_id 是逗号分隔的字符串无法直接用等号
SELECT * FROM person WHERE hobby_id LIKE '%2%'

使用 FIND_IN_SET

SELECT * FROM person WHERE FIND_IN_SET('2',hobby_id)

场景二

现在有字符串 String ids="1,3" 找出id为1和3的数据。

常规处理

先把字符串转换为 List 再通过 MyBatis 用 in 查询类似下面的sql

SELECT * FROM person WHERE id IN (1,3)

使用 FIND_IN_SET

SELECT * FROM person WHERE FIND_IN_SET(id,'1,3')

组合使用

表设计

person_2 表结构:

名	类型	长度	注释
id	int	10	主键id
hobby_id	int	10	关联hobby表主键id
name	varcher	255	姓名

person_2 表数据:

id	hobby_id	name
1	1	Jay
2	3	马里奥
3	4	林克

person_2 与 person 表唯一的区别就是 hobby_id 字段，person_2 表是个单一值.

hobby 表结构:

名	类型	长度	注释
id	int	10	主键id
hobby_name	archer	255	业余爱好名称

hobby 表数据:

id	hobby_name
1	唱歌
2	跑步
3	游戏
4	烹饪

场景

因业务调整每个 person 能有多个爱好(person_2 表结构修改为 person)。在UI上需要显示person.name 和多个 hobbyName（逗号分隔的形式显示）。

此处解决方案有很多,这里只选用一种最简单，修改代码最少的方式。

业务调整前SQL

一个 person 只有一个 hobby

select p.name,h.hobby_name from person_2 p 
left join hobby h on p.hobby_id = h.id

业务调整后SQL

一个 person 对应多个 hobby

使用最少改动代码的方式实现,快速响应需求变更。

SELECT p.name,
(SELECT GROUP_CONCAT(hobby_name) FROM hobby WHERE FIND_IN_SET(id,p.hobby_id)) AS hobby_name
FROM person p

总结

今天介绍了两个不怎么常用的MySQL函数 GROUP_CONCAT 和 FIND_IN_SET，在项目开发中尽量不要使用这两个函数，业务逻辑尽量不要写在SQL中比较好，而且这两个函数都会全表扫描对性能会有影响。如果业务访问量不是很大，或者要快速实现变更的需求那么可以考使用这两个内置函数。