Comment récupérer efficacement une valeur texte à partir d'un objet JSON dans MySQL en fonction d'une valeur clé ?-tutoriel mysql-php.cn

Comment récupérer efficacement une valeur texte à partir d'un objet JSON dans MySQL en fonction d'une valeur clé ?

Patricia Arquette

Libérer： 2024-11-05 00:19:01

original

816 Les gens l'ont consulté

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

Récupération d'objets à partir de clés JSON dans MySQL

Problème :
Lorsque vous travaillez avec des données JSON dans une base de données MySQL, comment pouvez-vous efficacement rechercher un objet spécifique en fonction d'une valeur de clé tout en interrogeant à l'aide d'une clé différente ?

Schéma :

Considérez l'exemple de schéma suivant :

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

Copier après la connexion

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

Copier après la connexion

Objectif :

Étant donné l'ID d'un champ, récupérer la valeur texte correspondante d'une option spécifique en fonction de sa valeur.

Précédent Solutions :

Tentatives initiales impliquaient l'utilisation d'une combinaison de fonctions JSON. Cependant, ces approches étaient lourdes et nécessitaient des expressions complexes.

Fonction JSON_TABLE() (MySQL 8.0) :

MySQL 8.0 a introduit la fonction JSON_TABLE(), qui simplifie la processus :

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

Copier après la connexion

Alternative non-JSON :

Une solution plus simple consiste à convertir les données JSON en une table normalisée :

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

Copier après la connexion

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

Copier après la connexion

Cette approche permet des recherches efficaces à l'aide de techniques SQL traditionnelles :

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

Copier après la connexion

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!