Hướng dẫn mysql json query
Đến phiên bản MySQL 5.7.8, MySQL đã hỗ trợ kiểu dữ liệu JSON cho phép truy cập hiệu quả với dữ liệu trong tài liệu JSON. Kiểu dữ liệu JSON cung cấp những lợi thế hơn so với việc lưu trữ các chuỗi định dạng JSON trong một column string: Show
Kích thước của tài liệu JSON được lưu trữ trong các cột JSON được giới hạn bằng giá trị của biến hệ thống max_allowed_packet (Trong khi các máy chủ thao tác một giá trị JSON trong nội bộ bộ nhớ trong, nó có thể lớn hơn; giới hạn được áp dụng khi các máy chủ lưu trữ nó). Các cột JSON không thể có một giá trị mặc định. Các cột JSON, giống như cột của các loại nhị phân khác, không được lập chỉ mục trực tiếp; thay vào đó, bạn có thể tạo một chỉ mục trên một cột được tạo ra bằng cách trích ra một giá trị vô hướng từ các cột JSON. Các bộ tối ưu của MySQL cũng sẽ tìm kiếm các chỉ số tương thích trên các cột ảo phù hợp với biểu thức JSON. MySQL Cluster NDB 7.5.2 và sau đó hỗ trợ các cột JSON và các function MySQL JSON, bao gồm cả việc tạo ra các chỉ mục trên một cột được tạo ra từ cột JSON như là một cách giải quyết khác cho vấn đề không thể đánh index cột JSON. Tối đa 3 cột JSON cho mỗi bảng NDB được hỗ trợ. Thảo luận sau đây bao gồm các chủ đề:
Cùng với các kiểu dữ liệu JSON, có sẵn một tập hợp các function SQL để hoạt động trên các giá trị JSON, chẳng hạn như tạo, thao tác và tìm kiếm. Một tập hợp các function không gian để hoạt động trên các giá trị GeoJSON cũng có sẵn. Tạo giá trị JSONMột mảng JSON chứa một danh sách các giá trị cách nhau bởi dấu phẩy và đặt trong cặp ký tự [ và ]: Như
các ví dụ minh họa, mảng JSON và các đối tượng có thể chứa các giá trị vô hướng đó là chuỗi hoặc số, JSON null, hoặc JSON boolean true hay false. Key trong đối tượng JSON phải là các chuỗi. Giá trị thời gian (date, time, hoặc datetime) cũng được phép:
Bạn cũng có thể có được giá trị JSON từ một số function được cung cấp bởi MySQL cho mục đích này cũng như bằng cách casting các giá trị của các kiểu khác thành kiểu JSON sử dụng CAST(value AS JSON). Các phần tiếp theo mô tả cách MySQL xử lý các giá trị JSON được cung cấp như là đầu vào. Trong MySQL, giá trị JSON đều là các chuỗi. MySQL phân tích bất kỳ chuỗi được sử dụng trong một bối cảnh đòi hỏi một giá trị JSON, và sinh ra một lỗi nếu nó không thỏa mãn chuỗi JSON. Những bối cảnh bao đó gồm thêm một giá trị vào một cột có kiểu dữ liệu JSON và truyền một tham số đến một function chờ đợi một giá trị JSON, như các ví dụ sau đây: Chèn một giá trị vào cột JSON thành công nếu đó là một giá trị JSON hợp lệ, nhưng không thành công nếu nó không phải:
Vị trí cho "at position N" trong các thông báo lỗi như vậy là bắt đầu từ 0, nhưng cần được xem xét dấu hiệu gần đúng của nơi mà vấn đề ở một giá trị thực sự xảy ra. Function JSON_TYPE() nhận một tham số JSON và cố gắng để phân tích nó thành một giá trị JSON. Nó trả về kiểu JSON của giá trị nếu nó là hợp lệ nếu không sẽ sinh ra một lỗi:
MySQL xử lý chuỗi được sử dụng trong bối cảnh JSON bằng cách sử dụng charset utf8mb4 và collation utf8mb4_bin. Strings ở charset khác được chuyển đổi thành utf8mb4 khi cần thiết (Đối với các string ở charset ascii hoặc utf8, không cần thiết chuyển đổi vì ascii và utf8 là tập con của utf8mb4). Để thay thế cho các giá trị JSON sử dụng các chuỗi, tồn tại các function để tạo giá trị JSON từ các phần tử thành phần. JSON_ARRAY()nhận một danh sách (có thể rỗng) các giá trị và trả về một mảng JSON chứa những giá trị đó:
JSON_OBJECT() nhận một danh sách (có thể rỗng) các cặp key/value và trả về một đối tượng JSON chứa những cặp giá trị đó:
JSON_MERGE() nhận vào hai hoặc nhiều tài liệu JSON và trả về kết quả kết hợp:
giá trị JSON có thể được gán cho biến do người dùng định nghĩa:
Tuy nhiên, biến do người dùng định nghĩa không thể là kiểu dữ liệu JSON, vì vậy mặc dù
Bởi vì utf8mb4_bin là một collation nhị phân, so sánh các giá trị JSON là trường hợp nhạy cảm.
Trường hợp nhạy cảm cũng áp dụng cho các JSON null, true và false, chúng luôn luôn phải được viết bằng chữ thường:
Normalization, Merging, and Autowrapping of JSON ValuesKhi một chuỗi được phân tích và tìm thấy là một tài liệu JSON hợp lệ, nó cũng thực hiện normalization: các thành phần với các key lặp với một key được tìm thấy trước đó trong tài liệu được bỏ đi (thậm chí nếu các giá trị khác nhau). Các giá trị object sinh ra bởi function JSON_OBJECT() sau đây không bao gồm phần tử key1 thứ hai, vì rằng tên key đã có trước đó:
Việc normalization được thực hiện bởi MySQL cũng sắp xếp các key của một đối tượng JSON (phục vụ mục đích làm cho tra cứu hiệu quả hơn). Kết quả của việc sắp xếp này có thể thay đổi và không đảm bảo được ổn định qua các phiên bản. Ngoài ra, khoảng trắng thêm giữa các key, value, hoặc các element trong văn bản gốc được loại bỏ. Function MySQL sinh giá trị JSON luôn luôn trả về giá trị normalized. Trong bối cảnh kết hợp nhiều mảng, mảng được sáp nhập vào một mảng duy nhất bằng cách kết hợp các mảng được đặt tên sau đó đến cuối mảng đầu tiên. Trong ví dụ sau đây, JSON_MERGE()kết hợp các tham số của mình vào một mảng duy nhất:
Khi sáp nhập nhiều đối tượng tạo ra một đối tượng duy nhất. Nếu có nhiều đối tượng có cùng key, giá trị cho key đó trong đối tượng kết quả là một mảng chứa các giá trị:
Giá trị Nonarray sử dụng trong bối cảnh đòi hỏi một mảng giá trị được autowrapped: Giá trị được bao bởi ký tự [ và ] để chuyển đổi nó vào một mảng. Trong statement sau đây, mỗi đối số được autowrapped như là một mảng ([1], [2]). Sau đó sáp nhập để tạo ra một mảng kết quả duy nhất:
Các giá trị mảng và đối tượng này được sáp nhập bởi autowrapping các đối tượng như là một mảng và sáp nhập hai mảng:
Searching and Modifying JSON ValuesMột biểu thức đường dẫn JSON chọn ra một giá trị trong một tài liệu JSON. Biểu thức đường dẫn hữu ích với function trích xuất các bộ phận hoặc chỉnh sửa tài liệu JSON, để chỉ định nơi trong tài liệu đó để hoạt động. Ví dụ, truy vấn sau đây trích từ một tài liệu JSON các giá trị của các thành phần với key là name:
Cú pháp đường dẫn sử dụng ký tự
Bây giờ $ đại diện mảng JSON này với ba phần tử: Khi đó:
Vì $[1] và $[2] biểu thị cho các giá trị nonscalar, chúng có thể được sử dụng làm cơ sở cho các đường dẫn biểu thức cụ thể hơn để chọn các giá trị lồng nhau. Ví dụ:
Như đã đề cập trước đó, các thành phần đường dẫn là tên các key phải được đặt trong dấu nháy kép nếu tên key là không hợp pháp trong biểu thức đường dẫn. Bây giờ $ đại diện giá trị này:
Đường dẫn sử dụng các kí hiệu biểu thị cho một mảng có thể chứa nhiều giá trị:
Trong ví dụ sau, đường dẫn
Trong MySQL 5.7.9 trở lên, bạn có thể sử dụng Một số function cần một tài liệu JSON tồn tại, sửa đổi nó bằng một
cách nào đó, và trả lại kết quả là tài liệu đã sửa đổi. Biểu thức đường dẫn chỉ ra nơi trong văn bản để thực hiện thay đổi. Ví dụ, function
JSON_SET() thay thế các giá trị cho các đường tồn tại và bổ sung thêm giá trị cho các đường dẫn không tồn tại :.
Trong trường hợp này, đường dẫn [1].b[0]lựachọnmộtgiaˊ
trịto^ˋntại(true),ro^ˋiđượcthayt
he^ˊba˘ˋnggiaˊtrịsauđườngda^~n(1).Đườngda^~n‘[1].b[0]lựa chọn một giá trị tồn tại (true), rồi được thay thế bằng giá trị sau đường dẫn (1). Đường dẫn `[2][2] JSON_INSERT() thêm giá trị mới nhưng không thay thế các giá trị hiện tại:
JSON_REPLACE() thay thế giá trị hiện có và bỏ qua các giá trị mới:
JSON_REMOVE()nhận một tài liệu JSON và một hoặc nhiều path xác định giá trị cần được loại bỏ từ tài liệu. Giá trị trả về là tài liệu gốc trừ đi giá trị của đường dẫn tồn tại trong tài liệu được lựa chọn: |