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

[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 限制参加部门，表字段设计的是否有问题？或者有没有其他的设计思路？

先感觉各位大佬了。

[liuxu]

你就别为难 mysql 了，好好建一对多表吧

[phpfpm]

left join department d on json_contains(department_limit."$.[*]",json_array(d.id))

然后过滤一下连表的即可

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

大概是这样 今天刚解决

[tyoung]

活动表的 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 ）为条件查询，即可查询所有能参与的活动。