Cara Menggunakan Bidang Data JSON dalam Pangkalan Data MySQL

Artikel ini menerangkan butir -butir pelaksanaan JSON dalam MySQL 9.1, dan meneruskan hujah bahawa sempadan antara pangkalan data SQL dan NoSQL kabur dalam artikel sebelumnya "SQL vs NOSQL: Perbezaan", kedua -duanya belajar dari satu sama lain ciri -ciri. Kedua -dua MySQL 5.7 Pangkalan Data InnoDB dan PostgreSQL 9.2 Sokongan langsung menyimpan jenis dokumen JSON dalam satu bidang.

Harus diingat bahawa mana -mana pangkalan data boleh menyimpan dokumen JSON sebagai satu gumpalan rentetan tunggal. Walau bagaimanapun, sokongan MySQL dan PostgreSQL menyimpan data JSON yang disahkan sebagai pasangan nilai utama sebenar dan bukannya rentetan mudah.

mata utama

MySQL 5.7 Pangkalan Data InnoDB dan PostgreSQL 9.2 Secara langsung menyokong jenis dokumen JSON, tetapi harus digunakan dengan berhati -hati kerana batasan pengindeksan langsung.
JSON adalah yang terbaik untuk menyimpan data jarang, sifat tersuai, hierarki, dan situasi di mana fleksibiliti diperlukan. Ia tidak boleh menggantikan lajur yang dinormalisasi yang sering menanyakan atau data indeks.
MySQL menyediakan pelbagai fungsi untuk membuat, mengesahkan, mencari dan mengubahsuai objek JSON. Fungsi ini termasuk json_array (), json_object (), json_quote (), json_type (), json_valid (), json_contains (), json_search (), dan json_set () dan json_merge_ untuk mengemas kini dokumen json menggunakan patch notation (), etc. fungsi.
MySQL 9.1 menyokong pengindeksan fungsi lajur yang dihasilkan yang diperolehi daripada data JSON, yang membolehkan pertanyaan yang cekap dari elemen JSON tertentu.
Walaupun MySQL menyokong JSON, ia masih merupakan pangkalan data relasi. Berlebihan JSON boleh mengimbangi kelebihan SQL.

menyimpan dokumen JSON dalam lajur MySQL JSON tidak bermakna anda harus melakukan ini

Normalisasi adalah teknologi yang digunakan untuk mengoptimumkan struktur pangkalan data. Peraturan formula biasa (1NF) yang pertama menetapkan bahawa setiap lajur harus memegang satu nilai dan menyimpan dokumen JSON yang bernilai multi-jelas melanggar peraturan ini.

Jika data anda mempunyai keperluan data relasi yang jelas, sila gunakan medan nilai tunggal yang sesuai. JSON harus digunakan dengan berhati -hati sebagai usaha terakhir. Bidang nilai JSON tidak boleh diindeks secara langsung, jadi elakkan menggunakannya pada lajur yang sering dikemas kini atau dicari.

Pengindeksan fungsi lajur yang dihasilkan yang diperolehi daripada data JSON membolehkan anda mengindeks bahagian tertentu objek JSON, dengan itu meningkatkan prestasi pertanyaan.

iaitu, JSON mempunyai beberapa kes penggunaan yang baik untuk data jarang atau sifat tersuai.

Buat jadual dengan lajur jenis data JSON

Pertimbangkan kedai menjual buku. Semua buku mempunyai ID, ISBN, tajuk, penerbit, kiraan halaman dan data hubungan yang jelas.

Sekarang, jika anda ingin menambah seberapa banyak tag kategori untuk setiap buku. Anda boleh melaksanakannya dalam SQL menggunakan kaedah berikut:

1. Jadual tag yang menyimpan setiap nama tag dan ID uniknya;
Jadual pemetaan tag yang mengandungi banyak rekod, pemetaan ID buku ke ID Tag.

Kaedah ini berfungsi, tetapi untuk fungsi sekunder ia terlalu rumit dan memerlukan usaha yang besar. Oleh itu, anda boleh menentukan medan MySQL JSON untuk tag dalam jadual buku pangkalan data MySQL:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Lajur MySQL JSON tidak boleh mempunyai nilai lalai, tidak boleh digunakan sebagai kunci utama, tidak boleh digunakan sebagai kunci asing, dan tidak boleh mempunyai indeks langsung.

Walau bagaimanapun, dengan MySQL 9.1, anda boleh membuat indeks fungsi pada lajur yang dihasilkan yang diperolehi daripada data JSON, yang membolehkan pengindeksan unsur -unsur tertentu dalam dokumen JSON. Lajur yang dihasilkan ini boleh menjadi maya atau disimpan dan diindeks sebagai indeks tambahan.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Tambah data JSON

