Comment utiliser les champs de données JSON dans les bases de données MySQL — SitePoint

Mon article «SQL vs NoSQL : les différences» a noté que la frontière entre les bases de données SQL et NoSQL est devenue de plus en plus floue, chaque camp adoptant les fonctionnalités de l'autre. Les bases de données MySQL 5.7+ InnoDB et PostgreSQL 9.2+ prennent toutes deux directement en charge les types de documents JSON dans un seul champ. Dans cet article, nous examinerons plus en détail l'implémentation de MySQL 8.0 JSON.

Notez que toute base de données acceptera les documents JSON en tant que blob à chaîne unique. Cependant, MySQL et PostgreSQL prennent en charge les données JSON validées dans de vraies paires clé/valeur plutôt que dans une chaîne de base.

Tout simplement parce que vous pouvez stocker du JSON…

… cela ne suit pas ce que vous devriez.

La normalisation est une technique utilisée pour optimiser la structure de la base de données. La règle de la première forme normale (1NF) stipule que chaque colonne doit contenir une valeur unique, ce qui est clairement rompu par le stockage de documents JSON à valeurs multiples.

Si vous avez des exigences claires en matière de données relationnelles, utilisez les champs à valeur unique appropriés. JSON doit être utilisé avec parcimonie en dernier recours. Les champs de valeur JSON ne peuvent pas être indexés, évitez donc de les utiliser sur des colonnes mises à jour ou recherchées régulièrement. De plus, moins d’applications client prennent en charge JSON et la technologie est plus récente, elle pourrait donc être moins stable que les autres types.

Cela dit, il existe de bons cas d'utilisation de JSON pour les données peu peuplées ou les attributs personnalisés.

Créer une table avec un champ JSON

Prenons l’exemple d’un magasin vendant des livres. Tous les livres ont un identifiant, un ISBN, un titre, un éditeur, un nombre de pages et d'autres données relationnelles claires. Supposons que vous souhaitiez ajouter un nombre illimité de balises de catégorie à chaque livre. Vous pouvez y parvenir en SQL en utilisant :

1. un étiqueter table qui stockait chaque nom de balise avec un identifiant unique, et
2. un carte de balises table avec plusieurs à plusieurs enregistrements mappant les ID de livre aux ID de balise

Cela fonctionnera, mais c'est un effort fastidieux et considérable pour une fonctionnalité mineure. Vous pouvez donc définir un Mots clés Champ JSON dans votre base de données MySQL livre tableau:

CREATE TABLE `book` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Notez que les colonnes JSON ne peuvent pas avoir de valeur par défaut, être utilisées comme clé primaire, être utilisées comme clé étrangère ou avoir un index. Vous pouvez créer index secondaires sur les colonnes virtuelles généréesmais il est plus facile et plus pratique de conserver une valeur dans un champ séparé si des index sont requis.

Ajout de données JSON

Des documents JSON entiers peuvent être transmis INSÉRER ou MISE À JOUR déclarations. Par exemple, nos balises de livre peuvent être transmises sous forme de tableau (à l'intérieur d'une chaîne) :

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON peut également être créé avec ceux-ci :

• Fonction JSON_ARRAY(), qui crée des tableaux. Par exemple:

  
  SELECT JSON_ARRAY(1, 2, 'abc');
  

• Fonction JSON_OBJECT(), qui crée des objets. Par exemple:

  
  SELECT JSON_OBJECT('a', 1, 'b', 2);
  

• Fonction JSON_QUOTE(), qui cite une chaîne comme valeur JSON. Par exemple:

  
  SELECT JSON_QUOTE('[1, 2, "abc"]');
  

• ou tu peux (CAST anyValue AS JSON).

Le Fonction JSON_TYPE() vous permet de vérifier les types de valeurs JSON. Il doit renvoyer OBJECT, ARRAY, un type scalaire (INTEGER, BOOLEAN, etc.), NULL ou une erreur. Par exemple:

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Le Fonction JSON_VALID() renvoie 1 si le JSON est valide ou 0 sinon :

SELECT JSON_TYPE('[1, 2, "abc"]');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Tenter d'insérer un document JSON non valide générera une erreur et l'ensemble de l'enregistrement ne sera pas inséré/mis à jour.

Recherche de données JSON

Le Fonction JSON_CONTAINS() accepte le document JSON recherché et un autre à comparer. Il renvoie 1 lorsqu'une correspondance est trouvée. Par exemple:

SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

