JSON MySQL

Ký hiệu đối tượng JavaScript (JSON) là định dạng tệp dựa trên văn bản có trọng lượng nhẹ tương tự như YAML hoặc XML giúp đơn giản hóa việc trao đổi dữ liệu. Nó được phát minh bởi Douglas Crockford vào đầu những năm 2000 và ngày càng trở nên phổ biến với sự gia tăng của cơ sở dữ liệu dựa trên tài liệu (còn gọi là NoSQL)

JSON hỗ trợ các chuỗi, số, booleans, đối tượng và mảng cũng như các giá trị null. Một ví dụ JSON đơn giản chứa các cặp khóa-giá trị, một đối tượng "bandMembers" và một mảng "songs" sẽ như thế này

JSON

{
  "artist": "Starlord Band",
  "bandMembers": {
    "vocals": "Steve Szczepkowski",
    "guitar": "Yohann Boudreault",
    "bass": "Yannick T.",
    "drums": "Vince T."
  },
  "bandMembersCount": 4,
  "album": "Space Rider",
  "releaseDate": "2021-10-25",
  "songs": [
    "Zero to Hero",
    "Space Riders with No Names",
    "Ghost",
    "Bit of Good (Bit of Bad)",
    "Watch me shine",
    "We’re Here",
    "The Darkness inside",
    "No Guts No Glory",
    "All for One",
    "Solar Skies"
  ],
  "songsCount": 10
}

MySQL đã triển khai hỗ trợ cơ bản cho kiểu dữ liệu JSON với phiên bản 5. 7. 8 vào giữa năm 2015 và đã bổ sung thêm các cải tiến và tính năng mới kể từ đó. Bảy năm sau, MySQL hiện hỗ trợ nhiều hàm SQL để hoạt động với các tài liệu JSON, nó cung cấp xác thực nội dung tự động, cho phép cập nhật tại chỗ một phần và sử dụng định dạng lưu trữ nhị phân để tăng hiệu suất

Cơ sở dữ liệu quan hệ tuân theo một cấu trúc được xác định trước và nhấn mạnh vào sự gắn kết và toàn vẹn của dữ liệu. Để đạt được điều này, các loại và định dạng dữ liệu cũng như kích thước dữ liệu của nó đều được thực thi nghiêm ngặt bằng phương tiện của lược đồ

Kiểu dữ liệu JSON hơi trái ngược với tính chất nghiêm ngặt của một lược đồ như vậy. Nó cho phép bạn thoát ra khỏi nó, để có được sự linh hoạt khi bạn cần. Và nó tỏ ra hữu ích miễn là bạn nhận thức được sự đánh đổi được mô tả trong phần tiếp theo

Một số ví dụ về thời điểm có thể hữu ích khi lưu trữ dữ liệu dưới dạng tài liệu JSON là

• Đầu ra nhật ký được viết bởi một ứng dụng hoặc máy chủ
• Phản hồi API còn lại mà bạn muốn lưu trữ
• Lưu trữ dữ liệu cấu hình
• Một tập hợp các thực thể có thuộc tính biến

Bạn cũng có thể sử dụng các tài liệu JSON trong thiết kế cơ sở dữ liệu quan hệ của mình để chia nhỏ các mối quan hệ phức tạp trải rộng trên nhiều bảng. Quá trình này được gọi là không chuẩn hóa, là một cơ sở dữ liệu quan hệ chống mẫu khác. Tuy nhiên, trong một số trường hợp nhất định, nó có thể giúp cải thiện hiệu suất tùy thuộc vào trường hợp sử dụng và thiết kế ứng dụng của bạn

Tính linh hoạt được cung cấp bởi kiểu dữ liệu JSON đi kèm với một số lưu ý mà bạn cần lưu ý

Đáng chú ý nhất, bạn sẽ cần tính đến việc các tài liệu JSON thường yêu cầu nhiều dung lượng lưu trữ hơn. Trong MySQL, dấu chân lưu trữ của chúng tương tự như kiểu dữ liệu

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

1 hoặc

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

2. Tuy nhiên, có một chi phí chung do mã hóa nhị phân và siêu dữ liệu và từ điển được thêm vào tồn tại để tăng tốc độ đọc cơ sở dữ liệu. Một nguyên tắc nhỏ là một chuỗi được lưu trữ trong JSON sử dụng khoảng 4 đến 10 byte dung lượng lưu trữ bổ sung so với cột

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

