问题:
在 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中文网其他相关文章!