keseluruhan dokumen JSON boleh diluluskan dalam penyataan memasukkan atau kemas kini, menjadikannya mudah untuk memindahkan JSON ke MySQL untuk penyimpanan dan operasi.

Sebagai contoh, tag buku kami boleh diluluskan sebagai array (dalam rentetan):

anda juga boleh membuat JSON menggunakan fungsi berikut:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

fungsi json_array (), digunakan untuk membuat tatasusunan. Sebagai contoh: - kembali [1, 2, "ABC"]: pilih JSON_ARRAY (1, 2, 'ABC');

fungsi json_object (), digunakan untuk membuat objek. Sebagai contoh: - kembali {"a": 1, "b": 2}: pilih json_object ('a', 1, 'b', 2);
fungsi JSON_QUOTE (), digunakan untuk merujuk rentetan sebagai nilai JSON. Sebagai contoh: - kembali "[1, 2," abc "]": pilih json_quote ('[1, 2, "abc"]');
Cast (AnyValue as JSON) berfungsi untuk menukar nilai ke jenis JSON untuk memastikan kesahihan: pilih cast ('{"a": 1, "b": 2}' sebagai json);
• fungsi json_type () membolehkan anda menyemak jenis nilai JSON. Ia harus mengembalikan objek, array, jenis skalar (integer, boolean, dan lain -lain), null atau kesilapan. Contohnya:

fungsi json_valid () pulangan 1 jika json sah, jika tidak 0 akan dikembalikan:

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');

cuba memasukkan dokumen JSON yang tidak sah akan membuang ralat dan keseluruhan rekod tidak akan dimasukkan/dikemas kini.

-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');

Cari dokumen JSON dalam lajur MySQL JSON

Menggunakan fungsi MySQL JSON seperti fungsi json_contains (), anda boleh menyemak sama ada dokumen JSON mengandungi nilai tertentu. Pulangan 1 Apabila perlawanan dijumpai. Contohnya:

fungsi json_search () mengembalikan laluan ke nilai dalam dokumen JSON. Mengembalikan Null apabila tidak ada perlawanan.

Anda juga boleh menentukan sama ada anda perlu mencari semua perlawanan atau satu perlawanan dengan lulus bendera "satu" dan "semua" dan rentetan carian (di mana % sepadan dengan bilangan aksara, _ sepadan dengan satu aksara seperti). Contohnya:

fungsi json_table () menukarkan data JSON ke dalam format relasi untuk memudahkan pertanyaan:

-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

json path

-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

pertanyaan MySQL JSON menggunakan fungsi JSON_EXTRACT () boleh mendapatkan nilai tertentu dari dokumen JSON berdasarkan laluan yang ditentukan.

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

semua definisi jalan bermula dengan $ diikuti oleh pemilih lain:

• Tempoh diikuti dengan nama, seperti $ .website
• [n], di mana n ialah kedudukan dalam array indeks sifar
• . [*] Semua ahli Objek Penilaian Wildcard
• [*] Array Penilaian Wildcard Semua Ahli
• awalan ** Suffix Wildcard menilai semua laluan yang bermula dengan awalan bernama dan berakhir dengan akhiran bernama

Contoh berikut merujuk kepada dokumen JSON berikut:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Contoh Path:

• $. Pulangan 1
• $. C kembali [3, 4]
• $. C [1] kembali 4
• $. D.E kembali 5
• $ **. E kembali [5]

anda boleh menggunakan JSON untuk mengekstrak fungsi MySQL untuk mengekstrak nama dan tag pertama dari jadual buku anda:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Untuk contoh yang lebih kompleks, katakan anda mempunyai jadual pengguna yang mengandungi data fail konfigurasi JSON. Contohnya:

anda boleh menggunakan laluan JSON untuk mengekstrak nama Twitter. Contohnya:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

anda boleh menggunakan laluan JSON dalam klausa WHERE untuk mengembalikan hanya pengguna dengan akaun Twitter:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Ubah suai sebahagian daripada dokumen JSON

Terdapat beberapa fungsi MySQL yang boleh mengubahsuai sebahagian daripada dokumen JSON menggunakan notasi laluan. Fungsi ini termasuk:

json_set (doc, path, val [, path, val] ...): masukkan atau kemas kini data dalam dokumen. Kemas kini buku set tags = json_set (tags, '$ [0]', 'tag dikemas kini');

