Mysql如何對(duì)json數(shù)據(jù)進(jìn)行查詢及修改

更新時(shí)間：2022年07月05日 14:57:37 作者：youcijibi

這篇文章主要介紹了Mysql如何對(duì)json數(shù)據(jù)進(jìn)行查詢及修改，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

對(duì)json數(shù)據(jù)進(jìn)行查詢及修改

使用字段->'$.json屬性' 進(jìn)行查詢條件
使用 json_extract 函數(shù)查詢，json_extract(字段, "$.json屬性")
根據(jù)json數(shù)組查詢，用 JSON_CONTAINS(字段, JSON_OBJECT('json屬性', "內(nèi)容")) : [{}]查詢這種形式的json數(shù)組
MySQL5.7以上支持JSON的操作，以及增加了JSON存儲(chǔ)類型
一般數(shù)據(jù)庫(kù)存儲(chǔ)JSON類型的數(shù)據(jù)會(huì)用JSON類型或者TEXT類型

幾個(gè)相關(guān)函數(shù)

示例

我這里沒有創(chuàng)建json的字段格式，而是使用了text存儲(chǔ)json 。

注意：用JSON類型的話1）JSON列存儲(chǔ)的必須是JSON格式數(shù)據(jù)，否則會(huì)報(bào)錯(cuò)。2）JSON數(shù)據(jù)類型是沒有默認(rèn)值的。

插入json格式的數(shù)據(jù)到這一列中：

{"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可以查看到無(wú)法使用索引。

所以需要修改：

mysql原生并不支持json列中的屬性索引，但是我們可以通過mysql的虛擬列間接的為json中的某些屬性創(chuàng)建索引，原理就是為json中的屬性創(chuàng)建虛擬列，然后通過給虛擬列建立索引，從而間接的給屬性創(chuàng)建了索引。

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

因此，MySQL 5.7中，不指定Generated Column的類型，默認(rèn)是Virtual Column。

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

格式如下：

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: 利用操作符-» 來(lái)引用JSON字段中的KEY。在本例中字段names_virtual為虛擬字段，我把它定義成不可以為空。在實(shí)際的工作中，一定要集合具體的情況來(lái)定。因?yàn)镴SON本身是一種弱結(jié)構(gòu)的數(shù)據(jù)對(duì)象。也就是說的它的結(jié)構(gòu)不是固定不變的。

給虛擬字段增加索引：

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

注意如果虛擬字段并不是創(chuàng)建表是添加的，而是后面加的，增加索引時(shí)如果有的行中虛擬字段為null，但是又設(shè)置了它不能為null，那么索引無(wú)法創(chuàng)建成功，提示column can not be null.

增加索引后 explain看下即可看到用到了索引，并且虛擬字段的值會(huì)隨著json字段的屬性修改而自動(dòng)變化。

來(lái)看看修改

update off_main set json_field = json_set(json_field,'$.phone', '132') WHERE id = 45 
//同時(shí)修改多個(gè)
UPDATE offcn_off_main set json_field = json_set(json_field,'$.name',456,'$.age','bbb') WHERE id = 45

json_set() 方法存在的則會(huì)覆蓋，不存在的會(huì)添加。

刪除

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不存在的會(huì)增加，存在的不會(huì)覆蓋

Mysql處理json數(shù)據(jù)

1.如果數(shù)據(jù)量小的話，將json數(shù)據(jù)直接復(fù)制到mysql的json字段中，如果數(shù)據(jù)過大可以通過java等后臺(tái)形式對(duì)json數(shù)據(jù)解析，然后寫入數(shù)據(jù)庫(kù)中。

查詢操作

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

從一張表讀取一部分?jǐn)?shù)據(jù)存入另一張表中（一條數(shù)據(jù)）

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

讀取json數(shù)據(jù)并寫入數(shù)據(jù)庫(kù)(此時(shí)使用的是定義函數(shù)的形式來(lái)執(zhí)行方法，可以定義便量)

#清空數(shù)據(jù)庫(kù)
TRUNCATE table DT_village;
 
#定義存儲(chǔ)過程
delimiter //
DROP PROCEDURE IF EXISTS insert_test_val;
##num_limit 要插入數(shù)據(jù)的數(shù)量,rand_limit 最大隨機(jī)的數(shù)值
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
//
 
#調(diào)用存儲(chǔ)過程
call insert_test_val();

調(diào)用游標(biāo)的方式獲取jsosn數(shù)據(jù)中的一行，并執(zhí)行插入操作

delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
  BEGIN
    #創(chuàng)建接收游標(biāo)數(shù)據(jù)的變量
    declare j json;#存儲(chǔ)json數(shù)據(jù)
    DECLARE i int default 0; #創(chuàng)建總數(shù)變量，記錄執(zhí)行次數(shù)，控制循環(huán)
    DECLARE a,b,c varchar(5000);#定義json數(shù)組中的某個(gè)數(shù)據(jù)的鍵值
 
    #創(chuàng)建結(jié)束標(biāo)志變量
    declare done int default false;
    #創(chuàng)建游標(biāo)
    declare cur cursor for select json from JSON where name = '1';
    #指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值
    declare continue HANDLER for not found set done = true;
    #設(shè)置初始值
    set a=CONCAT('$.features[',i,'].attributes.XZQDM');
    set b=CONCAT('$.features[',i,'].attributes.XZQMC');
    set c=CONCAT('$.features[',i,']');
    #打開游標(biāo)
    open cur;
    #開始循環(huán)游標(biāo)里的數(shù)據(jù)
    read_loop:loop
      #根據(jù)游標(biāo)當(dāng)前指向的一條數(shù)據(jù)
      fetch cur into j;
      #判斷游標(biāo)的循環(huán)是否結(jié)束
      if done then
        leave read_loop;#跳出游標(biāo)循環(huán)
      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;
      #結(jié)束游標(biāo)循環(huán)
    end loop;
    #關(guān)閉游標(biāo)
    close cur;
 
    #輸出結(jié)果
    select j,i;
  END;
#調(diào)用存儲(chǔ)過程
call StatisticStore();

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: