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 Dung dịch
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) Bạn có thể làm hầu hết mọi thứ bạn có thể làm trong SQL với các đối tượng và kiểu dữ liệu tiêu chuẩn, nhưng bạn có thể gặp phải các vấn đề và giới hạn cụ thể. Đây là một ví dụ về điều đó. Giả sử tôi muốn có được 10 bộ phim được đánh giá cao nhất mọi thời đại Vỏ bọc1 2 3 mysql> select json_column - >>'$.title' as title, . imdb_rating'->>'$.imdb_rating' như xếp hạng, . imdb_id'->>'$.imdb_id' như imdb_id từ movies_json where json_column->>'$.imdb_rating' > 8 đặt hàng bởi json_column->>'$.imdb_rating' desc giới hạn 10;
LỖI 1038 (HY001): Hết của sắp xếp bộ nhớ, consider increasing server sort buffer size Trong trường hợp này, kích thước cần thiết để sắp xếp tập dữ liệu lớn hơn nhiều so với kích thước sắp xếp có sẵn. Việc chọn dữ liệu này từ cấu trúc chuẩn hóa sẽ không gây ra điều này (cột cũng không được tạo) Vỏ bọc1 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 mysql> select @@sort_buffer_size; +---------------------+ | @@sort_buffer_size . +---------------------+ | 262144 . +---------------------+ 1 hàng trong bộ (0.00 giây)
mysql> đặt @@sort_buffer_size=26214400; Truy vấn OK, 0 hàng affected (0.00 giây)
mysql> select json_column - >>'$.title' as title, . imdb_rating'->>'$.imdb_rating' như xếp hạng, . imdb_id'->>'$.imdb_id' như imdb_id từ movies_json where json_column->>'$.imdb_rating' > 8 đặt hàng bởi json_column->>'$.imdb_rating' desc giới hạn 10; +------------------------------------------------- | tiêu đề . xếp hạng . imdb_id . +------------------------------------------------- | The Núi II (2016 . ) | 9. 9 . tt5813916 . | Độc hại Cám dỗ (2016) . | 9. 9 . tt4621978 . | 1985 (2016) < . | 9. 9 . tt5932546 . | Jag har din rygg ( . 2015) | 9. 8 . tt3689312 . | Đầu của tôi Đau (2000 . ) | 9. 8 . tt1346290 . | Boz. Màu sắc và Hình dạng (Video . 2006) | 9. 8 . tt0876256 . | Nhà bị tịch thu (Video 2010) . | 9. 8 . tt1648984 . | Nocturne trong Đen (2016 . ) | 9. 8 . tt4536608 . | Monrad & Rislund. Phát hiện cửa hàng triumftog (Video . 2004) | 9. 8 . tt0425266 . | Câu hỏi di Người bảo vệ (2014 . ) | 9. 8 . tt4423586 . +------------------------------------------------- 10 hàng trong bộ, 65535 warnings (0.78 giây) Vì vậy, chúng tôi đã giải quyết vấn đề không có đủ dung lượng để sắp xếp tập dữ liệu, nhưng bạn có để ý đến thời gian không? . Bất kỳ trang web nào bạn đang làm hiện nay đều không thể thành công nếu tất cả các truy vấn cơ sở dữ liệu của bạn bằng 0. 5-1 giây để quay lại. Có, bạn có thể đặt một bộ đệm trước những thứ này và có thể sẽ. Bộ đệm tốt hơn sẽ giải quyết tất cả, phải không? . Trong trường hợp này, bạn có thể tăng tốc đáng kể mọi thứ bằng một vài cải tiến nhỏ và quan trọng. Trước khi tiếp tục, hãy chú ý đến các cảnh báo? . (Đây là do các giá trị null trong cột xếp hạng (nhưng những giá trị này không ảnh hưởng đến hiệu suất) Hãy xem xét các cột được tạo. Các cột đã tạo cho phép bạn tạo các cột dựa trên các cột hoặc hoàn cảnh khác một cách nhanh chóng. Lưu ý, bạn cũng có thể sử dụng các chỉ mục chức năng mà tôi sẽ đề cập sau. Trong trường hợp của JSON, chúng ta có thể lấy các giá trị ra khỏi tài liệu của mình và lưu trữ chúng ở chế độ chỉ đọc trong một cột có thể được lập chỉ mục (tất cả chúng ta đều biết việc lập chỉ mục là tốt, đúng không?. ). Làm thôi nào. Tôi sẽ tạo một bảng có các cột được tạo cho các cột khác nhau. Sau đó, tôi sẽ tải dữ liệu thông qua chèn từ bảng phim đã được tải của chúng tôi Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> tạo bảng movies_json_generated ( - > ai_myid int AUTO_INCREMENT primary key, - > imdb_id varchar(255) generated always as (`json_column` ->> '$.imdb_id'), - > tiêu đề varchar(255) generated always as (`json_column` ->> '$.title'), - > imdb_rating decimal(5,2) generated always as (`json_column` ->> '$.imdb_rating'), - > tổng quan text generated always as (`json_column` ->> '$.overview'), - > giám đốc json generated always as (`json_column` ->> '$.director'), - > truyền json generated always as (`json_column` ->> '$.cast'), - > json_column json - > ) công cụ = innodb; Truy vấn OK, 0 hàng affected (0.04 giây)
mysql> tạo chỉ mục độc đáo imdb_idx on movies_json_generated(imdb_id); Truy vấn OK, 0 hàng affected (0.04 giây) Bản ghi. 0 Bản sao. 0 Cảnh báo. 0
mysql> chèn vào movies_json_generated (json_column ) select json_column from movies_json; ERROR 1366 (HY000): Không đúng giá trị thập phân. 'null' cho cột 'imdb_rating' at row 1 mysql> Thả Bảng movies_json_generated; Truy vấn OK, 0 hàng affected (0.04 giây) Tôi muốn dành một chút thời gian và đi sâu vào những gì tôi coi là một vấn đề phổ biến. Như bạn có thể thấy, bảng đã được tạo mà không gặp vấn đề gì, nhưng không thành công khi chèn dữ liệu từ các đối tượng JSON của chúng tôi. Nguyên nhân? . Loại chuyển đổi khi làm việc với JSON và MySQL có thể hơi phức tạp đối với một số người. Hãy nhớ những cảnh báo trên? . Dễ bị bỏ qua vì chúng dường như không gây ra sự cố ngay lập tức (nhưng chúng đã gây ra). Chuyển đổi loại và bộ ký tự có thể là một vấn đề khi truy cập dữ liệu JSON.
Trong trường hợp này, việc chuyển đổi loại khá đơn giản và bạn có thể giải quyết vấn đề này theo một số cách khác nhau. Cuối cùng, đó là về việc đảm bảo giá trị JSON được chuyển đổi chính xác. Tôi sẽ sử dụng hàm json_value để bỏ trích dẫn null Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 mysql> tạo bảng movies_json_generated_stored ( - > ai_myid int AUTO_INCREMENT primary key, - > imdb_id varchar(255) generated always as (`json_column` ->> '$.imdb_id') , - > tiêu đề varchar(255) generated always as (`json_column` ->> '$.title') , - > imdb_rating decimal(5,2) generated always as (json_value(json_column, '$.imdb_rating')) , - > tổng quan text generated always as (`json_column` ->> '$.overview') , - > giám đốc json generated always as (`json_column` ->> '$.director') , - > truyền json generated always as (`json_column` ->> '$.cast') , - > json_column json - > ) công cụ = innodb;
mysql> mysql> tạo chỉ mục độc đáo imdb_idx on movies_json_generated(imdb_id); Truy vấn OK, 0 hàng affected (0.01 giây) Bản ghi. 0 Bản sao. 0 Cảnh báo. 0
mysql> chèn vào movies_json_generated (json_column ) select json_column from movies_json; Truy vấn OK, 375359 hàng affected (40.26 giây) Bản ghi. 375359 Bản sao. 0 Cảnh báo. 0 Bây giờ, hãy so sánh việc tìm kiếm một bộ phim bằng cách sử dụng imdb_id đầu tiên từ tài liệu JSON và sau đó từ bảng được tạo bằng cột chúng tôi đã lập chỉ mục Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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.86 giây)
mysql> chọn tiêu đề, imdb_rating from movies_json_generated where imdb_id='tt2395427'; +---------------------------------+-------------------- + | tiêu đề . imdb_rating . +---------------------------------+-------------------- + | Avengers. Tuổi của Ultron (2015 . ) | 7. 50 . +---------------------------------+-------------------- + 1 hàng trong bộ (0.01 giây) Tuyệt quá. Hãy quay lại ví dụ của chúng tôi bằng cách sử dụng xếp hạng IMDB để có được 10 bộ phim hàng đầu. Để làm điều này nhanh hơn, trước tiên chúng ta cần tạo một chỉ mục trên cột được tạo Vỏ bọc1 2 3 mysql> tạo chỉ mục idx_rating on movies_json_generated ( imdb_rating ); Truy vấn OK, 0 hàng affected (1.45 giây) Bản ghi. 0 Bản sao. 0 Cảnh báo. 0 Ngoài ra, hãy lấy danh sách 10 người hàng đầu Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select json_column - >>'$.title' as title, . imdb_rating'->>'$.imdb_rating' như xếp hạng, . imdb_id'->>'$.imdb_id' như imdb_id từ movies_json_generated where imdb_rating > 8 order by imdb_rating desc limit 10; +------------------------------------------------- | tiêu đề . xếp hạng . imdb_id . +------------------------------------------------- | Mùa Vọng (IV) ( . 2016) | 10. 0 . tt6129028 . | 311 Trực tiếp. 3 / 11 Ngày 2006 . (2006) | 10. 0 . tt0872240 . | Ánh sáng Học tập (2013) . | 10. 0 . tt3130306 . | Tương lai Bạn trai (2016) . | 10. 0 . tt3955652 . | Cory trong the Nhà. Tất cả Sao Bản (Video . 2007) | 10. 0 . tt2402070 . | 1985 (2016) < . | 9. 9 . tt5932546 . | Độc hại Cám dỗ (2016) . | 9. 9 . tt4621978 . | The Núi II (2016 . ) | 9. 9 . tt5813916 . | Câu hỏi di Người bảo vệ (2014 . ) | 9. 8 . tt4423586 . | Nhà bị tịch thu (Video 2010) . | 9. 8 . tt1648984 . +------------------------------------------------- 10 hàng trong bộ (0.01 giây) Một sự sụt giảm rất đẹp từ 0. 78 giây đến 0. 01 giây. Nhưng chờ đã… tại sao dữ liệu lại khác? . Như đã thảo luận ở trên, việc lấy dữ liệu ra khỏi JSON thường yêu cầu một số loại chuyển đổi. Theo mặc định, các giá trị xuất phát từ JSON được coi là văn bản, không phải số, do đó, nó sắp xếp dựa trên Giá trị ASCII (rất tiếc). Vì vậy, bạn có thể nhận được kết quả tương tự bằng cách buộc chuyển đổi loại Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> select json_column - >>'$.title' as title, . imdb_rating'->>'$.imdb_rating' như xếp hạng, . imdb_id'->>'$.imdb_id' như imdb_id từ movies_json_generated where json_column->>'$.imdb_rating' > 8 đặt hàng bởi json_column->>'$.imdb_rating' * 1 desc giới hạn 10; +------------------------------------------------- | tiêu đề . xếp hạng . imdb_id . +------------------------------------------------- | 311 Trực tiếp. 3 / 11 Ngày 2006 . (2006) | 10. 0 . tt0872240 . | Mùa Vọng (IV) ( . 2016) | 10. 0 . tt6129028 . | Cory trong the Nhà. Tất cả Sao Bản (Video . 2007) | 10. 0 . tt2402070 . | Ánh sáng Học tập (2013) . | 10. 0 . tt3130306 . | Tương lai Bạn trai (2016) . | 10. 0 . tt3955652 . | Độc hại Cám dỗ (2016) . | 9. 9 . tt4621978 . | The Núi II (2016 . ) | 9. 9 . tt5813916 . | 1985 (2016) < . | 9. 9 . tt5932546 . | Nocturne trong Đen (2016 . ) | 9. 8 . tt4536608 . | Đầu của tôi Đau (2000 . ) | 9. 8 . tt1346290 . +------------------------------------------------- 10 hàng trong bộ, 65535 warnings (0.89 giây)
Giải pháp thay thế bạn có thể sử dụng. truyền(json_value(< . imdb_rating'json_column,'$.imdb_rating') as float) Vì vậy, bạn không chỉ có thể tăng tốc đáng kể hiệu suất của mình bằng cách lập chỉ mục với các cột được tạo, mà còn có thể đảm bảo rằng bạn đang nhận được các loại nhất quán và kết quả như mong đợi. Tài liệu này có một phần chi tiết về thứ tự và nhóm theo giá trị JSON. Các cột đã tạo cũng cải thiện hầu hết các truy vấn khác mà chúng tôi đã trình bày ở trên Chỉ mục chức năng không có cột được tạoCác cột được tạo hoạt động đủ tốt, nhưng đối với các truy vấn của chúng tôi (Trong MySQL 8. 0. 13 trở lên), chúng ta có thể tạo các chỉ mục trên các hàm JSON mà chúng ta gọi thường xuyên và bỏ qua các cột được tạo hoàn toàn. Vỏ bọc1 2 3 mysql> tạo chỉ mục title_idx on movies_json ((json_value(json_column,'$.title'))); Truy vấn OK, 0 hàng affected (2.90 giây) Bản ghi. 0 Bản sao. 0 Cảnh báo. 0 Điều này đủ hiệu quả nếu bạn đang cố so khớp một kết quả khớp chính xác; . Khi bạn khám phá bằng cách sử dụng các chỉ mục chức năng với JSON, hãy lưu ý rằng có một số hạn chế đối chiếu và bộ ký tự cũng như các hạn chế về chức năng có thể được sử dụng, do đó, số dặm của bạn sẽ thay đổi. Tôi sẽ tránh đào sâu ở đây, nhưng bạn có thể xem lại tài liệu ở đây & ở đây. . Trong thử nghiệm của tôi, JSON càng phức tạp hoặc lồng nhau thì tôi càng gặp nhiều vấn đề. Vì lý do này, tôi bị mắc kẹt với các cột được tạo cho các ví dụ của tôi ở trên. Chỉ mục đa giá trịTính đến ngày 8. 0. 17 MySQL cũng hỗ trợ , một chỉ mục phụ cho phép bạn lập chỉ mục cho một mảng. Điều này hữu ích nếu bạn có một mảng trong JSON của mình (điều này dường như không hỗ trợ các mảng ký tự tại thời điểm này, hữu ích “Phiên bản MySQL này chưa hỗ trợ 'CAST-ing . Điều này có thể trợ giúp với các thiết kế JSON đơn giản, nhưng khi JSON của bạn trở nên lồng ghép và phức tạp hơn, tôi đã gặp sự cố. . This can help with JSON designs that are straightforward, but as your JSON becomes more nested and complex I ran into problems. Thêm thông tin lịch sử về mặt hàng này Siêu phản khoa học, nhưng thời gian chạy truy vấn này kéo dài qua nhiều lần lặp lại, hãy xem xét sự khác biệt về tốc độ. Truy vấnTruy cập JSON trực tiếp (giây)Cột đã tạo(giây)Tìm kiếm đơn giản qua IMDB ID0. 75 0. 01Tìm kiếm phim có tựa đề Avengers0. 760. 01>Cập nhật một giá trị trong tìm kiếm JSON qua IMDB hoặc tiêu đề0. 800. 01>Tìm 10 bộ phim hay nhất mọi thời đại0. 890. 01Các nhân vật do Robert Downey JR thủ vai trong các bộ phim về những kẻ báo thù0. 740. 01> Hiệu suất cần thiết hơn?Cho đến nay chúng tôi đã thực hiện rất nhiều tương tác đơn giản và đã có thể tăng tốc độ truy cập lên mức chấp nhận được. Nhưng không phải mọi thứ đều phù hợp với hộp công cụ có sẵn. Khi tìm kiếm phim hoặc xếp hạng cho một diễn viên cụ thể ( cho tôi xem tất cả các phim Avengers Robert Downey JR. đã chơi và các ký tự ), chúng tôi đã sử dụng một chỉ mục trên cột được tạo tiêu đề để giảm Tài liệu JSON mà chúng tôi phải xử lý đầy đủ để có được nhân vật anh ấy đã chơi. Xem bên dưới Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> chọn tiêu đề, imdb_rating, t. * từ movies_json_generated, json_table . cast[*]'(json_column, '$.cast[*]' cột( - > V_name varchar(200) path '$.name', - > V_character varchar(200) path '$.character') - > ) t where t.V_name like 'Robert Downey Jr. %' và tiêu đề thích 'Avengers%'; +---------------------------------+---------+-- | tiêu đề . imdb_rating . V_name . V_character . +---------------------------------+---------+-- | Avengers. Tuổi của Ultron (2015 . ) | 7. 50 . Robert Downey Jr. . Tony Stark . | Avengers. Kết thúc trò chơi (2019) < . | 9. 00 . Robert Downey Jr. . Tony Stark / Sắt < . Man | | Avengers. Vô cực Chiến tranh (2018) . | NULL . Robert Downey Jr. . Tony Stark / Sắt < . Man | +---------------------------------+---------+-- 3 hàng trong bộ (0.00 giây)
mysql> giải thích chọn tiêu đề, imdb_rating, t. * từ movies_json_generated, json_table . cast[*]'(json_column, '$.cast[*]' columns( V_name varchar(200) path '$.name', V_character varchar . ký tự'200) path '$.character') ) t . V_name t.V_name like 'Robert Downey Jr. %' và tiêu đề thích 'Avengers%'; +----+-------------+-------------------+------ | id . select_type . bảng . phân vùng . loại . possible_keys . phím . key_len . ref . hàng . đã lọc . Bổ sung . +----+-------------+-------------------+------ | 1 . ĐƠN GIẢN . movies_json_generated . NULL . phạm vi . title_idx . title_idx . 1023 . NULL . 8 . 100. 00 . Sử dụng ở đâu . | 1 . ĐƠN GIẢN . t . NULL . TẤT CẢ . NULL . NULL . NULL . NULL . 2 . 50. 00 . Bảng hàm. json_table; Sử dụng tạm thời; . Using where | +----+-------------+-------------------+------ 2 hàng trong bộ, 1 warning (0.00 giây) Nhưng hãy giả sử rằng bạn cần lấy danh sách tất cả các nhân vật mà anh ấy đã đóng trong sự nghiệp của mình (Sẽ cắt bớt tập hợp kết quả đầy đủ). Vỏ bọc1 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 mysql> chọn tiêu đề, imdb_rating, t. * từ movies_json_generated, json_table . cast[*]'(json_column, '$.cast[*]' cột( - > V_name varchar(200) path '$.name', - > V_character varchar(200) path '$.character') - > ) t where t.V_name like 'Robert Downey Jr. %';
+------------------------------------------------- | tiêu đề . imdb_rating . V_name . V_character . +------------------------------------------------- | Giải thưởng lần thứ 65 Hàng năm Học viện . (1993) | NULL . Robert Downey Jr. . Bản thân anh ấy - Người trình bày . | Sherlock Holmes. A Trò chơi của Bóng tối ( . 2011) | 7. 50 . Robert Downey Jr. . Sherlock Holmes . | Hạn Ngày (2010) . | 6. 60 . Robert Downey Jr. . Peter Cao thủ . | Eros (2004) < . | 6. 00 . Robert Downey Jr. . Nick Penrose (phân đoạn " . ) | | The EE Viện hàn lâm Anh Phim . Awards (2015) | 7. 40 . Robert Downey Jr. . Bản thân - Cống nạp lên Chúa . Attenborough | | "Saturday Night Live" John Lithgow / Mr. Quý ông (TV Tập 1985 . ) | NULL . Robert Downey Jr. . Bruce Winston / Rudy < . Randolph III / Various (as Robert Downey) | +------------------------------------------------- 213 hàng trong bộ (7.14 giây)
mysql> giải thích chọn tiêu đề, imdb_rating, t. * từ movies_json_generated, json_table . cast[*]'(json_column, '$.cast[*]' columns( V_name varchar(200) path '$.name', V_character varchar . ký tự'200) path '$.character') ) t . V_name t.V_name like 'Robert Downey Jr. %'; +----+-------------+-------------------+------ | id . select_type . bảng . phân vùng . loại . possible_keys . phím . key_len . ref . hàng . đã lọc . Bổ sung . +----+-------------+-------------------+------ | 1 . ĐƠN GIẢN . movies_json_generated . NULL . TẤT CẢ . NULL . NULL . NULL . NULL . 358174 . 100. 00 . NULL . | 1 . ĐƠN GIẢN . t . NULL . TẤT CẢ . NULL . NULL . NULL . NULL . 2 . 50. 00 . Bảng hàm. json_table; Sử dụng tạm thời; . Using where | +----+-------------+-------------------+------ 2 hàng trong bộ, 1 warning (0.00 giây) Bây giờ, mẫu truy cập của chúng ta cần bắt đầu từ phần tử được lồng sâu hơn trong tài liệu JSON. Ở đây, bạn phải đối mặt với một số tùy chọn, nhưng hầu hết tất cả chúng đều dẫn đến việc tạo và duy trì một bảng mới để có được mức dữ liệu đó thông qua trình kích hoạt, mã để tách dữ liệu khi được tải hoặc thông qua công việc hàng loạt (hoặc lưu vào bộ nhớ cache điều ác này . Cuối cùng tôi đã tạo “Lược đồ cơ sở dữ liệu” sau cho nó Vỏ bọc1 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 tạo bảng movies_normalized_meta ( ai_myid int AUTO_INCREMENT chính key, imdb_id varchar(32), tiêu đề varchar(255), imdb_rating thập phân(5,2), json_column json ) công cụ = innodb;
tạo chỉ mục duy nhất imdb_id_idx trên movies_normalized_meta (imdb_id); tạo chỉ mục rating_idx trên movies_normalized_meta (imdb_rating);
tạo bảng movies_normalized_actors ( ai_actor_id int auto_increment chính key, actor_id varchar(50), actor_name varchar(500) ) công cụ = innodb; tạo chỉ mục actor_id_idx trên movies_normalized_actors (actor_id); tạo chỉ mục actor_name_idx trên movies_normalized_actors (actor_name);
tạo bảng movies_normalized_cast ( ai_actor_id int, ai_myid int, actor_character varchar(500) ) công cụ = innodb;
tạo chỉ mục cast_id_idx trên movies_normalized_cast (ai_actor_id,ai_myid); tạo chỉ mục cast_id2_idx trên movies_normalized_cast (ai_myid); tạo chỉ mục cast_character_idx trên movies_normalized_cast (actor_character); tạo chỉ mục duy nhất u_cast_idx trên movies_normalized_cast (ai_myid,ai_actor_id,actor_character); Khi tải JSON vào MySQL, tôi đã thêm một bảng diễn viên sẽ có một hàng cho mỗi diễn viên duy nhất mới cũng như một bảng diễn viên có phim, diễn viên và tên của nhân vật họ đóng trong phim (Lưu ý rằng tôi có thể tối ưu hóa . Điều này mang lại cho tôi rất nhiều sự linh hoạt trong báo cáo và tăng hiệu suất lớn. Bây giờ để có được tất cả các bộ phim Robert Downey JR đã tham gia và các nhân vật anh ấy đóng, tôi có thể xem qua Vỏ bọc1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysql> chọn tiêu đề, imdb_rating, actor_character from movies_normalized_meta a, movies_normalized_cast b, movies_normalized_actors c where a.ai_myid = b. ai_myid và b. ai_actor_id = c. ai_actor_id and actor_name = 'Robert Downey Jr. ';
+------------------------------------------------- | tiêu đề . imdb_rating . diễn viên_nhân vật . +------------------------------------------------- | Giải thưởng lần thứ 65 Hàng năm Học viện . (1993) | NULL . Bản thân anh ấy - Người trình bày . | Sherlock Holmes. A Trò chơi của Bóng tối ( . 2011) | 7. 50 . Sherlock Holmes . | Hạn Ngày (2010) . | 6. 60 . Peter Quý ông . | Eros (2004) < . | 6. 00 . Nick Penrose (phân đoạn ") | Thứ bảy Đêm Trực tiếp trong . '80s: Lạc mất & Tìm thấy (2005). 7. 20. Khác nhau (đoạn phim lưu trữ). "Trực tiếp đêm thứ bảy" John Lithgow/Mr. Quý Ông (TV Tập 1985). VÔ GIÁ TRỊ. Bruce Winston / Rudy Randolph III / Khác nhau (vai Robert Downey) +------------------------------------------------- 213 hàng trong bộ (0. 01 giây)
mysql> giải thích chọn tiêu đề, imdb_rating, actor_character từ movies_normalized_meta a, movies_normalized_cast b, movies_normalized_actors c trong đó a. ai_myid=b. ai_myid và b. ai_actor_id = c. ai_actor_id và actor_name='Robert Downey Jr. '; +----+-------------+-------+----------+--------+ | id . select_type . bảng . phân vùng . loại . possible_keys . phím . key_len . ref . hàng . đã lọc . Bổ sung . +----+-------------+-------+----------+--------+ | 1 . ĐƠN GIẢN . c . NULL . ref . CHÍNH CHÍNH,actor_name_idx . actor_name_idx . 2003 . const . 213 . 100. 00 . Sử dụng chỉ mục . | 1 . ĐƠN GIẢN . b . NULL . ref . u_cast_idx,cast_id_idx,cast_id2_idx . | cast_id_idx . 5 . movie_json_test. c. ai_actor_id . 2 . 100. 00 . Sử dụng chỉ mục điều kiện . | 1 . ĐƠN GIẢN . a . NULL . eq_ref . HỌC CHÍNH . CHÍNH CHÍNH . 4 . movie_json_test. b. ai_myid . 1 . 100. 00 . NULL . +----+-------------+-------+----------+--------+ 3 hàng trong bộ, 1 warning (0.00 giây) Điều này không chỉ nhanh hơn 7 giây so với việc truy cập cùng một dữ liệu thông qua các hàm JSON mà tôi còn có thể sử dụng các bảng này để dễ dàng thực hiện những việc như đưa cho tôi tất cả các bộ phim có hai hoặc ba diễn viên cùng nhau, chơi sáu độ, v.v. . Take-Aways và bài học kinh nghiệmTrước tiên, hãy suy nghĩ trước khi bạn lưu trữ dữ liệu của mình. Hiểu những gì bạn đang lưu trữ, tại sao bạn đang lưu trữ và cách bạn sẽ truy cập nó là điều tối quan trọng. Cách bạn sẽ truy cập và sử dụng dữ liệu của mình có tác động sâu sắc đến việc thiết lập, thiết kế và sử dụng cơ sở dữ liệu tối ưu. Nếu bạn chỉ truy cập các thuộc tính cấp cao nhất trong tài liệu của mình, một thiết kế đơn giản chỉ dựa vào việc sử dụng các hàm JSON tích hợp sẵn của MySQL có thể hoàn toàn ổn. Nhưng khi bạn muốn tìm hiểu sâu hơn về dữ liệu và bắt đầu xem xét việc lấy các tập dữ liệu con của chúng tôi hoặc liên kết các tài liệu khác nhau để đảm bảo hiệu suất và khả năng mở rộng, cuối cùng bạn có thể giảm tính linh hoạt của mình và xây dựng (nín thở) một lược đồ cơ sở dữ liệu thực tế được chuẩn hóa và . Thứ hai, các hàm JSON của MySQL hoàn thành công việc cho hầu hết các trường hợp sử dụng cơ bản. Bạn có thể nhận được những gì bạn cần, nhưng bạn cần hiểu các triển khai và những gì có sẵn và những gì không. Khi tôi thực hiện các quy trình tương tự này với PostgreSQL và MongoDB, bạn sẽ thấy một số chức năng tốt hơn các chức năng khác ở đâu. Cuối cùng, đừng sợ các lược đồ. Nghiêm túc, cấu trúc là tốt. Bất kể bạn truy cập dữ liệu của mình và sử dụng nó như thế nào, bạn sẽ sử dụng một số cấu trúc (tất cả phụ thuộc vào nơi bạn muốn thực thi cấu trúc đó). Tiếp theo trong loạt bài này, tôi sẽ đi sâu vào JSON với PostgreSQL, tiếp theo là JSON và MongoDB. Giữ nguyên Có thể lưu trữ JSON trong MySQL không?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 .
Làm cách nào để lưu dữ liệu JSON vào 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 để chèn dữ liệu JSON vào MySQL?php $json = file_get_contents('php. //đầu vào'); . php'; . `tên bảng` (tên, điện thoại, thành phố, email) VALUES ('"
Làm cách nào để đọc dữ liệu JSON trong MySQL?Điểm chính để trích xuất dữ liệu từ trường JSON trong MySQL. . Sử dụng $. . Sử dụng $[index] để trích xuất giá trị của phần tử từ mảng JSON Sử dụng -> làm lối tắt cho JSON_EXTRACT nếu giá trị không phải là chuỗi |
