Cách lưu JSON trong MySQL

Gần đây trong công việc, tôi gặp phải một vấn đề là lưu dữ liệu JSON của các điều kiện kết thúc trong cơ sở dữ liệu MySQL và phân tích cú pháp bất cứ khi nào tôi cần. Sau vài giờ google và thử và kiểm tra, đây là cách tôi giải quyết

Đầu tiên, để làm rõ vấn đề, đây là tập hợp JSON mà tôi phải lưu trữ trong cơ sở dữ liệu MySQL

{
  "condition": "and",
  "rules": [
    {
      "field": "age",
      "operator": "<=",
      "entity": "physical",
      "value": 10
    },
    {
      "field": "birthday",
      "operator": "=",
      "entity": "nonphysical",
      "value": "2019-11-10T13:46:54.128Z"
    }
  ]
}

Và, nhiệm vụ của tôi là lưu trữ nó trong MySQL và phân tích cú pháp bất cứ khi nào tôi cần

MySQL hỗ trợ lưu dữ liệu JSON dưới dạng Chuỗi và Phân tích cú pháp JSON bằng các hàm JSON

Trước hết, do bản chất của các điều kiện kết thúc, tôi sẽ đặt Bộ lưu trữ Chuỗi trong bộ lưu trữ lớn nhất — LONGTEXT

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Chèn dữ liệu

Sau đó, tôi có thể CẬP NHẬT điều kiện kết thúc cho trận đấu 143 dưới dạng

UPDATE MatchInfo SET endingCondition = '{
  "condition": "and",
  "rules": [
    {
      "field": "age",
      "operator": "<=",
      "entity": "physical",
      "value": 10
    },
    {
      "field": "birthday",
      "operator": "=",
      "entity": "nonphysical",
      "value": "2019-11-10T13:46:54.128Z"
    }
  ]
}' WHERE id = 143;

Lưu ý rằng dữ liệu JSON được bao bọc bằng dấu nháy đơn (‘ ‘)

Hoặc, nếu tôi muốn thêm hàng mới vào bảng có điều kiện kết thúc

INSERT INTO MatchInfo (name, start, .. endingCondition) 
VALUES ("new match", "2019-11-03 00:00:00", ..., '{
  "condition": "and",
  "rules": [
    {
      "field": "age",
      "operator": "<=",
      "entity": "physical",
      "value": 10
    },
    {
      "field": "birthday",
      "operator": "=",
      "entity": "nonphysical",
      "value": "2019-11-10T13:46:54.128Z"
    }
  ]
}');

Phân tích dữ liệu

Để phân tích cú pháp dữ liệu trong Đối tượng, bạn phải sử dụng hàm JSON_EXTRACT

Để CHỌN điều kiện từ điều kiện kết thúc

SELECT JSON_EXTRACT(endingCondition, '$.condition') FROM matchInfo where ID = 143;// "and"

với $ đề cập đến thư mục gốc JSON

Sau đó, để CHỌN giá trị của quy tắc đầu tiên

SELECT JSON_EXTRACT(endingCondition, '$.rules[0].value') FROM matchInfo where ID = 143;// 10

Hoặc, nếu tôi muốn chọn danh sách Quy tắc để làm việc trên

SELECT JSON_EXTRACT(endingCondition, '$.rules') FROM matchInfo where ID = 143;// [{"field": "age", "value": 10, "entity": "physical", "operator": "<="}, {"field": "birthday", "value": "2019-11-10T13:46:54.128Z", "entity": "nonphysical", "operator": "="}]

XONG. Khi bạn nhận được giá trị của JSON, bạn có thể sử dụng máy chủ để diễn giải nó và thực hiện logic

Xin chúc mừng. Bây giờ bạn đã biết một số cách cơ bản để sử dụng dữ liệu JSON trong cơ sở dữ liệu MySQL. Phương pháp này cũng sẽ áp dụng cho SQL Server, PostgreSQL (với cú pháp hơi khác một chút)

Bài viết này giải thích các cách khác nhau để xuất dữ liệu MySQL sang định dạng tệp JSON. JSON là viết tắt của Ký hiệu đối tượng JavaScript. Đây là một định dạng tệp nhẹ để lưu trữ và truyền dữ liệu, thường được sử dụng khi dữ liệu được gửi từ máy chủ đến trang web

