Comment interroger et modifier des données json avec Mysql

Interroger et modifier les données json

• Utilisez le champ->'Attribut $.json' pour interroger les conditions

• Utilisez la requête de fonction json_extract, json_extract(field, "Attribut $.json")

• Requête basée sur un tableau json, utilisez JSON_CONTAINS(field, JSON_OBJECT('jsonattribut', "content")) : [{}] pour interroger cette forme de tableau json

• MySQL 5.7 ou supérieur prend en charge les opérations JSON, et l'ajout du type de stockage JSON

• Les bases de données générales utiliseront le type JSON ou le type TEXTE pour stocker les données de type JSON

Plusieurs fonctions associées

Exemples

Je n'ai pas créé de json ici Format de champ , utilise à la place du texte pour stocker json.

Attention : Si vous souhaitez utiliser le type JSON, les données stockées dans la colonne doivent être conformes au format JSON, sinon une erreur se produira. 2) Le type de données JSON n'a pas de valeur par défaut.

Insérez les données au format json dans cette colonne :

{"age": "28", "pwd": "lisi", "name": "李四"}

Query

1,

select * from `offcn_off_main` where json_extract(json_field,"$.name") = '李四'

2,

select * from `offcn_off_main` where json_field->'$.name' = '李四'

Utilisez expliquer pour voir que l'index ne peut pas être utilisé.

Il faut donc le modifier :

mysql ne supporte pas nativement les index d'attributs dans les colonnes json, mais on peut indirectement créer des index pour certains attributs en json via les colonnes virtuelles de mysql. Le principe est de créer des index pour les attributs en json. Colonne virtuelle, puis créez un index pour la colonne virtuelle, créant ainsi indirectement un index pour l'attribut.

Dans MySQL 5.7, deux types de colonnes générées sont pris en charge, à savoir la colonne générée virtuelle et la colonne générée stockée. La première enregistre uniquement la colonne générée dans le dictionnaire de données (métadonnées de la table) et ne conserve pas les données de cette colonne sur le disque. on ; ce dernier conservera la colonne générée sur le disque au lieu de la calculer à chaque lecture. Évidemment, cette dernière stocke des données qui peuvent être calculées à partir de données existantes, nécessite plus d'espace disque et n'a aucun avantage par rapport à la colonne virtuelle ---- (En fait, je pense qu'elle a toujours un avantage, après tout, elle nécessite moins de calculs de requêtes . )

Par conséquent, dans MySQL 5.7, le type de colonne générée n'est pas spécifié et la valeur par défaut est la colonne virtuelle.

Si vous avez besoin d'une colonne générée stockée, il peut être plus approprié de créer un index sur une colonne générée virtuelle. Dans des circonstances normales, une colonne générée virtuelle est utilisée, qui est également la méthode par défaut de MySQL.

Le format est le suivant :

fieldname <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

Me voici donc :

ALTER TABLE 'off_main' `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_field` ->> '$.name') not null;

Attention : Vous pouvez utiliser l'opérateur "»" pour référencer la clé (KEY) dans le champ JSON. Dans cet exemple, le champ virtuel names_virtual a été défini comme non nullable. Dans le travail réel, cela doit être déterminé en fonction de circonstances spécifiques. Parce que JSON lui-même est un objet de données faiblement structuré. Autrement dit, sa structure n’est pas figée.

Ajouter un index au champ virtuel :

CREATE INDEX `names` ON `off_main`(`names_virtual`);

Notez que si le champ virtuel n'est pas ajouté lors de la création de la table, mais ajouté plus tard, lors de l'ajout de l'index, si le champ virtuel dans certaines lignes est nul, mais il l'est défini de manière à ce qu'il ne puisse pas être nul, l'index ne peut pas être créé avec succès et la colonne d'invite ne peut pas être nulle.

Après avoir ajouté l'index, veuillez expliquer et vous verrez que l'index est utilisé et la valeur du virtuel Le champ changera automatiquement à mesure que les attributs du champ json seront modifiés.

