Mysql如何對json數據進行查詢及修改

 更新時間:2022年07月05日 14:57:37   作者:youcijibi  
這篇文章主要介紹了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類型的話1)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;

Note: 利用操作符-» 來引用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('$.features[',i,'].attributes.CJQYMC');
      set b=CONCAT('$.features[',i,'].geometry.rings');
      insert into DT_village(name, border) select
              #json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings'
                                                 # (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();

以上為個人經驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關文章

最新評論

美丽人妻被按摩中出中文字幕