Mysql을 사용하여 json 데이터를 쿼리하고 수정하는 방법

json 데이터 쿼리 및 수정

• 쿼리 조건에 field->'$.json 속성' 사용

• json_extract 함수 쿼리, json_extract(field, "$.json attribute") 사용

• json 배열 기반 쿼리, JSON_CONTAINS(field, JSON_OBJECT('json attribute', "content")): [{}]를 사용하여 이 형식의 json 배열을 쿼리합니다.

• MySQL 5.7 이상에서는 JSON 작업을 지원합니다. JSON 저장 유형 추가

• 일반 데이터베이스에서는 JSON 유형 데이터를 저장하기 위해 JSON 유형 또는 TEXT 유형을 사용합니다.

여러 관련 기능

예제

여기서 json을 만들지 않았습니다. 필드 형식 , 대신 텍스트를 사용하여 json을 저장합니다.

참고: JSON 유형을 사용하려면 열에 저장된 데이터가 JSON 형식을 준수해야 합니다. 그렇지 않으면 오류가 발생합니다. 2) JSON 데이터 유형에는 기본값이 없습니다.

이 열에 json 형식 데이터를 삽입하세요.

{"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' = '李四'

explain을 사용하여 인덱스를 사용할 수 없는지 확인하세요.

따라서 수정해야 합니다.

mysql은 기본적으로 json 열의 속성 인덱스를 지원하지 않지만 mysql의 가상 열을 통해 json의 특정 속성에 대한 인덱스를 간접적으로 생성할 수 있습니다. 원칙은 json의 속성에 대한 인덱스를 생성하는 것입니다. 가상 열을 인덱싱하여 속성에 대한 인덱스를 간접적으로 생성합니다.

MySQL 5.7에서는 가상 생성 열과 저장된 생성 열이라는 두 가지 유형의 생성 열이 지원됩니다. 전자는 생성 열을 데이터 사전(테이블의 메타데이터)에만 저장하고 이 열 데이터를 디스크에 유지하지 않습니다. on; 후자는 생성된 열을 읽을 때마다 계산하는 대신 디스크에 유지합니다. 분명히 후자는 기존 데이터에서 계산할 수 있는 데이터를 저장하고 더 많은 디스크 공간이 필요하며 Virtual Column에 비해 장점이 없습니다---(사실 여전히 장점이 있다고 생각합니다. 결국 쿼리 계산이 덜 필요합니다 . )

따라서 MySQL 5.7에서는 생성된 열의 유형이 지정되지 않으며 기본값은 가상 열입니다.

저장된 생성 골룸이 필요한 경우 가상 생성 열에 인덱스를 생성하는 것이 더 적절할 수 있습니다. 일반적인 상황에서는 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은 null을 허용하지 않는 것으로 정의되었습니다. 실제 업무에서는 구체적인 상황에 따라 판단해야 합니다. JSON 자체는 구조가 약하게 구성된 데이터 개체이기 때문입니다. 즉, 구조가 고정되어 있지 않습니다.

가상 필드에 인덱스 추가:

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

테이블 생성 시 가상 필드가 추가되지 않고 나중에 추가되는 경우, 인덱스 추가 시 일부 행의 가상 필드가 null이지만 null이 될 수 없도록 설정하면 인덱스가 성공적으로 생성될 수 없으며, 프롬프트 컬럼도 null이 될 수 없습니다.

인덱스를 추가한 후 설명을 해주시면 해당 인덱스가 사용되고 가상의 값이 표시됩니다. 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() 메소드가 있으면 덮어쓰고, 없으면 추가합니다.

Delete

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

Insert

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

삽입과 업데이트의 차이점은 존재하지 않는 삽입은 추가되고 기존 삽입은 덮어쓰지 않는다는 점입니다

Mysql은 json 데이터를 처리합니다

1. , json 데이터를 직접 복사합니다. mysql의 json 필드에서 데이터가 너무 큰 경우 json 데이터는 java 및 기타 백그라운드 형식을 통해 구문 분석된 다음 데이터베이스에 기록될 수 있습니다.

Query 연산

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

한 테이블에서 데이터 일부를 읽어 다른 테이블에 저장(1개의 데이터)

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!