关于 MySQL5.7 json 字段 array 查询的疑惑?

2021-12-17 19:33:21 +08:00
 charmToby

最近写了一个需求,比如创建一个活动,限制某几个部门能参加,在活动列表仅对能参加的用户展示这些活动。

活动表主要字段如下:

id department_limit (json 字段)
1 [1,2,3]
2 [3,4,5]

这张表里面 department_limit 字段为部门 id 数组, 意思是只有这几个部门的用户能参加,否则就权限不足。

接口请求时,是可以查询到用户所有的部门, 比如用户在 [1, 2]部门, 就可以参加 id 为 1 的活动,且活动列表会显示 id 为 1 的活动,不会显示 id 为 2 的活动。

问题是:

如何通过 SQL 语句去实现这样的取交集过滤查询?

也上搜过类似的操作如下。 https://stackoverflow.com/questions/59846109/is-there-a-way-to-check-if-an-json-array-contains-at-least-one-item-of-another-j

MySQL 8.0 有个函数 JSON_OVERLAPS() 可以实现这样的操作,但是我用的是 5.7 版本。

目前我的做法是,SQL 语句查询时,不过滤部门限制的条件,查询出来后再通过代码过滤不符合的数据,但是会有个问题,会出现数据和分页对不上的情况,而且这样处理感觉不优雅。

所以我想问一下:

1 有什么更好的办法查询过滤能参加活动的数据?

2 限制参加部门,表字段设计的是否有问题?或者有没有其他的设计思路?

先感觉各位大佬了。

2318 次点击
所在节点    MySQL
3 条回复
liuxu
2021-12-17 21:27:23 +08:00
你就别为难 mysql 了,好好建一对多表吧
phpfpm
2021-12-17 22:04:11 +08:00
left join department d on json_contains(department_limit."$.[*]",json_array(d.id))

然后过滤一下连表的即可

当然真不推荐你用这样的数据结构。

大概是这样 今天刚解决
tyoung
2021-12-18 08:21:27 +08:00
活动表的 department_limit 字段存储的 json 数据格式可以改一下,以部门 id 为 key ,0 、1 为 value ( 1 表示这个部门有该活动权限,0 表示没有权限),每个活动以所有的部门的 key:value 组合构成一个 json ,存储到 department_limit 字段。如:{"dep_1": 1, "dep_2": 0, "dep_3": 1} 。然后对 department_limit 的 json 的所有部门字段建立虚拟列和索引(虚拟列名如:dep_1 、dep_2 、dep_N 等),提高查询效率。
查询用户具有的活动权限时,则可先查询用户所在部门,比如[1, 2],然后去活动表,以( dep_1 = 1 or dep_2 = 1 )为条件查询,即可查询所有能参与的活动。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/822891

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX