JSON et colonnes générées avec MySQL
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:
|
1 2 3 4 5 6 7 8 |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 |
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 :
|
1 |
$ 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$ 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:
|
1 2 |
mysql> TRUNCATE TABLE catalog; Query OK, 0 rows affected (0.25 sec) |
La table ne contient plus de donnée:
|
1 2 |
mysql> SELECT * FROM catalog; Empty set (0.00 sec) |
Chargement des données dans la table:
|
1 2 3 |
mysql> source test.catalog.sql Query OK, 0 rows affected (0.00 sec) ... |
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 |
mysql> SELECT * FROM catalog INTO OUTFILE '/tmp/test.catalog.tsv'; Query OK, 3 rows affected (0.01 sec) |
|
1 2 3 4 |
$ 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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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):
|
1 2 3 4 5 6 7 8 |
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):
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 |
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.
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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.
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 |
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é:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
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
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
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 🙂
[…] then indexed this column.Quite useful when dealing with JSON functions, you can find an example here and the documentation […]
[…] JSON et colonnes générées avec MySQL […]