如何在MySQL 5.7上模拟JSON_OVERLAPS函数？

Question

如何在MySQL 5.7上模拟JSON_OVERLAPS函数？

3

我有两个来自不同数据表的列，存储着格式化为JSON的数据。这两个列中存储的数据都是数组类型。例如：

users

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

items

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

我想编写一个查询，返回与给定的 items 行匹配的所有 users 行，使用 options 列执行匹配。规则是，如果数组中的任何值存在于两行中，则它们匹配。例如：user1 会匹配 item10（因为有 CD 选项）和 item11（因为有 XY 选项）；user2 会匹配 item10、11 和 12，因为所有这些都有 CD 或 GH。

在查看MySQL文档后，我发现 JSON_OVERLAPS 正好可以实现这个功能。但是，我正在运行 MySQL 5.7，该函数仅从 8.0.17 版本开始提供。网络上也没有太多关于此函数的讨论。

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

编辑：不幸的是，由于我们在生产环境中运行 MariaDB，升级到 MySQL 8 不是一个选项，而 MariaDB 也没有这个函数。

- Gustavo Straube

1

升级似乎更简单。 - Strawberry

@Strawberry 我以为会有人建议这样做，所以我刚刚编辑了问题，告诉大家我们在生产环境中运行MariaDB。不过还是谢谢你。 - Gustavo Straube

我发布了一个答案，我相信它也适用于MariaDB ~10.2+（我认为）... - Raymond Nijland

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Raymond Nijland · Accepted Answer

如何在MySQL 5.7上模拟JSON_OVERLAPS函数？编辑：不幸的是，升级到MySQL 8不是一个选项，因为我们在生产中运行MariaDB，它也没有这个函数。

请注意，就像Strawberry已经建议升级更容易一样，请小心。

既然这样，你仍然要求它，让我们玩一些有趣的游戏。我过去发布了一些答案来模拟MySQL 8的JSON_TABLE()，为什么我要提到这个？因为我使用这种方法来模拟MySQL 8的JSON_OVERLAPS，将模拟JSON_TABLE()的两个结果集简单地JOIN到最终结果集中。

这使得以下查询（请原谅格式）：

SELECT 
 *
FROM (

SELECT
    items.id
  ,    JSON_UNQUOTE(
       JSON_EXTRACT(items.options, CONCAT('$[', number_generator.number , ']'))
     ) AS json_options
                     
                                
FROM (

  SELECT 
   @items_row := @items_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 @items_row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    items.id
  , items.options
  , JSON_LENGTH(items.options) AS json_array_length    
FROM 
   items

) AS items 
WHERE
    number BETWEEN 0 AND  json_array_length - 1   
                    
) AS items 
INNER JOIN (

  SELECT
    users.id
  ,    JSON_UNQUOTE(
       JSON_EXTRACT(users.options, CONCAT('$[', number_generator.number , ']'))
     ) AS json_options
                     
                                
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 
    users.id
  , users.options
  , JSON_LENGTH(users.options) AS json_array_length    
FROM 
   users

) AS users 
WHERE
    number BETWEEN 0 AND  json_array_length - 1   
) AS users 
USING(json_options)

Result

| json_options | id  | id  |
| ------------ | --- | --- |
| CD           | 10  | 2   |
| CD           | 10  | 1   |
| GH           | 11  | 2   |
| GH           | 12  | 2   |
| XY           | 11  | 1   |

看到

请参见demo