如何根据键值高效检索MySQL中的JSON对象？

根据 MySQL JSON 中的键值查找对象

在 MySQL 中处理 JSON 数据时，根据特定键值查找对象可能具有挑战性。这是因为传统方法需要了解数组索引或复杂的提取技术。

JSON_TABLE() 来救援

幸运的是，MySQL 8.0 引入了 JSON_TABLE() 函数，这简化了这个过程。考虑以下架构：

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;

数据插入为：

INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);

要通过提供键“1”来搜索字符串“Grass”，您可以使用以下查询:

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

替代：传统表结构

虽然 JSON_TABLE() 提供了解决方案，重复使用会很麻烦。另一种方法是将数据存储在传统的表结构中，其中文本和值具有单独的列，如下所示：

CREATE TABLE field_options (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  text varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  value varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

将数据插入为：

INSERT INTO field_options (text, value) VALUES
('Grass', '1'),
('Synthetic (New Type - Soft)', '2'),
('Synthetic  (Old Type - Hard)', '3'),
('Gravel', '5'),
('Clay', '6'),
('Sand', '7'),
('Grass/Synthetic Mix', '8');

使用此结构，搜索变得更简单：

SELECT * FROM field_options WHERE value = '1';

这种方法消除了复杂的 JSON 查询的需要，使数据检索更加简单。

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