Le similaire Fonction JSON_SEARCH() renvoie le chemin d'accès à la correspondance donnée ou NULL lorsqu'il n'y a pas de correspondance. Le document JSON en cours de recherche a été transmis, 'one' pour trouver la première correspondance, ou 'all' pour trouver toutes les correspondances, et une chaîne de recherche (où % correspond à n'importe quel nombre de caractères et _ correspond à un caractère de manière identique à LIKE). Par exemple:

SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

Chemins JSON

Un chemin JSON cible des valeurs et peut être utilisé pour extraire ou modifier des parties d'un document JSON. Le Fonction JSON_EXTRACT() le démontre en extrayant une ou plusieurs valeurs :

SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Toutes les définitions de chemin commencent par un $ suivi d'autres sélecteurs :

• un point suivi d'un nom, tel que $.website
• [N] où N est la position dans un tableau indexé zéro
• le .[*] le caractère générique évalue tous les membres d'un objet
• le [*] le caractère générique évalue tous les membres d'un tableau
• le prefix**suffix le caractère générique évalue tous les chemins qui commencent par le préfixe nommé et se terminent par le suffixe nommé

Les exemples suivants font référence au document JSON suivant :

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

Exemples de chemins :

• $.a Retour 1
• $.c Retour [3, 4]
• $.c[1] Retour 4
• $.d.e Retour 5
• $**.e Retour [5]

Vous pouvez extraire le nom et la première balise de votre livre table en utilisant la requête :

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

Pour un exemple plus complexe, supposons que vous ayez un utilisateur table avec les données de profil JSON. Par exemple:

identifiant nom profil

1 Craig { « e-mail »: [“craig@email1.com”, “craig@email2.com”] »twitter »: « @craigbuckler » }

2 SitePoint { « e-mail »: [] »twitter »: « @sitepointdotcom » }

Vous pouvez extraire le nom Twitter à l'aide d'un chemin JSON. Par exemple:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

Vous pouvez utiliser un chemin JSON dans la clause WHERE pour renvoyer uniquement les utilisateurs disposant d'un compte Twitter :

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Modifier une partie d'un document JSON

Il y a plusieurs Fonctions MySQL pour modifier des parties d'un document JSON en utilisant la notation de chemin. Ceux-ci inclus:

• JSON_SET(doc, path, val[, path, val]...): insère ou met à jour des données dans le document
• JSON_INSERT(doc, path, val[, path, val]...): insère des données dans le document
• JSON_REPLACE(doc, path, val[, path, val]...): remplace les données dans le document
• JSON_MERGE(doc, doc[, doc]...): fusionne deux ou plusieurs documents
• JSON_ARRAY_APPEND(doc, path, val[, path, val]...): ajoute des valeurs à la fin d'un tableau
• JSON_ARRAY_INSERT(doc, path, val[, path, val]...): insère un tableau dans le document
• JSON_REMOVE(doc, path[, path]...): supprime les données du document

Vous pouvez donc ajouter une balise « technique » à n'importe quel livre qui possède déjà une balise « JavaScript » :

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Informations complémentaires

Le manuel MySQL fournit de plus amples informations sur le Type de données JSON et le fonctions JSON associées.

Encore une fois, je vous conseille de ne pas utiliser JSON sauf si cela est absolument nécessaire. Vous pourriez émuler toute une base de données NoSQL orientée document dans MySQL, mais cela annulerait de nombreuses avantages de SQL, et autant passer à un vrai système NoSQL ! Cela dit, les types de données JSON peuvent permettre d'économiser des efforts pour répondre à des exigences de données plus obscures au sein d'une application SQL.

FAQ sur l'utilisation des données JSON dans MySQL

Pouvez-vous utiliser JSON dans MySQL ?

MySQL prend en charge les données JSON en offrant un JSON type de données pour stocker les données au format JSON dans des colonnes. À partir de MySQL 5.7.8, vous pouvez créer des tables avec des colonnes JSON, vous permettant d'insérer, de mettre à jour et d'interroger des données JSON à l'aide de SQL. MySQL fournit une gamme de fonctions JSON pour travailler avec les données JSON dans ces colonnes, permettant l'extraction, la modification et la manipulation. De plus, vous pouvez utiliser des données JSON dans des requêtes SQL, en les convertissant en données relationnelles si nécessaire à l'aide de fonctions telles que JSON_TABLE. Cependant, il est important de comprendre que MySQL est fondamentalement une base de données relationnelle et que sa prise en charge JSON est destinée à faciliter l'utilisation des données JSON dans un contexte relationnel, plutôt que d'être une base de données NoSQL JSON à part entière. Comme indiqué dans l'article ci-dessus, ce n'est pas parce que vous pouvez stocker du JSON que vous devriez le faire : la normalisation est une technique utilisée pour optimiser la structure de la base de données. La règle de la première forme normale (1NF) stipule que chaque colonne doit contenir une valeur unique, ce qui est clairement rompu par le stockage de documents JSON à valeurs multiples.

