【对于多选字段存入数据库到底是分条存储还是逗号隔开存储成一个字段】

最新推荐文章于 2024-06-28 01:33:43 发布

这只猫好肥

最新推荐文章于 2024-06-28 01:33:43 发布

阅读量2.1k

点赞数 1

分类专栏： Java mysql 多选逗号隔开文章标签： java mysql

本文链接：https://blog.csdn.net/u010449880/article/details/127927054

版权

Java 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

mysql

1 篇文章 0 订阅

订阅专栏

多选逗号隔开

1 篇文章 0 订阅

订阅专栏

数据库的城市字段到底用逗号隔开存还是分条存

场景
存储方式
- 优劣分析

场景

当我们在B端配置一个任务的时候，很有可能需要选择这个任务可以在那些城市生效，那么，对于这个任务我们怎么去存这些城市。这里以城市举例，当然还会有很多其他的这样多选的情况

存储方式

1.任务表中有一个字段标识城市，城市code通过逗号隔开存储
例如：任务1 100000,110000,110100
当选择的城市特别的多的时候，方式1的城市字段会特别的长。当一个字段特别长的时候我们就要考虑这个字段设置多长合适，甚至需要用到文本来存储该字段
2.按照城市分条存储
例如：
任务1 100000
任务1 110000
任务1 110100
任务中可能包含很多其他字段这里统一用任务1表示
方式2当任务本身内容很多，并且任务数量也多，再与城市做笛卡尔积就会造成数据量特别多，每条数据的重复数据特别多，不满足范式

3.在城市表中以任务id为主键分条存储城市信息
例如：
任务id1 100000
任务id1 110000
任务id1 110100
他与上面的区别是这个表里只存了任务id与城市的关系，而任务信息表只有一条数据
方式3在查询数据的时候不得不进行表关联的查询，而且若有返现可能还要再额外关联表。另外，对于选择信息的更新就会比较复杂。例如开始选了100000,110000,110100，后来重新选择变成100000,210000,110100。对于方式3只能先把任务id1的所有城市表数据删除，然后再重新插入新的数据。对于方式1来说就不用考虑这些问题，直接更新即可

优劣分析

一般情况下，这样的城市还会涉及到反显。这种的可能分两种情况，对于中文名不会改变的存储字段，可能我们还会存储一个字段，跟城市code一样，用逗号隔开例如北京,上海,大连，这样对于这一条数据我们不用做任何的关联查询，只需要直接查出字段或者更新字段即可。当我们配置的可能是容易改变中文名的字段时，我们可能会有一张基础的城市表，里面有城市code与名字的关系。这样的对于方式1我们可以用关联表把中文名查出来。对于方式1，会用到比较高频的几个sql如下
FIND_IN_SET(#{showAppType}, t.show_app_type)
concat(‘,’,t.show_app_type, ‘,’) regexp concat(‘,’,replace(#{showAppType},‘,’,‘,|,’),‘,’)
GROUP_CONCAT(p.project_name SEPARATOR ‘,’) AS projectName

find in set 用于查找，比如传进来1,2 库里某条条数据的这个字段是1,3,4 那么这条数据就会被查出来，同时最后我们还可以加上order by FIND_IN_SET(#{showAppType}, t.show_app_type),这样查出来的数据会按照传入的1,2的顺序返回。

concat regexp concat 用于查两个字段是否有交集。比如配置的一条任务的城市是 100000,110000,110100 当我们再次配置一条任务的时候要保证已经配置过的城市不允许再保存。就可以通过这个sql来查出有交集的，比如此时传入 200000,110000,210100 ，则会查出100000,110000,110100这条数据，因为他们有交集110000.

GROUP_CONCAT 他可以把查出的list混成一个字段并且用逗号隔开，比如我们查一条任务与城市基础表关联。正常情况下同一条任务我们可能查出多条每条的城市名字不一样，通过这个sql，我们就会查出一条数据，并且中文名字也是按照逗号隔开的。

对于方式3，同一条任务我们查出来的可能是多条数据，这样的对于反显已选过的城市来说不友好，我们可以通过指定resultMap使查出来的数据转换成如下格式

public class task{
String taskName;
String taskCode;
List cityList;
}
public class cityEnty{
String cityCode;
String cityName;
}

指定的resultMap举例：

<resultMap type="com.longfor.longhub.lmember.godspen.common.pojo.vo.FrameInfoVo" id="QueryFrameMap">
    <result column="taskName" property="taskName"/>
    <result column="taskCode" property="taskCode"/>
    <collection property="cityList" resultMap="QueryFrameSourceMap" column="taskCode"/>
</resultMap>
<resultMap type="com.longfor.longhub.lmember.godspen.common.pojo.req.FrameContent" id="QueryFrameSourceMap">
    <result column="cityCode" property="cityCode"/>
    <result column="cityName" property="cityName"/>
</resultMap>

这样查出来的数据其实就是组装好数据的list<\task>,对于前端反显的来说一个任务中多条城市直接展示城市list即可。后续保存也是修改城市list，后端入库时则会有上面说到的问题需要先删除再入库

目前来说方式1跟3都可以，方式2的话基本不推荐。个人比较喜欢方式1，目前还没有遇到什么瓶颈。至于性能问题，没有做过测试，因为这些基本都是B端使用，对于性能并发要求的不是很高。而对于C端来说，一般都是要将查询好的数据预热进缓存或其他缓存方式，很少实时查库。所以性能这块没有做比较。目前来看方式1唯一的缺点就是这个字段的长度是不可控的，当过长的时候可能就不太好。而方式3的话一个是城市表的数据量可能会非常的多，比如每次配任务都要生成200条城市数据，若配置了1万条任务，则城市表就会有200万条数据，还有个问题则是修改的时候可能要做大量的删除。

欢迎大家讨论各方式的优劣及其他方式。