Bạn có nên sử dụng JSON trong MySQL không?

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). Since then, a few minor enhancements have made the implementation a bit more liveable. The current iteration MySQL 8 offers a fully functional implementation of JSON functions and features. Let me show you some examples of how to store and interact with your JSON documents within MySQL

Setup

For all my tests, I wanted a reasonable amount of data to test the performance implications of certain functions.   I opted to use the metadata JSON from http. //movienet. site/, about 2. 3GB of individual JSON files (one per movie)

I wrote a small python script to load and iterate through the JSON files and load them into MySQL

I will walk through the examples and show you how I have seen many developers use MySQL to interact with JSON and point out why some of them may be incorrect or cause you issues you may not be aware of.   I will also show you a few other features you may want to look into and explore and offer some design advice.   Let us start with the following simple table definition

1

2

3

4

5

6

7

create table movies_json (

   ai_myid int AUTO_INCREMENT primary key,

   imdb_id varchar(255),

   json_column json

) engine = innodb;

create unique index imdb_idx on movies_json(imdb_id);

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 MySQL

Có, 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

1

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

1

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

1

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”

1

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ữ Bruce"

    },

    {

      "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ì

1

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

1

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. %' và 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

Chúng ta có nên sử dụng JSON trong MySQL không?

Sử dụng JSON trong cơ sở dữ liệu có tốt không?

Việc lưu trữ JSON trong SQL có phải là một ý tưởng hay không?

Hạn chế của việc sử dụng JSON trong MySQL là gì?