Est-il acceptable de stocker JSON dans MySQL ?

Le stockage des données JSON dans MySQL peut être approprié en fonction des besoins de votre projet. MySQL propose un JSON type de données pour le stockage structuré de données au format JSON, permettant une utilisation efficace des fonctions JSON de MySQL. Pensez à l'utiliser lorsque vous disposez de données semi-structurées ou non structurées qui ne correspondent pas à un schéma rigide. Évaluez vos exigences en matière de requêtes, votre évolutivité, vos besoins en performances et votre intégration avec d'autres composants. Si votre équipe est familiarisée avec MySQL et que votre projet s'aligne sur ses fonctionnalités JSON, cela peut être un choix pratique. Cependant, pour les ensembles de données JSON étendus ou complexes avec des exigences NoSQL spécifiques, vous devez explorer d'autres options de base de données.

Comment utiliser JSON dans une requête MySQL ?

Vous pouvez utiliser JSON dans les requêtes MySQL en utilisant les fonctions JSON de MySQL. Ces fonctions vous permettent d'extraire, de manipuler et d'interroger les données JSON stockées dans des colonnes JSON ou des chaînes au format JSON dans votre base de données. Pour accéder aux données JSON dans une colonne JSON, utilisez le -> opérateur suivi du chemin d’accès à l’élément JSON souhaité. Fonctions JSON comme JSON_EXTRACT, JSON_SETet JSON_OBJECTAGG vous permettent de filtrer, modifier, agréger et travailler avec des données JSON. Vous pouvez également filtrer les lignes en fonction des valeurs JSON à l'aide de l'outil WHERE clause. Les fonctionnalités JSON de MySQL offrent un moyen polyvalent d'interagir avec et de manipuler les données JSON directement dans vos requêtes de base de données.

Quand utiliser JSON dans MySQL ?

Vous devez utiliser JSON dans MySQL lorsque vous traitez des données semi-structurées ou non structurées, des schémas variables ou évolutifs, des structures de données imbriquées ou hiérarchiques, ou lorsque vous avez besoin de fonctionnalités de type NoSQL tout en bénéficiant des fonctionnalités de base de données relationnelles de MySQL. JSON convient aux scénarios dans lesquels la flexibilité des données, la récupération efficace des données et l'intégration avec les API basées sur JSON sont importantes. Cependant, il est crucial d'évaluer les compromis et de prendre en compte des facteurs tels que les besoins et les performances des requêtes de données, car JSON n'est peut-être pas le meilleur choix dans toutes les situations, en particulier lorsque des requêtes complexes ou des contraintes de données strictes sont impliquées. En fin de compte, la décision doit correspondre aux exigences spécifiques de votre projet et aux avantages que la flexibilité de JSON apporte à vos besoins en matière de modélisation et de stockage de données.

Comment stocker les données JSON dans MySQL ?

Pour stocker des données JSON dans MySQL, vous disposez de deux options principales. Tout d'abord, vous pouvez utiliser le JSON type de données introduit dans MySQL pour créer une table avec une colonne JSON. Cette méthode fournit un stockage structuré et de meilleures performances de requête pour les données JSON. Alternativement, vous pouvez stocker les données JSON sous forme de texte dans un fichier standard. VARCHAR ou TEXT colonne. Cette approche convient lorsque vous avez principalement besoin de stocker et de récupérer des données JSON sans opérations de base de données complexes.

to www.sitepoint.com

---

Suivez-nous sur Facebook : https://web.facebook.com/mycamer.net/
Pour recevoir les dernières nouvelles sur votre téléphone grâce à l’application Telegram, cliquez ici : https://t.me/+KMdLTc0qS6ZkMGI0
Envoyez-nous un message par Whatsapp : Whatsapp +237 650 50 31 31

---

The post Comment utiliser les champs de données JSON dans les bases de données MySQL — SitePoint first appeared on Technique de pointe.