點擊查看原文
MySQL支持RFC 7159JSON定義的本機資料型別，該型別可有效訪問JSON（JavaScript物件表示法）檔案中的資料，該 資料型別提供了這些優點超過存盤在字串列JSON格式的字串： JSON
- 自動驗證存盤在
JSON列中的JSON檔案 ，無效的檔案會產生錯誤， - 優化的存盤格式，
JSON列中存盤的JSON檔案將 轉換為內部格式，以允許快速讀取檔案元素，當服務器稍后必須讀取以該二進制格式存盤的JSON值時，無需從文本表示形式決議該值，二進制格式的結構使服務器能夠直接通過鍵或陣列索引查找子物件或嵌套值，而無需讀取檔案中它們之前或之后的所有值，
MySQL 8.0還使用該 功能支持RFC 7396中定義的JSON Merge Patch格式 ，有關示例和更多資訊，請參見此函式的描述以及 JSON值的規范化，合并和自動包裝， JSON_MERGE_PATCH()
本討論使用
JSONmonotype專門表示JSON資料型別，而 使用常規字體的“ JSON ”通常表示JSON資料，
存盤JSON檔案所需的空間與LONGBLOB或 大致相同LONGTEXT，有關更多資訊，請參見 第11.7節“資料型別存盤要求”，重要的是要記住，存盤在JSON列中的任何JSON檔案的大小都限于max_allowed_packet系統變數的值，（當服務器內部在記憶體中操作JSON值時，該值可以大于此值；該限制在服務器存盤它時適用，）您可以使用JSON_STORAGE_SIZE()函式獲取存盤JSON檔案所需的空間量 ；請注意，對于JSON 在列中，存盤大小（以及由此函式回傳的值）是該列在可能對其執行的任何部分更新之前使用的大小（請參閱本節后面有關JSON部分更新優化的討論），
在MySQL 8.0.13之前，JSON列不能具有非NULL默認值，
除JSON資料型別外，還有一組SQL函式可用于啟用對JSON值的操作，例如創建，操作和搜索，以下討論顯示了這些操作的示例，有關各個函式的詳細資訊，請參見第12.18節“ JSON函式”，
還提供了一組用于處理GeoJSON值的空間函式，請參見第12.17.11節“空間GeoJSON函式”，
JSON像其他二進制型別的列一樣，列也不直接建立索引；相反，您可以在生成的列上創建索引，以從該JSON列中提取標量值 ，有關詳細示例，請參見 索引生成的列以提供JSON列索引，
MySQL優化器還在與JSON運算式匹配的虛擬列上尋找兼容的索引，
在MySQL 8.0.17及更高版本中，InnoDB 存盤引擎支持JSON陣列上的多值索引，請參閱 多值索引，
MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函式，包括在從列生成的JSON列上創建索引，以作為無法索引JSON列的解決方法，JSON每個NDB表最多支持3列 ，
JSON值的部分更新
在MySQL 8.0中，優化器可以對列執行部分就地更新，JSON而不是洗掉舊檔案并將新檔案全部寫入該列，可以對滿足以下條件的更新執行此優化：
-
正在更新的列宣告為
JSON， -
該
UPDATE陳述句使用任何的三個功能JSON_SET()，JSON_REPLACE()或JSON_REMOVE()更新列，列值的直接分配（例如UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作為部分更新執行，可以通過這種方式優化
JSON單個UPDATE陳述句 中多個列的更新，MySQL只能對使用剛剛列出的三個函式更新其值的那些列執行部分更新， -
輸入列和目標列必須是同一列；這樣的陳述句
UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能作為部分更新執行，只要輸入和目標列相同，此更新就可以以任何組合方式使用對上一項列出的任何函式的嵌套呼叫，
-
所有更改都會用新值替換現有的陣列或物件值，并且不會將任何新元素添加到父物件或陣列，
-
要替換的值必須至少與替換值一樣大，換句話說，新值不能大于舊值，
當先前的部分更新為較大的值留出了足夠的空間時，可能會發生此要求的例外情況，您可以使用該函式
JSON_STORAGE_FREE()查看任何部分JSON列更新已釋放了多少空間 ，
可以使用節省空間的緊湊格式將此類部分更新寫入二進制日志，可以通過將binlog_row_value_options 系統變數設定為啟用此功能PARTIAL_JSON，有關更多資訊，請參見此變數的描述，
接下來的幾節提供有關JSON值的創建和操作的基本資訊，
創建JSON值
JSON陣列包含一個值串列，這些值用逗號分隔并包含在[和] 字符內：
["abc", 10, null, true, false]
JSON物件包含一組鍵值對，以逗號分隔并包含在{和 }字符內：
{"k1": "value", "k2": 10}
如示例所示，JSON陣列和物件可以包含字串或數字的標量值，JSON空文字或JSON布林值true或false文字，JSON物件中的鍵必須是字串，還允許使用時間（日期，時間或日期時間）標量值：
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]
JSON陣列元素和JSON物件鍵值中允許嵌套：
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
為此，您還可以從MySQL提供的許多函式中獲取JSON值（請參見 第12.18.2節“創建JSON值的函式”），以及通過JSON使用將其他型別的值強制轉換為使用該型別的值（請參見 在JSON之間進行 轉換）和非JSON值），接下來的幾段描述MySQL如何處理作為輸入提供的JSON值， CAST(*value* AS JSON)
在MySQL中，JSON值被寫為字串，MySQL會決議在需要JSON值的背景關系中使用的任何字串，如果該字串作為JSON無效，則會產生錯誤，這些背景關系包括將值插入具有JSON資料型別的列中， 并將引數傳遞給需要JSON值的函式（通常顯示為MySQL JSON函式的檔案json_doc或 json_val在檔案中顯示 ），如以下示例所示：
-
JSON如果值是有效的JSON值，則 嘗試將值插入到列中會成功，但如果不是，則嘗試失敗：
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排序規則處理JSON背景關系中使用的 字串 ，其他字符集中的字串將utf8mb4根據需要轉換為，（對于ascii或 utf8字符集中的字串，無需進行轉換，因為ascii和utf8是的子集utf8mb4，）
作為使用文字字串撰寫JSON值的替代方法，存在用于從組件元素組成JSON值的函式，JSON_ARRAY()接受（可能為空）值串列，并回傳包含這些值的JSON陣列：
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檔案并回傳合并的結果：
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)
有關合并規則的資訊，請參見 JSON值的規范化，合并和自動包裝，
（MySQL 8.0.3和更高版本也支持 JSON_MERGE_PATCH()，其行為有所不同，有關這兩個函式之間的區別，請參見 JSON_MERGE_PATCH（）與JSON_MERGE_PRESERVE（）的比較，）
可以將JSON值分配給用戶定義的變數：
mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j |
+------------------+
| {"key": "value"} |
+------------------+
但是，用戶定義的變數不能是 JSON資料型別，所以雖然 @j在前面的例子中看起來像一個JSON值，并且具有相同的字符集并歸類為JSON值，但它不具有 JSON資料型別，而是將來自的結果 JSON_OBJECT()分配給變數后轉換為字串，
通過轉換JSON值產生的字串的字符集為utf8mb4，排序規則為 utf8mb4_bin：
mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4 | utf8mb4_bin |
+-------------+---------------+
因為utf8mb4_bin是二進制排序規則，所以JSON值的比較區分大小寫，
mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
| 0 |
+-----------------------------------+
區分大小寫也適用于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檔案中插入引號字符（"或'），假設在此示例中，您想要在表中插入使用以下陳述句創建的表中的JSON物件，這些JSON物件包含表示有關MySQL的某些事實的句子，每個句子都宣告了有關MySQL的一些事實，每個事實與適當的關鍵字配對，
mysql> CREATE TABLE facts (sentence JSON);
這些關鍵字句子對中的一個是：
mascot: The MySQL mascot is a dolphin named "Sakila".
將其作為JSON物件插入facts表中的一種方法 是使用MySQL JSON_OBJECT()函式，在這種情況下，必須使用反斜杠對每個引號字符進行轉義，如下所示：
mysql> INSERT INTO facts VALUES
> (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
如果您將值作為JSON物件文字插入，則此方法無法以相同的方式作業，在這種情況下，必須使用雙反斜杠轉義序列，如下所示：
mysql> INSERT INTO facts VALUES
> ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
使用雙反斜杠使MySQL無法執行轉義序列處理，而是使它將字串文字傳遞給存盤引擎進行處理，以上述兩種方式插入JSON物件后，您可以通過執行以下簡單操作看到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鍵顯示所需的值 ，但不包括周圍的引號或任何轉義符->>，請使用行內路徑運算子 ，如下所示：
mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot" |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+
注意
如果NO_BACKSLASH_ESCAPES啟用了服務器SQL模式，上一個示例將無法正常 運行，如果設定了此模式，則可以使用單個反斜杠而不是雙反斜杠來插入JSON物件文字，并且保留反斜杠，如果JSON_OBJECT()在執行插入操作時使用了該函式，并且設定了此模式，則必須使用單引號和雙引號，例如：
mysql> INSERT INTO facts VALUES
> (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
有關JSON_UNQUOTE()此模式對JSON值中的轉義字符的影響的更多資訊，請參見函式的描述 ，
JSON值的規范化，合并和自動包裝 等其他更多內容請查看原文
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/254747.html
標籤：MySQL
上一篇：null調整為not null default xxx，不得不注意的坑
下一篇：如何判斷redis變慢了