Trong phần đầu tiên của bài viết, chúng ta sẽ xem xét một số chức năng của MySQL như một giải pháp để xuất dữ liệu MySQL sang JSON. Tiếp theo, chúng ta sẽ xem cách sử dụng các hàm đầu ra MySQL Shell và JSON để đạt được cùng một mục tiêu và cuối cùng nhưng không kém phần quan trọng, chúng ta sẽ làm quen với một tiện ích mở rộng, ApexSQL Database Power Tools for VS Code và tính năng Xuất sang JSON của nó

Phương pháp xuất sau đây sẽ được sử dụng

• và chức năng
• và chức năng
• MySQL Shell và đầu ra định dạng JSON
• Xuất sang JSON bằng phần mềm của bên thứ ba

Đối với bài viết này, mã SQL bên dưới sẽ được sử dụng làm ví dụ

CREATE DATABASE addresses;

    USE addresses;
    
    CREATE TABLE location (
      address_id int NOT NULL AUTO_INCREMENT,
      address varchar(50) NOT NULL,
      address2 varchar(50) DEFAULT NULL,
      PRIMARY KEY (address_id)
    );
    
    INSERT INTO location VALUES
    (NULL, '1586 Guaruj Place', '47 MySakila Drive'),
    (NULL, '934 San Felipe de Puerto Plata Street', NULL),
    (NULL, '360 Toulouse Parkway', '270, Toulon Boulevard');

(nguồn. Cách xuất dữ liệu MySQL sang CSV)

Xuất dữ liệu MySQL sang JSON bằng hàm CONCAT() và GROUP_CONCAT()

Sử dụng kết hợp các hàm, dữ liệu từ chuỗi SQL có thể được chuyển đổi thành định dạng JSON

Trong trình chỉnh sửa truy vấn, hãy thực thi đoạn mã sau

________số 8

Kết quả sẽ được một cái gì đó như thế này

[{address_id. ’1’địa chỉ. '1586 Guaruj Place',địa chỉ2. '47 MySakila Drive'},{address_id. ’3’địa chỉ. '360 Toulouse Parkway',địa chỉ2. '270, Đại lộ Toulon'}]

Để xuất các kết quả này từ kết quả truy vấn sang tệp JSON, hãy thêm một dòng mã nữa

SELECT 
    CONCAT("[",
         GROUP_CONCAT(
              CONCAT("{address_id:'",address_id,"'"),
              CONCAT("address:'",address,"'"),
              CONCAT(",address2:'",address2,"'}")
         )
    ,"]") 
AS json FROM location
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/location.json'

Sau khi thực hiện, kết quả sẽ tương tự như trong hình bên dưới

• Bạn có thể tìm hiểu thêm về cách sử dụng INTO OUTFILE trong bài viết Cách xuất dữ liệu MySQL sang CSV

Xuất dữ liệu MySQL sang JSON bằng các hàm JSON_OBJECT và JSON_ARRAYAGG

Hàm JSON_OBJECT ánh xạ một số lượng đối số khác nhau tới một đối tượng JSON. Nó tạo một danh sách các cặp khóa-giá trị và trả về một đối tượng JSON chứa các cặp đó. Bằng cách cung cấp mỗi cặp khóa/giá trị dưới dạng hai đối số riêng biệt. Mỗi cặp trở thành một cặp khóa/giá trị trong đối tượng JSON kết quả

Khi đoạn mã dưới đây được thực thi

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Kết quả sẽ giống như thế này

Bây giờ, hãy sửa đổi mã để xuất dữ liệu sang tệp JSON. Thực hiện như sau

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Khi mở tệp JSON vừa tạo ra, kết quả sẽ như thế này

Nếu bạn muốn tập hợp kết quả thành một hàng chứa một mảng JSON với một đối tượng trên mỗi hàng, thì nên sử dụng hàm JSON_ARRAYAGG kết hợp với hàm JSON_OBJECT. Mỗi hàng của tập hợp kết quả kết thúc dưới dạng một phần tử trong mảng

Thực hiện kết quả dưới đây để xem kết quả

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Thêm lệnh INTO OUTFILE để xuất dữ liệu MySQL ra file JSON

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

• Ghi chú. Hàm JSON_ARRAYAGG có sẵn từ máy chủ MySQL 8. 0 phiên bản

Xuất dữ liệu MySQL sang JSON bằng cách sử dụng MySQL Shell và Định dạng JSON

MySQL Shell cung cấp một số tùy chọn định dạng JSON để in tập kết quả

• json – trả về các giá trị JSON theo cách được định dạng độc đáo, giúp con người dễ đọc hơn
• ndjson – trả về JSON thô được phân tách bằng dòng mới
• json/array – trả về JSON thô được bọc trong một mảng JSON