Jetons un œil aux modifications

update off_main set json_field = json_set(json_field,'$.phone', '132') WHERE id = 45 
//同时修改多个
UPDATE offcn_off_main set json_field = json_set(json_field,'$.name',456,'$.age','bbb') WHERE id = 45

json_set() Si la méthode existe, elle sera écrasée, et si elle n'existe pas, elle sera ajoutée.

Supprimer

UPDATE offcn_off_main set json_field = json_remove(json_field,'$.pwd','$.phone') WHERE id = 45

Insérer

UPDATE offcn_off_main set json_field = json_insert(json_field,'$.pwd','111') WHERE id = 45

La différence entre l'insertion et la mise à jour est que les insertions qui n'existent pas seront ajoutées et celles existantes ne seront pas écrasées

Mysql traite les données json

1 Si la quantité de données est petite. , copiez les données json directement Dans le champ json de mysql, si les données sont trop volumineuses, les données json peuvent être analysées via Java et d'autres formulaires en arrière-plan, puis écrites dans la base de données.

Opération de requête

select *,json->'$.features[0].geometry.rings' as rings from JSON;

Lire une partie des données d'une table et la stocker dans une autre table (une donnée)

insert into DT_village(name, border) SELECT
  json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings'
from JSON;

Lire les données json et les écrire dans la base de données (à ce moment, la forme de la fonction définie est utilisé pour exécuter la méthode, vous pouvez définir le montant)

#清空数据库
TRUNCATE table DT_village;
 
#定义存储过程
delimiter //
DROP PROCEDURE IF EXISTS insert_test_val;
##num_limit 要插入数据的数量,rand_limit 最大随机的数值
CREATE PROCEDURE insert_test_val()
  BEGIN
 
    DECLARE i int default 0;
    DECLARE a,b varchar(5000);
 
    WHILE i<10 do
      set a=CONCAT(&#39;$.features[&#39;,i,&#39;].attributes.CJQYMC&#39;);
      set b=CONCAT(&#39;$.features[&#39;,i,&#39;].geometry.rings&#39;);
      insert into DT_village(name, border) select
              #json->&#39;$.features[0].attributes.CJQYMC&#39;,json->&#39;$.features[0].geometry.rings&#39;
                                                 # (json->a),(json->b)
   json_extract(json,a),json_extract(json,b)
      from JSON;
      set i = i + 1;
 
    END WHILE;
 
  END
//
 
#调用存储过程
call insert_test_val();

Appelez le curseur pour obtenir une ligne dans les données jsosn et effectuer l'opération d'insertion

delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
  BEGIN
    #创建接收游标数据的变量
    declare j json;#存储json数据
    DECLARE i int default 0; #创建总数变量，记录执行次数，控制循环
    DECLARE a,b,c varchar(5000);#定义json数组中的某个数据的键值
 
    #创建结束标志变量
    declare done int default false;
    #创建游标
    declare cur cursor for select json from JSON where name = '1';
    #指定游标循环结束时的返回值
    declare continue HANDLER for not found set done = true;
    #设置初始值
    set a=CONCAT('$.features[',i,'].attributes.XZQDM');
    set b=CONCAT('$.features[',i,'].attributes.XZQMC');
    set c=CONCAT('$.features[',i,']');
    #打开游标
    open cur;
    #开始循环游标里的数据
    read_loop:loop
      #根据游标当前指向的一条数据
      fetch cur into j;
      #判断游标的循环是否结束
      if done then
        leave read_loop;#跳出游标循环
      end if;
      #这里可以做任意你想做的操作
      WHILE i<11 do
        insert into dt_border(xzq_code,name,border) select
                                                           json_extract(j,a),json_extract(j,b),json_extract(j,c)
        from JSON;
        set i = i + 1;
      END WHILE;
      #结束游标循环
    end loop;
    #关闭游标
    close cur;
 
    #输出结果
    select j,i;
  END;
#调用存储过程
call StatisticStore();

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!