Gestion efficace des données de type tableau dans MySQL grâce aux types JSON

1. Introduction

Le besoin de gérer des données de type tableau dans MySQL

Dans les bases de données, les données sont généralement stockées selon un schéma relationnel. Toutefois, selon les exigences de l’application, il peut être utile de stocker plusieurs valeurs dans une seule colonne. Dans ce cas, une structure de données telle qu’un « tableau » peut être utile.

Par exemple, envisagez les situations suivantes :

• Stocker plusieurs tags sélectionnés par un utilisateur.
• Enregistrer plusieurs URLs d’images pour un produit.
• Consolider l’historique ou les logs dans un seul champ.

Avantages d’utiliser le type JSON

MySQL ne propose pas de type « tableau » direct, mais en utilisant le type JSON, vous pouvez gérer des données de type tableau. Le type JSON est très flexible et offre les avantages suivants :

• Prise en charge des structures de données imbriquées.
• Manipulation directe des données au sein des requêtes.
• Gestion de plusieurs formats de données dans un seul champ.

Dans cet article, nous vous montrons comment gérer efficacement des données de type tableau dans MySQL grâce au type JSON.

2. Fondamentaux de la gestion des tableaux avec le type JSON de MySQL

Qu’est-ce que le type JSON ?

JSON (JavaScript Object Notation) est un format léger et simple d’échange de données. MySQL prend en charge un type JSON natif à partir de la version 5.7, vous permettant de stocker et de manipuler directement des données formatées en JSON dans la base de données.

Exemple : les données suivantes peuvent être stockées dans un champ de type JSON :

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Avantages et cas d’utilisation du type JSON

Les principaux avantages de l’utilisation du type JSON sont :

1. Structure de données flexible : vous pouvez gérer des données de longueur variable sans modifier le schéma relationnel.
2. Manipulation des données efficace : utilisez les fonctions intégrées de MySQL (par ex. JSON_EXTRACT, JSON_ARRAY) pour manipuler facilement les données.
3. Conception sans schéma : vous n’avez pas besoin de modifier fréquemment le schéma au fur et à mesure que les spécifications de l’application évoluent.

Cas d’utilisation :

• Attribuer plusieurs catégories à l’information d’un produit.
• Stocker des paramètres utilisateur personnalisés.
• Utiliser dans des applications web traitant des données JSON imbriquées.

3. Opérations de base sur les tableaux JSON

Création de tableaux JSON

Dans MySQL, vous pouvez utiliser la fonction JSON_ARRAY pour créer facilement des tableaux formatés en JSON. Les tableaux sont utiles lorsque vous souhaitez stocker plusieurs valeurs dans une seule colonne.

Exemple d’utilisation

Dans la requête suivante, nous créons un tableau JSON nommé tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Résultat :

["PHP", "MySQL", "JavaScript"]

Exemple appliqué

Voici un exemple de stockage d’un tableau JSON dans la base de données via une instruction INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Extraction de données à partir de tableaux JSON

Pour récupérer des données stockées dans un tableau JSON, vous utilisez la fonction JSON_EXTRACT. Elle vous permet d’extraire des éléments spécifiques à l’intérieur du tableau.

Exemple d’utilisation

Dans l’exemple suivant, nous récupérons le deuxième élément (index basé sur 0) du tableau.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Résultat :

"MySQL"

Récupération de plusieurs éléments

Vous pouvez également obtenir plusieurs éléments simultanément.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Ajouter, mettre à jour et supprimer des données

Ajout de données à un tableau

En utilisant la fonction JSON_ARRAY_APPEND, vous pouvez ajouter de nouvelles données à un tableau existant.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Résultat :

["PHP", "MySQL", "JavaScript"]

Mise à jour des données à l’intérieur d’un tableau

Vous pouvez mettre à jour un élément spécifique du tableau en utilisant JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Résultat :

["PHP", "Python", "JavaScript"]

%

Use JSON_REMOVE pour supprimer un élément spécifique dans le tableau.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Résultat:

["PHP", "JavaScript"]

4. Recherche et filtrage des tableaux JSON

Recherche de tableaux contenant des données spécifiques

Pour déterminer si un tableau JSON contient une valeur spécifique, vous utilisez la fonction JSON_CONTAINS. Cette fonction indique si le tableau JSON spécifié contient la valeur donnée.

Exemple d’utilisation

Dans l’exemple suivant, nous vérifions si le tableau JSON contient “MySQL”.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Résultat:

1  (if present)
0  (if not present)

Exemple appliqué : recherche conditionnelle

Si vous souhaitez interroger des lignes dans la base de données qui contiennent un tableau JSON contenant une valeur spécifique, vous pouvez utiliser JSON_CONTAINS dans la clause WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Cette requête récupère les lignes où la colonne tags inclut “MySQL”.

Obtenir la longueur d’un tableau

Pour obtenir le nombre d’éléments dans un tableau JSON, utilisez la fonction JSON_LENGTH. Cela est utile pour l’analyse de données ou le filtrage conditionnel.

Exemple d’utilisation

Dans l’exemple suivant, nous obtenons le nombre d’éléments du tableau.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Résultat:

3

Exemple pratique : extraire les lignes qui répondent à une condition

Pour extraire les lignes où le nombre d’éléments est égal ou supérieur à une certaine valeur, utilisez JSON_LENGTH dans la clause WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Cette requête sélectionne les lignes où la colonne tags contient deux éléments ou plus.

Exemple pratique avec requêtes conditionnelles

Vous pouvez combiner plusieurs conditions pour des recherches plus avancées. La requête suivante trouve les lignes où le tableau tags contient “JavaScript” et comporte trois éléments ou plus.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Cas d’utilisation pratiques : apprendre par des scénarios réels pour les tableaux JSON

Comment stocker les catégories de produit sous forme de tableau JSON

Sur les sites de commerce électronique, un produit peut appartenir à plusieurs catégories. Dans ces cas, utiliser un tableau JSON pour stocker les informations de catégorie peut être efficace.

Exemple : stockage des données de catégorie pour les produits

Ci-dessous un exemple de création d’une table avec une colonne JSON nommée categories et de stockage de plusieurs catégories.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Cette structure de données permet un stockage succinct même lorsqu’un produit appartient à plusieurs catégories.

Interrogation des produits appartenant à une catégorie spécifique

En tirant parti du type JSON, vous pouvez facilement rechercher des produits appartenant à des catégories spécifiques.

Exemple de requête

La requête suivante trouve tous les produits de la catégorie “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Résultat:

Laptop
Smartphone

Cette requête vous permet de récupérer de manière flexible les listes de produits par catégorie.

Exemple : filtrage par gamme de prix

Voyons comment stocker les informations de prix en JSON et interroger les produits en fonction d’une gamme de prix.

Exemple de données

Ci-dessous, nous stockons les informations de prix des produits par article en utilisant le type JSON.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Exemple de requête

Pour rechercher des produits dont le prix est de 100 000 ou plus, vous utilisez JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Résultat :

Laptop

Utilisation de JSON_TABLE pour l’expansion et des exemples de requêtes

Lorsque vous souhaitez interroger des données JSON sous forme relationnelle, vous pouvez utiliser JSON_TABLE.
Cette fonction vous permet d’étendre un tableau JSON en tant que table virtuelle.

Exemple d’utilisation

Voici un exemple où un tableau JSON est étendu et chaque catégorie est affichée en tant que ligne distincte.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Résultat :

category_name
--------------
Electronics
Computers
Mobile Devices

6. Précautions lors de l’utilisation du type JSON

Points d’optimisation des performances

Bien que le type JSON soit très flexible, sans une conception adéquate, il peut nuire aux performances de la base de données. Voici les points clés d’optimisation des performances.

1. Utilisation des index

Sous MySQL, vous ne pouvez pas définir directement un index sur une colonne JSON elle‑même, mais vous pouvez créer une colonne virtuelle et indexer une clé spécifique.

Exemple : création d’un index via une colonne virtuelle

Dans cet exemple, nous indexons la clé price dans les données JSON.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

En utilisant une colonne virtuelle, vous pouvez améliorer considérablement les performances de recherche sur les données de type JSON.

2. Éviter des structures JSON trop complexes

Les structures JSON profondément imbriquées affectent la lisibilité des requêtes et les performances. Lors de la conception des données, privilégiez une structure JSON aussi simple que possible.

Bon exemple :

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Structure à éviter :

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Comment utiliser les index efficacement

Lors de l’utilisation de colonnes virtuelles pour la création d’index, les points suivants doivent être pris en compte :

1. La colonne virtuelle doit être STORED.
2. Utilisez JSON_EXTRACT pour extraire des clés spécifiques dans la colonne virtuelle.

Par exemple, pour extraire la valeur de la clé categories et l’indexer :

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

L’importance de la validation des données

