SQL中的JSON數(shù)據(jù)類型
概述
MySQL支持原生JSON類型嵌言，使用JSON數(shù)據(jù)類型相較于將JSON格式的字符串存儲在String型中的優(yōu)勢有：
存儲時會自動驗證JSON文本魏烫；
可以優(yōu)化存儲格式辣苏。存儲在
JSON型中的JSON文本會被轉(zhuǎn)換成一個支持快速讀取的文檔元素，這樣在使用時不需要再解析文本则奥，并且可以直接通過鍵和索引訪問其中的子對象而無需讀取全部文本考润。
JSON型中可存儲的JSON文本的大小不會超過mysql.ini配置文件中設置的max_allowed_packet的值。
JSON值的局部更新
MySQL從8.0開始支持對JSON型中存儲的數(shù)據(jù)進行局部更新读处，而不需要清除原有數(shù)據(jù)并寫入新值。
局部更新按照下述規(guī)則執(zhí)行：
數(shù)據(jù)列被聲明為
JSON型-
UPDATE語句使用JSON_SET()唱矛、JSON_REPLACE()罚舱、JSON_REMOVE()三個函數(shù)實現(xiàn)JSON的局部更新但對該列直接賦值不屬于局部更新，例如：
UPDATE myTable SET json1 = '{"a": 10, "b": 25}'局部更新操作可以實現(xiàn)在單個
UPDATE語句中更新多個JSON列 -
局部更新是針對同一列數(shù)據(jù)的操作绎谦，對不同列的操作不屬于局部更新管闷，例如：
UPDATE myTable SET json1 = JSON_SET(json2, '$.a', 100)局部更新中可以使用上述三個函數(shù)的嵌套調(diào)用形式
局部更新僅將現(xiàn)有JSON對象中的數(shù)組或子對象替換成新值，但不能給父對象或數(shù)組添加新元素
所替換的新值不能比原值占據(jù)的存儲空間更大窃肠，除非上一次更新留下了足夠的存儲空間
創(chuàng)建JSON值
-
JSON數(shù)組：
["abc", 10, null, true, false]JSON數(shù)組中可以存儲數(shù)字量包个、字符串、null冤留、布爾量碧囊、時間量
-
JSON對象：
{"key1": "value", "key2": 10}JSON對象中的鍵必須為字符串
JSON數(shù)組和JSON對象中可以嵌套子JSON數(shù)組和對象。
MySQL中的JSON編碼格式為CHARSET=utf8mb4 COLLATE=utf8mb4_bin纤怒。
使用字符串字面量創(chuàng)建JSON
在MySQL中JSON值按照字符串的形式寫入糯而，在要求為JSON值的上下文中（例如將值插入JSON列，或調(diào)用輸入?yún)?shù)為JSON的函數(shù)）MySQL會解析該字符串泊窘，若不符合JSON格式則報錯熄驼。
例如：
mysql> SELECT JSON_TYPE('["abc", 1]');
+-------------------------+
| JSON_TYPE('["abc", 1]') |
+-------------------------+
| ARRAY |
+-------------------------+
mysql> SELECT JSON_TYPE('{"a": 1, "b": 2}');
+-------------------------------+
| JSON_TYPE('{"a": 1, "b": 2}') |
+-------------------------------+
| OBJECT |
+-------------------------------+
mysql> SELECT JSON_TYPE('"abc"');
+--------------------+
| JSON_TYPE('"abc"') |
+--------------------+
| STRING |
+--------------------+
mysql> SELECT JSON_TYPE('abc');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.
使用函數(shù)創(chuàng)建JSON
介紹三個常用的創(chuàng)建JSON的函數(shù)：
-
JSON_ARRAY()函數(shù)會將傳入其中的參數(shù)組成JSON數(shù)組，例如：mysql> SELECT JSON_ARRAY('a', 1, NOW()); +----------------------------------------+ | JSON_ARRAY('a', 1, NOW()) | +----------------------------------------+ | ["a", 1, "2019-03-22 10:01:53.000000"] | +----------------------------------------+ -
JSON_OBJECT()會將傳入的鍵值對轉(zhuǎn)換為JSON對象烘豹，例如：mysql> SELECT JSON_OBJECT('a', 1, 'b', 2); +-----------------------------+ | JSON_OBJECT('a', 1, 'b', 2) | +-----------------------------+ | {"a": 1, "b": 2} | +-----------------------------+ -
JSON_MERGE_PRESERVE()將多個JSON文本組合成一個JSON瓜贾，例如：mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}'); +-----------------------------------------------------+ | JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}') | +-----------------------------------------------------+ | ["a", 1, {"a": 1, "b": 2}] | +-----------------------------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}'); +-------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}') | +-------------------------------------------------------------+ | {"a": 1, "b": 2, "c": 3, "d": 4} | +-------------------------------------------------------------+
使用JSON
-
將創(chuàng)建出的JSON值賦值給一個自定義的變量時，該JSON會被轉(zhuǎn)換成字符串携悯，因此自定義的變量屬于字符串類型祭芦、而非JSON類型，例如：
SET @j = JSON_OBJECT('key', 'value');創(chuàng)建的
@j為字符串類型蚌卤。 -
JSON值在進行比較時會區(qū)分大小寫实束，例如：
mysql> SELECT JSON_ARRAY('X') = JSON_ARRAY('x'); +-----------------------------------+ | JSON_ARRAY('X') = JSON_ARRAY('x') | +-----------------------------------+ | 0 | +-----------------------------------+'x'和`'X'不相等奥秆，返回false（即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 | +--------------------+--------------------+--------------------+可以看到
Null和NULL的形式是對JSON型來說非法的避矢。但應注意悼瘾，SQL中的
null、true审胸、false字面量不區(qū)分大小寫亥宿。 -
單引號
''和雙引號""的使用：-
使用
JSON_OBJECT()等函數(shù)創(chuàng)建JSON時，字符串中出現(xiàn)的引號需要使用轉(zhuǎn)義字符\來和標記字符串開始結(jié)束的引號作區(qū)分砂沛，否則會報錯烫扼，例如：正確寫法： mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\".")); Query OK, 1 row affected (0.22 sec) mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named 'Sakila'.")); Query OK, 1 row affected (0.22 sec) 錯誤寫法： mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot2", "Our mascot is a dolphin named "Sakila".")); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"."))' at line 1 -
使用JSON對象字面量創(chuàng)建JSON時，需要使用兩個轉(zhuǎn)義字符
\\來指明MySQL不需要對引號進行轉(zhuǎn)義碍庵，而保留其字面值映企，例如：兩種正確寫法： mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}'); Query OK, 1 row affected (0.11 sec) mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \'Sakila\'."}'); Query OK, 1 row affected (0.07 sec) 三種錯誤寫法： mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}'); ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column 'facts.sentence'. mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named "Sakila"."}'); ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column 'facts.sentence'. mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named 'Sakila'."}'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Sakila'."}')' at line 1
-
-
查找JSON中某個鍵對應的值：
-
使用
->操作符，返回值會顯示為帶有包裹引號和轉(zhuǎn)義字符的形式静浴，例如：mysql> SELECT sentence->"$.mascot" FROM facts; +---------------------------------------------+ | sentence->"$.mascot" | +---------------------------------------------+ | "Our mascot is a dolphin named \"Sakila\"." | +---------------------------------------------+ -
使用
->>操作符堰氓，返回值會顯示為無包裹引號和轉(zhuǎn)義字符的形式，例如：mysql> SELECT sentence->>"$.mascot" FROM facts; +-----------------------------------------+ | sentence->>"$.mascot" | +-----------------------------------------+ | Our mascot is a dolphin named "Sakila". | +-----------------------------------------+
-
JSON值的標準化苹享、合并双絮、和自動包裝
JSON值的標準化
當使用JSON_OBJECT()函數(shù)創(chuàng)建JSON對象時，傳入?yún)?shù)中的重復鍵會被忽略得问，即當出現(xiàn)重復鍵值對時囤攀，會對已存在鍵值對的值進行更新，例如：
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 2, 'key1', 10);
+-----------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 2, 'key1', 10) |
+-----------------------------------------------+
| {"key1": 10, "key2": 2} |
+-----------------------------------------------+
在使用INSERT()函數(shù)插入JSON對象時椭赋，也會忽略重復鍵抚岗，并更新該鍵對應的值，例如：
mysql> CREATE TABLE t1 (c1 JSON);
mysql> INSERT INTO t1 VALUES
-> ('{"x": 1, "x": "a"}'),
-> ('{"x": 1, "x": "a", "x": [1, 2, 3]}');
mysql> SELECT c1 FROM t1;
+------------------+
| c1 |
+------------------+
| {"x": "a"} |
| {"x": [1, 2, 3]} |
+------------------+
（注意：在8.0.3之前版本的MySQL中哪怔，遇到重復出現(xiàn)的鍵時宣蔚，不會用新值更新舊值，而僅忽略該鍵值對）
JSON值的合并和自動包裝
- 使用
JSON_MERGE_PRESERVE()函數(shù)合并多個JSON值時會保留重復的鍵 - 使用
JSON_MERGE_PATCH()函數(shù)時僅保留重復鍵的最后一個认境，即會發(fā)生JSON的標準化過程
合并JSON數(shù)組
-
JSON_MERGE_PRESERVE()函數(shù)會將多個數(shù)組串聯(lián)mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]'); +------------------------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]') | +------------------------------------------------------------------+ | [1, 2, "a", "b", "true", "false"] | +------------------------------------------------------------------+ -
JSON_MERGE_PATCH()函數(shù)僅保留傳入的最后一個數(shù)組mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]'); +---------------------------------------------------------------+ | JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]') | +---------------------------------------------------------------+ | ["true", "false"] | +---------------------------------------------------------------+
合并JSON對象
-
JSON_MERGE_PRESERVE()函數(shù)會將重復鍵對應的所有值組合成一個數(shù)組mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}'); +--------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') | +--------------------------------------------------------------+ | {"a": [1, 10], "b": 2, "c": 3} | +--------------------------------------------------------------+ -
JSON_MERGE_PATCH()函數(shù)僅保留最后一個重復鍵對應的值mysql> SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}'); +-----------------------------------------------------------+ | JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') | +-----------------------------------------------------------+ | {"a": 10, "b": 2, "c": 3} | +-----------------------------------------------------------+
合并非JSON數(shù)組或?qū)ο蟮脑?/h4>
當待合并的元素既非JSON數(shù)組也非JSON對象時胚委，會將傳入的元素自動包裝為長度為1的JSON數(shù)組，并按照合并數(shù)組的規(guī)則合并
mysql> SELECT JSON_MERGE_PRESERVE('1', '2');
+-------------------------------+
| JSON_MERGE_PRESERVE('1', '2') |
+-------------------------------+
| [1, 2] |
+-------------------------------+
mysql> SELECT JSON_MERGE_PATCH('1', '2');
+----------------------------+
| JSON_MERGE_PATCH('1', '2') |
+----------------------------+
| 2 |
+----------------------------+
將JSON數(shù)組和JSON對象合并到一起
當待合并的元素既有JSON數(shù)組也有JSON對象時叉信，會將JSON對象自動包裝成數(shù)組亩冬，并按照合并數(shù)組的規(guī)則合并
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}');
+---------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}') |
+---------------------------------------------------+
| [1, 2, {"a": 1, "b": 2}] |
+---------------------------------------------------+
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}') |
+------------------------------------------------+
| {"a": 1, "b": 2} |
+------------------------------------------------+
在JSON中查找和修改元素
在JSON中查找和修改元素的語法為：$，該符號后跟隨需要查找的鍵名或索引。
查找元素
JSON_EXTRACT()函數(shù)用于從JSON中提取元素硅急，例如：
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.a');
+-----------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2}', '$.a') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[2]');
+-----------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[2]') |
+-----------------------------------+
| 3 |
+-----------------------------------+
修改元素
-
JSON_SET()函數(shù)用于修改JSON中對應的元素覆享，例如：mysql> SELECT JSON_SET('{"a": 1, "b": 2}', '$.a', 3); +----------------------------------------+ | JSON_SET('{"a": 1, "b": 2}', '$.a', 3) | +----------------------------------------+ | {"a": 3, "b": 2} | +----------------------------------------+ mysql> SELECT JSON_SET('[1, 2, 3]', '$[2]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[2]', 4) | +----------------------------------+ | [1, 2, 4] | +----------------------------------+當路徑對應的元素不存在時，會添加新的元素
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2); +--------------------------------------------+ | JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +--------------------------------------------+ | ["a", {"b": [1, false]}, [10, 20, 2]] | +--------------------------------------------+ -
JSON_INSERT()函數(shù)會給JSON添加新的元素营袜，但是不會更改原有元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2); +-----------------------------------------------+ | JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +-----------------------------------------------+ | ["a", {"b": [true, false]}, [10, 20, 2]] | +-----------------------------------------------+ -
JSON_REPLACE()函數(shù)會修改現(xiàn)有元素撒顿，但不會添加新元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2); +------------------------------------------------+ | JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +------------------------------------------------+ | ["a", {"b": [1, false]}, [10, 20]] | +------------------------------------------------+ -
JSON_REMOVE()函數(shù)會刪除路徑對應的元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]'); +-----------------------------------------+ | JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]') | +-----------------------------------------+ | ["a", {"b": [false]}, [10, 20]] | +-----------------------------------------+
路徑語法
-
當鍵名中包含空格時需要用雙引號包含鍵名，即
mysql> SELECT JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"'); +------------------------------------------------------------------------+ | JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"') | +------------------------------------------------------------------------+ | "shark" | +------------------------------------------------------------------------+ -
當查找不到路徑中的元素時荚板，返回null
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[3]'); +-----------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[3]') | +-----------------------------------+ | NULL | +-----------------------------------+ -
可以使用
'$[M to N]'的形式提取出某一范圍內(nèi)的元素凤壁，last關(guān)鍵字指代數(shù)組中最后一個元素（但不能用于修改元素）
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[1 to last]'); +-------------------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[1 to last]') | +-------------------------------------------+ | [2, 3] | +-------------------------------------------+ -
可以使用
*和**通配符來提取元素（但不能用于修改元素）-
.*表示提取JSON對象中所有鍵的值，并返回一個數(shù)組mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.*'); +-----------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2}', '$.*') | +-----------------------------------------+ | [1, 2] | +-----------------------------------------+ -
[*]表示提取JSON數(shù)組中所有元素mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[*]'); +-----------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[*]') | +-----------------------------------+ | [1, 2, 3] | +-----------------------------------+ -
prefix**suffix表示提取路徑以prefix開始跪另、以suffix結(jié)束的元素拧抖，其中prefix是可選的，但suffix是必須的mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b'); +-------------------------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b') | +-------------------------------------------------------------------------+ | [1, 3] | +-------------------------------------------------------------------------+ mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b'); +---------------------------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b') | +---------------------------------------------------------------------------+ | [1] | +---------------------------------------------------------------------------+
-
JSON值的比較和排序
JSON值的比較
JSON值使用<免绿、>唧席、=、<=针姿、>=袱吆、<>、<=>距淫、!=操作符進行比較。比較時會將JSON轉(zhuǎn)換為MySQL的原生數(shù)值類型或字符串來比較婶希。
JSON在比較時分為兩步：
-
比較JSON類型（即
JSON_TYPE()的返回值）榕暇，若類型不同則按照類型的優(yōu)先級順序得出比較結(jié)果，優(yōu)先級越高則越大喻杈，若類型相同則進行第二步彤枢；JSON類型的優(yōu)先級如下：
BLOB<BIT<OPAQUE<DATETIME<TIME<DATE<BOOLEAN<ARRAY<OBJECT<STRING<INTEGER=DOUBLE<NULL -
根據(jù)各類型具體的比較規(guī)則比較
-
BLOB、BIT筒饰、OPAQUE缴啡、STRING：先比較兩個值長度相同的部分，如果都相同瓷们，則長度較短的值排在長度較長的值之前业栅。對STRING的比較是基于utf8mb4編碼格式的。"a" < "ab" < "b" < "bc" "A" < "a" DATATIME谬晕、TIME碘裕、DATE：表示較早時間點的值排在表示較晚時間點的值之前。表示相同時間點的DATATIME值和TIMESTAMP值相等-
ARRAY：兩JSON數(shù)組長度相同且對應元素相等時兩數(shù)組相等攒钳。長度不同時帮孔，對應位置的元素的值較小的數(shù)組排在前面；對應元素都相同時不撑，較短的數(shù)組排在前面[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"] BOOLEAN：false值排在true值之前-
OBJECT：當兩個JSON對象具有相同的鍵文兢，且各鍵對應的值也相等時晤斩，兩個JSON對象相等。不相等的JSON對象的的排序不定{"a": 1, "b": 2} = {"b": 2, "a": 1} INTEGER姆坚、DOUBLE：當兩個比較對象一個是INT型澳泵、一個是DOUBLE型時，INT型會被轉(zhuǎn)換為DOUBLE型旷偿；但當兩個比較對象無法預先判斷是INT型還是DOUBLE型時烹俗，會轉(zhuǎn)換為INT型比較JSON值和SQL
NULL比較時，比較結(jié)果未知JSON值和非JSON值比較時萍程，非JSON值會被轉(zhuǎn)換為JSON值
-
JSON值的排序
使用ORDER BY和GROUP BY對JSON值排序時遵循以下規(guī)則：
- 按照前述比較規(guī)則排序
- 升序時幢妄，SQL
NULL排在所有JSON值之前（包括JSONnull）；降序時茫负，SQLNULL排在所有JSON值之后
推薦將JSON值轉(zhuǎn)換為MySQL基本類型再進行排序蕉鸳。
