點擊查看原文

MySQL支持RFC 7159JSON定義的本機數(shù)據(jù)類型，該類型可有效訪問JSON（JavaScript對象表示法）文檔中的數(shù)據(jù),。該 數(shù)據(jù)類型提供了這些優(yōu)點超過存儲在字符串列JSON格式的字符串： JSON

• 自動驗證存儲在JSON列中的JSON文檔 ,。無效的文檔會產(chǎn)生錯誤。
• 優(yōu)化的存儲格式,。JSON列中存儲的JSON文檔將 轉(zhuǎn)換為內(nèi)部格式,，以允許快速讀取文檔元素。當服務器稍后必須讀取以該二進制格式存儲的JSON值時,，無需從文本表示形式解析該值,。二進制格式的結(jié)構(gòu)使服務器能夠直接通過鍵或數(shù)組索引查找子對象或嵌套值，而無需讀取文檔中它們之前或之后的所有值,。

MySQL 8.0還使用該 功能支持RFC 7396中定義的JSON Merge Patch格式 ,。有關示例和更多信息，請參見此函數(shù)的描述以及 JSON值的規(guī)范化,，合并和自動包裝,。 JSON_MERGE_PATCH()

本討論使用JSONmonotype專門表示JSON數(shù)據(jù)類型，而 使用常規(guī)字體的“ JSON ”通常表示JSON數(shù)據(jù),。

存儲JSON文檔所需的空間與LONGBLOB或 大致相同LONGTEXT,。有關更多信息,，請參見 第11.7節(jié)“數(shù)據(jù)類型存儲要求”。重要的是要記住,，存儲在JSON列中的任何JSON文檔的大小都限于max_allowed_packet系統(tǒng)變量的值,。（當服務器內(nèi)部在內(nèi)存中操作JSON值時，該值可以大于此值,；該限制在服務器存儲它時適用,。）您可以使用JSON_STORAGE_SIZE()函數(shù)獲取存儲JSON文檔所需的空間量 ；請注意,，對于JSON 在列中,，存儲大小（以及由此函數(shù)返回的值）是該列在可能對其執(zhí)行的任何部分更新之前使用的大?。ㄕ垍㈤啽竟?jié)后面有關JSON部分更新優(yōu)化的討論）,。

在MySQL 8.0.13之前，JSON列不能具有非NULL默認值,。

除JSON數(shù)據(jù)類型外,，還有一組SQL函數(shù)可用于啟用對JSON值的操作，例如創(chuàng)建,，操作和搜索,。以下討論顯示了這些操作的示例。有關各個函數(shù)的詳細信息,，請參見第12.18節(jié)“ JSON函數(shù)”,。

還提供了一組用于處理GeoJSON值的空間函數(shù)。請參見第12.17.11節(jié)“空間GeoJSON函數(shù)”,。

JSON像其他二進制類型的列一樣,，列也不直接建立索引；相反,，您可以在生成的列上創(chuàng)建索引,，以從該JSON列中提取標量值 。有關詳細示例,，請參見 索引生成的列以提供JSON列索引,。

MySQL優(yōu)化器還在與JSON表達式匹配的虛擬列上尋找兼容的索引。

在MySQL 8.0.17及更高版本中,，InnoDB 存儲引擎支持JSON數(shù)組上的多值索引,。請參閱 多值索引。

MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函數(shù),，包括在從列生成的JSON列上創(chuàng)建索引，以作為無法索引JSON列的解決方法,。JSON每個NDB表最多支持3列 ,。

JSON值的部分更新

在MySQL 8.0中，優(yōu)化器可以對列執(zhí)行部分就地更新，JSON而不是刪除舊文檔并將新文檔全部寫入該列,?？梢詫M足以下條件的更新執(zhí)行此優(yōu)化：

• 正在更新的列聲明為 JSON。

• 該UPDATE語句使用任何的三個功能 JSON_SET(),， JSON_REPLACE()或 JSON_REMOVE()更新列,。列值的直接分配（例如 UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作為部分更新執(zhí)行。

  可以通過這種方式優(yōu)化JSON單個UPDATE語句 中多個列的更新,。MySQL只能對使用剛剛列出的三個函數(shù)更新其值的那些列執(zhí)行部分更新,。

• 輸入列和目標列必須是同一列；這樣的語句UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能作為部分更新執(zhí)行,。

  只要輸入和目標列相同,，此更新就可以以任何組合方式使用對上一項列出的任何函數(shù)的嵌套調(diào)用。

• 所有更改都會用新值替換現(xiàn)有的數(shù)組或?qū)ο笾?，并且不會將任何新元素添加到父對象或?shù)組,。

• 要替換的值必須至少與替換值一樣大。換句話說,，新值不能大于舊值,。

  當先前的部分更新為較大的值留出了足夠的空間時，可能會發(fā)生此要求的例外情況,。您可以使用該函數(shù) JSON_STORAGE_FREE()查看任何部分JSON列更新已釋放了多少空間 ,。

