MySQL5.7中JSON的操作接口及路徑表達式

本篇內容主要講解“MySQL5.7中JSON的操作接口及路徑表達式”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL5.7中JSON的操作接口及路徑表達式”吧!

為什么JSON的原生支持

1. 文檔合法性
   在MySQL5.7.7對JSON提供原生類型的支持之前，用戶可以用TEXT或者BLOB類型來存儲JSON文檔。但對于MySQL來說，用戶插入的數據只是序列化后的一個普通的字符串，不會對JSON文檔本身的語法合法性做檢查，文檔的合法性需要用戶自己保證。在引入新的JSON類型之后，插入語法錯誤的JSON文檔，MySQL會提示錯誤，并在插入之后做歸一化處理，保證每一個鍵對應一個值。

2. 更有效的訪問
   MySQL 5.7.7+本身提供了很多原生的函數以及路徑表達式來方便用戶訪問JSON數據。例如對于下面的JSON文檔：
   { "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }

   用戶可以使用
   $.a[1][0]獲取{ "c" : "d" }，
   $.a[1]獲取[ { "c" : "d" }, 1 ]
   還可以使用通配符 * 和 ** 來進行模糊匹配，詳見下一段。

3. 性能優化
   在MySQL提供JSON原生支持之前，如果用戶需要獲取或者修改某個JSON文檔的鍵值，需要把TEXT或者BLOB整個字符串讀出來反序列化成JSON對象，然后通過各種庫函數訪問JSON數據。顯然這樣是非常沒有效率的，特別是對較大的文檔。而原生JSON的性能，特別是讀性能非常好。根據Oracle公司針對200K+數據文檔做的性能測試表明，同樣的數據用TEXT和JSON類型的查詢性能差異達到兩個數量級以上，而且用戶還可以對經常訪問的JSON鍵值做索引，進一步提升性能。JSON數據操作性能的提升是基于JSON數據本身的存儲結構的，下文會進一步介紹。

JSON的操作接口及路徑表達式

1. JSON的操作接口
   根據MySQL官方文檔的介紹，服務器端JSON函數的實現需要滿足以下條件：

   Requirements:

   Non-requirements:

   提供的函數列表具體為：

   JSON_APPEND() JSON_ARRAY_INSERT() JSON_UNQUOTE() JSON_ARRAY()
   JSON_REPLACE() JSON_CONTAINS() JSON_DEPTH() JSON_EXTRACT()
   JSON_INSERT() JSON_KEYS() JSON_LENGTH() JSON_VALID()
   JSON_MERGE() JSON_OBJECT() JSON_QUOTE() JSON_REMOVE()
   JSON_CONTAINS_PATH() JSON_SEARCH() JSON_SET() JSON_TYPE()

   
   

   以上函數的調用規則大多形如：

   JSON_APPEND(json_doc, path, val[, path, val] ...)

   
   

   第一個參數json_doc為JSON文檔，或者是表里面的某一列，也可以是JSON文檔里面的嵌套子文檔變量；
   第二個參數path為路徑表達式，用來定位要訪問的鍵，path(即路徑表達式)下面緊接著會介紹；
   第三個參數val有的函數可能沒有，若有表示鍵對應的操作數值。

1. May produce surprising results on strings which are not utf8mb4 encoded.

2. There is limited support for decimal values nested inside JSON documents.

3. Performance may not be suitable for write-intensive applications.

4. Lets users construct JSON data values from other relational data.

5. Lets users extract relational data from JSON data values.

6. Lets users minimally introspect the structure of JSON values and text (validity, length, depth, keys).

7. Works on strings which are utf8mb4 encoded.

8. Performance should be suitable for read-intensive applications.

2. JSON路徑表達式
   為了更方便快速的訪問JSON的鍵值，MySQL 5.7.7+提供了新的路徑表達式語法支持。前文提到的$.a[1][0]就是路徑表達式的一個具體的示例。完整的路徑表達式語法為：

   pathExpression> ::= scope  [ ( pathLeg )* ]
   scope ::= [ columnReference ] dollarSign
   columnReference ::= [ [ databaseIdentifier period  ] tableIdentifier period ] columnIdentifier
   databaseIdentifier ::= sqlIdentifier
   tableIdentifier ::= sqlIdentifier
   columnIdentifier ::= sqlIdentifier
   pathLeg ::= member | arrayLocation | doubleAsterisk
   member ::= period ( keyName | asterisk )
   arrayLocation ::= leftBracket ( non-negative-integer | asterisk ) rightBracket
   keyName ::= ECMAScript-identifier | double-quoted-string-literal
   doubleAsterisk ::= **

   
   

   還是以

   { "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }

   
   

   為例，再舉幾個例子說明：

   $.a[1] 獲取的值為 [ { "c" : "d" }, 1 ]
$.b.c 獲取的值為 6
   $."b.c" 獲取的值為 8

   對比上面最后兩個例子，可以看到用引號包圍的表達式會被當作一個字符串鍵值。

   關于通配符*和**來進行模糊匹配需要做進一步的說明。

   兩個連著星號**不能作為表達式的結尾，不能出現連續的三個星號***
   單個星號*表示匹配某個JSON對象中所有的成員
   [*]表示匹配某個JSON數組中的所有元素
   prefix**suffix表示所有以prefix開始，以suffix結尾的路徑

   舉個具體的例子，直接在MySQL命令行里面輸入：
   select json_extract('{ "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }','$**.c');

   得到顯示結果：["d", 6]。

JSON的存儲結構及具體實現

在處理JSON時，MySQL使用的utf8mb4字符集，utf8mb4是utf8和ascii的超集。由于歷史原因，這里utf8并非是我們常說的UTF-8 Unicode變長編碼方案，而是MySQL自身定義的utf8編碼方案，最長為三個字節。具體區別非本文重點，請大家自行Google了解。

MySQL在內存中是以DOM的形式表示JSON文檔，而且在MySQL解析某個具體的路徑表達式時，只需要反序列化和解析路徑上的對象，而且速度極快。要弄清楚MySQL是如何做到這些的，我們就需要了解JSON在硬盤上的存儲結構。有個有趣的點是，JSON對象是BLOB的子類，在其基礎上做了特化。

根據MySQL官方文檔的表述：

On a high level, we will store the contents of the JSON document in three sections:

• A table of pointers to all the keys and values, in the order in which the keys and values are stored. Each pointer contains information about where the data associated with the key or the value is located, as well as type information about the key or value pointed to.
  *All the keys. The keys are sorted, so that lookup can use binary search to locate the key quickly.

• All the values, in the same order as their corresponding keys.
  If the document is an array, it has two sections only: the dictionary and the values.
  If the document is a scalar, it has a single section which contains the scalar value

我們來使用示意圖更清晰的展示它的結構：

JSON文檔本身是層次化的結構，因而MySQL對JSON存儲也是層次化的。對于每一級對象，存儲的最前面為存放當前對象的元素個數，以及整體占的大小。需要注意的是：

• JSON對象的Key索引（圖中橙色部分）都是排序好的，先按長度排序，長度相同的按照code point排序；Value索引（圖中黃色部分）根據對應的Key的位置依次排列，最后面真實的數據存儲（圖中白色部分）也是如此

• Key和Value的索引對存儲了對象內的偏移和大小，單個索引的大小固定，可以通過簡單的算術跳轉到距離為N的索引

• 通過MySQL5.7.16源代碼可以看到，在序列化JSON文檔時，MySQL會動態檢測單個對象的大小，如果小于64KB使用兩個字節的偏移量，否則使用四個字節的偏移量，以節省空間。同時，動態檢查單個對象是否是大對象，會造成對大對象進行兩次解析，源代碼中也指出這是以后需要優化的點

• 現在受索引中偏移量和存儲大小四個字節大小的限制，單個JSON文檔的大小不能超過4G；單個KEY的大小不能超過兩個字節，即64K

• 索引存儲對象內的偏移是為了方便移動，如果某個鍵值被改動，只用修改受影響對象整體的偏移量

• 索引的大小現在是冗余信息，因為通過相鄰偏移可以簡單的得到存儲大小，主要是為了應對變長JSON對象值更新，如果長度變小，JSON文檔整體都不用移動，只需要當前對象修改大小

• 現在MySQL對于變長大小的值沒有預留額外的空間，也就是說如果該值的長度變大，后面的存儲都要受到影響

• 結合JSON的路徑表達式可以知道，JSON的搜索操作只用反序列化路徑上涉及到的元素，速度非常快，實現了讀操作的高性能

• 不過，MySQL對于大型文檔的變長鍵值的更新操作可能會變慢，可能并不適合寫密集的需求

JSON的索引

現在MySQL不支持對JSON列進行索引，官網文檔的說明是：

JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.

雖然不支持直接在JSON列上建索引，但MySQL規定，可以首先使用路徑表達式對JSON文檔中的標量值建立虛擬列，然后在虛擬列上建立索引。這樣用戶可以使用表達式對自己感興趣的鍵值建立索引。舉個具體的例子來說明：

CREATE TABLE features (
 id INT NOT NULL AUTO_INCREMENT,
 feature JSON NOT NULL,
 PRIMARY KEY (id)
);

插入它的JSON數據的格式為：

{
   "type":"Feature",
   "properties":{
      "TO_ST":"0",
      "BLKLOT":"0001001",
      "STREET":"UNKNOWN",
      "FROM_ST":"0",
      "LOT_NUM":"001",
      "ST_TYPE":null,
      "ODD_EVEN":"E",
      "BLOCK_NUM":"0001",
      "MAPBLKLOT":"0001001"
   }
}

使用：

ALTER TABLE features ADD feature_street VARCHAR(30) AS (JSON_UNQUOTE(feature->"$.properties.STREET"));
ALTER TABLE features ADD INDEX (feature_street);

兩個步驟，可以對feature列中properties鍵值下的STREET鍵(feature->"$.properties.STREET")創建索引。

其中，feature_street列就是新添加的虛擬列。之所以取名虛擬列，是因為與它對應的還有一個存儲列(stored column)。它們最大的區別為虛擬列只修改數據庫的metadata，并不會存儲真實的數據在硬盤上，讀取過程也是實時計算的方式；而存儲列會把表達式的列存儲在硬盤上。兩者使用的場景不一樣，默認情況下通過表達式生成的列為虛擬列。

這樣虛擬列的添加和刪除都會非常快，而在虛擬列上建立索引跟傳統的建立索引的方式并沒有區別，會提高虛擬列讀取的性能，減慢整體插入的性能。虛擬列的特性結合JSON的路徑表達式，可以方便的為用戶提供高效的鍵值索引功能。

JSON比較與排序

JSON值可以使用=, <, <=, >, >=, <>, !=, <=>等操作符，BETWEEN, IN,GREATEST, LEAST等操作符現在還不支持。JSON值使用的兩級排序規則，第一級基于JSON的類型，類型不同的使用每個類型特有的排序規則。

JSON類型按照優先級從高到低為

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

優先級高的類型大，不用再進行其他的比較操作；如果類型相同，每個類型按自己的規則排序。具體的規則如下：

1. BLOB/BIT/OPAQUE: 比較兩個值前N個字節，如果前N個字節相同，短的值小

2. DATETIME/TIME/DATE: 按照所表示的時間點排序

3. BOOLEAN: false小于true

4. ARRAY: 兩個數組如果長度和在每個位置的值相同時相等，如果不想等，取第一個不相同元素的排序結果，空元素最小

5. OBJECT: 如果兩個對象有相同的KEY，并且KEY對應的VALUE也都相同，兩者相等。否則，兩者大小不等，但相對大小未規定。

6. STRING: 取兩個STRING較短的那個長度為N，比較兩個值utf8mb4編碼的前N個字節，較短的小，空值最小

7. INTEGER/DOUBLE: 包括精確值和近似值的比較，稍微有點復雜，可能出現與直覺相悖的結果，具體參見官方文檔相關說明。

任何JSON值與SQL的NULL常量比較，得到的結果是UNKNOWN。對于JSON值和非JSON值的比較，按照一定的規則將非JSON值轉化為JSON值，然后按照以上的規則進行比較。

到此，相信大家對“MySQL5.7中JSON的操作接口及路徑表達式”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！