Mysql怎麼對json資料進行查詢及修改

對json資料進行查詢及修改

• #使用欄位->'$.json屬性' 進行查詢條件

• 使用json_extract 函數查詢，json_extract(字段, "$.json屬性")

• 根據json數組查詢，用JSON_CONTAINS(字段, JSON_OBJECT( 'json屬性', "內容")) : [{}]查詢這種形式的json數組

• #MySQL5.7以上支援JSON的操作，以及增加了JSON儲存類型

• 一般資料庫儲存JSON類型的資料會用JSON類型或TEXT類型

幾個相關函數

範例

我這裡沒有建立json的欄位格式，而是使用了text儲存json 。

請注意：若要使用JSON類型，則列儲存的資料必須是符合JSON格式，否則會導致錯誤。 2）JSON資料型態是沒有預設值的。

插入json格式的資料到這一列：

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

查詢

1、

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

2、

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

使用explain可以查看到無法使用索引。

所以需要修改：

mysql原生並不支援json列中的屬性索引，但是我們可以透過mysql的虛擬列間接的為json中的某些屬性建立索引，原理就是為json中的屬性建立虛擬列，然後透過給虛擬列建立索引，從而間接的給屬性建立了索引。

在MySQL 5.7中，支援兩種Generated Column，即Virtual Generated Column和Stored Generated Column，前者只將Generated Column保存在資料字典中（表的元資料），並不會將這一列數據持久化到磁碟上；後者會將Generated Column持久化到磁碟上，而不是每次讀取的時候計算所得。很明顯，後者存放了可以透過已有數據計算而得的數據，需要更多的磁碟空間，與Virtual Column相比並沒有優勢----（其實我覺得還是有優勢畢竟會少一些查詢計算）

因此，MySQL 5.7中，不指定Generated Column的類型，預設是Virtual Column。

如果需要Stored Generated Golumn的話，可能在Virtual Generated Column上建立索引更加合適，一般情況下，都使用Virtual Generated Column，這也是MySQL預設的方式。

格式如下：

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

所以我在這裡：

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

請注意：可以使用"»"運算子來引用JSON欄位中的鍵（KEY）。在本例中，虛擬欄位names_virtual已被定義為不可為空。在實際的工作中，一定要集合具體的情況來定。因為JSON本身就是一種弱結構的資料對象。也就是說的它的結構不是固定不變的。

為虛擬欄位增加索引：

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

注意如果虛擬欄位並不是建立表格是新增的，而是後面加的，增加索引時如果有的行中虛擬欄位為null，但是又設定了它不能為null，那麼索引無法創建成功，提示column can not be null.

增加索引後explain看下即可看到用到了索引，並且虛擬字段的值會隨著json欄位的屬性修改而自動變更。

來看看修改

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() 方法存在的則會覆寫，不存在的會新增。

刪除

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

插入

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

insert與update不同之處在於insert不存在的會增加，存在的不會覆蓋

Mysql處理json資料

1.如果資料量小的話，將json資料直接複製到mysql的json欄位中，如果資料過大可以透過java等後台形式對json資料解析，然後寫入資料庫。

查詢操作 

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

從一張表讀取一部分資料存入另一張表中（一條資料）

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

讀取json資料並寫入資料庫(此時使用的是定義函數的形式來執行方法，可以定義便量)

#清空数据库
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();

呼叫遊標的方式來取得jsosn資料中的一行，並執行插入操作

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();

以上是Mysql怎麼對json資料進行查詢及修改的詳細內容。更多資訊請關注PHP中文網其他相關文章！