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

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

王林
王林앞으로
2023-06-02 20:19:175007검색

    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 유형을 사용합니다.

    여러 관련 기능

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

    예제

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

    여기서 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 &#39;off_main&#39; `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_field` ->> &#39;$.name&#39;) 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,&#39;$.phone&#39;, &#39;132&#39;) WHERE id = 45 
    //同时修改多个
    UPDATE offcn_off_main set json_field = json_set(json_field,&#39;$.name&#39;,456,&#39;$.age&#39;,&#39;bbb&#39;) WHERE id = 45

    json_set() 메소드가 있으면 덮어쓰고, 없으면 추가합니다.

    Delete

    UPDATE offcn_off_main set json_field = json_remove(json_field,&#39;$.pwd&#39;,&#39;$.phone&#39;) WHERE id = 45

    Insert

    UPDATE offcn_off_main set json_field = json_insert(json_field,&#39;$.pwd&#39;,&#39;111&#39;) WHERE id = 45

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

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

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

    Query 연산

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

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

    insert into DT_village(name, border) SELECT
      json->&#39;$.features[0].attributes.CJQYMC&#39;,json->&#39;$.features[0].geometry.rings&#39;
    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 = &#39;1&#39;;
        #指定游标循环结束时的返回值
        declare continue HANDLER for not found set done = true;
        #设置初始值
        set a=CONCAT(&#39;$.features[&#39;,i,&#39;].attributes.XZQDM&#39;);
        set b=CONCAT(&#39;$.features[&#39;,i,&#39;].attributes.XZQMC&#39;);
        set c=CONCAT(&#39;$.features[&#39;,i,&#39;]&#39;);
        #打开游标
        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 중국어 웹사이트의 기타 관련 기사를 참조하세요!

    성명:
    이 기사는 yisu.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제