可以使用節(jié)省空間的緊湊格式將此類部分更新寫入二進制日志?？梢酝ㄟ^將binlog_row_value_options 系統(tǒng)變量設置為啟用此功能PARTIAL_JSON,。有關更多信息，請參見此變量的描述,。

接下來的幾節(jié)提供有關JSON值的創(chuàng)建和操作的基本信息,。

創(chuàng)建JSON值

JSON數(shù)組包含一個值列表，這些值用逗號分隔并包含在[和] 字符內(nèi)：

["abc", 10, null, true, false]

JSON對象包含一組鍵值對,，以逗號分隔并包含在{和 }字符內(nèi)：

{"k1": "value", "k2": 10}

如示例所示,，JSON數(shù)組和對象可以包含字符串或數(shù)字的標量值，JSON空文字或JSON布爾值true或false文字,。JSON對象中的鍵必須是字符串,。還允許使用時間（日期，時間或日期時間）標量值：

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON數(shù)組元素和JSON對象鍵值中允許嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

{"k1": "value", "k2": [10, 20]}

為此,，您還可以從MySQL提供的許多函數(shù)中獲取JSON值（請參見 第12.18.2節(jié)“創(chuàng)建JSON值的函數(shù)”）,，以及通過JSON使用將其他類型的值強制轉(zhuǎn)換為使用該類型的值（請參見 在JSON之間進行 轉(zhuǎn)換）和非JSON值）。接下來的幾段描述MySQL如何處理作為輸入提供的JSON值,。 CAST(*value* AS JSON)

在MySQL中,，JSON值被寫為字符串,。MySQL會解析在需要JSON值的上下文中使用的任何字符串，如果該字符串作為JSON無效,，則會產(chǎn)生錯誤,。這些上下文包括將值插入具有JSON數(shù)據(jù)類型的列中， 并將參數(shù)傳遞給需要JSON值的函數(shù)（通常顯示為MySQL JSON函數(shù)的文檔json_doc或 json_val在文檔中顯示 ）,，如以下示例所示：

• JSON 如果值是有效的JSON值,，則 嘗試將值插入到列中會成功，但如果不是,，則嘗試失?。?/p>

   

mysql> CREATE TABLE t1 (jdoc JSON);

Query OK, 0 rows affected (0.20 sec)

?

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

Query OK, 1 row affected (0.01 sec)

?

mysql> INSERT INTO t1 VALUES('[1, 2,');

ERROR 3140 (22032) at line 2: Invalid JSON text:

mysql> SELECT JSON_TYPE('["a", "b", 1]');

+----------------------------+

| JSON_TYPE('["a", "b", 1]') |

+----------------------------+

| ARRAY                      |

+----------------------------+

?

mysql> SELECT JSON_TYPE('"hello"');

+----------------------+

| JSON_TYPE('"hello"') |

+----------------------+

| STRING               |

+----------------------+

?

mysql> SELECT JSON_TYPE('hello');

ERROR 3146 (22032): Invalid data type for JSON data in argument 1

• to function json_type; a JSON string or JSON type is required.

MySQL使用utf8mb4字符集和utf8mb4_bin排序規(guī)則處理JSON上下文中使用的 字符串 。其他字符集中的字符串將utf8mb4根據(jù)需要轉(zhuǎn)換為,。（對于ascii或 utf8字符集中的字符串,，無需進行轉(zhuǎn)換，因為ascii和utf8是的子集utf8mb4,。）

作為使用文字字符串編寫JSON值的替代方法,，存在用于從組件元素組成JSON值的函數(shù)。JSON_ARRAY()接受（可能為空）值列表,，并返回包含這些值的JSON數(shù)組：

mysql> SELECT JSON_ARRAY('a', 1, NOW());

+----------------------------------------+

| JSON_ARRAY('a', 1, NOW())              |

+----------------------------------------+

| ["a", 1, "2015-07-27 09:43:47.000000"] |

+----------------------------------------+

JSON_OBJECT() 接受鍵值對的列表（可能為空）,，并返回包含這些對的JSON對象：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');

+---------------------------------------+

| JSON_OBJECT('key1', 1, 'key2', 'abc') |

+---------------------------------------+

| {"key1": 1, "key2": "abc"}            |

+---------------------------------------+

JSON_MERGE_PRESERVE() 接受兩個或多個JSON文檔并返回合并的結(jié)果：

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');

+-----------------------------------------------------+

| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |

+-----------------------------------------------------+

| ["a", 1, {"key": "value"}]                          |

+-----------------------------------------------------+

1 row in set (0.00 sec)

有關合并規(guī)則的信息，請參見 JSON值的規(guī)范化,，合并和自動包裝,。

（MySQL 8.0.3和更高版本也支持 JSON_MERGE_PATCH()，其行為有所不同,。有關這兩個函數(shù)之間的區(qū)別,，請參見 JSON_MERGE_PATCH（）與JSON_MERGE_PRESERVE（）的比較。）

可以將JSON值分配給用戶定義的變量：

mysql> SET @j = JSON_OBJECT('key', 'value');

mysql> SELECT @j;

+------------------+

| @j               |

+------------------+

| {"key": "value"} |

+------------------+

