By

文章目录

前一段时间，有一个需求，需要做一个比较复杂的权限判断。问题是这样，已知用户拥有权限[a,b,c].某个资源需要[c,d,e]权限之一即可访问。那么如何用Mysql判断两个json数组是否存交集。

答案就是使用MySQL提供的 JSON_OVERLAPS() 函数。

注意，这个JSON_OVERLAPS() 是MySQL 8.0.17版本以上才支持的，如果你的MySQL版本低于这个版本，就可以考虑一下升级了

先看两个简单示例：

1
2
3
4
5
6
7
mysql> select JSON_OVERLAPS('[1,2]','[2,3]');
+--------------------------------+
 JSON_OVERLAPS('[1,2]','[2,3]') 
+--------------------------------+
                              1 
+--------------------------------+
1 row in set (0.00 sec)

因为数组[1,2]和数组[2,3]存在交集，所以返回值是1

1
2
3
4
5
6
7
mysql> select JSON_OVERLAPS('[1,2]','[3,4]');
+--------------------------------+
 JSON_OVERLAPS('[1,2]','[3,4]') 
+--------------------------------+
                              0 
+--------------------------------+
1 row in set (0.00 sec)

因为数组[1,2]和数组[3,4]不存在交集，所以返回值是0。

上面两个示例都是数值型的数组，那么字符串类的数组支持吗？当然是支持的，请看：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select JSON_OVERLAPS('["a","b"]','["b","c"]');
+----------------------------------------+
 JSON_OVERLAPS('["a","b"]','["b","c"]') 
+----------------------------------------+
                                      1 
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_OVERLAPS('["a","b"]','["c","d"]');
+----------------------------------------+
 JSON_OVERLAPS('["a","b"]','["c","d"]') 
+----------------------------------------+
                                      0 
+----------------------------------------+
1 row in set (0.00 sec)

那么我们要如何把这个函数应用到select 查询当中呢，请看下面的示例。 我们先创建表，准备基础数据

1
2
3
4
5
6
7
8
CREATE TABLE `user`  (
  `username` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `role` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  UNIQUE INDEX `username_UNIQUE`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES ('u1', '[\"a\",\"b\"]');
INSERT INTO `user` VALUES ('u2', '[\"b\",\"c\"]');
INSERT INTO `user` VALUES ('u3', '[\"c\",\"d\"]');

所以表里数据是这样的。 username

role

u1

[“a”,”b”]

u2

[“b”,”c”]

u3

[“c”,”d”]

如果我需要查询拥有 角色a 或者 角色d 的用户，就可以这样查询：

1
2
3
4
5
6
7
8
mysql> select * from `user` where JSON_OVERLAPS(role,'["a","d"]')=1;
+----------+-----------+
 username  role      
+----------+-----------+
 u1        ["a","b"] 
 u3        ["c","d"] 
+----------+-----------+
2 rows in set (0.00 sec)

使用还是蛮简单的，你学会了吗？

文章目录