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

Cet article explore les détails de l'implémentation JSON dans MySQL 9.1 et poursuit l'argument selon lequel les limites entre les bases de données SQL et NOSQL sont floues dans l'article précédent "SQL vs Nosql: les différences", qui apprennent tous deux de l'autre de l'autre de l'autre de l'autre de l'autre caractéristiques. La base de données InNODB MySQL 5.7 et PostgreSQL 9.2 prennent directement en charge le stockage des types de documents JSON dans un seul champ.

Il convient de noter que toute base de données peut stocker des documents JSON en tant que blob de chaîne unique. Cependant, MySQL et PostgreSQL prennent en charge le stockage des données JSON validées en tant que paires de valeurs clés réelles au lieu de chaînes simples.

Points clés

• MySQL 5.7 Innodb Database et PostgreSQL 9.2 Soutiennent directement les types de documents JSON, mais doivent être utilisés avec prudence en raison de la limitation de l'indexation directe.
• JSON est le meilleur pour stocker des données clairsemées, des propriétés personnalisées, des hiérarchies et des situations où la flexibilité est requise. Il ne doit pas remplacer les colonnes normalisées qui interrogent fréquemment ou index des données.
• MySQL fournit diverses fonctions pour créer, vérifier, rechercher et modifier les objets JSON. Ces fonctions incluent json_array (), json_object (), json_quote (), json_type (), json_valid (), json_contains (), json_search (), et json_set () et json_merge_ pour mettre à jour les documents JSON à l'aide de patch de path (), etc. fonction.
• MySQL 9.1 prend en charge l'indexation des fonctions des colonnes générées dérivées des données JSON, permettant une requête efficace d'éléments JSON spécifiques.
• Bien que MySQL prenne en charge JSON, il s'agit toujours d'une base de données relationnelle. La surutilisation de JSON peut compenser les avantages de SQL.

Le stockage des documents JSON dans la colonne MySQL JSON ne signifie pas que vous devez le faire

La normalisation est une technologie utilisée pour optimiser la structure de la base de données. La première règle de formule normale (1NF) stipule que chaque colonne doit contenir une seule valeur et stocker des documents JSON à valeur multiple viole évidemment cette règle.

Si vos données ont des exigences de données relationnelles claires, veuillez utiliser le champ de valeur unique approprié. JSON doit être utilisé avec prudence en dernier recours. Les champs de valeur JSON ne peuvent pas être indexés directement, donc évitez de les utiliser sur des colonnes fréquemment mises à jour ou recherchées.

L'indice de fonction des colonnes générées dérivées des données JSON vous permet d'indexer certaines parties d'objets JSON, améliorant ainsi les performances de la requête.

c'est-à-dire que JSON a de bons cas d'utilisation pour les données clairsemées ou les propriétés personnalisées.

Créez une table avec la colonne de type de données JSON

Considérez un magasin vendant des livres. Tous les livres ont ID, ISBN, titre, éditeur, nombre de pages et autres données relationnelles claires.

Maintenant, si vous souhaitez ajouter autant de balises de catégorie à chaque livre. Vous pouvez l'implémenter dans SQL en utilisant la méthode suivante:

1. une table de balise qui stocke chaque nom de balise et son identifiant unique;
Une table de mappage de balises contenant beaucoup à de nombreux enregistrements, cartographiant l'ID de livre sur l'ID de balise.

Cette méthode fonctionne, mais pour une fonction secondaire, elle est trop lourde et nécessite un effort considérable. Par conséquent, vous pouvez définir un champ MySQL JSON pour la balise dans la table du livre de la base de données MySQL:

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

Les colonnes JSON MySQL ne peuvent pas avoir de valeurs par défaut, ne peuvent pas être utilisées comme clés principales, ne peuvent pas être utilisées comme clés étrangères et ne peuvent pas avoir d'index directs.

Cependant, avec MySQL 9.1, vous pouvez créer des index de fonction sur des colonnes générées dérivées de données JSON, ce qui permet d'indexer des éléments spécifiques dans les documents JSON. Ces colonnes générées peuvent être virtuelles ou stockées et indexées sous forme d'indices auxiliaires.

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

Ajouter des données JSON

L'ensemble du document JSON peut être passé dans une instruction INSERT ou UPDATE, ce qui facilite le déplacement de JSON vers MySQL pour le stockage et le fonctionnement.

Par exemple, nos balises de livre peuvent être passées sous forme de tableau (dans une chaîne):

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

Vous pouvez également créer JSON en utilisant les fonctions suivantes:

JSON_Array () Fonction, utilisée pour créer des tableaux. Par exemple: - return [1, 2, "ABC"]: SELECT JSON_Array (1, 2, 'ABC');
json_object () Fonction, utilisée pour créer des objets. Par exemple: - return {"a": 1, "b": 2}: select json_object ('a', 1, 'b', 2);
json_quote () Fonction, utilisée pour référencer les chaînes comme valeurs JSON. Par exemple: - retourne "[1, 2," ABC "]": SELECT JSON_QUOTE ('[1, 2, "ABC"]');
CAST (AnyValue as JSON) Fonction Pour convertir les valeurs en types JSON pour assurer la validité: sélectionnez Cast ('{"A": 1, "B": 2}' As JSON);

JSON_TYPE () vous permet de vérifier le type de valeur JSON. Il doit renvoyer l'objet, le tableau, le type scalaire (entier, booléen, etc.), null ou erreur. Par exemple:

<code class="language-sql">-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');</code>

JSON_VALID () Renvoie 1 Si JSON est valide, sinon 0 sera retourné:

<code class="language-sql">-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');</code>

Tenter d'insérer un document JSON non valide lancera une erreur et l'intégralité de l'enregistrement ne sera pas inséré / mis à jour.

Rechercher des documents JSON dans la colonne MySQL JSON

En utilisant des fonctions JSON MySQL telles que la fonction JSON_CONTAINS (), vous pouvez vérifier si le document JSON contient une valeur spécifique. Renvoie 1 lorsqu'un match est trouvé. Par exemple:

<code class="language-sql">-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');</code>

JSON_SEARCH () Renvoie le chemin d'accès à la valeur dans le document JSON. Renvoie Null lorsqu'il n'y a pas de correspondance.

