Làm cách nào để lưu dữ liệu JSON trong cơ sở dữ liệu 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 Show Đầ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 {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 Giải pháp
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'Chèn dữ liệuSau đó, 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 = '{
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) 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) Có vẻ như hầu hết mọi cơ sở dữ liệu trong vài năm qua đã giới thiệu nhiều mức độ hỗ trợ khác nhau để lưu trữ và tương tác trực tiếp với các đối tượng JSON. Mặc dù các tính năng này được thiết kế để giúp các nhà phát triển ứng dụng viết mã nhanh hơn dễ dàng hơn, nhưng việc triển khai của mỗi triển khai có xu hướng khác nhau rất nhiều và có thể gây ra một số điều kỳ lạ. Trong vài tuần/tháng tới, tôi sẽ chỉ cho bạn một số phương pháp, lỗi và cách phổ biến mà các nhà phát triển lưu trữ JSON. Chỉ vì bạn có thể sử dụng hỗ trợ JSON gốc của cơ sở dữ liệu không phải lúc nào cũng có nghĩa là bạn nên. Tôi hy vọng sẽ cho bạn thấy cái nào hoạt động tốt nhất cho trường hợp sử dụng nào Đối với phần một của loạt bài này, tôi sẽ tập trung vào MySQL. Việc triển khai kiểu dữ liệu JSON của MySQL đã được giới thiệu trở lại vào năm 5. 7 (Khung thời gian cuối năm 2015/đầu năm 2016). Kể từ đó, một vài cải tiến nhỏ đã giúp việc triển khai trở nên dễ sử dụng hơn một chút. Phiên bản MySQL 8 hiện tại cung cấp triển khai đầy đủ chức năng của các chức năng và tính năng JSON. Để tôi chỉ cho bạn một số ví dụ về cách lưu trữ và tương tác với các tài liệu JSON của bạn trong MySQL Cài đặtĐối với tất cả các thử nghiệm của mình, tôi muốn có một lượng dữ liệu hợp lý để kiểm tra ý nghĩa hiệu suất của một số chức năng nhất định. Tôi đã chọn sử dụng siêu dữ liệu JSON từ http. //phimet. trang web/, khoảng 2. 3GB tệp JSON riêng lẻ (một tệp cho mỗi phim) Tôi đã viết một tập lệnh python nhỏ để tải và lặp qua các tệp JSON và tải chúng vào MySQL  Tôi sẽ xem qua các ví dụ và cho bạn thấy tôi đã thấy nhiều nhà phát triển sử dụng MySQL để tương tác với JSON như thế nào và chỉ ra lý do tại sao một số ví dụ đó có thể không chính xác hoặc gây ra sự cố mà bạn có thể không biết. Tôi cũng sẽ chỉ cho bạn một số tính năng khác mà bạn có thể muốn xem xét và khám phá cũng như đưa ra một số lời khuyên thiết kế. Hãy để chúng tôi bắt đầu với định nghĩa bảng đơn giản sau đây Vỏ bọc1 2 3 4 5 6 7 tạo bảng movies_json ( ai_myid int AUTO_INCREMENT chính key, imdb_id varchar(255), json_column json ) công cụ = innodb;
tạo chỉ mục duy nhất imdb_idx trên movies_json(imdb_id); Vỏ bọc 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 { "imdb_id". "tt8408760", "tmdb_id". null, "douban_id". null, "tiêu đề". "Rubes (2019)", "thể loại". [ "Ngắn", "Phim hài", "Kinh dị" ], "quốc gia". "Hoa Kỳ", "phiên bản". [ { "thời gian chạy". "7 phút", "mô tả". "" } ], "imdb_rating". null, "giám đốc". [ { "id". "nm3216042", "tên". "Nathan Alan Bunker" } ], "nhà văn". null, "truyền". [ { "id". "nm1899908", "tên". "Brendan Jennings", "ký tự". "Milton" }, { "id". "nm2384265", "tên". "Ben Begley", "ký tự". "Phao-lô" }, { "id". "nm2287013", "tên". "Jerry Marr", "ký tự". "Giáo sư Henson" }, { "id". "nm7529700", "tên". "Allene Prince", "ký tự". "Margaret" } ], "tổng quan". null, "cốt truyện". "Hai giáo viên bất mãn sử dụng máy Rube Goldberg để trả thù chính xác những người đã đối xử tệ với họ. ", "âm mưu". null, "tóm tắt". null } Bạn có thể xem một ví dụ về định dạng JSON 101. Tương tác JSON đơn giản trong MySQLCó, một cột trong bảng có một hoặc hai khóa. Mỗi hàng sẽ lưu trữ một trong các phim trong tệp JSON đã tải xuống. Có khóa auto_increment và ID IMDB mà tôi đã trích xuất từ JSON trong khi tải. Cấu trúc và thiết lập này là một thiết kế đơn giản với nỗ lực tối thiểu. Tuy nhiên, thiết kế này cũng có nghĩa là bạn thường dựa vào MySQL chỉ đơn thuần là nơi lưu trữ dữ liệu của bạn. Miễn là bạn đang truy cập mọi thứ bằng khóa imdb_id, bạn có thể nhận và cập nhật JSON cho ứng dụng của mình một cách dễ dàng bằng một Vỏ bọc1 2 3 chọn json_column từ movies_json ở đâu imdb_id = ‘tt4154796’;
update movies_json set json_column = ‘<new JSON>’ where imdb_id = ‘tt4154796’; Tuy nhiên, cuối cùng, bạn sẽ muốn tìm kiếm trong JSON của mình hoặc chỉ trả về một phần của Tài liệu JSON. Ví dụ: giả sử bạn chỉ muốn tìm tiêu đề và xếp hạng IMDB cho một bộ phim cụ thể. Bạn có thể làm điều này với chức năng được tích hợp sẵn Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select json_column - >>'$.title', json_column - >>'$.imdb_rating' từ movies_json ở đâu json_column->>'$.imdb_id' = 'tt2395427'; +---------------------------------+---------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . +---------------------------------+---------------- | Avengers. Tuổi của Ultron (2015 . ) | 7. 5 . +---------------------------------+---------------- 1 hàng trong bộ (0.77 giây)
mysql> select json_column - >>'$.title', json_column - >>'$.imdb_rating' từ movies_json ở đâu json_column->>'$.imdb_id' = 'tt4154796'; +-----------------------+---------------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . +-----------------------+---------------------- | Avengers. Kết thúc trò chơi (2019) . null . +-----------------------+---------------------- 1 hàng trong bộ (0.75 giây) Ở đây bạn có thể thấy chúng ta có thể tương tác bên trong cột JSON giống như chúng ta làm với dữ liệu tiêu chuẩn thông qua SQL bằng cách sử dụng cú pháp đặc biệt “'”. Bạn có thể xem Biệt đội siêu anh hùng. Trò chơi kết thúc có xếp hạng không. Điều đó không hay, và đó là một bộ phim hay hơn thế nhiều. Thay vì cập nhật và lưu trữ lại toàn bộ tài liệu JSON, MySQL cung cấp một để thiết lập một phần tử trong tài liệu Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> cập nhật movies_json đặt json_column = JSON_SET(json_column, "$.imdb_rating", 9) ở đâu json_column - >>'$.imdb_id' = 'tt4154796'; Truy vấn OK, 1 hàng affected (0.93 giây) Hàng khớp. 1 Đã thay đổi. 1 Cảnh báo. 0
mysql> select json_column - >>'$.title', json_column - >>'$.imdb_rating' từ movies_json ở đâu json_column->>'$.imdb_id' = 'tt4154796'; +-----------------------+---------------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . +-----------------------+---------------------- | Avengers. Kết thúc trò chơi (2019) . 9 . +-----------------------+---------------------- 1 hàng trong bộ (0.80 giây) Chúng tôi hiện đã sửa lỗi đánh giá còn thiếu cho Endgame. Nhưng chúng tôi có thể không biết ID IMDB khi chúng tôi đang tìm kiếm. Giống như làm việc với các kiểu dữ liệu tiêu chuẩn, bạn có thể sử dụng dữ liệu từ bên trong tài liệu của mình theo mệnh đề where. Trong trường hợp này, chúng tôi sẽ tìm kiếm tất cả các phim bắt đầu bằng “Avengers” Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select json_column - >>'$.title', json_column - >>'$.imdb_rating', json_column - >>'$.imdb_id' từ movies_json ở đâu json_column - >>'$.title' like 'Avengers%';
+------------------------------------------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . json_column - >>' . imdb_id' . +------------------------------------------------- | Avengers. Kết thúc trò chơi (2019) < . | 9. 0 . tt4154796 . | Avengers Bí mật. Đen Góa phụ & Kẻ trừng phạt ( . Video 2014) | 5. 8 . tt3482378 . | Avengers của Công lý. Trò hề Chiến tranh (2018) . | null . tt6172666 . | Avengers. Tuổi của Ultron (2015 . ) | 7. 5 . tt2395427 . | Avengers. Vô cực Chiến tranh (2018) . | null . tt4154756 . | Avengers Grimm. Thời gian Chiến tranh (Video 2018 . ) | null . tt8159584 . | Avengers Tập hợp. (TV Sê-ri 2010 – . ) | null . tt1779952 . | Avengers Grimm (Video 2015 . ) | 2. 8 . tt4296026 . +------------------------------------------------- 8 hàng trong bộ (0.74 giây) Sử dụng “json_column->'$. title' trong mệnh đề where cho chúng ta một danh sách hay về các bộ phim và chương trình truyền hình có tựa đề Avengers. Tuy nhiên, bạn có thể thấy từ truy vấn này, chúng tôi không chỉ có những bộ phim bom tấn Avengers. Giả sử bạn muốn tinh chỉnh điều này thêm một chút và chỉ tìm các phim Avengers có Robert Downey Jr. trong dàn diễn viên. Thành thật mà nói, điều này khó hơn một chút vì định dạng của các tài liệu JSON của chúng tôi sử dụng một mảng cho các thành viên diễn viên Đây là giao diện của JSON 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 { "imdb_id". "tt2395427", "tmdb_id". "99861", "douban_id". "10741834", "tiêu đề". "Avengers. Thời Đại Ultron (2015)", "thể loại". [ "Hành động", "Phiêu lưu", "Khoa học viễn tưởng" ], "quốc gia". "Hoa Kỳ", "phiên bản". [ { "thời gian chạy". "141 phút", "mô tả". "" } ], "imdb_rating". 7. 5, "giám đốc". [ { "id". "nm0923736", "tên". "Joss Whedon" } ], "nhà văn". [ { "id". "nm0923736", "tên". "Joss Whedon", "mô tả". "được viết bởi" }, { "id". "nm0498278", "tên". "Stan Lee", "mô tả". "dựa trên truyện tranh Marvel của và" }, { "id". "nm0456158", "tên". "Jack Kirby", "mô tả". "dựa trên truyện tranh Marvel của" } ], "truyền". [ { "id". "nm0000375", "tên". "Robert Downey Jr. ", "ký tự". "Tony Stark" }, { "id". "nm1165110", "tên". "Chris Hemsworth", "ký tự". "Thor" }, { "id". "nm0749263", "tên". "Mark Ruffalo", "ký tự". "Biểu ngữ Lý Tiểu Long" }, { "id". "nm0262635", "tên". "Chris Evans", "ký tự". "Steve Rogers" }, { "id". "nm0424060", "tên". "Scarlett Johansson", "ký tự". "Natasha Romanoff" }, { "id". "nm0719637", "tên". "Jeremy Renner", "ký tự". "Clint Barton"
Bạn có thể truy cập các mảng trong tài liệu JSON bằng cách tham chiếu chỉ mục cụ thể cho phần tử bạn muốn ( i. e. [0]. name ), tuy nhiên, nếu bạn không biết cái nào chứa dữ liệu mà bạn đang tìm kiếm thì bạn cần phải tìm kiếm nó. MySQL có chức năng trợ giúp việc này (cũng có các chức năng khác như json_contains). json_search tìm kiếm một giá trị được cung cấp và sẽ trả về vị trí nếu tìm thấy và null nếu không tìm thấy 1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select json_column - >>'$.title', json_column - >>'$. imdb_rating', json_column - >>'$. imdb_id' từ movies_json ở đâu json_column - >>'$.title' like 'Avengers%' and json_search(json_column->>'$.cast', 'one','Robert Downey Jr. ', NULL,'$[*]. name' ) là không phải null;
+---------------------------------+---------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . json_column - >>' . imdb_id' . +---------------------------------+---------------- | Avengers. Kết thúc trò chơi (2019) < . | 9 . tt4154796 . | Avengers. Tuổi của Ultron (2015 . ) | 7. 5 . tt2395427 . | Avengers. Vô cực Chiến tranh (2018) . | null . tt4154756 . +---------------------------------+---------------- 3 hàng trong bộ (0.79 giây) Bạn sẽ nhận thấy rằng tôi đã sử dụng tham số 'một', tham số này sẽ tìm giá trị đầu tiên. Bạn cũng có thể sử dụng 'tất cả' để trả về mọi giá trị khớp. Trong trường hợp bạn tò mò về kết quả mà json_search thực sự trả về thì đây là gì Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> chọn json_column->>'$.title' như tiêu đề, json_search(json_column - >>'$.cast', 'one','Robert Downey Jr. ') như search_output từ movies_json ở đâu json_column->>'$.title' like 'Avengers%' và json_search(json_column - >>'$.cast', 'one','Robert Downey Jr. ', NULL,'$[*]. name' ) là không phải null;
+---------------------------------+--------------------+ | tiêu đề . search_output . +---------------------------------+--------------------+ | Avengers. Kết thúc trò chơi (2019) < . | "$[0]. tên" . | Avengers. Tuổi của Ultron (2015 . ) | "$[0]. tên" . | Avengers. Vô cực Chiến tranh (2018) . | "$[0]. tên" . +---------------------------------+--------------------+ 3 hàng trong bộ (0.72 giây) Bạn có thể thấy nó trả về vị trí và thuộc tính chứa giá trị. Đầu ra này hữu ích vì nhiều lý do. Một là nếu bạn cần tìm giá trị chỉ mục nào chứa văn bản cụ thể đó. Trong ví dụ tìm kiếm phim của Robert Downey JR, chúng ta có thể sử dụng thông tin chỉ mục này để trả về nhân vật anh ấy đóng trong mỗi phim. Cách đầu tiên tôi đã thấy điều này được thực hiện đòi hỏi một chút tranh cãi không lành mạnh nhưng 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select json_column - >>'$.title' như tiêu đề, json_column - >>'$.imdb_rating' như Xếp hạng, json_column - >>'$.imdb_id' như IMDB_ID, json_extract(json_column - >>'$.cast',concat(substr(json_unquote(json_search(json_column->>'$.cast', 'one','Robert Downey Jr. ')),1, - > xác định vị trí('.',json_unquote(json_search . diễn viên'json_column->>'$.cast', 'one','Robert Downey Jr. ')))),'character')) as Char_played từ movies_json ở đâu json_column->>'$.title' like 'Avengers%' and json_search(json_column->>'$.cast', 'one','Robert Downey Jr. ') là không phải null;
+---------------------------------+--------+------- | tiêu đề . Xếp hạng . IMDB_ID . Char_played . +---------------------------------+--------+------- | Avengers. Kết thúc trò chơi (2019) < . | 9 . tt4154796 . "Tony Stark / Người sắt" . | Avengers. Tuổi của Ultron (2015 . ) | 7. 5 . tt2395427 . "Tony Stark" . | Avengers. Vô cực Chiến tranh (2018) . | null . tt4154756 . "Tony Stark / Người sắt" . +---------------------------------+--------+------- 3 hàng trong bộ (0.68 giây) Ở đây tôi đang tìm vị trí trong tài liệu mà Robert Downey Jr được liệt kê, sau đó trích xuất chỉ mục và sử dụng chỉ mục đó với hàm Trích xuất JSON để lấy ra giá trị của “$[0]. ký tự” thay vì “$[0]. Tên". Trong khi điều này hoạt động, nó là xấu. MySQL cung cấp một giải pháp thay thế để làm điều này bằng cách sử dụng json_table Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select json_column - >>'$.title', json_column - . imdb_rating'>'$.imdb_rating', t. * từ movies_json, json_table . cast[*]'(json_column, '$.cast[*]' cột( V_name varchar(200) path '$.name', V_character varchar(200) path '$.character') ) t ở đâu t.V_name like 'Robert Downey Jr. %' and json_column - >>'$.title' like 'Avengers%';
+---------------------------------+---------------- | json_column - >>' . tiêu đề' . json_column - >>' . imdb_rating' . V_name . V_character . +---------------------------------+---------------- | Avengers. Kết thúc trò chơi (2019) < . | 9 . Robert Downey Jr. . Tony Stark / Sắt < . Man | | Avengers. Tuổi của Ultron (2015 . ) | 7. 5 . Robert Downey Jr. . Tony Stark . | Avengers. Vô cực Chiến tranh (2018) . | null . Robert Downey Jr. . Tony Stark / Sắt < . Man | +---------------------------------+---------------- 3 hàng trong bộ (0.74 giây) Về cơ bản, json_table lấy một mảng và biến nó thành một đối tượng bảng, cho phép bạn tham gia và truy vấn nó. Bạn cũng có thể sử dụng điều này để liệt kê tất cả các nhân vật mà một diễn viên đã đóng trong bất kỳ bộ phim nào trong sự nghiệp của anh ấy. Đây là phần hai của loạt bài này, khi chúng tôi sẽ chỉ cho bạn một số cách dễ dàng hơn và nhanh hơn để sử dụng JSON từ bên trong MySQL Làm cách nào để lưu trữ dữ liệu JSON trong cơ sở dữ liệu MySQL?Lưu ý rằng bất kỳ cơ sở dữ liệu nào cũng sẽ chấp nhận 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. . Hàm JSON_ARRAY(), tạo mảng. . Hàm JSON_OBJECT(), tạo đối tượng Chúng tôi có thể lưu dữ liệu JSON trong MySQL không?MySQL hỗ trợ loại dữ liệu JSON gốc được xác định bởi RFC 7159, cho phép truy cập hiệu quả vào dữ liệu trong tài liệu JSON (Ký hiệu đối tượng JavaScript) . Kiểu dữ liệu JSON cung cấp những lợi thế này so với việc lưu trữ các chuỗi định dạng JSON trong một cột chuỗi. Tự động xác thực các tài liệu JSON được lưu trữ trong các cột JSON.
Làm cách nào để chèn dữ liệu JSON vào cơ sở dữ liệu MySQL?Bước 1. Kết nối PHP với cơ sở dữ liệu MySQL. Là bước đầu tiên và quan trọng nhất, chúng ta phải kết nối PHP với cơ sở dữ liệu MySQL để chèn dữ liệu JSON vào MySQL DB. . Bước 2. Đọc tệp JSON trong PHP. . Bước 3. Chuyển đổi chuỗi JSON thành mảng PHP. . Bước 4. Trích xuất các giá trị mảng. . Bước 5. Chèn JSON vào cơ sở dữ liệu MySQL bằng mã PHP Làm cách nào để lưu dữ liệu JSON trong DB?Lưu trữ JSON trong bảng cơ sở dữ liệu. Truy vấn bảng cho các hàng lưu trữ các giá trị cụ thể trong tài liệu JSON. Tạo chỉ mục để tìm các hàng này một cách hiệu quả. . Có mệnh đề cột ban đầu. . Trong phần này xác định một đường dẫn lồng nhau, trả về mảng nhân viên (employees[*]) |