• json_insert (doc, path, val [, path, val] ...): masukkan data ke dalam dokumen tanpa menimpa nilai sedia ada. Kemas kini buku set tags = json_insert (tags, '$ [0]', 'tag baru');
• json_replace (doc, path, val [, path, val] ...): gantikan data dalam dokumen. Kemas kini buku set tags = json_replace (tags, '$ [0]', 'diganti tag');
• json_merge_patch (doc, doc [, doc] ...): Gabungkan dua atau lebih dokumen JSON dan gantikan kunci sedia ada dengan nilai -nilai dokumen berikutnya. Kemas kini buku set tags = json_merge_patch (tags, '["technical"]') di mana json_search (tags, 'one', 'javascript') tidak null;
• json_array_append (Doc, Path, Val [, Path, Val] ...): Masukkan nilai ke akhir array. Kemas kini buku set tags = json_array_append (tags, '$', 'tag baru');
• json_array_insert (doc, path, val [, path, val] ...): masukkan nilai di lokasi tertentu dalam array JSON. Kemas kini buku set tags = json_array_insert (tags, '$ [0]', 'tag dimasukkan');
• json_remove (doc, path [, path] ...): Padam data dari dokumen. Kemas kini buku set tags = json_remove (tags, '$ [1]');
• json_pretty (val): Mencantikkan dokumen JSON untuk memudahkan mereka dibaca. Pilih json_pretty ('{"name": "sitepoint", "tags": ["mysql", "json"]}');
• Contohnya, jika anda ingin menambah tag "Teknikal" ke mana -mana buku yang sudah mempunyai tag "JavaScript", anda boleh menggunakan fungsi JSON_MERGE_PATCH ():

Maklumat lanjut

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Dokumentasi MySQL menyediakan maklumat terperinci mengenai jenis data MySQL JSON dan fungsi JSON yang berkaitan.

ingatkan sekali lagi, jangan gunakan JSON melainkan benar -benar perlu. Anda boleh mensimulasikan keseluruhan pangkalan data NoSQL yang berorientasikan dokumen di MySQL, tetapi ini akan mengimbangi banyak manfaat SQL, dan anda juga boleh beralih terus ke sistem NoSQL yang sebenar!

iaitu, untuk keperluan data yang lebih samar dalam aplikasi SQL, jenis data JSON boleh menjimatkan beberapa usaha.

soalan yang sering ditanya mengenai penggunaan data JSON dalam mysql

Bolehkah anda menggunakan JSON di MySQL?

MySQL menyokong JSON dengan menyediakan jenis data JSON, yang digunakan untuk menyimpan data dalam format JSON dalam lajur. Bermula dengan MySQL 5.7.8, anda boleh membuat jadual dengan lajur JSON, yang membolehkan anda memasukkan, mengemas kini, dan memohon data JSON menggunakan SQL. MySQL menyediakan satu siri fungsi JSON untuk memproses data JSON dalam lajur ini, supaya data JSON dapat diekstrak, diubahsuai, dan dimanipulasi.

Di samping itu, anda boleh menggunakan data JSON dalam pertanyaan SQL dan mengubahnya menjadi data relasi menggunakan fungsi seperti JSON_TABLE apabila diperlukan. Walau bagaimanapun, adalah penting untuk memahami bahawa MySQL pada asasnya merupakan pangkalan data relasi dengan sokongan jenis data JSON yang direka untuk memudahkan pemprosesan data JSON dalam konteks hubungan dan bukannya menjadi pangkalan data NoSQL JSON sepenuhnya.

Seperti yang disebutkan di atas, hanya kerana anda boleh menyimpan JSON tidak bermakna anda harus melakukan ini: Normalisasi adalah teknik untuk mengoptimumkan struktur pangkalan data. Peraturan Formula Normal (1NF) yang pertama menyatakan bahawa setiap lajur harus memegang satu nilai dan menyimpan dokumen JSON multi-bernilai melanggar peraturan ini.

Adakah ok untuk menyimpan JSON di MySQL?

menyimpan json di mysql adalah ok jika:

• data separa berstruktur atau dinamik, tidak sesuai untuk corak yang ketat.
• sifat tersuai, di mana reka bentuk hubungan tidak cekap.
• Bersepadu dengan API JSON untuk menyimpan muatan atau log.

Walau bagaimanapun, JSON tidak boleh menggantikan penyimpanan relasi normal data berstruktur dan kerap. Walaupun MySQL 9.1 meningkatkan fungsi JSON dengan ciri -ciri seperti pengindeksan fungsi dan JSON_TABLE, operasi JSON masih boleh menyebabkan overhead untuk dataset besar atau pertanyaan kompleks.

Bagaimana menggunakan JSON dalam pertanyaan MySQL?

Anda boleh menggunakan JSON dalam pertanyaan MySQL dengan menggunakan fungsi JSON MySQL. Fungsi ini membolehkan anda mengekstrak, memanipulasi, dan menanyakan data JSON yang disimpan dalam rentetan format JSON dalam lajur JSON atau pangkalan data. Untuk mengakses data JSON dalam lajur JSON, gunakan -& gt;

json_extract, json_set, dan json_objectagg dan fungsi JSON lain membolehkan anda menapis, mengubahsuai, agregat dan memproses data JSON. Anda juga boleh menggunakan klausa WHERE untuk menapis baris berdasarkan nilai JSON. Fungsi JSON MySQL menyediakan cara yang fleksibel untuk berinteraksi dan memanipulasi objek JSON secara langsung dalam pertanyaan pangkalan data.

Bilakah saya harus menggunakan JSON di MySQL?

anda harus menggunakan JSON dalam MySQL di bawah keadaan berikut:

Data separa berstruktur: Gunakan JSON semasa mengendalikan medan yang tidak dapat diramalkan atau jarang seperti sifat tersuai.
Mod Dinamik: JSON menyediakan fleksibiliti apabila keperluan data berubah dengan kerap.
Data hidraft atau bersarang: JSON menyokong data dengan hubungan ibu bapa atau anak.
Integrasi API: Simpan muatan, respons, atau log sebagai dokumen JSON.

Walau bagaimanapun, elakkan menggunakan JSON apabila:

Bidang yang sering ditanya yang memerlukan pengindeksan (pengindeksan fungsi dapat membantu, tetapi reka bentuk hubungan biasanya lebih cepat).
Data hubungan ketat yang diseragamkan diperlukan.
Pertanyaan kompleks di laluan JSON dapat merendahkan prestasi.

Bagaimana untuk menyimpan data JSON di MySQL?

Untuk menyimpan data JSON di MySQL, anda mempunyai dua pilihan utama. Pertama, anda boleh menggunakan jenis data JSON yang diperkenalkan di MySQL untuk membuat jadual dengan lajur JSON. Kaedah ini menyediakan penyimpanan berstruktur dan prestasi pertanyaan yang lebih baik untuk data JSON.

Sebagai alternatif, anda boleh menyimpan data JSON sebagai teks dalam varchar biasa atau lajur teks. Kaedah ini terpakai apabila perlu menyimpan dan mengambil data JSON tanpa operasi pangkalan data yang kompleks.

Bagaimana untuk mengindeks data JSON dalam MySQL?

Walaupun anda tidak dapat mengindeks lajur JSON secara langsung, MySQL membolehkan anda membuat indeks fungsi pada lajur yang dihasilkan yang diperolehi dari nilai JSON.

Sebagai contoh, untuk mengindeks elemen pertama pelbagai JSON:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Kaedah ini meningkatkan prestasi pertanyaan untuk laluan JSON yang sering diakses.

Untuk data JSON, adakah anda menggunakan pangkalan data MySQL atau NoSQL?

ia bergantung pada keperluan projek anda:

• Jika anda memerlukan penyimpanan hubungan, kadang-kadang memproses data separa berstruktur, sifat tersuai, atau data hierarki dalam model hubungan, pilih MySQL.
• Jika projek anda melibatkan banyak penyimpanan JSON, skema fleksibel dan operasi berasaskan dokumen sebagai kes penggunaan utamanya, pilih pangkalan data NoSQL (seperti MongoDB).

Sokongan JSON MySQL sangat sesuai untuk beban kerja hibrid, tetapi ia bukan pengganti lengkap untuk pangkalan data NoSQL khusus untuk penyimpanan dokumen.

Bagaimana untuk mengekstrak nilai -nilai tertentu dari medan mysql json?

Untuk mengekstrak nilai khusus dari medan MySQL JSON, gunakan fungsi JSON_EXTRACT () atau singkatan -& gt;

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Bagaimana untuk menanyakan dan menapis data dalam medan MySQL JSON?

Untuk menanyakan dan menapis data yang disimpan dalam medan MySQL JSON, anda boleh menggunakan fungsi seperti json_contains () dan json_search (). Anda juga boleh menggunakan json_extract () untuk mendapatkan nilai tertentu untuk penapisan selanjutnya.

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Atas ialah kandungan terperinci Cara Menggunakan Bidang Data JSON dalam Pangkalan Data MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!