如何根據鍵值從 MySQL 中的 JSON 物件中高效檢索文字值？-mysql教程-PHP中文網

如何根據鍵值從 MySQL 中的 JSON 物件中高效檢索文字值？

Patricia Arquette

發布： 2024-11-05 00:19:01

原創

817 人瀏覽過

How to Efficiently Retrieve a Text Value from a JSON Object in MySQL Based on a Key Value?

從MySQL 中的JSON 鍵檢索物件

問題：
在MySQL 資料庫中處理JSON 資料時，如何高效在使用不同鍵查詢時根據鍵值搜尋特定物件？

架構：

考慮以下範例架構：

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

登入後複製

目標：

目標：

目標：給定欄位ID，根據其值檢索特定選項對應的文字值。

上一頁解：

初步嘗試涉及使用 JSON 函數的組合。然而，這些方法都很麻煩，並且需要複雜的表達式。

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

登入後複製

JSON_TABLE() 函數(MySQL 8.0)：

MySQL 8.0 引入了JSON_TABLE() 函數，它簡化了處理：

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

登入後複製

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

登入後複製

非JSON 替代方案：更簡單的解決方案是將JSON 資料轉換為規範化表：這種方法允許使用傳統SQL技術進行高效查找：

以上是如何根據鍵值從 MySQL 中的 JSON 物件中高效檢索文字值？的詳細內容。更多資訊請關注PHP中文網其他相關文章！