1 hoặc

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

2

Nếu bạn muốn tối ưu hóa lược đồ cơ sở dữ liệu của mình theo hướng hiệu quả lưu trữ, thì tốt nhất nên sử dụng các kiểu dữ liệu truyền thống hơn của MySQL (

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

5,

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

6,

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

7, v.v.), vì tất cả chúng đều có hiệu quả lưu trữ cao hơn JSON.

Một cảnh báo khác cần lưu ý là tác động hiệu suất. Tương tự như các định dạng nhị phân khác, tài liệu JSON không thể được lập chỉ mục trực tiếp. Điều này và lượng dữ liệu thay đổi mà bạn có thể lưu trữ trong tài liệu JSON, có nghĩa là việc truy vấn một cột JSON thường sử dụng nhiều không gian bộ đệm hơn và trả về các tập kết quả lớn hơn, dẫn đến nhiều trao đổi dữ liệu hơn

Ghi chú. Mặc dù các tài liệu JSON không thể được lập chỉ mục trực tiếp trong MySQL, nhưng chúng có thể được lập chỉ mục gián tiếp. Tìm hiểu cách lập chỉ mục JSON trong MYSQL

Mặc dù tài liệu JSON được lưu trữ trong MySQL có thể lên tới 1 GB, nhưng theo lý thuyết, bạn nên giữ tài liệu JSON ở kích thước chỉ vài MB. Trên PlanetScale, chúng tôi hỗ trợ tài liệu JSON lên tới 67 MB

MySQL đi kèm với một bộ chức năng JSON mạnh mẽ cho phép bạn tạo, cập nhật, đọc hoặc xác thực tài liệu JSON của mình. PlanetScale hỗ trợ tất cả các hàm JSON ngoại trừ

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

8

Hãy cùng nhau xem qua một vài ví dụ

Đầu tiên, chúng ta tạo một bảng có cột

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

9 và cột

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

0

SQL

CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);

Một bảng trống cần dữ liệu, vì vậy hãy sử dụng

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

1 để thêm một số

SQL

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

Làm thế nào để chúng ta biết đây là một mảng?

SQL

SELECT JSON_TYPE(songs) FROM songs;
+------------------+
| json_type(songs) |
+------------------+
| ARRAY            |
+------------------+

Nếu chúng ta muốn trích xuất một mục từ mảng, chúng ta có thể làm như vậy với

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

3. Trong ví dụ dưới đây, chúng tôi trích xuất phần tử thứ tư từ mảng

SQL

blog-mysql-json/main> SELECT JSON_EXTRACT(songs, '$[3]') FROM songs;
+-----------------------------+
| json_extract(songs, '$[3]') |
+-----------------------------+
| "Ghost"                     |
+-----------------------------+

Chúng ta cũng có thể sử dụng

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

4, là toán tử tương đương với

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

3

SQL

blog-mysql-json/main> SELECT songs->'$[3]' FROM songs;
+-----------------+
| songs -> '$[3]' |
+-----------------+
| "Ghost"         |
+-----------------+

Nếu chúng ta cần kết quả không được trích dẫn, chúng ta có thể sử dụng

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

6, viết tắt của

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

7

SQL

blog-mysql-json/main> SELECT songs->>'$[3]' FROM songs;
+------------------+
| songs ->> '$[3]' |
+------------------+
| Ghost            |
+------------------+

Nếu chúng ta cần thêm dữ liệu vào mảng JSON, chúng ta có thể sử dụng

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

8 hoặc

INSERT INTO songs VALUES(id, JSON_ARRAY('Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies'));

9 để cập nhật nó

SQL

UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', "One last song");
UPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', "First song");

Để biết thêm thông tin về cách sử dụng tất cả các hàm JSON khác nhau, vui lòng xem tài liệu của MySQL về kiểu dữ liệu JSON và tham chiếu Hàm JSON

JSON trong MySQL là gì?

Bạn có nên sử dụng JSON trong MySQL không?

Làm cách nào để lấy dữ liệu JSON trong MySQL?

Làm cách nào để chèn JSON vào MySQL?