首页 >数据库 >mysql教程 >如何根据键值从 MySQL 中的 JSON 对象中高效检索文本值?

如何根据键值从 MySQL 中的 JSON 对象中高效检索文本值?

Patricia Arquette
Patricia Arquette原创
2024-11-05 00:19:01801浏览

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 函数的组合。然而,这些方法都很麻烦,并且需要复杂的表达式。

JSON_TABLE() 函数 (MySQL 8.0):

MySQL 8.0 引入了 JSON_TABLE() 函数,它简化了进程:

<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替代方案:

更简单的解决方案是将 JSON 数据转换为规范化表:

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

此方法允许使用传统 SQL 技术进行高效查找:

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

以上是如何根据键值从 MySQL 中的 JSON 对象中高效检索文本值?的详细内容。更多信息请关注PHP中文网其他相关文章!

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn