Comment gérer les données de tableau dans MySQL à l’aide de JSON (Guide complet avec exemples)

1. Introduction

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

Les bases de données stockent généralement les données selon les principes de conception relationnelle. Cependant, selon les exigences de l’application, il peut arriver que vous souhaitiez stocker plusieurs valeurs dans une seule colonne. Dans de telles situations, une structure de données similaire à un « tableau » devient utile.

Par exemple, considérez les scénarios suivants :

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

Avantages de l’utilisation du type de données JSON

MySQL ne fournit pas de « type tableau » direct, mais en utilisant le type de données JSON, vous pouvez gérer des structures de 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.
• Permet une manipulation facile des données dans les requêtes.
• Autorise la gestion de plusieurs formats de données dans un seul champ.

Dans cet article, nous présenterons comment gérer efficacement les données de type tableau dans MySQL en utilisant le type de données JSON.

2. Connaissances de base sur la gestion des tableaux avec MySQL JSON

Qu’est-ce que le type de données JSON ?

JSON (JavaScript Object Notation) est un format d’échange de données léger et simple. Dans MySQL, la prise en charge native du JSON a été introduite à partir de la version 5.7, permettant de stocker et de manipuler directement des données au format JSON au sein de la base de données.

Exemple : ci‑dessous un exemple de données pouvant être stockées dans une colonne JSON.

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

Avantages et cas d’utilisation du type JSON

Les principaux bénéfices de l’utilisation du type JSON sont les suivants :

1. Structure de données flexible : vous pouvez gérer des données de longueur variable sans modifier le schéma relationnel.
2. Manipulation efficace des données : vous pouvez facilement manipuler les données à l’aide des fonctions dédiées de MySQL (par ex. JSON_EXTRACT, JSON_ARRAY).
3. Conception sans schéma : il n’est pas nécessaire de modifier fréquemment le schéma lorsque les exigences de l’application évoluent.

Exemples de cas d’utilisation :

• Attribuer plusieurs catégories aux informations produit.
• Enregistrer des paramètres utilisateur personnalisés.
• Utiliser des données JSON imbriquées dans les applications web.

3. Opérations de base sur les tableaux JSON

Création d’un tableau JSON

Dans MySQL, vous pouvez créer facilement un tableau JSON à l’aide de la fonction JSON_ARRAY. Les tableaux sont utiles lorsqu’on stocke plusieurs valeurs dans une seule colonne.

Exemple

La requête suivante crée un tableau JSON nommé tags.

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

Résultat :

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

Exemple pratique

L’exemple suivant montre comment stocker un tableau JSON dans la base de données à l’aide d’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 d’un tableau JSON

Pour récupérer les données stockées dans un tableau JSON, utilisez la fonction JSON_EXTRACT. Cette fonction permet d’extraire facilement des éléments spécifiques du tableau.

Exemple

L’exemple suivant récupère le deuxième élément du tableau (l’index commence à 0).

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

Résultat :

"MySQL"

Récupération de plusieurs éléments

Vous pouvez également récupérer plusieurs éléments en une seule fois.

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

Ajout, mise à jour et suppression de données

Ajout de données à un tableau

Vous pouvez utiliser la fonction JSON_ARRAY_APPEND pour 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 de données dans un tableau

Vous pouvez mettre à jour un élément spécifique du tableau à l’aide de la fonction JSON_SET.

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

Résultat :

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

Suppression de données d’un tableau

Vous pouvez supprimer un élément spécifique du tableau en utilisant la fonction JSON_REMOVE.

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 vérifier si un tableau JSON contient des données spécifiques, utilisez la fonction JSON_CONTAINS. Cette fonction détermine si une valeur spécifiée existe dans le tableau JSON.

Exemple

L’exemple suivant vérifie 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 pratique : recherche conditionnelle

Pour rechercher des lignes contenant une valeur spécifique dans un tableau JSON d’une table de base de données, utilisez 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 contient « MySQL ».

Obtention de la longueur d’un tableau

Pour obtenir le nombre d’éléments d’un tableau JSON, utilisez la fonction JSON_LENGTH. Cette fonction renvoie le nombre d’éléments du tableau et est utile pour l’analyse de données et la logique conditionnelle.

Exemple

L’exemple suivant récupère le nombre d’éléments du tableau.

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

Résultat :

3

Exemple pratique : extraction de lignes répondant à une condition spécifique

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

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

Cette requête récupère les lignes où la colonne tags contient deux éléments ou plus.

Exemple de requête conditionnelle avancée

Vous pouvez combiner plusieurs conditions pour des recherches plus avancées. La requête suivante recherche les lignes où le tableau tags contient « JavaScript » et possède trois éléments ou plus.

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

5. Exemples pratiques : utilisation des tableaux JSON dans des cas réels

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

Sur les sites de commerce électronique et systèmes similaires, un produit peut appartenir à plusieurs catégories. Dans ces cas, vous pouvez stocker efficacement les informations de catégorie en utilisant un tableau JSON.

Exemple : stockage des données de catégorie de produit

Voici un exemple de création d’une colonne JSON nommée categories dans une table produit 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 maintient les données concises même lorsqu’un produit appartient à plusieurs catégories.

Requête pour extraire les produits d’une catégorie spécifique

En tirant parti du type de données JSON, vous pouvez facilement rechercher les produits appartenant à une catégorie spécifique.

Exemple de requête

La requête suivante récupère tous les produits de la catégorie « Électronique ».

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

Résultat :

Laptop
Smartphone

Cette requête facilite la récupération des listes de produits par catégorie de manière flexible.

Exemple : filtrage par fourchette de prix

Voyons comment stocker des données JSON incluant des informations de prix, puis rechercher des produits en fonction d’une fourchette de prix.

Données d’exemple

L’exemple suivant stocke les informations de prix par produit 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, utilisez la fonction JSON_EXTRACT.

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

Résultat :

Laptop

Expansion du JSON avec JSON_TABLE et exemple de requête

Si vous souhaitez interroger des données JSON sous forme relationnelle, la fonction JSON_TABLE est très utile. Cette fonction vous permet d’étendre un tableau JSON en une table virtuelle.

Exemple

L’exemple suivant étend un tableau JSON et affiche chaque catégorie comme une 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. Considérations importantes lors de l’utilisation du type de données JSON

Conseils d’optimisation des performances

Bien que le type JSON soit flexible, une mauvaise conception peut nuire aux performances de la base de données. Voici les points clés d’optimisation.

1. Utilisation des index

Dans MySQL, vous ne pouvez pas créer d’index directement sur une colonne JSON, mais vous pouvez créer une colonne générée et indexer une clé spécifique.

Exemple : création d’un index à l’aide d’une colonne générée

Dans l’exemple suivant, la clé price contenue dans les données JSON est utilisée comme cible d’index.

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

En utilisant une colonne générée, vous pouvez améliorer considérablement les performances de recherche sur les données JSON.

2. Éviter les structures JSON trop complexes

Les structures JSON fortement imbriquées peuvent réduire la lisibilité des requêtes et les performances. Lors de la conception des données, choisissez la structure JSON la plus simple possible.

Bon exemple :

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

Exemple à éviter :

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

Comment exploiter les index

Lors de l’indexation à l’aide de colonnes générées, gardez les points suivants à l’esprit :

1. La colonne générée doit être STORED.
2. Utilisez la fonction JSON_EXTRACT pour extraire une clé spécifique en tant que colonne générée.

Par exemple, pour extraire le premier élément de la clé categories et créer un index, procédez comme suit.

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

Comme les données JSON sont flexibles, il est également plus facile de stocker des données dans un format incorrect. Pour maintenir l’intégrité des données, utilisez les approches suivantes.

1. Utiliser les contraintes CHECK

Dans MySQL 8.0 et versions ultérieures, vous pouvez valider la structure et le contenu du JSON à l’aide de contraintes CHECK.

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, il est recommandé de valider le format JSON côté application. Des langages de programmation comme PHP et Python peuvent valider le JSON à l’aide de leurs bibliothèques standard.

7. Questions fréquentes sur l’utilisation des tableaux dans MySQL

Q1 : MySQL possède-t-il un type de données tableau ?

A1 : MySQL n’a pas de « type de tableau » direct. Cependant, en utilisant le type JSON, vous pouvez gérer des structures de données similaires à des tableaux. Avec le type JSON, vous pouvez stocker plusieurs valeurs dans une colonne et les manipuler via des requêtes.

Exemple :

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Résultat :

["Value 1", "Value 2", "Value 3"]

Q2 : Pouvez-vous créer un index sur des données JSON ?

A2 : Vous ne pouvez pas créer d’index directement sur le type JSON lui‑même. Cependant, vous pouvez extraire une clé ou une valeur spécifique dans une colonne générée et créer un index sur cette colonne générée.

Exemple :

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

Cela vous permet de rechercher efficacement des valeurs à l’intérieur des données JSON.

Q3 : Existe‑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 documents JSON extrêmement volumineux peut réduire les performances, il faut donc concevoir vos données avec soin.

Recommandations :

• Stockez uniquement les données minimales requises.
• Évitez les structures JSON fortement imbriquées.

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

A4 : Vous pouvez mettre à jour un élément spécifique dans un tableau en utilisant 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 du type JSON vs. conception de table normale

A5 : Bien que le type JSON soit très flexible, il possède des caractéristiques différentes par rapport à la conception traditionnelle de bases de données relationnelles.

8. Résumé

Avantages de l’utilisation du type de données JSON pour les opérations sur les tableaux dans MySQL

Dans cet article, nous avons expliqué le type de données JSON, qui est utile lorsqu’on travaille avec des données de type tableau dans MySQL. Voici un résumé des points clés abordés :

1. Pourquoi utiliser le type JSON MySQL n’a pas de type tableau direct, mais en utilisant le type JSON, vous pouvez stocker plusieurs valeurs dans une colonne et obtenir une manipulation flexible des données.

2. Opérations JSON de base

• Nous avons vu comment créer des tableaux JSON, extraire des données, mettre à jour des données et les supprimer.
• En utilisant des fonctions pratiques telles que JSON_ARRAY, JSON_EXTRACT et JSON_SET, vous pouvez manipuler efficacement les données de type tableau.

1. Recherche et filtrage

• Comment rechercher des données contenant des valeurs spécifiques à l’aide de JSON_CONTAINS.
• Comment récupérer le nombre d’éléments avec JSON_LENGTH et effectuer un filtrage conditionnel.

1. Exemples pratiques À travers des cas d’utilisation réels tels que la gestion des catégories de produits et le filtrage par prix, nous avons appris des méthodes concrètes d’application des tableaux JSON dans les applications.

2. Considérations et optimisation

• Nous avons expliqué comment mettre en place l’indexation à l’aide de colonnes générées et souligné l’importance de valider les données JSON.

Prochaines étapes lors de l’utilisation du type de données JSON

En utilisant le type JSON dans MySQL, vous pouvez gérer les données plus flexiblement que dans les conceptions de bases de données relationnelles traditionnelles. Cependant, une bonne conception et des considérations de performance sont essentielles.

Sujets à apprendre ensuite :

• Utilisation des index composites Conception d’index combinant les données JSON avec des colonnes normales.
• Utilisation des fonctions JSON avancées Effectuer des opérations plus complexes avec des fonctions telles que JSON_MERGE et JSON_OBJECT.
• Gestion des données au niveau de l’application Comment manipuler efficacement les données JSON de MySQL en utilisant PHP ou Python.

Résumé

Grâce à cet article, vous devriez maintenant comprendre comment gérer efficacement des données de type tableau en utilisant le type de données JSON de MySQL. En appliquant ces connaissances, vous pouvez concevoir des bases de données plus flexibles et évolutives.