MYSQL5.7模拟MYSQL8.0的JSON_OVERLAPS替代方案

我有来自不同表的两列，它们保存 JSON 格式的数据。两列中存储的数据都是数组。示例:

用户

+----+------------------+
| id | roleIds          |
+----+------------------+
|  1 | ["AB","CD","XY"] |
|  2 | ["CD","GH"]      |
+----+------------------+

权限

+----+-------------+
| id | roleIds     |
+----+-------------+
| 10 | ["CD","EF"] |
| 11 | ["GH","XY"] |
| 12 | ["GH"]      |
+----+-------------+

我想编写一个查询，返回与 items 中给定行匹配的 users 中的所有行，并使用 permission 列来执行比赛。规则是如果数组中的任何值都出现在两行中，则它们是匹配的。示例:用户 1 将匹配项目 10(因为 CD 选项)和 11(因为 XY 选项)；用户 2 将匹配项目 10、11 和 12，因为它们都具有 CD 或 GH。

查看 MySQL 文档我发现 JSON_OVERLAPS正是这样做的。但是，我运行的是 MySQL 5.7，该功能仅从 8.0.17 开始可用。网络上也没有太多关于此功能的讨论。

如何在查询中模拟 MySQL 5.7 上的 JSON_OVERLAPS 行为？

编辑:不幸的是，升级到 MySQL 8 不是一个选择，因为我们在生产环境中运行 MariaDB，它也没有该功能。

MYSQL5.7模拟MYSQL8.0的JSON_OVERLAPS替代方案

SELECT * FROM (
    SELECT permission.id,permission.alias,JSON_UNQUOTE(JSON_EXTRACT(permission.roleIds,CONCAT('$[', number_generator.number , ']'))) AS json_rid
        FROM (
            SELECT @permission_row := @permission_row + 1 AS number
            FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) row1
  			CROSS JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) row2 
            CROSS JOIN (SELECT @permission_row := -1) init_user_params
        ) AS number_generator
		CROSS JOIN (SELECT permission.id,permission.alias, permission.roleIds, JSON_LENGTH(permission.roleIds) AS json_array_length FROM permission) AS permissionWHERE number BETWEEN 0 AND  json_array_length - 1                 
) AS permission
INNER JOIN (
    SELECT user.id,JSON_UNQUOTE(JSON_EXTRACT(user.roleIds,CONCAT('$[', number_generator.number , ']'))) AS json_rid
    FROM (
        SELECT @users_row := @users_row + 1 AS number
        FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) row1
        CROSS JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) row2
        CROSS JOIN (SELECT @users_row := -1) init_user_params 
    ) AS number_generator
    CROSS JOIN (SELECT user.id,user.roleIds,JSON_LENGTH(user.roleIds) AS json_array_length FROM user) AS user
    WHERE number BETWEEN 0 AND  json_array_length - 1   
) AS user
USING(json_rid)