MySQL : remplacer les tables de liaison par des données JSON

Publié par Fabrice le 14 janvier 2019, temps de lecture estimé : 2 minutes Webmastering

MySQL : remplacer les tables de liaison par des données JSON

Les tables de liaisons sont régulièrement utilisées pour relier plusieurs tables entres-elles - en utilisant le format JSON, on économise une table et on simplifie les requêtes.

Un exemple pratique avec des articles que l'on peut classer dans plusieurs catégories : 2 tables suffisent au lieu de 3. La table des articles contient une colonne supplémentaire de type json ou text (cela fonctionne également) qui reprend simplement les id des catégories dans un format simple : [14, 25, 33] (note : dans ce cas précis pas besoin de reprendre les couples clé/valeur).

Soit pour l'exemple, les deux tables suivantes :

CREATE TABLE `posts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `cats` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Ainsi, pour rechercher tous les articles de la catégorie dont l'id est 33 (nécessite MySQL 5.7) :

SELECT * FROM posts WHERE JSON_CONTAINS(cats, '33');

Pour les versions de MySQL inférieur à 5.7, il existe une astuce en utilisant une expression régulière :

SELECT * FROM posts WHERE cats REGEXP '[[:<:]]33[[:>:]]';