如何根據 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是否包含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 欄位建立。
• CROSS JOIN 子句確保 user 表中的每一行都與虛擬表中的所有行關聯。
• 巢狀子查詢會擷取 uv 虛擬表中每個 user_group_id 的 group_name。

透過利用這些技術，使用者可以有效地利用 JSON 欄位在 MySQL 中連接表格並執行複雜的資料操作。

以上是如何根據 JSON 欄位中儲存的 ID 連接 MySQL 表？的詳細內容。更多資訊請關注PHP中文網其他相關文章！