Vous pouvez également spécifier si vous devez trouver toutes les matchs ou une seule correspondance en passant les drapeaux "un" et "tous" et les chaînes de recherche (où% correspond à n'importe quel nombre de caractères, _ correspond à un personnage comme). Par exemple:

<code class="language-sql">-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;</code>

JSON_TABLE () Convertit les données JSON en formats relationnels pour faciliter la question de l'interrogation:

<code class="language-sql">SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;</code>

JSON PATH

MySQL JSON Query Utilisation de la fonction JSON_Extract () peut récupérer des valeurs spécifiques à partir de documents JSON en fonction du chemin spécifié.

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

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

• Une période suivie d'un nom, comme $ .website
• [n], où n est la position dans le tableau d'index zéro
• . [*] Tous les membres de l'objet d'évaluation des caractères génériques
• [*] Tableau d'évaluation de la carte générique Tous les membres
• Prefix ** suffixe que le joker évalue tous les chemins qui commencent par un préfixe nommé et se terminent par un suffixe nommé

L'exemple suivant fait référence au document JSON suivant:

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

Exemple de chemin:

• $. Un retour 1
• $. C retourner [3, 4]
• $. C [1] Retour 4
• $. D.E retour 5
• $ **. E retourner [5]

Vous pouvez utiliser JSON pour extraire les fonctions MySQL pour extraire efficacement le nom et la première balise de votre table de livre:

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

Pour des exemples plus complexes, supposons que vous ayez un tableau utilisateur contenant des données de fichier de configuration JSON. Par exemple:

id	name	profile
1	Craig	{ "email": ["craig@email1.com", "craig@email2.com"], "twitter": "@craigbuckler" }
2	SitePoint	{ "email": [], "twitter": "@sitepointdotcom" }

Vous pouvez utiliser le chemin JSON pour extraire le nom Twitter. Par exemple:

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

Vous pouvez utiliser le chemin JSON dans la clause WHERE pour retourner uniquement les utilisateurs avec un compte Twitter:

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

Modifier la partie du document JSON

Il existe plusieurs fonctions MySQL qui peuvent modifier une partie d'un document JSON en utilisant la notation de chemin. Ces fonctions incluent:

• json_set (doc, path, val [, path, val]…): insérer ou mettre à jour les données dans le document. Mettre à jour les tags de set de livres = JSON_SET (Tags, '$ [0]', 'TAGE MISE À JOUR');
• json_insert (doc, path, val [, path, val]…): insérer des données dans le document sans écraser les valeurs existantes. Mettre à jour les bornes de livre tags = json_insert (tags, '$ [0]', 'new tag');
• JSON_REPLACE (DOC, PATH, Val [, Path, Val]…): Remplacez les données dans le document. Mettre à jour le jeu de livres tags = json_replace (tags, '$ [0]', 'Remplaced tag');
• json_merge_patch (doc, doc [, doc]…): fusionner deux documents JSON ou plus et remplacer les clés existantes par les valeurs des documents suivants. Mise à jour des tags de jeu de livres = JSON_MERGE_PATCH (TAGS, '["Technical"]') où JSON_SEARCH (Tags, 'One', 'JavaScript') n'est pas nul;
• json_array_append (doc, path, val [, path, val]…): ajoutez la valeur à la fin du tableau. Mise à jour des balises de livre = JSON_ARRAY_APPEND (Tags, '$', 'new tag');
• json_array_insert (doc, path, val [, path, val]…): insérer les valeurs à un emplacement spécifique dans le tableau JSON. Mise à jour des tags de set de livre = JSON_Array_insert (tags, «$ [0]», «balise insérée»);
• json_remove (doc, path [, path]…): supprimer les données du document. Mettre à jour les tags de jeu de livres = JSON_Remove (tags, '$ [1]');
• JSON_PRETTY (Val): Embellir les documents JSON pour les rendre plus faciles à lire. SELECT JSON_PRETTY ('{"Name": "SitePoint", "Tags": ["MySQL", "JSON"]}');

Par exemple, si vous souhaitez ajouter une balise "technique" à n'importe quel livre qui a déjà une balise "javascript", vous pouvez utiliser la fonction json_merge_patch ():

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

Plus d'informations

La documentation MySQL fournit des informations détaillées sur les types de données JSON MySQL et les fonctions JSON associées.

Rappelez à nouveau, n'utilisez pas JSON sauf si nécessaire. Vous pouvez simuler l'intégralité de la base de données NOSQL orientée document dans MySQL, mais cela compensera bon nombre des avantages de SQL, et vous pourriez aussi bien passer à un véritable système NOSQL!

c'est-à-dire que pour les exigences de données plus vagues dans les applications SQL, les types de données JSON peuvent économiser des efforts.

Des questions fréquemment posées sur l'utilisation des données JSON dans MySQL

Pouvez-vous utiliser JSON dans MySQL?

MySQL prend en charge JSON en fournissant un type de données JSON, qui est utilisé pour stocker les données au format JSON dans des colonnes. En commençant par 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 les données JSON à l'aide de SQL. MySQL fournit une série de fonctions JSON pour traiter les données JSON dans ces colonnes, afin que les données JSON puissent être extraites, modifiées et manipulées.

De plus, vous pouvez utiliser des données JSON dans les requêtes SQL et les convertir en données relationnelles à l'aide de fonctions telles que JSON_TABLE en cas de besoin. Cependant, il est important de comprendre que MySQL est fondamentalement une base de données relationnelle avec le support de type de données JSON conçu pour faciliter le traitement des données JSON dans un contexte relationnel plutôt que de devenir une base de données JSON NOSQL à part entière.

Comme mentionné ci-dessus, ce n'est pas parce que vous pouvez stocker JSON que vous devez le faire: la normalisation est une technique pour optimiser la structure de la base de données. La première règle de formule normale (1NF) indique que chaque colonne doit contenir une seule valeur et le stockage des documents JSON à valeur multiple viole cette règle.

Est-il acceptable de stocker JSON dans MySQL?

stocker json dans mysql est ok si:

• Données semi-structurées ou dynamiques, non adaptées aux modèles stricts.
• Propriétés personnalisées, où la conception des relations est inefficace.
• intégré à l'API JSON pour stocker des charges utiles ou des journaux.

Cependant, JSON ne doit pas remplacer le stockage relationnel normalisé des données structurées et fréquemment interrogées. Bien que MySQL 9.1 améliore la fonctionnalité JSON avec des fonctionnalités telles que l'indexation des fonctions et JSON_TABLE, les opérations JSON peuvent toujours provoquer des frais généraux pour de grands ensembles de données ou des requêtes complexes.

Comment utiliser JSON dans la requête MySQL?

Vous pouvez utiliser JSON dans les requêtes MySQL en utilisant la fonction JSON de MySQL. Ces fonctions vous permettent d'extraire, de manipuler et d'interroger les données JSON stockées dans des chaînes de format JSON dans des colonnes ou bases de données JSON. Pour accéder aux données JSON dans une colonne JSON, utilisez l'opérateur - & gt; suivi du chemin d'accès à l'élément JSON souhaité.

json_extract, json_set et json_objectagg et autres fonctions json vous permettent de filtrer, modifier, agréger et traiter les données JSON. Vous pouvez également utiliser la clause où filtrer les lignes en fonction des valeurs JSON. La fonctionnalité JSON de MySQL fournit un moyen flexible d'interagir et de manipuler les objets JSON directement dans les requêtes de base de données.

Quand dois-je utiliser JSON dans MySQL?

Vous devriez utiliser JSON dans MySQL dans les circonstances suivantes:

1. Données semi-structurées: utilisez JSON lors de la gestion des champs imprévisibles ou clairsemés tels que les propriétés personnalisées.
2. Mode dynamique: JSON offre une flexibilité lorsque les exigences de données changent fréquemment.
3. Hydraft ou données imbriquées: JSON prend en charge les données avec les relations parent-enfant ou les tableaux.
4. Intégration de l'API: Stockez les charges utiles, les réponses ou les journaux en tant que documents JSON.

Cependant, évitez d'utiliser JSON quand:

• Les champs fréquemment interrogés qui nécessitent l'indexation (l'indexation des fonctions peut aider, mais la conception des relations est généralement plus rapide).
• Des données de relation strictes standardisées sont nécessaires.
• Les requêtes complexes sur les chemins JSON peuvent dégrader les performances.

