[*]

Mon article « SQL vs NoSQL: les différences«Nous avons constaté que la frontière entre les bases de données SQL et NoSQL est de plus en plus floue, chaque entrepôt prenant en charge les fonctions de l’autre. Les bases de données MySQL 5.7+ InnoDB et PostgreSQL 9.2+ prennent tous deux directement en charge les types de documents JSON dans un seul champ. Dans cet article, nous examinerons de plus près l’implémentation JSON de MySQL 8.0.

Notez que chaque base de données accepte les documents JSON en tant que blob de 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 simple chaîne.

Juste parce que vous pouvez stocker JSON …

… Cela ne veut pas dire que vous devriez.

La normalisation est une technique utilisée pour optimiser la structure de la base de données. La règle First Normal Form (1NF) stipule que chaque colonne doit contenir une seule valeur, ce qui est clairement rompu en stockant des documents JSON avec plusieurs valeurs.

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. Par conséquent, ne les utilisez pas sur des colonnes régulièrement mises à jour ou recherchées. De plus, moins d’applications clientes prennent en charge JSON et la technologie est plus récente, elle peut donc être moins stable que les autres types.

Cependant, il existe de bons cas d’utilisation JSON pour les données rares ou les attributs personnalisés.

Créer une table avec un champ JSON

Imaginez une boutique qui vend des livres. Tous les livres ont un identifiant, un ISBN, un titre, un éditeur, un numéro de page et d’autres données relationnelles uniques. Par exemple, supposons que vous souhaitiez ajouter un nombre quelconque de balises de catégorie à chaque livre. Vous pouvez le faire en SQL en:

  1. une étiqueter Table dans laquelle chaque nom de balise est stocké avec un identifiant unique, et
  2. une Carte de tag Tableau des enregistrements plusieurs-à-plusieurs mappant les ID de livre aux ID de balise

Cela fonctionnera, mais c’est encombrant et une surcharge importante pour une petite fonctionnalité. Par conséquent, vous pouvez 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 n’ont pas de valeur par défaut et ne peuvent pas être utilisées comme clé primaire, clé étrangère ou index. Vous pouvez créer Index secondaires sur les colonnes virtuelles généréesCependant, il est plus facile et plus pratique de conserver une valeur dans un champ séparé lorsque des index sont requis.

Ajouter des données JSON

Des documents JSON entiers peuvent être transférés INSÉRER ou alors METTRE À JOUR Déclaration. Par exemple, nos balises de livre peuvent être passées sous forme de tableau (dans une chaîne):

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

JSON peut également être créé avec les éléments suivants:

  • Fonction JSON_ARRAY ()qui ont créé des tableaux favoris. Par example:

    
    SELECT JSON_ARRAY(1, 2, 'abc');
    
  • Fonction JSON_OBJECT ()qui a créé des objets. Par example:

    
    SELECT JSON_OBJECT('a', 1, 'b', 2);
    
  • Fonction JSON_QUOTE ()qui cite une chaîne comme valeur JSON. Par example:

    
    SELECT JSON_QUOTE('[1, 2, "abc"]');
    
  • ou tu peux (CAST anyValue AS JSON).

le Fonction JSON_TYPE () Vous pouvez utiliser cette option pour valider les types de valeur JSON. Il doit renvoyer OBJECT, ARRAY, un type scalaire (INTEGER, BOOLEAN, etc.), NULL ou une erreur. Par example:


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


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 entraînera une erreur et l’intégralité de l’enregistrement ne sera pas insérée / mise à jour.

Rechercher des données JSON

le Fonction JSON_CONTAINS () Accepte le document JSON recherché et un autre à comparer. Il renverra 1 si une correspondance est trouvée. Par example:


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

Le même Fonction JSON_SEARCH () Renvoie le chemin d’accès à la correspondance spécifiée, ou NULL s’il n’y a pas de correspondance. Le document JSON recherché 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 la même manière que LIKE). Par example:


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

Chemins JSON

Un chemin JSON vise 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 par d’autres sélecteurs:

  • un point suivi d’un nom, tel que $.website
  • [N] Où N est la position dans un tableau avec un index nul
  • les .[*] L’espace réservé évalue tous les membres d’un objet
  • les [*] L’espace réservé évalue tous les membres d’un tableau
  • les prefix**suffix L’espace réservé est évalué pour tous les chemins qui commencent par le préfixe nommé et se terminent par le suffixe nommé

Les exemples suivants concernent le 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 nommer et le premier jour de votre livre Table avec la requête:

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

Pour un exemple plus complexe, disons que vous en avez un utilisateur Table avec les données de profil JSON. Par example:

je voudrais Nom de famille profil
1 Craig {« E-mail »: [“[email protected]”, “[email protected]”], « Twitter »: « @craigbuckler »}
2 SitePoint {« E-mail »: [], « Twitter »: « @sitepointdotcom »}

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

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

Vous pouvez utiliser un chemin JSON dans la clause WHERE pour renvoyer uniquement les utilisateurs avec 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 un peu Fonctions MySQL pour changer des parties d’un document JSON avec 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 » à tout livre qui contient 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 contient plus d’informations sur Type de données JSON et le fonctions JSON associées.

Encore une fois, je vous exhorte à n’utiliser JSON que lorsque cela est absolument nécessaire. Vous pouvez émuler toute une base de données orientée document NoSQL dans MySQL, mais cela annulerait de nombreux Avantages de SQLet vous pouvez également passer à un vrai système NoSQL! Cependant, les types de données JSON peuvent réduire la surcharge pour des exigences de données plus ambiguës dans une application SQL.

[*]
[*]Source link

Recent Posts