Làm cách nào để trích xuất giá trị từ đối tượng JSON trong MySQL?

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

1. một bảng thẻ lưu trữ từng tên thẻ với một ID duy nhất và
2. 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ể

  INSERT INTO `book` (`title`, `tags`)
  VALUES (
    'ECMAScript 2015: A SitePoint Anthology',
    '["JavaScript", "ES2015", "JSON"]'
  );
  

  5

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ư

  -- returns [1, 2, "abc"]:
  SELECT JSON_ARRAY(1, 2, 'abc');
  

  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
• ký tự đại diện

  -- returns [1, 2, "abc"]:
  SELECT JSON_ARRAY(1, 2, 'abc');
  

  4 đánh giá tất cả các thành viên của một đối tượng
• ký tự đại diện

  -- returns [1, 2, "abc"]:
  SELECT JSON_ARRAY(1, 2, 'abc');
  

  5 đánh giá tất cả các phần tử của một mảng
• ký tự đại diện

  -- returns [1, 2, "abc"]:
  SELECT JSON_ARRAY(1, 2, 'abc');
  

  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

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ụ

• -- returns [1, 2, "abc"]:
  SELECT JSON_ARRAY(1, 2, 'abc');
  

  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 {"a": 1, "b": 2}:
  SELECT JSON_OBJECT('a', 1, 'b', 2);
  

  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

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"]'
);

2

Bạ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"]'
);

3

Sử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

• -- returns {"a": 1, "b": 2}:
  SELECT JSON_OBJECT('a', 1, 'b', 2);
  

  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 "[1, 2, \"abc\"]":
  SELECT JSON_QUOTE('[1, 2, "abc"]');
  

  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

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"]'
);

4

Thê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

Làm cách nào tôi có thể nhận được giá trị trong một đối tượng JSON?

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

Làm cách nào để trích xuất các giá trị từ trường JSON lồng nhau trong MySQL?