Bài viết của tôi “SQL vs NoSQL. Sự khác biệt” lưu ý rằng ranh giới giữa cơ sở dữ liệu SQL và NoSQL ngày càng trở nên mờ nhạt, với mỗi bên áp dụng các tính năng từ bên kia. mysql 5. 7+ cơ sở dữ liệu InnoDB và PostgreSQL 9. 2+ đều hỗ trợ trực tiếp các loại tài liệu JSON trong một trường duy nhất. Trong bài viết này, chúng ta sẽ kiểm tra MySQL 8. 0 triển khai JSON chi tiết hơn
Lưu ý rằng bất kỳ cơ sở dữ liệu nào cũng sẽ chấp nhận các tài liệu JSON dưới dạng một chuỗi đơn. Tuy nhiên, MySQL và PostgreSQL hỗ trợ dữ liệu JSON được xác thực theo các cặp khóa/giá trị thực thay vì một chuỗi cơ bản
Chỉ Vì Bạn Có Thể Lưu trữ JSON…
… nó không theo bạn nên
Chuẩn hóa là một kỹ thuật được sử dụng để tối ưu hóa cấu trúc cơ sở dữ liệu. Quy tắc Biểu mẫu thông thường đầu tiên [1NF] quy định rằng mọi cột phải chứa một giá trị — quy tắc này rõ ràng bị phá vỡ bằng cách lưu trữ các tài liệu JSON đa giá trị
Nếu bạn có các yêu cầu rõ ràng về dữ liệu quan hệ, hãy sử dụng các trường giá trị đơn thích hợp. JSON nên được sử dụng một cách tiết kiệm như là phương sách cuối cùng. Không thể lập chỉ mục các trường giá trị JSON, vì vậy hãy tránh sử dụng nó trên các cột được cập nhật hoặc tìm kiếm thường xuyên. Ngoài ra, ít ứng dụng khách hỗ trợ JSON hơn và công nghệ này mới hơn nên có thể kém ổn định hơn các loại khác
Điều đó nói rằng, có những trường hợp sử dụng JSON tốt cho dữ liệu được điền thưa thớt hoặc thuộc tính tùy chỉnh
Tạo bảng với trường JSON
Xem xét một cửa hàng bán sách. Tất cả sách đều có ID, ISBN, tên sách, nhà xuất bản, số trang và dữ liệu quan hệ rõ ràng khác. Giả sử bạn muốn thêm bất kỳ số lượng thẻ danh mục nào vào mỗi cuốn sách. Bạn có thể đạt được điều này trong SQL bằng cách sử dụng
- một bảng thẻ lưu trữ từng tên thẻ với một ID duy nhất và
- một bảng sơ đồ thẻ với nhiều bản ghi ánh xạ ID sách tới ID thẻ
Nó sẽ hoạt động, nhưng nó cồng kềnh và tốn nhiều công sức cho một tính năng nhỏ. Do đó, bạn có thể xác định trường JSON của thẻ trong bảng sách của cơ sở dữ liệu MySQL của mình
CREATE TABLE `book` [
`id` MEDIUMINT[8] UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR[200] NOT NULL,
`tags` JSON DEFAULT NULL,
PRIMARY KEY [`id`]
] ENGINE=INNODB;
Lưu ý rằng các cột JSON không thể có giá trị mặc định, được sử dụng làm khóa chính, được sử dụng làm khóa ngoại hoặc có chỉ mục. Bạn có thể tạo các chỉ mục phụ trên các cột ảo được tạo, nhưng việc giữ lại một giá trị trong một trường riêng biệt sẽ dễ dàng và thực tế hơn nếu cần có các chỉ mục
Thêm dữ liệu JSON
Toàn bộ tài liệu JSON có thể được chuyển qua các câu lệnh INSERT hoặc UPDATE. Ví dụ: các thẻ sách của chúng tôi có thể được truyền dưới dạng một mảng [bên trong một chuỗi]
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
JSON cũng có thể được tạo bằng những
, tạo mảng. Ví dụ
-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
, tạo ra các đối tượng. Ví dụ
-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
, trích dẫn một chuỗi dưới dạng giá trị JSON. Ví dụ
-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE['[1, 2, "abc"]'];
hoặc bạn có thể
5INSERT INTO `book` [`title`, `tags`] VALUES [ 'ECMAScript 2015: A SitePoint Anthology', '["JavaScript", "ES2015", "JSON"]' ];
Cho phép bạn kiểm tra các loại giá trị JSON. Nó sẽ trả về ĐỐI TƯỢNG, Mảng, loại vô hướng [INTEGER, BOOLEAN, v.v.], NULL hoặc lỗi. Ví dụ
-- returns ARRAY:
SELECT JSON_TYPE['[1, 2, "abc"]'];
-- returns OBJECT:
SELECT JSON_TYPE['{"a": 1, "b": 2}'];
-- returns an error:
SELECT JSON_TYPE['{"a": 1, "b": 2'];
Trả về 1 nếu JSON hợp lệ hoặc 0 nếu không
-- returns 1:
SELECT JSON_TYPE['[1, 2, "abc"]'];
-- returns 1:
SELECT JSON_TYPE['{"a": 1, "b": 2}'];
-- returns 0:
SELECT JSON_TYPE['{"a": 1, "b": 2'];
Cố gắng chèn một tài liệu JSON không hợp lệ sẽ gây ra lỗi và toàn bộ bản ghi sẽ không được chèn/cập nhật
Tìm kiếm dữ liệu JSON
Chấp nhận tài liệu JSON đang được tìm kiếm và tài liệu khác để so sánh với. Nó trả về 1 khi tìm thấy kết quả khớp. Ví dụ
________số 8_______Tương tự trả về đường dẫn đến trận đấu đã cho hoặc NULL khi không có trận đấu nào. Nó chuyển qua tài liệu JSON đang được tìm kiếm,
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
6 để tìm kết quả khớp đầu tiên hoặc INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
7 để tìm tất cả các kết quả khớp và một chuỗi tìm kiếm [trong đó INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
8 khớp với bất kỳ số lượng ký tự nào và INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
9 khớp một ký tự theo cách giống hệt với ]. Ví dụ-- all books with tags starting 'Java':
SELECT * FROM `book` WHERE JSON_SEARCH[tags, 'one', 'Java%'] IS NOT NULL;
Đường dẫn JSON
Một đường dẫn JSON nhắm mục tiêu các giá trị và có thể được sử dụng để trích xuất hoặc sửa đổi các phần của tài liệu JSON. Chứng minh điều này bằng cách trích xuất một hoặc nhiều giá trị
-- returns "SitePoint":
SELECT JSON_EXTRACT['{"id": 1, "website": "SitePoint"}', '$.website'];
Tất cả các định nghĩa đường dẫn bắt đầu bằng một
-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY[1, 2, 'abc'];
1 theo sau là các bộ chọn khác- một khoảng thời gian theo sau bởi một tên, chẳng hạn như
2-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
3 trong đó N là vị trí trong mảng không có chỉ mục-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
- ký tự đại diện
4 đánh giá tất cả các thành viên của một đối tượng-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
- ký tự đại diện
5 đánh giá tất cả các phần tử của một mảng-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
- ký tự đại diện
6 đánh giá tất cả các đường dẫn bắt đầu bằng tiền tố được đặt tên và kết thúc bằng hậu tố được đặt tên-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
Các ví dụ sau đây đề cập đến tài liệu JSON sau đây
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
0đường dẫn ví dụ
7 trả lại-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
8-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
9 trả lại-- returns [1, 2, "abc"]: SELECT JSON_ARRAY[1, 2, 'abc'];
0-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
1 trả lại-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
2-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
3 trả lại-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
4-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
5 trả lại-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
6-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
Trích xuất đường dẫn JSON trong truy vấn
Bạn có thể trích xuất tên và thẻ đầu tiên của bảng sách của mình bằng truy vấn
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
1Đối với một ví dụ phức tạp hơn, giả sử bạn có một bảng người dùng với dữ liệu hồ sơ JSON. Ví dụ
idnameprofile1Craig{ “email”. [“craig@email1. com”, “craig@email2. com”], “twitter”. “@craigbuckler” }2SitePoint{ “email”. [], “twitter”. “@sitepointdotcom” }Bạn có thể trích xuất tên Twitter bằng đường dẫn JSON. Ví dụ
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
2Bạn có thể sử dụng đường dẫn JSON trong mệnh đề WHERE để chỉ trả về người dùng có tài khoản Twitter
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
3Sửa đổi một phần của tài liệu JSON
Có một số hàm MySQL để sửa đổi các phần của tài liệu JSON bằng cách sử dụng ký hiệu đường dẫn. Bao gồm các
7. chèn hoặc cập nhật dữ liệu trong tài liệu-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
8. chèn dữ liệu vào tài liệu-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
9. thay thế dữ liệu trong tài liệu-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT['a', 1, 'b', 2];
0. hợp nhất hai hoặc nhiều tài liệu-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE['[1, 2, "abc"]'];
1. nối các giá trị vào cuối một mảng-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE['[1, 2, "abc"]'];
2. chèn một mảng trong tài liệu-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE['[1, 2, "abc"]'];
3. xóa dữ liệu khỏi tài liệu-- returns "[1, 2, \"abc\"]": SELECT JSON_QUOTE['[1, 2, "abc"]'];
Do đó, bạn có thể thêm thẻ “kỹ thuật” vào bất kỳ cuốn sách nào đã có thẻ “JavaScript”
INSERT INTO `book` [`title`, `tags`]
VALUES [
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
];
4Thêm thông tin
Hướng dẫn sử dụng MySQL cung cấp thêm thông tin về kiểu dữ liệu JSON và các hàm JSON được liên kết
Một lần nữa, tôi khuyên bạn không nên sử dụng JSON trừ khi thực sự cần thiết. Bạn có thể mô phỏng toàn bộ cơ sở dữ liệu NoSQL hướng tài liệu trong MySQL, nhưng nó sẽ phủ nhận nhiều lợi ích của SQL và bạn cũng có thể chuyển sang một hệ thống NoSQL thực sự. Điều đó nói rằng, các kiểu dữ liệu JSON có thể tiết kiệm công sức cho các yêu cầu dữ liệu khó hiểu hơn trong ứng dụng SQL
Chia sẻ bài viết này
Craig Buckler
Craig là một nhà tư vấn web tự do ở Vương quốc Anh, người đã xây dựng trang đầu tiên của mình cho IE2. 0 vào năm 1995. Kể từ thời điểm đó, anh ấy đã ủng hộ các tiêu chuẩn, khả năng truy cập và các kỹ thuật HTML5 thực hành tốt nhất. Anh ấy đã tạo các thông số kỹ thuật doanh nghiệp, trang web và ứng dụng trực tuyến cho các công ty và tổ chức bao gồm Nghị viện Vương quốc Anh, Nghị viện Châu Âu, Bộ Năng lượng & Biến đổi Khí hậu, Microsoft, v.v. Anh ấy đã viết hơn 1.000 bài báo cho SitePoint và bạn có thể tìm thấy anh ấy @craigbuckler