Khởi động MySQL Shell và kết nối với cơ sở dữ liệu mà bạn muốn xuất dữ liệu từ đó

Để kết nối với cơ sở dữ liệu, trong MySQL Shell, hãy thực hiện lệnh sau

\connect root@localhost. 33060/địa chỉ

Định dạng tệp đầu ra json

Để định dạng các giá trị dữ liệu trong đầu ra json, trong MySQL Shell hãy thực thi đoạn mã sau

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Sau đó, thực thi đoạn mã sau để lấy dữ liệu từ bảng vị trí

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Đầu ra sẽ trông giống như thế này

• {
• “địa chỉ_id”. 1,
• "Địa chỉ". “Địa điểm 1586 Guaruj”,
• "Địa chỉ 2". “Ổ đĩa 47 MySakila”
• }
• {
• “địa chỉ_id”. 2,
• "Địa chỉ". “934 Phố San Felipe de Puerto Plata”,
• "Địa chỉ 2". vô giá trị
• }
• {
• “địa chỉ_id”. 3,
• "Địa chỉ". “Đại lộ 360 Toulouse”,
• "Địa chỉ 2". “270, Đại lộ Toulon”
• }

Định dạng tệp đầu ra ndjson

Trong MySQL Shell, hãy thực thi đoạn mã sau để thay đổi định dạng kết quả

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Bây giờ, khi mã được thực thi

ALTER TABLE 'MatchInfo'
ADD COLUMN 'endingCondition' LONGTEXT NOT NULL;

Kết quả sẽ như thế này

{“address_id”. 1, "địa chỉ". ”1586 Guaruj Place”,”địa chỉ2″. ”47 Ổ đĩa MySakila”}
{“address_id”. 2, "địa chỉ". ”934 Phố San Felipe de Puerto Plata”,”địa chỉ2″. null}
{“address_id”. 3, "địa chỉ". ”360 Toulouse Parkway”,”địa chỉ2″. ”270, Đại lộ Toulon”}

Định dạng tệp đầu ra json/mảng

Để chuyển sang định dạng kết quả json/mảng, trong MySQL Shell hãy thực thi mã bên dưới

SELECT 
    CONCAT("[",
         GROUP_CONCAT(
              CONCAT("{address_id:'",address_id,"'"),
              CONCAT("address:'",address,"'"),
              CONCAT(",address2:'",address2,"'}")
         )
    ,"]") 
AS json FROM location;

Bây giờ, khi một câu lệnh SELECT được thực thi, đầu ra sẽ như thế này

[
{“address_id”. 1, "địa chỉ". ”1586 Guaruj Place”,”địa chỉ2″. ”47 MySakila Drive”},
{“address_id”. 2, "địa chỉ". ”934 Phố San Felipe de Puerto Plata”,”địa chỉ2″. null},
{“address_id”. 3, "địa chỉ". ”360 Toulouse Parkway”,”địa chỉ2″. ”270, Đại lộ Toulon”}
]

Xuất dữ liệu MySQL bằng phần mềm của bên thứ ba

ApexSQL Database Power Tools for VS Code là một tiện ích mở rộng có thể xuất dữ liệu từ lưới kết quả sang định dạng tệp JSON chỉ trong vài bước. Bên cạnh JSON, tiện ích mở rộng có các tùy chọn để xuất dữ liệu MySQL sang định dạng tệp CSV, Excel và HTML

Để xuất dữ liệu, trong Visual Studio Code bên dưới khung trình khám phá máy chủ ApexSQL, hãy mở một truy vấn mới

Trong trình soạn thảo truy vấn mới mở, hãy thực thi câu lệnh SELECT mà bạn muốn xuất kết quả từ đó

Ở góc trên cùng bên phải của lưới kết quả, nhấp vào biểu tượng Xuất sang JSON. Trong cửa sổ Lưu dưới dạng, chọn vị trí xuất dữ liệu MySQL sang tệp JSON, bên dưới Tên tệp, nhập tên cho tệp và nhấp vào nút Lưu

Sau khi tệp được lưu, ở góc dưới cùng bên phải của ứng dụng, một thông báo thông tin xuất hiện với nút Hiển thị. Khi click vào nó sẽ hiện ra nơi lưu file

Làm cách nào để lưu tệp JSON trong cơ sở dữ liệu MySQL?

Làm cách nào để lưu dữ liệu JSON trong cơ sở dữ liệu?

Làm cách nào để thêm JSON trong MySQL?

Làm cách nào để tạo tệp JSON từ MySQL?