Bien que les données de type JSON soient flexibles, elles comportent également le risque de stocker des formats incorrects. Pour maintenir l’intégrité des données, utilisez les approches suivantes.

1. Utiliser les contraintes CHECK

À partir de MySQL 8.0, vous pouvez utiliser les contraintes CHECK pour valider la structure ou le contenu des données JSON.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Validation au niveau de l’application

Lors de l’insertion de données, vous devez valider le format JSON au niveau de l’application. Dans des langages de programmation comme PHP ou Python, vous pouvez utiliser des bibliothèques standard pour valider le JSON.

7. Questions fréquentes sur l’utilisation de données de style tableau dans MySQL

Q1 : MySQL fournit‑il un type de tableau ?

R1 : MySQL ne possède pas de type « tableau » direct. Cependant, vous pouvez gérer des données de type tableau en utilisant le type JSON. En utilisant le type JSON, vous pouvez stocker plusieurs valeurs dans une seule colonne et les manipuler via des requêtes.

Exemple :

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Résultat :

["value1", "value2", "value3"]

Q2 : Pouvez‑vous créer un index sur des données de type JSON ?

R2 : Vous ne pouvez pas directement appliquer un index sur un type JSON. Cependant, vous pouvez extraire des clés ou valeurs spécifiques sous forme de colonne virtuelle et ensuite créer un index sur cette colonne.

Exemple :

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Cela permet une recherche efficace des valeurs à l’intérieur des données JSON.

Q3 : Y a-t‑il une limite de taille pour les données JSON ?

A3 : Le type JSON de MySQL peut stocker jusqu’à 4 Go de données. Cependant, l’utilisation de très gros jeux JSON peut dégrader les performances, donc une conception de données appropriée est essentielle.

Recommandations :

• Stocker uniquement les données minimales nécessaires.
• Éviter les structures JSON trop imbriquées.

Q4 : Comment mettre à jour un élément spécifique dans un tableau JSON ?

A4 : Vous mettez à jour des éléments spécifiques à l’intérieur d’un tableau JSON avec la fonction JSON_SET.

Exemple :

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Résultat :

["PHP", "Python", "JavaScript"]

Q5 : Comparaison entre le type JSON et la conception de tables traditionnelles

A5 : Le type JSON offre une grande flexibilité mais diffère également dans la nature du design relationnel traditionnel.

8. Résumé

Avantages de la gestion de données de type tableau avec le type JSON dans MySQL

Cet article expliquait le type JSON pour la gestion de données de type tableau dans MySQL. Les principaux points abordés ici sont :

1. Pourquoi utiliser le type JSON : MySQL ne possède pas de type tableau direct, mais en utilisant le type JSON vous pouvez stocker plusieurs valeurs dans une seule colonne et effectuer des opérations de données flexibles.
2. Opérations JSON de base

• Appris comment créer des tableaux JSON, extraire des données, mettre à jour et supprimer des valeurs.
• Utilisation de fonctions telles que JSON_ARRAY, JSON_EXTRACT et JSON_SET pour manipuler efficacement les données de type tableau.

1. Recherche et filtrage

• Utilisation de JSON_CONTAINS pour rechercher des valeurs spécifiques à l’intérieur des tableaux JSON.
• Utilisation de JSON_LENGTH pour obtenir la longueur du tableau et effectuer un filtrage conditionnel.

1. Cas d’utilisation pratiques : Nous avons appris des scénarios d’application concrets tels que la gestion des catégories de produits et le filtrage par prix.
2. Précautions et optimisation

• Discussion sur la façon d’indexer les données JSON via des colonnes virtuelles et l’importance de valider les données JSON.

Étapes suivantes pour exploiter le type JSON

En utilisant le type JSON dans MySQL vous pouvez permettre une gestion de données plus flexible que la conception de bases de données relationnelles traditionnelle. Cependant une conception appropriée et des considérations de performance sont essentielles.

Sujets à apprendre ensuite :

• Utilisation d’index composites : conception d’index combinant des colonnes de type JSON avec des colonnes régulières.
• Fonctions JSON avancées : utilisation de JSON_MERGE, JSON_OBJECT et d’autres fonctions pour des opérations plus complexes.
• Manipulation des données au niveau de l’application : manipulation efficace des données JSON MySQL en utilisant des langages tels que PHP ou Python.

Résumé final

À travers cet article vous avez appris comment gérer efficacement des données de type tableau en utilisant le type JSON dans MySQL. En appliquant ces connaissances vous pouvez concevoir des systèmes de bases de données plus flexibles et évolutifs.