Lưu trữ mảng JSON trong MySQL

Tóm lược. trong hướng dẫn này, bạn sẽ học cách sử dụng kiểu dữ liệu JSON của MySQL để lưu trữ các tài liệu JSON trong cơ sở dữ liệu

Giới thiệu về kiểu dữ liệu JSON của MySQL

MySQL hỗ trợ kiểu dữ liệu JSON gốc kể từ phiên bản 5. 7. 8. Kiểu dữ liệu JSON gốc cho phép bạn lưu trữ các tài liệu JSON hiệu quả hơn so với định dạng văn bản JSON trong các phiên bản trước

MySQL lưu trữ các tài liệu JSON ở định dạng nội bộ cho phép truy cập nhanh vào các phần tử tài liệu. Định dạng nhị phân JSON được cấu trúc theo cách cho phép máy chủ tìm kiếm các giá trị trong tài liệu JSON trực tiếp bằng chỉ mục khóa hoặc mảng, rất nhanh

Việc lưu trữ tài liệu JSON gần giống với việc lưu trữ dữ liệu

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

1 hoặc

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

2

Để định nghĩa một cột có kiểu dữ liệu là JSON, bạn sử dụng cú pháp sau

CREATE TABLE table_name (
    ...
    json_column_name JSON,
    .. 
);
Code language: SQL (Structured Query Language) (sql)

Lưu ý rằng cột JSON không thể có giá trị mặc định. Ngoài ra, một cột JSON không thể được lập chỉ mục trực tiếp. Thay vào đó, bạn có thể tạo chỉ mục trên cột được tạo có chứa các giá trị được trích xuất từ ​​cột JSON. Khi bạn truy vấn dữ liệu từ cột JSON, trình tối ưu hóa MySQL sẽ tìm các chỉ mục tương thích trên các cột ảo khớp với các biểu thức JSON

Ví dụ về kiểu dữ liệu MySQL JSON

Giả sử, chúng tôi phải theo dõi khách truy cập và hành động của họ trên trang web của chúng tôi. Một số khách truy cập có thể chỉ xem các trang và những người khác có thể xem các trang và mua sản phẩm. Để lưu trữ thông tin này, chúng tôi sẽ tạo một bảng mới có tên là

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);



Code language: SQL (Structured Query Language) (sql)

3

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

Mỗi sự kiện trong bảng sự kiện có một

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

4 xác định duy nhất sự kiện. Một sự kiện cũng có tên e. g. , lượt xem trang, mua hàng, v.v. , Cột

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

5 dùng để lưu thông tin người truy cập

Các cột

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

6 và

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

7 là các cột JSON. Chúng được sử dụng để lưu trữ các thuộc tính của một sự kiện và thông số kỹ thuật của trình duyệt mà khách truy cập sử dụng để duyệt trang web

Hãy chèn một số dữ liệu vào bảng

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

3

INSERT INTO events(event_name, visitor,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '2',
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '1',
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
   '3',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
   '4',
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '4',
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);
Code language: SQL (Structured Query Language) (sql)

Để lấy các giá trị ra khỏi các cột JSON, bạn sử dụng toán tử đường dẫn cột (

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

9)

SELECT id, browser->'$.name' browser
FROM events;
Code language: SQL (Structured Query Language) (sql)

Truy vấn này trả về đầu ra sau

+----+-----------+
| id | browser   |
+----+-----------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+-----------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

Lưu ý rằng dữ liệu trong cột

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

7 được bao quanh bởi dấu ngoặc kép. Để bỏ dấu ngoặc kép, bạn sử dụng toán tử đường dẫn nội tuyến (

INSERT INTO events(event_name, visitor,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '2',
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '1',
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
   '3',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
   '4',
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '4',
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);
Code language: SQL (Structured Query Language) (sql)

1) như sau

SELECT id, browser->>'$.name' browser
FROM events;



Code language: SQL (Structured Query Language) (sql)

Như bạn có thể thấy trong đầu ra sau, các dấu ngoặc kép đã bị xóa

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

Để có được việc sử dụng trình duyệt, bạn có thể sử dụng câu lệnh sau

SELECT browser->>'$.name' browser, 
      count(browser)
FROM events
GROUP BY browser->>'$.name';
Code language: SQL (Structured Query Language) (sql)

Đầu ra của truy vấn như sau

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rows in set (0.02 sec)
Code language: SQL (Structured Query Language) (sql)

Để tính tổng doanh thu theo khách truy cập, bạn sử dụng truy vấn sau

SELECT visitor, SUM(properties->>'$.amount') revenue
FROM events
WHERE properties->>'$.amount' > 0
GROUP BY visitor;
Code language: SQL (Structured Query Language) (sql)

Đây là đầu ra

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

0

Trong hướng dẫn này, bạn đã tìm hiểu về kiểu dữ liệu JSON của MySQL và cách sử dụng nó để lưu trữ các tài liệu JSON trong cơ sở dữ liệu

Tôi có thể lưu trữ mảng JSON trong MySQL không?

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

Làm cách nào để lưu trữ tệp JSON trong MySQL?

Làm cách nào để lưu trữ dữ liệu trong một mảng trong MySQL?