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

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

為什么JSON的原生支持

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

2. 更有效的訪問
   MySQL 5.7.7+本身提供了很多原生的函數(shù)以及路徑表達(dá)式來方便用戶訪問JSON數(shù)據(jù)。例如對于下面的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 ]
   還可以使用通配符 * 和 ** 來進(jìn)行模糊匹配，詳見下一段。

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

JSON的操作接口及路徑表達(dá)式

1. JSON的操作接口
   根據(jù)MySQL官方文檔的介紹，服務(wù)器端JSON函數(shù)的實(shí)現(xiàn)需要滿足以下條件：

   Requirements:

   Non-requirements:

   提供的函數(shù)列表具體為：

   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()

   
   

   以上函數(shù)的調(diào)用規(guī)則大多形如：

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

   
   

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

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路徑表達(dá)式
   為了更方便快速的訪問JSON的鍵值，MySQL 5.7.7+提供了新的路徑表達(dá)式語法支持。前文提到的$.a[1][0]就是路徑表達(dá)式的一個具體的示例。完整的路徑表達(dá)式語法為：

   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

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

   關(guān)于通配符*和**來進(jìn)行模糊匹配需要做進(jìn)一步的說明。

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

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

   得到顯示結(jié)果：["d", 6]。

JSON的存儲結(jié)構(gòu)及具體實(shí)現(xiàn)

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

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

根據(jù)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

我們來使用示意圖更清晰的展示它的結(jié)構(gòu)：

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

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

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

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

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

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

• 索引的大小現(xiàn)在是冗余信息，因?yàn)橥ㄟ^相鄰偏移可以簡單的得到存儲大小，主要是為了應(yīng)對變長JSON對象值更新，如果長度變小，JSON文檔整體都不用移動，只需要當(dāng)前對象修改大小

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

• 結(jié)合JSON的路徑表達(dá)式可以知道，JSON的搜索操作只用反序列化路徑上涉及到的元素，速度非?？欤瑢?shí)現(xiàn)了讀操作的高性能

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

JSON的索引

現(xiàn)在MySQL不支持對JSON列進(jìn)行索引，官網(wǎng)文檔的說明是：

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規(guī)定，可以首先使用路徑表達(dá)式對JSON文檔中的標(biāo)量值建立虛擬列，然后在虛擬列上建立索引。這樣用戶可以使用表達(dá)式對自己感興趣的鍵值建立索引。舉個具體的例子來說明：

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

插入它的JSON數(shù)據(jù)的格式為：

{
   "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")創(chuàng)建索引。

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

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

JSON比較與排序

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

JSON類型按照優(yōu)先級從高到低為

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

優(yōu)先級高的類型大，不用再進(jìn)行其他的比較操作；如果類型相同，每個類型按自己的規(guī)則排序。具體的規(guī)則如下：

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

2. DATETIME/TIME/DATE: 按照所表示的時間點(diǎn)排序

3. BOOLEAN: false小于true

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

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

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

7. INTEGER/DOUBLE: 包括精確值和近似值的比較，稍微有點(diǎn)復(fù)雜，可能出現(xiàn)與直覺相悖的結(jié)果，具體參見官方文檔相關(guān)說明。

任何JSON值與SQL的NULL常量比較，得到的結(jié)果是UNKNOWN。對于JSON值和非JSON值的比較，按照一定的規(guī)則將非JSON值轉(zhuǎn)化為JSON值，然后按照以上的規(guī)則進(jìn)行比較。

到此，相信大家對“MySQL5.7中JSON的操作接口及路徑表達(dá)式”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！