JSON et colonnes générées avec MySQL

novembre 30, 2015

JSON

Le 24 novembre dernier, lors du Forum PHP, Tomas Ulin (Oracle’s MySQL VP of Engineering) a parlé de l’utilisation de JSON dans MySQL « MySQL 5.7 & JSON: New opportunities for developers« .

 

Voici les réponses à quelques questions qui m’ont été posées:

  • Comment se comporte mysqldump avec les colonnes générées ?
  • Comment utiliser la commande LOAD DATA INFILE avec des colonnes générées ?
  • JSON est il sensible à la casse dans MySQL ?

 

Le contexte, une table InnoDB catalog qui contient un champs doc de type JSON ainsi que des colonnes générées virtuelles isbn & publisher:

Quelques documents JSON :

à insérer dans la table catalog:

La table catalog contient donc 3 enregistrements:

 

Sauvegarder et restaurer une table contenant  des colonnes générées

Q: Comment se comporte mysqldump avec les colonnes générées ?

 

Faire une sauvegardes des données avec mysqldump, le résultat étant redirigé vers le fichier test.catalog.sql :

En visualisant le dump on s’appercoit que:

  • Les colonnes générées sont bien présentent dans la structure de la table.
  • La commande INSERT ne renseigne que les colonnes « classiques » id et doc

Avant de restaurer le dump dans la table, je vais la vider:

La table ne contient plus de donnée:

Chargement des données dans la table:

Ta damm!!!
La table a bien été restaurée, les colonnes générées virtuelles sont, comme convenu, recalculées à la volée.

 

Importer un fichier texte dans une table avec des colonnes générées

Q: Comment utiliser la commande LOAD DATA INFILE avec des colonnes générées ?

L’export des données se fait avec SELECT … INTO OUTFILE:

Avant d’importer le fichier texte dans la table, je vais la vider:

Import des données dans la table:

L’import de fichiers, avec la commande LOAD DATA INFILE, dans une table qui contient des colonnes générées ne diffère en rien de l’import dans une table sans colonne générée.

 

Sensibilité à la casse du contenu JSON

Q: JSON est il sensible à la casse dans MySQL ?

 

Rechercher tous les documents où l’éditeur est ENI (en majuscule):

Rechercher tous les documents où l’éditeur est eni (en minuscule):

Les documents JSON sont donc sensible à la casse.

 

Il est évidemment possible d’utiliser une fonction pour modifier la casse:

Cependant 2 contraintes:
– Si la colonne est indexée ce dernier ne pourra pas être utilisé.
– Ça fonctionne si et seulement si le mot est écrit dans la base entièrement en majuscule.

Une meilleure solution consiste à créer et a indexer une colonne générée qui contient les données en minuscule.

La colonne générée contient les directives suivantes:

  • LOWER : transformer en minuscules les chaines de caractères
  • JSON_UNQUOTE : supprimer les guillemets
  • VIRTUAL : la colonne générée est virtuelle. Les données ne sont pas stockées mais calculées à la volée.

 

La structure de la tables est maintenant:

La colonne publisher_lower contient la version tout en minuscule des données de la colonne publisher.

Ajout de l’index:

Nouvelle structure de table:

Maintenant les données étant stockées dans une collation non sensible à la casse (utf8_general_ci) la requête n’est plus sensible à la casse:

La commande EXPLAIN confirme que l’index est vu et utilisé:

En fait on vient d’implémenter un index sur une fonction (functional index).

Vous voulez en savoir plus sur MySQL 5.7 ?
Rejoignez nous sur l’Oracle MySQL Tech Tour Paris le 8 décember 2015.

Infos & inscriptions

Note: cet article vient en complément de l’article 30 mins avec JSON en MySQL

Je vous invite également à consulter:

BGOUG15: JSON support in MySQL 5.7 from Georgi Kodinov

 

 

 

One Response to “JSON et colonnes générées avec MySQL”

  1. C’est une excellente idée de publier ce tuto, ça complète tout à fait bien la conférence qui a été faite sur ce sujet lors du Forum PHP 2015 et c’est très complet 🙂

Leave a Reply