关于mysql:mysql判断json数组是否存在交集

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

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

留神，这个 JSON_OVERLAPS() 是 MySQL 8.0.17 版本以上才反对的，如果你的 MySQL 版本低于这个版本，就能够考虑一下降级了

先看两个简略示例：

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

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。

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

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 查问当中呢，请看上面的示例。
咱们先创立表，筹备根底数据

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\"]');

所以表里数据是这样的。

如果我须要查问领有 角色 a 或者 角色 d 的用户，就能够这样查问：

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)

应用还是蛮简略的，你学会了吗？

转自 http://www.genlei.cn/?p=155