Bagaimana untuk Mendapatkan Nilai Teks dengan Cekap daripada Objek JSON dalam MySQL Berdasarkan Nilai Utama?

Mengambil semula Objek daripada JSON Keys dalam MySQL

Masalah:
Apabila bekerja dengan data JSON dalam pangkalan data MySQL, bagaimana anda boleh cekap cari objek tertentu berdasarkan nilai kunci semasa membuat pertanyaan menggunakan kunci lain?

Skema:

Pertimbangkan contoh skema berikut:

<code class="sql">CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;</code>

<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, ...]');</code>

Objektif:

Memandangkan ID medan, dapatkan semula nilai teks yang sepadan bagi pilihan tertentu berdasarkan nilainya.

Sebelumnya Penyelesaian:

Percubaan awal yang terlibat menggunakan gabungan fungsi JSON. Walau bagaimanapun, pendekatan ini menyusahkan dan memerlukan ungkapan yang kompleks.

Fungsi JSON_TABLE() (MySQL 8.0):

MySQL 8.0 memperkenalkan fungsi JSON_TABLE(), yang memudahkan proses:

<code class="sql">SELECT field_options.* 
FROM fields 
CROSS JOIN 
JSON_TABLE(fields.options, 
  '$[*]' 
COLUMNS(
  text TEXT PATH '$.text',
  value TEXT PATH '$.value'
 )
) AS field_options
WHERE field_options.value = 1;

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+</code>

Alternatif Bukan JSON:

Penyelesaian yang lebih mudah ialah menukar data JSON kepada jadual ternormal:

<code class="sql">CREATE TABLE `field_options` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field_id` int(10) unsigned NOT NULL,
  `text` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`field_id`) REFERENCES `fields`(`id`)
);</code>

<code class="sql">INSERT INTO `field_options` (field_id, text, value) VALUES
(
  1, 'Grass', '1'),
  (1, 'Synthetic (New Type - Soft)', '2'),
  ...
);</code>

Pendekatan ini membolehkan carian cekap menggunakan teknik SQL tradisional:

<code class="sql">SELECT *
FROM `field_options`
WHERE value = '1';</code>

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Nilai Teks dengan Cekap daripada Objek JSON dalam MySQL Berdasarkan Nilai Utama?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!