Depuis MySQL 5.7, il devient simple de stocker des données en JSON.
C’est très pratique quand les enregistrements n’ont pas tous les mêmes attribus.
Documentation officielle
Le mieux est de commencer par la doc :
https://dev.mysql.com/doc/refman/5.7/en/json.html
Requêtes JSON en MySQL
Création d’une table avec un champ « donnees » en JSON et ajout de données d’exemple.
CREATE TABLE equipe (id INTEGER NOT NULL AUTO_INCREMENT, prenom VARCHAR(200), couleur VARCHAR(20), donnees JSON, PRIMARY KEY (id)); INSERT INTO equipe VALUES(NULL, 'julien', 'bleu', '{"twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985, "poste": "tech"}'); INSERT INTO equipe VALUES(NULL, 'thomas', 'bleu', '{"pays": "france", "telephone": "0722222222", "poste": "president"}'); INSERT INTO equipe VALUES(NULL, 'benjamin', 'rouge', '{"pays": "suisse", "telephone": "0833333333", "poste": "tech"}');
Nous remarquons que le champ « donnees » ne renseigne pas forcément les mêmes informations pour l’ensemble des 3 enregistrements : julien a précisé son twitter mais pas son pays par exemple.
SELECT * FROM equipe; 1 julien bleu {"poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985} 2 thomas bleu {"pays": "france", "poste": "president", "telephone": "0722222222"} 3 benjamin rouge {"pays": "suisse", "poste": "tech", "telephone": "0833333333"}
SELECT : Requêtes en lecture sur du JSON
SELECT * FROM equipe WHERE couleur = 'bleu' AND json_extract (donnees,"$.annee_naissance") = 1985; 1 julien bleu {"poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985}
UPDATE : Requêtes en mise à jour sur du JSON
UPDATE equipe SET donnees = JSON_SET(donnees, "$.pays", "fra") WHERE id = 1;
Vérifions le résultat pour julien :
SELECT * FROM equipe WHERE json_extract (donnees,"$.pays") LIKE '%fra%'; 1 julien bleu {"pays": "fra", "poste": "tech", "twitter": "ywilien", "telephone": "0611111111", "annee_naissance": 1985} 2 thomas bleu {"pays": "france", "poste": "president", "telephone": "0722222222"}
Complément
Attention au comportement étrange sur le LIKE bordé d’un seul coté :
SELECT * FROM equipe WHERE json_extract (donnees,"$.pays") LIKE 'fra%'; Query OK, 1 row affected (0,00 sec)
Nous devrions avoir les 2 mêmes enregistrements que le résultat précédent. Je n’ai pas encore cherché l’origine de ce comportement.
L’utilisation de json_extract est un peu lourde, une notation plus simple existe :
SELECT * FROM equipe WHERE donnees->"$.pays" = 'france';
Cette notation bien plus agréable a cependant quelques limitations à voir ici : http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/