Bagaimana untuk bertanya dan mengubah suai data json dengan Mysql

Soal dan ubah suai data json

• Gunakan medan->'$.json attribute' untuk pertanyaan syarat

• Gunakan fungsi json_extract untuk membuat pertanyaan, json_extract(medan, "atribut $.json")

• Pertanyaan berdasarkan tatasusunan json, gunakan JSON_CONTAINS(medan, JSON_OBJECT( 'json attribute', "content")): [{}] Tanya bentuk tatasusunan json ini

• MySQL 5.7 atau lebih tinggi menyokong operasi JSON dan menambah jenis storan JSON

• Pangkalan data umum akan menggunakan jenis JSON atau jenis TEKS untuk menyimpan data jenis JSON

Beberapa fungsi berkaitan

Contoh

Saya tidak mencipta format medan json di sini, tetapi menggunakan teks untuk menyimpan json.

Sila ambil perhatian: Jika anda ingin menggunakan jenis JSON, data yang disimpan dalam lajur mesti mematuhi format JSON, jika tidak, ralat akan berlaku. 2) Jenis data JSON tidak mempunyai nilai lalai.

Masukkan data format json ke dalam lajur ini:

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

Gunakan terangkan untuk melihat bahawa indeks tidak boleh digunakan.

Jadi ia perlu diubah suai:

mysql tidak menyokong indeks atribut secara asli dalam lajur json, tetapi kami secara tidak langsung boleh mengindeks atribut tertentu dalam json melalui lajur maya mysql Prinsip mencipta indeks untuk atribut adalah untuk mencipta lajur maya untuk atribut dalam json, dan kemudian mencipta indeks untuk lajur maya, dengan itu secara tidak langsung mencipta indeks untuk atribut.

Dalam MySQL 5.7, dua jenis Lajur Terhasil disokong, iaitu Lajur Terhasil Maya dan Lajur Terhasil Tersimpan yang pertama hanya menyimpan Lajur Terhasil dalam kamus data (metadata jadual) dan tidak menyimpan lajur ini daripada data Berterusan ke cakera; Jelas sekali, yang terakhir menyimpan data yang boleh dikira daripada data sedia ada, memerlukan lebih banyak ruang cakera, dan tidak mempunyai kelebihan berbanding Lajur Maya----(Malah, saya fikir masih ada kelebihan, lagipun, akan ada kurang pertanyaan pengiraan. )

Oleh itu, dalam MySQL 5.7, jenis Lajur Dihasilkan tidak ditentukan, dan lalainya ialah Lajur Maya.

Jika Stored Generated Golumn diperlukan, mungkin lebih sesuai untuk mencipta indeks pada Virtual Generated Column Dalam keadaan biasa, Virtual Generated Column digunakan, yang juga merupakan kaedah lalai MySQL.

Formatnya adalah seperti berikut:

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

Jadi di sini saya:

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

Sila ambil perhatian: Anda boleh menggunakan operator "»" untuk merujuk kunci dalam medan JSON (KEY). Dalam contoh ini, medan maya names_virtual telah ditakrifkan sebagai tidak boleh dibatalkan. Dalam kerja sebenar, ia mesti ditentukan berdasarkan keadaan tertentu. Kerana JSON itu sendiri adalah objek data berstruktur lemah. Dengan kata lain, strukturnya tidak tetap.

Tambah indeks pada medan maya:

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

Perhatikan bahawa jika medan maya tidak ditambah apabila jadual dibuat, tetapi ditambah kemudian, apabila menambah indeks, jika medan maya dalam beberapa baris adalah batal, Tetapi jika ia ditetapkan kepada tidak menjadi nol, maka indeks tidak boleh dibuat dengan jayanya, dan lajur gesaan tidak boleh menjadi batal

Selepas menambah indeks, terangkan dan anda akan melihat bahawa indeks digunakan, dan nilai medan maya akan berubah dengan Sifat medan json berubah secara automatik apabila diubah suai.

Mari kita lihat pengubahsuaian

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

Jika kaedah json_set() wujud, ia akan ditimpa, dan jika ia tidak wujud, ia akan ditambah.

Padam

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

Sisipkan

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

Perbezaan antara sisipan dan kemas kini ialah sisipan yang tidak wujud akan ditambah, dan sisipan sedia ada tidak akan ditimpa

Mysql memproses Data json

1 Jika jumlah data adalah kecil, salin data json terus ke medan json mysql Jika data terlalu besar, anda boleh menghuraikan data json java dan bentuk latar belakang lain, dan kemudian tuliskannya ke pangkalan data.

Operasi pertanyaan

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

Baca sebahagian daripada data dari satu jadual dan simpan dalam jadual lain (satu keping data)

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

Baca data json dan tuliskannya ke pangkalan data (pada masa ini Kaedah ini dilaksanakan dalam bentuk fungsi yang ditentukan, dan jumlahnya boleh ditakrifkan)

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

Panggil kursor untuk mendapatkan baris dalam data jsosn, dan lakukan operasi sisipan

Atas ialah kandungan terperinci Bagaimana untuk bertanya dan mengubah suai data json dengan Mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!