Bagaimana untuk Menyertai Jadual MySQL Berdasarkan ID yang Disimpan dalam Medan JSON?

Menggunakan Medan JSON MySQL untuk Menyertai Jadual

Menyimpan data dalam medan JSON boleh memberikan fleksibiliti, tetapi ia juga memberikan cabaran apabila melaksanakan operasi data . Dalam pertanyaan ini, objektifnya adalah untuk menyertai jadual menggunakan medan JSON yang menyimpan tatasusunan ID.

Pernyataan Masalah

Pengguna telah mencipta dua jadual, pengguna dan kumpulan_pengguna, dan medan JSON kumpulan_pengguna rekaan dalam jadual pengguna untuk menyimpan senarai ID. Pengguna ingin melakukan operasi pada medan JSON ini dan menggunakannya dalam SQL.

Secara khusus, pengguna berusaha untuk mendapatkan hasil yang menyenaraikan ID pengguna, nama pengguna, ID kumpulan pengguna dan nama kumpulan. Keputusan ini harus digabungkan berdasarkan ID yang disimpan dalam medan JSON.

Penyelesaian

Untuk mencapai ini, pengguna boleh memanfaatkan fungsi JSON_CONTAINS. Fungsi ini membolehkan pengguna menyemak sama ada dokumen JSON mengandungi nilai atau subkueri tertentu.

Kod

<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>

Penjelasan

• Fungsi JSON_CONTAINS menyemak sama ada medan JSON kumpulan_pengguna bagi setiap baris dalam jadual pengguna mengandungi id_kumpulan_pengguna yang sepadan daripada jadual kumpulan_pengguna. Ia menggunakan $ untuk menunjukkan objek peringkat atas dalam dokumen JSON.
• Ekspresi CAST menukar user_group_id kepada rentetan JSON sebelum membandingkannya dengan medan JSON.
• Hasil perbandingan digunakan untuk melakukan gabungan kiri antara jadual pengguna dan kumpulan_pengguna, memastikan semua baris pengguna disertakan, walaupun mereka tidak mempunyai kumpulan yang sepadan.
• Pertanyaan kemudian memilih lajur yang dikehendaki: user_id, user_name, user_group_id, dan nama_kumpulan.

Penyelesaian Alternatif

Jika pengguna lebih suka, mereka juga boleh menggunakan fungsi JSON_TABLE untuk mengekstrak tatasusunan ID daripada medan JSON dan mencipta meja maya. Pendekatan ini membolehkan lebih fleksibiliti dalam memanipulasi data.

Kod

<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>

Penjelasan

• The Fungsi JSON_TABLE mencipta uv jadual maya daripada medan JSON kumpulan_pengguna.
• Klausa CROSS JOIN memastikan setiap baris dalam jadual pengguna dikaitkan dengan semua baris dalam jadual maya.
• Subquery bersarang mendapatkan semula nama_kumpulan untuk setiap user_group_id dalam jadual maya uv.

Dengan memanfaatkan teknik ini, pengguna boleh menggunakan medan JSON dengan berkesan untuk menyertai jadual dan melaksanakan operasi data yang kompleks dalam MySQL.

Atas ialah kandungan terperinci Bagaimana untuk Menyertai Jadual MySQL Berdasarkan ID yang Disimpan dalam Medan JSON?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!