Comment stocker les données JSON dans MySQL?

Pour stocker les données JSON dans MySQL, vous avez deux options principales. Tout d'abord, vous pouvez utiliser le type de données JSON introduit dans MySQL pour créer une table avec des colonnes 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 en tant que texte dans les colonnes VARCHAR ou Texte ordinaires. Cette méthode s'applique lorsqu'il est principalement nécessaire de stocker et de récupérer les données JSON sans opérations de base de données complexes.

Comment indexer les données JSON dans MySQL?

Bien que vous ne puissiez pas indexer directement les colonnes JSON, MySQL vous permet de créer des index de fonction sur les colonnes générées dérivées de valeurs JSON.

par exemple, pour indexer le premier élément du tableau JSON:

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

Cette méthode améliore les performances de la requête pour les chemins JSON fréquemment accessibles.

Pour les données JSON, devriez-vous utiliser la base de données MySQL ou NOSQL?

Cela dépend des exigences de votre projet:

• Si vous avez besoin de stockage relationnel, de traitement occasionnellement des données semi-structurées, des propriétés personnalisées ou des données hiérarchiques dans des modèles relationnels, sélectionnez MySQL.
• Si votre projet implique beaucoup de stockage JSON, de schémas flexibles et d'opérations basées sur des documents comme principaux cas d'utilisation, choisissez des bases de données NoSQL (telles que MongoDB).

La prise en charge JSON de MySQL est idéale pour les charges de travail hybrides, mais ce n'est pas un remplacement complet pour les bases de données NoSQL spécifiquement pour le stockage de documents.

Comment extraire des valeurs spécifiques du champ MySQL JSON?

Pour extraire une valeur spécifique du champ MySQL JSON, utilisez la fonction JSON_Extract () ou l'opérateur Abréviation - & gt;

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

Comment interroger et filtrer les données dans les champs MySQL JSON?

Pour interroger et filtrer les données stockées dans les champs MySQL JSON, vous pouvez utiliser des fonctions telles que JSON_CONTAINS () et JSON_SEARCH (). Vous pouvez également utiliser JSON_Extract () pour récupérer des valeurs spécifiques pour un filtrage supplémentaire.

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

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!