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
1 hoặcCode language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
2Code language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
Để định nghĩa một cột có kiểu dữ liệu là JSON, bạn sử dụng cú pháp sau
Code language: SQL [Structured Query Language] [sql]
CREATE TABLE table_name [ ... json_column_name JSON, .. ];
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à
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]
Code language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
Mỗi sự kiện trong bảng sự kiện có một
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ộtCode language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
5 dùng để lưu thông tin người truy cậpCode language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
Các cột
6 vàCode language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
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 webCode language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
Hãy chèn một số dữ liệu vào bảng
3Code language: SQL [Structured Query Language] [sql]
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]
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 } }' ];
Để 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 [
9]Code language: SQL [Structured Query Language] [sql]
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]
SELECT id, browser->'$.name' browser FROM events;
Truy vấn này trả về đầu ra sau
Code language: SQL [Structured Query Language] [sql]
+----+-----------+ | id | browser | +----+-----------+ | 1 | "Safari" | | 2 | "Firefox" | | 3 | "Safari" | | 4 | "Firefox" | | 5 | "Firefox" | | 6 | "Chrome" | +----+-----------+ 6 rows in set [0.00 sec]
Lưu ý rằng dữ liệu trong cột
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 [Code language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
1] như sauCode language: SQL [Structured Query Language] [sql]
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 } }' ];
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
Code language: SQL [Structured Query Language] [sql]
+----+---------+ | id | browser | +----+---------+ | 1 | Safari | | 2 | Firefox | | 3 | Safari | | 4 | Firefox | | 5 | Firefox | | 6 | Chrome | +----+---------+ 6 rows in set [0.00 sec]
Để có được việc sử dụng trình duyệt, bạn có thể sử dụng câu lệnh sau
Code language: SQL [Structured Query Language] [sql]
SELECT browser->>'$.name' browser, count[browser] FROM events GROUP BY browser->>'$.name';
Đầu ra của truy vấn như sau
Code language: SQL [Structured Query Language] [sql]
+---------+----------------+ | browser | count[browser] | +---------+----------------+ | Safari | 2 | | Firefox | 3 | | Chrome | 1 | +---------+----------------+ 3 rows in set [0.02 sec]
Để tính tổng doanh thu theo khách truy cập, bạn sử dụng truy vấn sau
Code language: SQL [Structured Query Language] [sql]
SELECT visitor, SUM[properties->>'$.amount'] revenue FROM events WHERE properties->>'$.amount' > 0 GROUP BY visitor;
Đây là đầu ra
0Code language: SQL [Structured Query Language] [sql]
CREATE TABLE events[ id int auto_increment primary key, event_name varchar[255], visitor varchar[255], properties json, browser json ];
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