但是,，用戶定義的變量不能是 JSON數(shù)據(jù)類型,，所以雖然 @j在前面的例子中看起來像一個JSON值，并且具有相同的字符集并歸類為JSON值,，但它不具有 JSON數(shù)據(jù)類型,。而是將來自的結(jié)果 JSON_OBJECT()分配給變量后轉(zhuǎn)換為字符串。

通過轉(zhuǎn)換JSON值產(chǎn)生的字符串的字符集為utf8mb4,，排序規(guī)則為 utf8mb4_bin：

mysql> SELECT CHARSET(@j), COLLATION(@j);

+-------------+---------------+

| CHARSET(@j) | COLLATION(@j) |

+-------------+---------------+

| utf8mb4     | utf8mb4_bin   |

+-------------+---------------+

因為utf8mb4_bin是二進制排序規(guī)則,，所以JSON值的比較區(qū)分大小寫。

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');

+-----------------------------------+

| JSON_ARRAY('x') = JSON_ARRAY('X') |

+-----------------------------------+

|                                 0 |

+-----------------------------------+

區(qū)分大小寫也適用于JSON null,，true和 false文字,，它們必須始終以小寫形式編寫：

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');

+--------------------+--------------------+--------------------+

| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |

+--------------------+--------------------+--------------------+

|                  1 |                  0 |                  0 |

+--------------------+--------------------+--------------------+

?

mysql> SELECT CAST('null' AS JSON);

+----------------------+

| CAST('null' AS JSON) |

+----------------------+

| null                 |

+----------------------+

1 row in set (0.00 sec)

?

mysql> SELECT CAST('NULL' AS JSON);

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:

"Invalid value." at position 0 in 'NULL'.

從該SQL的JSON文字不同的情況下的靈敏度NULL，TRUE和 FALSE文字,，它可以在任何大小寫被寫成：

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);

+--------------+--------------+--------------+

| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |

+--------------+--------------+--------------+

|            1 |            1 |            1 |

+--------------+--------------+--------------+

有時可能需要或希望在JSON文檔中插入引號字符（"或'）,。假設在此示例中,，您想要在表中插入使用以下語句創(chuàng)建的表中的JSON對象,，這些JSON對象包含表示有關MySQL的某些事實的句子,，每個句子都聲明了有關MySQL的一些事實，每個事實與適當?shù)年P鍵字配對,。

mysql> CREATE TABLE facts (sentence JSON);

這些關鍵字句子對中的一個是：

mascot: The MySQL mascot is a dolphin named "Sakila".

將其作為JSON對象插入facts表中的一種方法 是使用MySQL JSON_OBJECT()函數(shù),。在這種情況下，必須使用反斜杠對每個引號字符進行轉(zhuǎn)義,，如下所示：

mysql> INSERT INTO facts VALUES

     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

如果您將值作為JSON對象文字插入,，則此方法無法以相同的方式工作，在這種情況下,，必須使用雙反斜杠轉(zhuǎn)義序列,，如下所示：

mysql> INSERT INTO facts VALUES

     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

使用雙反斜杠使MySQL無法執(zhí)行轉(zhuǎn)義序列處理，而是使它將字符串文字傳遞給存儲引擎進行處理,。以上述兩種方式插入JSON對象后,，您可以通過執(zhí)行以下簡單操作看到JSON列值中存在反斜杠SELECT：

mysql> SELECT sentence FROM facts;

+---------------------------------------------------------+

| sentence                                                |

+---------------------------------------------------------+

| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |

+---------------------------------------------------------+

要查找使用mascot關鍵字作為關鍵字的特定句子 ，可以使用column-path運算符 ->,，如下所示：

mysql> SELECT col->"$.mascot" FROM qtest;

+---------------------------------------------+

| col->"$.mascot"                             |

+---------------------------------------------+

| "Our mascot is a dolphin named \"Sakila\"." |

+---------------------------------------------+

1 row in set (0.00 sec)

這使反斜杠以及周圍的引號保持完整,。要使用mascot鍵顯示所需的值 ，但不包括周圍的引號或任何轉(zhuǎn)義符->>,，請使用內(nèi)聯(lián)路徑運算符 ,，如下所示：

mysql> SELECT sentence->>"$.mascot" FROM facts;

+-----------------------------------------+

| sentence->>"$.mascot"                   |

+-----------------------------------------+

| Our mascot is a dolphin named "Sakila". |

+-----------------------------------------+

注意

如果NO_BACKSLASH_ESCAPES啟用了服務器SQL模式，上一個示例將無法正常 運行,。如果設置了此模式,，則可以使用單個反斜杠而不是雙反斜杠來插入JSON對象文字，并且保留反斜杠,。如果JSON_OBJECT()在執(zhí)行插入操作時使用了該函數(shù),，并且設置了此模式，則必須使用單引號和雙引號,，例如：

mysql> INSERT INTO facts VALUES

     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

有關JSON_UNQUOTE()此模式對JSON值中的轉(zhuǎn)義字符的影響的更多信息,，請參見函數(shù)的描述 。

JSON值的規(guī)范化,，合并和自動包裝 等其他更多內(nèi)容請查看原文