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:

CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(1000) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(1000) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB;

Quelques documents JSON :

{
    "isbn": "978-2-7460-7864-2",
    "title": "MySQL 5.6 Administration et optimisation",
    "publisher": "ENI",
    "language": "FR"
}, {
    "isbn": "978-2212126341",
    "title": "Audit et optimisation de MySQL 5",
    "publisher": "Eyrolles",
    "language": "FR"
}, {
    "isbn": "978-2-7460-5516-2",
    "title": "MySQL 5 Administration et optimisation",
    "publisher": "ENI",
    "language": "FR"
}

à insérer dans la table catalog:

mysql> INSERT  catalog (doc) VALUES 
('{"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "publisher": "ENI", "language": "FR"}'), 
('{"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "publisher": "Eyrolles", "language": "FR"}'), 
('{"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "publisher": "ENI", "language": "FR"}');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

La table catalog contient donc 3 enregistrements:

mysql> SELECT * FROM catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
3 rows in set (0.00 sec)

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 :

$ mysqldump test catalog > 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

$ cat test.catalog.sql
-- MySQL dump 10.13  Distrib 5.7.9, for linux-glibc2.5 (x86_64)
-- Server version    5.7.9
...
-- Table structure for table `catalog`
--

CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `catalog`
--

INSERT INTO `catalog` (`id`, `doc`) VALUES (1,'{\"isbn\": \"978-2-7460-7864-2\", \"title\": \"MySQL 5.6 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}'),(2,'{\"isbn\": \"978-2212126341\", \"title\": \"Audit et optimisation de MySQL 5\", \"language\": \"FR\", \"publisher\": \"Eyrolles\"}'),(3,'{\"isbn\": \"978-2-7460-5516-2\", \"title\": \"MySQL 5 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}');
...

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

mysql> TRUNCATE TABLE catalog;
Query OK, 0 rows affected (0.25 sec)

La table ne contient plus de donnée:

mysql> SELECT * FROM catalog;
Empty set (0.00 sec)

Chargement des données dans la table:

mysql> source test.catalog.sql
Query OK, 0 rows affected (0.00 sec)
...

mysql> SELECT * FROM catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

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:

mysql> SELECT * FROM catalog INTO OUTFILE '/tmp/test.catalog.tsv';
Query OK, 3 rows affected (0.01 sec)

$ cat /tmp/test.catalog.tsv
1    {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"}    "978-2-7460-7864-2"    "ENI"
2    {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}    "978-2212126341"    "Eyrolles"
3    {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}    "978-2-7460-5516-2"    "ENI"

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

mysql> TRUNCATE TABLE catalog;
Query OK, 0 rows affected (0.34 sec)

mysql> SELECT * FROM catalog;
Empty set (0.00 sec)

Import des données dans la table:

mysql>  LOAD DATA INFILE '/tmp/test.catalog.tsv' INTO TABLE test.catalog;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
3 rows in set (0.01 sec)

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

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='ENI';
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

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

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='eni';
Empty set (0.00 sec)

Les documents JSON sont donc sensible à la casse.

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

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"=UPPER('eni');
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

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.

mysql> ALTER TABLE catalog ADD COLUMN publisher_lower varchar(20) GENERATED ALWAYS AS (LOWER(JSON_UNQUOTE(doc->"$.publisher"))) VIRTUAL;

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:

mysql> SHOW CREATE TABLE catalog;
CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  `publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

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

mysql> SELECT publisher, publisher_lower FROM catalog;
+------------+-----------------+
| publisher  | publisher_lower |
+------------+-----------------+
| "ENI"      | "eni"           |
| "Eyrolles" | "eyrolles"      |
| "ENI"      | "eni"           |
+------------+-----------------+
3 rows in set (0.00 sec)

Ajout de l’index:

mysql> ALTER TABLE catalog ADD KEY idx_publisher_lower(publisher_lower);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nouvelle structure de table:

mysql> SHOW CREATE TABLE catalog;
CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  `publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`),
  KEY `idx_publisher_lower` (`publisher_lower`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

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:

mysql> SELECT doc FROM catalog WHERE publisher_lower = 'ENI' AND publisher_lower = 'eni' AND publisher_lower = 'eNi';
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+

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

mysql> EXPLAIN SELECT doc FROM catalog WHERE publisher_lower = 'eNi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalog
   partitions: NULL
         type: ref
possible_keys: idx_publisher_lower
          key: idx_publisher_lower
      key_len: 63
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL

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:

• 30 mins avec les fonctions JSON de MySQL
• Document validation of JSON columns in MySQL de Mario Beck
• New JSON functions in MySQL 5.7.22
• JSON support in MySQL 5.7 de Georgi Kodinov

http://www.slideshare.net/gkodinov/bgoug15-json-support-in-mysql-57

Filed under:
json, MySQL by Olivier DASINI