如何根据 JSON 字段中存储的 ID 连接 MySQL 表？

使用 MySQL JSON 字段连接表

将数据存储在 JSON 字段中可以提供灵活性，但在执行数据操作时也带来了挑战。在此查询中，目标是使用存储 ID 数组的 JSON 字段连接表。

问题陈述

用户创建了两个表， user user_group，以及用户表中用于存储 ID 列表的虚构 user_groups JSON 字段。用户想要对此 JSON 字段执行操作并在 SQL 中使用它们。

具体来说，用户寻求获取列出用户 ID、用户名、用户组 ID 和组名称的结果。应根据 JSON 字段中存储的 ID 连接这些结果。

解决方案

要实现此目的，用户可以利用 JSON_CONTAINS 函数。此函数使用户能够检查 JSON 文档是否包含特定值或子查询。

代码

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')</code>

说明

• JSON_CONTAINS 函数检查 user 表中每一行的 user_groups JSON 字段是否包含 user_group 表中相应的 user_group_id 。它使用 $ 表示 JSON 文档中的顶级对象。
• CAST 表达式将 user_group_id 转换为 JSON 字符串，然后将其与 JSON 字段进行比较。
• 比较的结果用于在 user 和 user_group 表之间执行左连接，确保包含所有用户行，即使它们没有匹配的组。
• 然后查询选择所需的列：user_id、user_name、user_group_id，和 group_name。

替代解决方案

如果用户愿意，他们还可以使用 JSON_TABLE 函数从 JSON 字段中提取 ID 数组并创建一个虚拟表。这种方法可以更灵活地操作数据。

代码

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       uv.user_group_id
       (
           SELECT 
               g.group_name
           FROM user_group g
           WHERE g.user_group_id = uv.user_group_id
       ) AS group_name
   FROM user u
   CROSS JOIN JSON_TABLE(
       u.user_groups,
       '$[*]' COLUMNS (user_group_id INT PATH '$')
   ) uv</code>

解释

• JSON_TABLE 函数根据 user_groups JSON 字段创建虚拟表 uv。
• CROSS JOIN 子句确保 user 表中的每一行都与虚拟表中的所有行关联。
• 嵌套子查询检索 uv 虚拟表中每个 user_group_id 的 group_name。

通过利用这些技术，用户可以有效地利用 JSON 字段在 MySQL 中连接表并执行复杂的数据操作。

以上是如何根据 JSON 字段中存储的 ID 连接 MySQL 表？的详细内容。更多信息请关注PHP中文网其他相关文章！