一文說(shuō)透 MySQL JSON 數(shù)據(jù)類(lèi)型（收藏）
JSON 數(shù)據(jù)類(lèi)型是 MySQL 5.7.8 開(kāi)始支持的。在此之前，只能通過(guò)字符類(lèi)型（CHAR，VARCHAR 或 TEXT ）來(lái)保存 JSON 文檔。
相對(duì)字符類(lèi)型，原生的 JSON 類(lèi)型具有以下優(yōu)勢(shì)：
在插入時(shí)能自動(dòng)校驗(yàn)文檔是否滿(mǎn)足 JSON 格式的要求。 優(yōu)化了存儲(chǔ)格式。無(wú)需讀取整個(gè)文檔就能快速訪問(wèn)某個(gè)元素的值。
在 JSON 類(lèi)型引入之前，如果我們想要獲取 JSON 文檔中的某個(gè)元素，必須首先讀取整個(gè) JSON 文檔，然后在客戶(hù)端將其轉(zhuǎn)換為 JSON 對(duì)象，最后再通過(guò)對(duì)象獲取指定元素的值。
下面是 Python 中的獲取方式。
import?json
#?JSON?字符串:
x?=??'{?"name":"John",?"age":30,?"city":"New?York"}'
#?將?JSON?字符串轉(zhuǎn)換為?JSON?對(duì)象:
y?=?json.loads(x)
#?讀取?JSON?對(duì)象中指定元素的值:
print(y["age"])
這種方式有兩個(gè)弊端：一、消耗磁盤(pán) IO，二、消耗網(wǎng)絡(luò)帶寬，如果 JSON 文檔比較大，在高并發(fā)場(chǎng)景，有可能會(huì)打爆網(wǎng)卡。
如果使用的是 JSON 類(lèi)型，相同的需求，直接使用 SQL 命令就可搞定。不僅能節(jié)省網(wǎng)絡(luò)帶寬，結(jié)合后面提到的函數(shù)索引，還能降低磁盤(pán) IO 消耗。
mysql>?create?table?t(c1?json);
Query?OK,?0?rows?affected?(0.09?sec)
mysql>?insert?into?t?values('{?"name":"John",?"age":30,?"city":"New?York"}');
Query?OK,?1?row?affected?(0.01?sec)
mysql>?select?c1->"$.age"?from?t;
+-------------+
|?c1->"$.age"?|
+-------------+
|?30??????????|
+-------------+
1?row?in?set?(0.00?sec)
本文將從以下幾個(gè)方面展開(kāi)：
什么是 JSON。 JSON 字段的增刪改查操作。 如何對(duì) JSON 字段創(chuàng)建索引。 如何將存儲(chǔ) JSON 字符串的字符字段升級(jí)為 JSON 字段。 使用 JSON 時(shí)的注意事項(xiàng)。 Partial Updates。 其它 JSON 函數(shù)。
一、什么是 JSON
JSON 是 JavaScript Object Notation（JavaScript 對(duì)象表示法）的縮寫(xiě)，是一個(gè)輕量級(jí)的，基于文本的，跨語(yǔ)言的數(shù)據(jù)交換格式。易于閱讀和編寫(xiě)。
JSON 的基本數(shù)據(jù)類(lèi)型如下：
數(shù)值：十進(jìn)制數(shù)，不能有前導(dǎo) 0，可以為負(fù)數(shù)或小數(shù)，還可以為 e 或 E 表示的指數(shù)。
字符串：字符串必須用雙引號(hào)括起來(lái)。
布爾值：true，false。
數(shù)組：一個(gè)由零或多個(gè)值組成的有序序列。每個(gè)值可以為任意類(lèi)型。數(shù)組使用方括號(hào)
[]括起來(lái)，元素之間用逗號(hào),分隔。譬如，[1,?"abc",?null,?true,?"10:27:06.000000",?{"id":?1}]對(duì)象：一個(gè)由零或者多個(gè)鍵值對(duì)組成的無(wú)序集合。其中鍵必須是字符串，值可以為任意類(lèi)型。
對(duì)象使用花括號(hào)
{}括起來(lái)，鍵值對(duì)之間使用逗號(hào),分隔，鍵與值之間用冒號(hào):分隔。譬如，{"db":?["mysql",?"oracle"],?"id":?123,?"info":?{"age":?20}}空值：null。
二、JSON 字段的增刪改查操作
下面我們看看 JSON 字段常見(jiàn)的增刪改查操作：
2.1 插入操作
可直接插入 JSON 格式的字符串。
mysql>?create?table?t(c1?json);
Query?OK,?0?rows?affected?(0.03?sec)
mysql>?insert?into?t?values('[1,?"abc",?null,?true,?"08:45:06.000000"]');
Query?OK,?1?row?affected?(0.01?sec)
mysql>?insert?into?t?values('{"id":?87,?"name":?"carrot"}');
Query?OK,?1?row?affected?(0.01?sec)
也可使用函數(shù)，常用的有 JSON_ARRAY() 和 JSON_OBJECT()，前者用于構(gòu)造 JSON 數(shù)組，后者用于構(gòu)造 JSON 對(duì)象。如，
mysql>?select?json_array(1,?"abc",?null,?true,curtime());
+--------------------------------------------+
|?json_array(1,?"abc",?null,?true,curtime())?|
+--------------------------------------------+
|?[1,?"abc",?null,?true,?"10:12:25.000000"]??|
+--------------------------------------------+
1?row?in?set?(0.01?sec)
mysql>?select?json_object('id',?87,?'name',?'carrot');
+-----------------------------------------+
|?json_object('id',?87,?'name',?'carrot')?|
+-----------------------------------------+
|?{"id":?87,?"name":?"carrot"}????????????|
+-----------------------------------------+
1?row?in?set?(0.00?sec)
對(duì)于 JSON 文檔，KEY 名不能重復(fù)。
如果插入的值中存在重復(fù) KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原則，會(huì)保留第一個(gè) KEY，后面的將被丟棄掉。
從 MySQL 8.0.3 開(kāi)始，遵循的是 last duplicate key wins 原則，只會(huì)保留最后一個(gè) KEY。
下面通過(guò)一個(gè)具體的示例來(lái)看看兩者的區(qū)別。
MySQL 5.7.36
mysql>?select?json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
|?json_object('key1',10,'key2',20,'key1',30)?|
+--------------------------------------------+
|?{"key1":?10,?"key2":?20}???????????????????|
+--------------------------------------------+
1?row?in?set?(0.02?sec)
MySQL 8.0.27
mysql>?select?json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
|?json_object('key1',10,'key2',20,'key1',30)?|
+--------------------------------------------+
|?{"key1":?30,?"key2":?20}???????????????????|
+--------------------------------------------+
1?row?in?set?(0.00?sec)
2.2 查詢(xún)操作
JSON_EXTRACT(json_doc, path[, path] ...)
其中，json_doc 是 JSON 文檔，path 是路徑。該函數(shù)會(huì)從 JSON 文檔提取指定路徑（path）的元素。如果指定 path 不存在，會(huì)返回 NULL。可指定多個(gè) path，匹配到的多個(gè)值會(huì)以數(shù)組形式返回。
下面我們結(jié)合一些具體的示例來(lái)看看 path 及 JSON_EXTRACT 的用法。
首先我們看看數(shù)組。
數(shù)組的路徑是通過(guò)下標(biāo)來(lái)表示的。第一個(gè)元素的下標(biāo)是 0。
mysql>?select?json_extract('[10,?20,?[30,?40]]',?'$[0]');
+--------------------------------------------+
|?json_extract('[10,?20,?[30,?40]]',?'$[0]')?|
+--------------------------------------------+
|?10?????????????????????????????????????????|
+--------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_extract('[10,?20,?[30,?40]]',?'$[0]',?'$[1]','$[2][0]');
+--------------------------------------------------------------+
|?json_extract('[10,?20,?[30,?40]]',?'$[0]',?'$[1]','$[2][0]')?|
+--------------------------------------------------------------+
|?[10,?20,?30]?????????????????????????????????????????????????|
+--------------------------------------------------------------+
1?row?in?set?(0.00?sec)
除此之外，還可通過(guò) [M to N] 獲取數(shù)組的子集。
mysql>?select?json_extract('[10,?20,?[30,?40]]',?'$[0?to?1]');
+-------------------------------------------------+
|?json_extract('[10,?20,?[30,?40]]',?'$[0?to?1]')?|
+-------------------------------------------------+
|?[10,?20]????????????????????????????????????????|
+-------------------------------------------------+
1?row?in?set?(0.00?sec)
#?這里的?last?代表最后一個(gè)元素的下標(biāo)
mysql>?select?json_extract('[10,?20,?[30,?40]]',?'$[last-1?to?last]');
+---------------------------------------------------------+
|?json_extract('[10,?20,?[30,?40]]',?'$[last-1?to?last]')?|
+---------------------------------------------------------+
|?[20,?[30,?40]]??????????????????????????????????????????|
+---------------------------------------------------------+
1?row?in?set?(0.00?sec)
也可通過(guò) [*] 獲取數(shù)組中的所有元素。
mysql>?select?json_extract('[10,?20,?[30,?40]]',?'$[*]');
+--------------------------------------------+
|?json_extract('[10,?20,?[30,?40]]',?'$[*]')?|
+--------------------------------------------+
|?[10,?20,?[30,?40]]?????????????????????????|
+--------------------------------------------+
1?row?in?set?(0.00?sec)
接下來(lái)，我們看看對(duì)象。
對(duì)象的路徑是通過(guò) KEY 來(lái)表示的。
mysql>?set?@j='{"a":?1,?"b":?[2,?3],?"a?c":?4}';
Query?OK,?0?rows?affected?(0.00?sec)
#?如果 KEY 在路徑表達(dá)式中不合法（譬如存在空格），則在引用這個(gè) KEY 時(shí)，需用雙引號(hào)括起來(lái)。
mysql>?select?json_extract(@j,?'$.a'),?json_extract(@j,?'$."a?c"'),?json_extract(@j,?'$.b[1]');
+-------------------------+-----------------------------+----------------------------+
|?json_extract(@j,?'$.a')?|?json_extract(@j,?'$."a?c"')?|?json_extract(@j,?'$.b[1]')?|
+-------------------------+-----------------------------+----------------------------+
|?1???????????????????????|?4???????????????????????????|?3??????????????????????????|
+-------------------------+-----------------------------+----------------------------+
1?row?in?set?(0.00?sec)
除此之外，還可通過(guò) .* 獲取對(duì)象中的所有元素。
mysql>?select?json_extract('{"a":?1,?"b":?[2,?3],?"a?c":?4}',?'$.*');
+--------------------------------------------------------+
|?json_extract('{"a":?1,?"b":?[2,?3],?"a?c":?4}',?'$.*')?|
+--------------------------------------------------------+
|?[1,?[2,?3],?4]?????????????????????????????????????????|
+--------------------------------------------------------+
1?row?in?set?(0.00?sec)
#?這里的?$**.b?匹配?$.a.b?和?$.c.b
mysql>?select?json_extract('{"a":?{"b":?1},?"c":?{"b":?2}}',?'$**.b');
+---------------------------------------------------------+
|?json_extract('{"a":?{"b":?1},?"c":?{"b":?2}}',?'$**.b')?|
+---------------------------------------------------------+
|?[1,?2]??????????????????????????????????????????????????|
+---------------------------------------------------------+
1?row?in?set?(0.00?sec)
column->path
column->path，包括后面講到的 column->>path，都是語(yǔ)法糖，在實(shí)際使用的時(shí)候都會(huì)轉(zhuǎn)化為 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一個(gè)path。
create?table?t(c2?json);
insert?into?t?values('{"empno":?1001,?"ename":?"jack"}'),?('{"empno":?1002,?"ename":?"mark"}');
mysql>?select?c2,?c2->"$.ename"?from?t;
+----------------------------------+---------------+
|?c2???????????????????????????????|?c2->"$.ename"?|
+----------------------------------+---------------+
|?{"empno":?1001,?"ename":?"jack"}?|?"jack"????????|
|?{"empno":?1002,?"ename":?"mark"}?|?"mark"????????|
+----------------------------------+---------------+
2?rows?in?set?(0.00?sec)
mysql>?select?*?from?t?where?c2->"$.empno"?=?1001;
+------+----------------------------------+
|?c1???|?c2???????????????????????????????|
+------+----------------------------------+
|????1?|?{"empno":?1001,?"ename":?"jack"}?|
+------+----------------------------------+
1?row?in?set?(0.00?sec)
column->>path
同 column->path 類(lèi)似，只不過(guò)其返回的是字符串。以下三者是等價(jià)的。
JSON_UNQUOTE( JSON_EXTRACT(column, path) ) JSON_UNQUOTE(column -> path) column->>path
mysql>?select?c2->'$.ename',json_extract(c2,?"$.ename"),json_unquote(c2->'$.ename'),c2->>'$.ename'?from?t;
+---------------+-----------------------------+-----------------------------+----------------+
|?c2->'$.ename'?|?json_extract(c2,?"$.ename")?|?json_unquote(c2->'$.ename')?|?c2->>'$.ename'?|
+---------------+-----------------------------+-----------------------------+----------------+
|?"jack"????????|?"jack"??????????????????????|?jack????????????????????????|?jack???????????|
|?"mark"????????|?"mark"??????????????????????|?mark????????????????????????|?mark???????????|
+---------------+-----------------------------+-----------------------------+----------------+
2?rows?in?set?(0.00?sec)
2.3 修改操作
JSON_INSERT(json_doc, path, val[, path, val] ...)
插入新值。
僅當(dāng)指定位置或指定 KEY 的值不存在時(shí)，才執(zhí)行插入操作。另外，如果指定的 path 是數(shù)組下標(biāo)，且 json_doc 不是數(shù)組，該函數(shù)首先會(huì)將 json_doc 轉(zhuǎn)化為數(shù)組，然后再插入新值。
下面我們看幾個(gè)示例。
mysql>?select?json_insert('1','$[0]',"10");
+------------------------------+
|?json_insert('1','$[0]',"10")?|
+------------------------------+
|?1????????????????????????????|
+------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_insert('1','$[1]',"10");
+------------------------------+
|?json_insert('1','$[1]',"10")?|
+------------------------------+
|?[1,?"10"]????????????????????|
+------------------------------+
1?row?in?set?(0.01?sec)
mysql>?select?json_insert('["1","2"]','$[2]',"10");
+--------------------------------------+
|?json_insert('["1","2"]','$[2]',"10")?|
+--------------------------------------+
|?["1",?"2",?"10"]?????????????????????|
+--------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?set?@j?=?'{?"a":?1,?"b":?[2,?3]}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_insert(@j,?'$.a',?10,?'$.c',?'[true,?false]');
+----------------------------------------------------+
|?json_insert(@j,?'$.a',?10,?'$.c',?'[true,?false]')?|
+----------------------------------------------------+
|?{"a":?1,?"b":?[2,?3],?"c":?"[true,?false]"}????????|
+----------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_SET(json_doc, path, val[, path, val] ...)
插入新值，并替換已經(jīng)存在的值。
換言之，如果指定位置或指定 KEY 的值不存在，會(huì)執(zhí)行插入操作，如果存在，則執(zhí)行更新操作。
mysql>?set?@j?=?'{?"a":?1,?"b":?[2,?3]}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_set(@j,?'$.a',?10,?'$.c',?'[true,?false]');
+-------------------------------------------------+
|?json_set(@j,?'$.a',?10,?'$.c',?'[true,?false]')?|
+-------------------------------------------------+
|?{"a":?10,?"b":?[2,?3],?"c":?"[true,?false]"}????|
+-------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_REPLACE(json_doc, path, val[, path, val] ...)
替換已經(jīng)存在的值。
mysql>?set?@j?=?'{?"a":?1,?"b":?[2,?3]}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_replace(@j,?'$.a',?10,?'$.c',?'[true,?false]');
+-----------------------------------------------------+
|?json_replace(@j,?'$.a',?10,?'$.c',?'[true,?false]')?|
+-----------------------------------------------------+
|?{"a":?10,?"b":?[2,?3]}??????????????????????????????|
+-----------------------------------------------------+
1?row?in?set?(0.00?sec)
2.4 刪除操作
JSON_REMOVE(json_doc, path[, path] ...)
刪除 JSON 文檔指定位置的元素。
mysql>?set?@j?=?'{?"a":?1,?"b":?[2,?3]}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_remove(@j,?'$.a');
+------------------------+
|?JSON_REMOVE(@j,?'$.a')?|
+------------------------+
|?{"b":?[2,?3]}??????????|
+------------------------+
1?row?in?set?(0.00?sec)
mysql>?set?@j?=?'["a",?["b",?"c"],?"d",?"e"]';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_remove(@j,?'$[1]');
+-------------------------+
|?JSON_REMOVE(@j,?'$[1]')?|
+-------------------------+
|?["a",?"d",?"e"]?????????|
+-------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_remove(@j,?'$[1]','$[2]');
+--------------------------------+
|?JSON_REMOVE(@j,?'$[1]','$[2]')?|
+--------------------------------+
|?["a",?"d"]?????????????????????|
+--------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_remove(@j,?'$[1]','$[1]');
+--------------------------------+
|?JSON_REMOVE(@j,?'$[1]','$[1]')?|
+--------------------------------+
|?["a",?"e"]?????????????????????|
+--------------------------------+
1?row?in?set?(0.00?sec)
最后一個(gè)查詢(xún)，雖然兩個(gè) path 都是 '$[1]' ，但作用對(duì)象不一樣，第一個(gè) path 的作用對(duì)象是 '["a", ["b", "c"], "d", "e"]' ，第二個(gè) path 的作用對(duì)象是刪除了 '$[1]' 后的數(shù)組，即 '["a", "d", "e"]' 。
三、如何對(duì) JSON 字段創(chuàng)建索引
同 TEXT，BLOB 字段一樣，JSON 字段不允許直接創(chuàng)建索引。
mysql>?create?table?t(c1?json,?index?(c1));
ERROR?3152?(42000):?JSON?column?'c1'?supports?indexing?only?via?generated?columns?on?a?specified?JSON?path.
即使支持，實(shí)際意義也不大，因?yàn)槲覀円话闶腔谖臋n中的元素進(jìn)行查詢(xún)，很少會(huì)基于整個(gè) ?JSON 文檔。
對(duì)文檔中的元素進(jìn)行查詢(xún)，就需要用到 MySQL 5.7 引入的虛擬列及函數(shù)索引。
下面我們來(lái)看一個(gè)具體的示例。
#?C2?即虛擬列
# index (c2)?對(duì)虛擬列添加索引。
create?table?t?(?c1?json,?c2?varchar(10)?as?(JSON_UNQUOTE(c1?->?"$.name")),?index?(c2)?);
insert?into?t?(c1)?values??('{"id":?1,?"name":?"a"}'),?('{"id":?2,?"name":?"b"}'),?('{"id":?3,?"name":?"c"}'),?('{"id":?4,?"name":?"d"}');
mysql>?explain?select?*?from?t?where?c2?=?'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref???|?rows?|?filtered?|?Extra?|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|??1?|?SIMPLE??????|?t?????|?NULL???????|?ref??|?c2????????????|?c2???|?43??????|?const?|????1?|???100.00?|?NULL??|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)
mysql>?explain?select?*?from?t?where?c1->'$.name'?=?'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref???|?rows?|?filtered?|?Extra?|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|??1?|?SIMPLE??????|?t?????|?NULL???????|?ref??|?c2????????????|?c2???|?43??????|?const?|????1?|???100.00?|?NULL??|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1?row?in?set,?1?warning?(0.00?sec)
可以看到，無(wú)論是使用虛擬列，還是文檔中的元素來(lái)查詢(xún)，都可以利用上索引。
注意，在創(chuàng)建虛擬列時(shí)需指定 ?JSON_UNQUOTE，將 c1 -> "$.name" 的返回值轉(zhuǎn)換為字符串。
四、如何將存儲(chǔ) JSON 字符串的字符字段升級(jí)為 JSON 字段
在 MySQL 支持 JSON 類(lèi)型之前，對(duì)于 JSON 文檔，一般是以字符串的形式存儲(chǔ)在字符類(lèi)型（VARCHAR 或 TEXT）中。
在 JSON 類(lèi)型出來(lái)之后，如何將這些字符字段升級(jí)為 JSON 字段呢？
為方便演示，這里首先構(gòu)建測(cè)試數(shù)據(jù)。
create?table?t?(id?int?auto_increment?primary?key,?c1?text);
insert?into?t?(c1)?values?('{"id":?"1",?"name":?"a"}'),?('{"id":?"2",?"name":?"b"}'),?('{"id":?"3",?"name":?"c"}'),?('{"id",?"name":?"d"}');
注意，最后一個(gè)文檔有問(wèn)題，不是合格的 JSON 文檔。
如果使用 DDL 直接修改字段的數(shù)據(jù)類(lèi)型，會(huì)報(bào)錯(cuò)。
mysql>?alter?table?t?modify?c1?json;
ERROR?3140?(22032):?Invalid?JSON?text:?"Missing?a?colon?after?a?name?of?object?member."?at?position?5?in?value?for?column?'#sql-7e1c_1f6.c1'.
下面，我們看看具體的升級(jí)步驟。
（1）使用 json_valid 函數(shù)找出不滿(mǎn)足 JSON 格式要求的文檔。
mysql>?select?*?from?t?where?json_valid(c1)?=?0;
+----+---------------------+
|?id?|?c1??????????????????|
+----+---------------------+
|??4?|?{"id",?"name":?"d"}?|
+----+---------------------+
1?row?in?set?(0.00?sec)
（2）處理不滿(mǎn)足 JSON 格式要求的文檔。
mysql>?update?t?set?c1='{"id":?"4",?"name":?"d"}'?where?id=4;
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
（3）將 TEXT 字段修改為 JSON 字段。
mysql>?select?*?from?t?where?json_valid(c1)?=?0;
Empty?set?(0.00?sec)
mysql>?alter?table?t?modify?c1?json;
Query?OK,?4?rows?affected?(0.13?sec)
Records:?4??Duplicates:?0??Warnings:?0
五、使用 JSON 時(shí)的注意事項(xiàng)
對(duì)于 JSON 類(lèi)型，有以下幾點(diǎn)需要注意：
在 MySQL 8.0.13 之前，不允許對(duì) BLOB，TEXT，GEOMETRY，JSON 字段設(shè)置默認(rèn)值。從 MySQL 8.0.13 開(kāi)始，取消了這個(gè)限制。
設(shè)置時(shí)，注意默認(rèn)值需通過(guò)小括號(hào)
()括起來(lái)，否則的話(huà)，還是會(huì)提示 JSON 字段不允許設(shè)置默認(rèn)值。mysql>?create?table?t(c1?json?not?null?default?(''));
Query?OK,?0?rows?affected?(0.03?sec)
mysql>?create?table?t(c1?json?not?null?default?'');
ERROR?1101?(42000):?BLOB,?TEXT,?GEOMETRY?or?JSON?column?'c1'?can't?have?a?default?value不允許直接創(chuàng)建索引，可創(chuàng)建函數(shù)索引。
JSON 列的最大大小和 LONGBLOB（LONGTEXT）一樣，都是 4G。
插入時(shí)，單個(gè)文檔的大小受到 max_allowed_packet 的限制，該參數(shù)最大是 1G。
六、Partial Updates
在 MySQL 5.7 中，對(duì) JSON 文檔進(jìn)行更新，其處理策略是，刪除舊的文檔，再插入新的文檔。即使這個(gè)修改很微小，只涉及幾個(gè)字節(jié)，也會(huì)替換掉整個(gè)文檔。很顯然，這種處理方式的效率較為低下。
在 MySQL 8.0 中，針對(duì) JSON 文檔，引入了一項(xiàng)新的特性-Partial Updates（部分更新），支持 JSON 文檔的原地更新。得益于這個(gè)特性，JSON 文檔的處理性能得到了極大提升。
下面我們具體來(lái)看看。
6.1 使用 Partial Updates 的條件
為方便闡述，這里先構(gòu)造測(cè)試數(shù)據(jù)。
create?table?t?(id?int?auto_increment?primary?key,?c1?json);
insert?into?t?(c1)?values??('{"id":?1,?"name":?"a"}'),?('{"id":?2,?"name":?"b"}'),?('{"id":?3,?"name":?"c"}'),?('{"id":?4,?"name":?"d"}');
mysql>?select?*?from?t;
+----+------------------------+
|?id?|?c1?????????????????????|
+----+------------------------+
|??1?|?{"id":?1,?"name":?"a"}?|
|??2?|?{"id":?2,?"name":?"b"}?|
|??3?|?{"id":?3,?"name":?"c"}?|
|??4?|?{"id":?4,?"name":?"d"}?|
+----+------------------------+
4?rows?in?set?(0.00?sec)
使用 Partial Updates 需滿(mǎn)足以下條件：
被更新的列是 JSON 類(lèi)型。
使用 JSON_SET，JSON_REPLACE，JSON_REMOVE 進(jìn)行 UPDATE 操作，如，
update?t?set?c1=json_remove(c1,'$.id')?where?id=1;不使用這三個(gè)函數(shù)，而顯式賦值，就不會(huì)進(jìn)行部分更新，如，
update?t?set?c1='{"id":?1,?"name":?"a"}'?where?id=1;輸入列和目標(biāo)列必須是同一列，如，
update?t?set?c1=json_replace(c1,'$.id',10)?where?id=1;否則的話(huà)，就不會(huì)進(jìn)行部分更新，如，
update?t?set?c1=json_replace(c2,'$.id',10)?where?id=1;變更前后，JSON 文檔的空間使用不會(huì)增加。
關(guān)于最后一個(gè)條件，我們看看下面這個(gè)示例。
mysql>?select?*,json_storage_size(c1),json_storage_free(c1)?from?t?where?id=1;
+----+------------------------+-----------------------+-----------------------+
|?id?|?c1?????????????????????|?json_storage_size(c1)?|?json_storage_free(c1)?|
+----+------------------------+-----------------------+-----------------------+
|??1?|?{"id":?1,?"name":?"a"}?|????????????????????27?|?????????????????????0?|
+----+------------------------+-----------------------+-----------------------+
1?row?in?set?(0.00?sec)
mysql>?update?t?set?c1=json_remove(c1,'$.id')?where?id=1;
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
mysql>?select?*,json_storage_size(c1),json_storage_free(c1)?from?t?where?id=1;
+----+---------------+-----------------------+-----------------------+
|?id?|?c1????????????|?json_storage_size(c1)?|?json_storage_free(c1)?|
+----+---------------+-----------------------+-----------------------+
|??1?|?{"name":?"a"}?|????????????????????27?|?????????????????????9?|
+----+---------------+-----------------------+-----------------------+
1?row?in?set?(0.00?sec)
mysql>?update?t?set?c1=json_set(c1,'$.id',3306)?where?id=1;
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
mysql>?select?*,json_storage_size(c1),json_storage_free(c1)?from?t?where?id=1;
+----+---------------------------+-----------------------+-----------------------+
|?id?|?c1????????????????????????|?json_storage_size(c1)?|?json_storage_free(c1)?|
+----+---------------------------+-----------------------+-----------------------+
|??1?|?{"id":?3306,?"name":?"a"}?|????????????????????27?|?????????????????????0?|
+----+---------------------------+-----------------------+-----------------------+
1?row?in?set?(0.00?sec)
mysql>?update?t?set?c1=json_set(c1,'$.id','mysql')?where?id=1;
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
mysql>?select?*,json_storage_size(c1),json_storage_free(c1)?from?t?where?id=1;
+----+------------------------------+-----------------------+-----------------------+
|?id?|?c1???????????????????????????|?json_storage_size(c1)?|?json_storage_free(c1)?|
+----+------------------------------+-----------------------+-----------------------+
|??1?|?{"id":?"mysql",?"name":?"a"}?|????????????????????33?|?????????????????????0?|
+----+------------------------------+-----------------------+-----------------------+
1?row?in?set?(0.00?sec)
示例中，用到了兩個(gè)函數(shù)：JSON_STORAGE_SIZE 和 JSON_STORAGE_FREE ，前者用來(lái)獲取 JSON 文檔的空間使用情況，后者用來(lái)獲取 JSON 文檔在執(zhí)行原地更新后的空間釋放情況。
這里一共執(zhí)行了三次 UPDATE 操作，前兩次是原地更新，第三次不是。同樣是 JSON_SET 操作，為什么第一次是原地更新，而第二次不是呢？
因?yàn)榈谝淮蔚?JSON_SET 復(fù)用了 JSON_REMOVE 釋放的空間。而第二次的 JSON_SET 執(zhí)行的是更新操作，且 'mysql' 比 3306 需要更多的存儲(chǔ)空間。
6.2 如何在 binlog 中開(kāi)啟 Partial Updates
Partial Updates 不僅僅適用于存儲(chǔ)引擎層，還可用于主從復(fù)制場(chǎng)景。
主從復(fù)制開(kāi)啟 Partial Updates，只需將參數(shù) binlog_row_value_options（默認(rèn)為空）設(shè)置為 PARTIAL_JSON。
下面具體來(lái)看看，同一個(gè) UPDATE 操作，開(kāi)啟和不開(kāi)啟 Partial Updates，在 binlog 中的記錄有何區(qū)別。
update?t?set?c1=json_replace(c1,'$.id',10)?where?id=1;
不開(kāi)啟
###?UPDATE?`slowtech`.`t`
###?WHERE
###???@1=1
###???@2='{"id":?"1",?"name":?"a"}'
###?SET
###???@1=1
###???@2='{"id":?10,?"name":?"a"}'
開(kāi)啟
###?UPDATE?`slowtech`.`t`
###?WHERE
###???@1=1
###???@2='{"id":?1,?"name":?"a"}'
###?SET
###???@1=1
###???@2=JSON_REPLACE(@2,?'$.id',?10)
對(duì)比 binlog 的內(nèi)容，可以看到，不開(kāi)啟，無(wú)論是修改前的鏡像（before_image）還是修改后的鏡像（after_image），記錄的都是完整文檔。而開(kāi)啟后，對(duì)于修改后的鏡像，記錄的是命令，而不是完整文檔，這樣可節(jié)省近一半的空間。
在將 binlog_row_value_options 設(shè)置為 PARTIAL_JSON 后，對(duì)于可使用 Partial Updates 的操作，在 binlog 中，不再通過(guò) ROWS_EVENT 來(lái)記錄，而是新增了一個(gè) PARTIAL_UPDATE_ROWS_EVENT 的事件類(lèi)型。
需要注意的是，binlog 中使用 Partial Updates，只需滿(mǎn)足存儲(chǔ)引擎層使用 Partial Updates 的前三個(gè)條件，無(wú)需考慮變更前后，JSON 文檔的空間使用是否會(huì)增加。
6.3 關(guān)于 Partial Updates 的性能測(cè)試
首先構(gòu)造測(cè)試數(shù)據(jù)，t 表一共有 16 個(gè)文檔，每個(gè)文檔近 10 MB。
create?table?t(id?int?auto_increment?primary?key,
???????????????json_col?json,
???????????????name?varchar(100)?as?(json_col->>'$.name'),
???????????????age?int?as?(json_col->'$.age'));
insert?into?t(json_col)?values
(json_object('name',?'Joe',?'age',?24,
?????????????'data',?repeat('x',?10?*?1000?*?1000))),
(json_object('name',?'Sue',?'age',?32,
?????????????'data',?repeat('y',?10?*?1000?*?1000))),
(json_object('name',?'Pete',?'age',?40,
?????????????'data',?repeat('z',?10?*?1000?*?1000))),
(json_object('name',?'Jenny',?'age',?27,
?????????????'data',?repeat('w',?10?*?1000?*?1000)));
insert?into?t(json_col)?select?json_col?from?t;
insert?into?t(json_col)?select?json_col?from?t;
接下來(lái)，測(cè)試下述 SQL
update?t?set?json_col?=?json_set(json_col,?'$.age',?age?+?1);
在以下四種場(chǎng)景下的執(zhí)行時(shí)間：
MySQL 5.7.36 MySQL 8.0.27 MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL
分別執(zhí)行 10 次，去掉最大值和最小值后求平均值。
最后的測(cè)試結(jié)果如下：
以 MySQL 5.7.36 的查詢(xún)時(shí)間作為基準(zhǔn)：
MySQL 8.0 只開(kāi)啟存儲(chǔ)引擎層的 Partial Updates，查詢(xún)時(shí)間比 MySQL 5.7 快 1.94 倍。 MySQL 8.0 同時(shí)開(kāi)啟存儲(chǔ)引擎層和 binlog 中的 Partial Updates，查詢(xún)時(shí)間比 MySQL 5.7 快 4.87 倍。 如果在 2 的基礎(chǔ)上，同時(shí)將 binlog_row_image 設(shè)置為 MINIMAL，查詢(xún)時(shí)間更是比 MySQL 5.7 快 102.22 倍。
當(dāng)然，在生產(chǎn)環(huán)境，我們一般很少將 binlog_row_image 設(shè)置為 MINIMAL。
但即使如此，只開(kāi)啟存儲(chǔ)引擎層和 binlog 中的 Partial Updates，查詢(xún)時(shí)間也比 MySQL 5.7 快 4.87 倍，性能提升還是比較明顯的。
七、其它 JSON 函數(shù)
7.1 查詢(xún)相關(guān)
JSON_CONTAINS(target, candidate[, path])
判斷 target 文檔是否包含 candidate 文檔，如果包含，則返回 1，否則是 0。
mysql>?set?@j?=?'{"a":?[1,?2],?"b":?3,?"c":?{"d":?4}}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_contains(@j,?'1',?'$.a'),json_contains(@j,?'1',?'$.b');
+-------------------------------+-------------------------------+
|?json_contains(@j,?'1',?'$.a')?|?json_contains(@j,?'1',?'$.b')?|
+-------------------------------+-------------------------------+
|?????????????????????????????1?|?????????????????????????????0?|
+-------------------------------+-------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_contains(@j,'{"d":?4}','$.a'),json_contains(@j,'{"d":?4}','$.c');
+------------------------------------+------------------------------------+
|?json_contains(@j,'{"d":?4}','$.a')?|?json_contains(@j,'{"d":?4}','$.c')?|
+------------------------------------+------------------------------------+
|??????????????????????????????????0?|??????????????????????????????????1?|
+------------------------------------+------------------------------------+
1?row?in?set?(0.00?sec)
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
判斷指定的 path 是否存在，存在，則返回 1，否則是 0。
函數(shù)中的 one_or_all 可指定 one 或 all，one 是任意一個(gè)路徑存在就返回 1，all 是所有路徑都存在才返回 1。
mysql>?set?@j?=?'{"a":?[1,?2],?"b":?3,?"c":?{"d":?4}}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_contains_path(@j,?'one',?'$.a',?'$.e'),?json_contains_path(@j,?'all',?'$.a',?'$.e');
+---------------------------------------------+---------------------------------------------+
|?json_contains_path(@j,?'one',?'$.a',?'$.e')?|?json_contains_path(@j,?'all',?'$.a',?'$.e')?|
+---------------------------------------------+---------------------------------------------+
|???????????????????????????????????????????1?|???????????????????????????????????????????0?|
+---------------------------------------------+---------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_contains_path(@j,?'one',?'$.c.d'),json_contains_path(@j,?'one',?'$.a.d');
+----------------------------------------+----------------------------------------+
|?json_contains_path(@j,?'one',?'$.c.d')?|?json_contains_path(@j,?'one',?'$.a.d')?|
+----------------------------------------+----------------------------------------+
|??????????????????????????????????????1?|??????????????????????????????????????0?|
+----------------------------------------+----------------------------------------+
1?row?in?set?(0.00?sec)
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
返回某個(gè)字符串（search_str）在 JSON 文檔中的位置，其中，
one_or_all：匹配的次數(shù)，one 是只匹配一次，all 是匹配所有。如果匹配到多個(gè)，結(jié)果會(huì)以數(shù)組的形式返回。 search_str：子串，支持模糊匹配： %和_。escape_char：轉(zhuǎn)義符，如果該參數(shù)不填或?yàn)?NULL，則取默認(rèn)轉(zhuǎn)義符 \。path：查找路徑。
mysql>?set?@j?=?'["abc",?[{"k":?"10"},?"def"],?{"x":"abc"},?{"y":"bcd"}]';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_search(@j,?'one',?'abc'),json_search(@j,?'all',?'abc'),json_search(@j,?'all',?'ghi');
+-------------------------------+-------------------------------+-------------------------------+
|?json_search(@j,?'one',?'abc')?|?json_search(@j,?'all',?'abc')?|?json_search(@j,?'all',?'ghi')?|
+-------------------------------+-------------------------------+-------------------------------+
|?"$[0]"????????????????????????|?["$[0]",?"$[2].x"]????????????|?NULL??????????????????????????|
+-------------------------------+-------------------------------+-------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_search(@j,?'all',?'%b%',?NULL,?'$[1]'),?json_search(@j,?'all',?'%b%',?NULL,?'$[3]');
+---------------------------------------------+---------------------------------------------+
|?json_search(@j,?'all',?'%b%',?NULL,?'$[1]')?|?json_search(@j,?'all',?'%b%',?NULL,?'$[3]')?|
+---------------------------------------------+---------------------------------------------+
|?NULL????????????????????????????????????????|?"$[3].y"????????????????????????????????????|
+---------------------------------------------+---------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_KEYS(json_doc[, path])
返回 JSON 文檔最外層的 key，如果指定了 path，則返回該 path 對(duì)應(yīng)元素最外層的 key。
mysql>?select?json_keys('{"a":?1,?"b":?{"c":?30}}');
+---------------------------------------+
|?json_keys('{"a":?1,?"b":?{"c":?30}}')?|
+---------------------------------------+
|?["a",?"b"]????????????????????????????|
+---------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_keys('{"a":?1,?"b":?{"c":?30}}',?'$.b');
+----------------------------------------------+
|?json_keys('{"a":?1,?"b":?{"c":?30}}',?'$.b')?|
+----------------------------------------------+
|?["c"]????????????????????????????????????????|
+----------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_VALUE(json_doc, path)
8.0.21 引入的，從 JSON 文檔提取指定路徑（path）的元素。
該函數(shù)的完整語(yǔ)法如下：
JSON_VALUE(json_doc,?path?[RETURNING?type]?[on_empty]?[on_error])
on_empty:
????{NULL?|?ERROR?|?DEFAULT?value}?ON?EMPTY
on_error:
????{NULL?|?ERROR?|?DEFAULT?value}?ON?ERROR
其中：
RETURNING type：返回值的類(lèi)型，不指定，則默認(rèn)是 VARCHAR(512)。不指定字符集，則默認(rèn)是 utf8mb4，且區(qū)分大小寫(xiě)。 on_empty：如果指定路徑?jīng)]有值，會(huì)觸發(fā) on_empty 子句， 默認(rèn)是返回 NULL，也可指定 ERROR 拋出錯(cuò)誤，或者通過(guò) DEFAULT value 返回默認(rèn)值。 on_error：三種情況下會(huì)觸發(fā) on_error 子句：從數(shù)組或?qū)ο笾刑崛≡貢r(shí)，會(huì)解析到多個(gè)值；類(lèi)型轉(zhuǎn)換錯(cuò)誤，譬如將 "abc" 轉(zhuǎn)換為 unsigned 類(lèi)型；值被 truncate 了。默認(rèn)是返回 NULL。
mysql>?select?json_value('{"item":?"shoes",?"price":?"49.95"}',?'$.item');
+-------------------------------------------------------------+
|?json_value('{"item":?"shoes",?"price":?"49.95"}',?'$.item')?|
+-------------------------------------------------------------+
|?shoes???????????????????????????????????????????????????????|
+-------------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_value('{"item":?"shoes",?"price":?"49.95"}',?'$.price'?returning?decimal(4,2))?as?price;
+-------+
|?price?|
+-------+
|?49.95?|
+-------+
1?row?in?set?(0.00?sec)
mysql>?select?json_value('{"item":?"shoes",?"price":?"49.95"}',?'$.price1'?error?on?empty);
ERROR?3966?(22035):?No?value?was?found?by?'json_value'?on?the?specified?path.
mysql>?select?json_value('[1,?2,?3]',?'$[1?to?2]'?error?on?error);
ERROR?3967?(22034):?More?than?one?value?was?found?by?'json_value'?on?the?specified?path.
mysql>?select?json_value('{"item":?"shoes",?"price":?"49.95"}',?'$.item'?returning?unsigned?error?on?error)?as?price;
ERROR?1690?(22003):?UNSIGNED?value?is?out?of?range?in?'json_value'
value MEMBER OF(json_array)
判斷 value 是否是 JSON 數(shù)組的一個(gè)元素，如果是，則返回 1，否則是 0。
mysql>?select?17?member?of('[23,?"abc",?17,?"ab",?10]');
+-------------------------------------------+
|?17?member?of('[23,?"abc",?17,?"ab",?10]')?|
+-------------------------------------------+
|?????????????????????????????????????????1?|
+-------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?cast('[4,5]'?as?json)?member?of('[[3,4],[4,5]]');
+--------------------------------------------------+
|?cast('[4,5]'?as?json)?member?of('[[3,4],[4,5]]')?|
+--------------------------------------------------+
|????????????????????????????????????????????????1?|
+--------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_OVERLAPS(json_doc1, json_doc2)
MySQL 8.0.17 引入的，用來(lái)比較兩個(gè) JSON 文檔是否有相同的鍵值對(duì)或數(shù)組元素，如果有，則返回 1，否則是 0。如果兩個(gè)參數(shù)都是標(biāo)量，則判斷這兩個(gè)標(biāo)量是否相等。
mysql>?select?json_overlaps('[1,3,5,7]',?'[2,5,7]'),json_overlaps('[1,3,5,7]',?'[2,6,8]');
+---------------------------------------+---------------------------------------+
|?json_overlaps('[1,3,5,7]',?'[2,5,7]')?|?json_overlaps('[1,3,5,7]',?'[2,6,8]')?|
+---------------------------------------+---------------------------------------+
|?????????????????????????????????????1?|?????????????????????????????????????0?|
+---------------------------------------+---------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_overlaps('{"a":1,"b":2}',?'{"c":3,"d":4,"b":2}');
+-------------------------------------------------------+
|?json_overlaps('{"a":1,"b":2}',?'{"c":3,"d":4,"b":2}')?|
+-------------------------------------------------------+
|?????????????????????????????????????????????????????1?|
+-------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_overlaps('{"a":1,"b":2}',?'{"c":3,"d":4,"b":10}');
+--------------------------------------------------------+
|?json_overlaps('{"a":1,"b":2}',?'{"c":3,"d":4,"b":10}')?|
+--------------------------------------------------------+
|??????????????????????????????????????????????????????0?|
+--------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_overlaps('5',?'5'),json_overlaps('5',?'6');
+-------------------------+-------------------------+
|?json_overlaps('5',?'5')?|?json_overlaps('5',?'6')?|
+-------------------------+-------------------------+
|???????????????????????1?|???????????????????????0?|
+-------------------------+-------------------------+
1?row?in?set?(0.00?sec)
從 MySQL 8.0.17 開(kāi)始，InnoDB 支持多值索引，可用在 JSON 數(shù)組中。當(dāng)我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進(jìn)行數(shù)組相關(guān)的操作時(shí)，可使用多值索引來(lái)加快查詢(xún)。
7.2 修改相關(guān)
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
向數(shù)組指定位置追加元素。如果指定 path 不存在，則不添加。
mysql>?set?@j?=?'["a",?["b",?"c"],?"d"]';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_array_append(@j,?'$[0]',?1,?'$[1][0]',?2,?'$[3]',?3);
+-----------------------------------------------------------+
|?json_array_append(@j,?'$[0]',?1,?'$[1][0]',?2,?'$[3]',?3)?|
+-----------------------------------------------------------+
|?[["a",?1],?[["b",?2],?"c"],?"d"]??????????????????????????|
+-----------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?set?@j?=?'{"a":?1,?"b":?[2,?3],?"c":?4}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_array_append(@j,?'$.b',?'x',?'$',?'z');
+---------------------------------------------+
|?json_array_append(@j,?'$.b',?'x',?'$',?'z')?|
+---------------------------------------------+
|?[{"a":?1,?"b":?[2,?3,?"x"],?"c":?4},?"z"]???|
+---------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
向數(shù)組指定位置插入元素。
mysql>?set?@j?=?'["a",?["b",?"c"],{"d":"e"}]';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_array_insert(@j,?'$[0]',?1);
+----------------------------------+
|?json_array_insert(@j,?'$[0]',?1)?|
+----------------------------------+
|?[1,?"a",?["b",?"c"],?{"d":?"e"}]?|
+----------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_array_insert(@j,?'$[1]',?cast('[1,2]'?as?json));
+------------------------------------------------------+
|?json_array_insert(@j,?'$[1]',?cast('[1,2]'?as?json))?|
+------------------------------------------------------+
|?["a",?[1,?2],?["b",?"c"],?{"d":?"e"}]????????????????|
+------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_array_insert(@j,?'$[5]',?2);
+----------------------------------+
|?json_array_insert(@j,?'$[5]',?2)?|
+----------------------------------+
|?["a",?["b",?"c"],?{"d":?"e"},?2]?|
+----------------------------------+
1?row?in?set?(0.00?sec)
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
MySQL 8.0.3 引入的，用來(lái)合并多個(gè) JSON 文檔。其合并規(guī)則如下：
如果兩個(gè)文檔不全是 JSON 對(duì)象，則合并后的結(jié)果是第二個(gè)文檔。 如果兩個(gè)文檔都是 JSON 對(duì)象，且不存在著同名 KEY，則合并后的文檔包括兩個(gè)文檔的所有元素，如果存在著同名 KEY，則第二個(gè)文檔的值會(huì)覆蓋第一個(gè)。
mysql>?select?json_merge_patch('[1,?2]',?'[3,?4]'),?json_merge_patch('[1,?2]',?'{"a":?123}');
+--------------------------------------+------------------------------------------+
|?json_merge_patch('[1,?2]',?'[3,?4]')?|?json_merge_patch('[1,?2]',?'{"a":?123}')?|
+--------------------------------------+------------------------------------------+
|?[3,?4]???????????????????????????????|?{"a":?123}???????????????????????????????|
+--------------------------------------+------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_merge_patch('{"a":?1}',?'{"b":?2}'),json_merge_patch('{?"a":?1,?"b":2?}','{?"a":?3,?"c":4?}');
+------------------------------------------+-----------------------------------------------------------+
|?json_merge_patch('{"a":?1}',?'{"b":?2}')?|?json_merge_patch('{?"a":?1,?"b":2?}','{?"a":?3,?"c":4?}')?|
+------------------------------------------+-----------------------------------------------------------+
|?{"a":?1,?"b":?2}?????????????????????????|?{"a":?3,?"b":?2,?"c":?4}??????????????????????????????????|
+------------------------------------------+-----------------------------------------------------------+
1?row?in?set?(0.00?sec)
#?如果第二個(gè)文檔存在 null 值，文檔合并后不會(huì)輸出對(duì)應(yīng)的 KEY。
mysql>?select?json_merge_patch('{"a":1,?"b":2}',?'{"a":3,?"b":null}');
+---------------------------------------------------------+
|?json_merge_patch('{"a":1,?"b":2}',?'{"a":3,?"b":null}')?|
+---------------------------------------------------------+
|?{"a":?3}????????????????????????????????????????????????|
+---------------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
MySQL 8.0.3 引入的，用來(lái)代替 JSON_MERGE。也是用來(lái)合并文檔，但合并規(guī)則與 JSON_MERGE_PATCH 有所不同。
兩個(gè)文檔中，只要有一個(gè)文檔是數(shù)組，則另外一個(gè)文檔會(huì)合并到該數(shù)組中。 兩個(gè)文檔都是 JSON 對(duì)象，若存在著同名 KEY ，第二個(gè)文檔并不會(huì)覆蓋第一個(gè)，而是會(huì)將值 append 到第一個(gè)文檔中。
mysql>?select?json_merge_preserve('1','2'),json_merge_preserve('[1,?2]',?'[3,?4]');
+------------------------------+-----------------------------------------+
|?json_merge_preserve('1','2')?|?json_merge_preserve('[1,?2]',?'[3,?4]')?|
+------------------------------+-----------------------------------------+
|?[1,?2]???????????????????????|?[1,?2,?3,?4]????????????????????????????|
+------------------------------+-----------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_merge_preserve('[1,?2]',?'{"a":?123}'),?json_merge_preserve('{"a":?123}',?'[3,4]');
+---------------------------------------------+--------------------------------------------+
|?json_merge_preserve('[1,?2]',?'{"a":?123}')?|?json_merge_preserve('{"a":?123}',?'[3,4]')?|
+---------------------------------------------+--------------------------------------------+
|?[1,?2,?{"a":?123}]??????????????????????????|?[{"a":?123},?3,?4]?????????????????????????|
+---------------------------------------------+--------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_merge_preserve('{"a":?1}',?'{"b":?2}'),?json_merge_preserve('{?"a":?1,?"b":2?}','{?"a":?3,?"c":4?}');
+---------------------------------------------+--------------------------------------------------------------+
|?json_merge_preserve('{"a":?1}',?'{"b":?2}')?|?json_merge_preserve('{?"a":?1,?"b":2?}','{?"a":?3,?"c":4?}')?|
+---------------------------------------------+--------------------------------------------------------------+
|?{"a":?1,?"b":?2}????????????????????????????|?{"a":?[1,?3],?"b":?2,?"c":?4}????????????????????????????????|
+---------------------------------------------+--------------------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
與 JSON_MERGE_PRESERVE 作用一樣，從 MySQL 8.0.3 開(kāi)始不建議使用，后續(xù)會(huì)移除。
7.3 其它輔助函數(shù)
JSON_QUOTE(string)
生成有效的 JSON 字符串，主要是對(duì)一些特殊字符（如雙引號(hào)）進(jìn)行轉(zhuǎn)義。
mysql>?select?json_quote('null'),?json_quote('"null"'),?json_quote('[1,?2,?3]');
+--------------------+----------------------+-------------------------+
|?json_quote('null')?|?json_quote('"null"')?|?json_quote('[1,?2,?3]')?|
+--------------------+----------------------+-------------------------+
|?"null"?????????????|?"\"null\""???????????|?"[1,?2,?3]"?????????????|
+--------------------+----------------------+-------------------------+
1?row?in?set?(0.00?sec)
除此之外，也可通過(guò) CAST(value AS JSON) 進(jìn)行類(lèi)型轉(zhuǎn)換。
JSON_UNQUOTE(json_val)
將 JSON 轉(zhuǎn)義成字符串輸出。
mysql>?select?c2->'$.ename',json_unquote(c2->'$.ename'),
????->?json_valid(c2->'$.ename'),json_valid(json_unquote(c2->'$.ename'))?from?t;
+---------------+-----------------------------+---------------------------+-----------------------------------------+
|?c2->'$.ename'?|?json_unquote(c2->'$.ename')?|?json_valid(c2->'$.ename')?|?json_valid(json_unquote(c2->'$.ename'))?|
+---------------+-----------------------------+---------------------------+-----------------------------------------+
|?"jack"????????|?jack????????????????????????|?????????????????????????1?|???????????????????????????????????????0?|
|?"mark"????????|?mark????????????????????????|?????????????????????????1?|???????????????????????????????????????0?|
+---------------+-----------------------------+---------------------------+-----------------------------------------+
2?rows?in?set?(0.00?sec)
直觀地看，沒(méi)加 JSON_UNQUOTE 字符串會(huì)用雙引號(hào)引起來(lái)，加了 JSON_UNQUOTE 就沒(méi)有。但本質(zhì)上，前者是 JSON 中的 STRING 類(lèi)型，后者是 MySQL 中的字符類(lèi)型，這一點(diǎn)可通過(guò) JSON_VALID 來(lái)判斷。
JSON_OBJECTAGG(key, value)
取表中的兩列作為參數(shù)，其中，第一列是 key，第二列是 value，返回 JSON 對(duì)象。如，
mysql>?select?*?from?emp;
+--------+----------+--------+
|?deptno?|?ename????|?sal????|
+--------+----------+--------+
|?????10?|?emp_1001?|?100.00?|
|?????10?|?emp_1002?|?200.00?|
|?????20?|?emp_1003?|?300.00?|
|?????20?|?emp_1004?|?400.00?|
+--------+----------+--------+
4?rows?in?set?(0.00?sec)
mysql>?select?json_objectagg(ename,sal)?from?emp;
+----------------------------------------------------------------------------------+
|?json_objectagg(ename,sal)????????????????????????????????????????????????????????|
+----------------------------------------------------------------------------------+
|?{"emp_1001":?100.00,?"emp_1002":?200.00,?"emp_1003":?300.00,?"emp_1004":?400.00}?|
+----------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?deptno,json_objectagg(ename,sal)?from?emp?group?by?deptno;
+--------+------------------------------------------+
|?deptno?|?json_objectagg(ename,sal)????????????????|
+--------+------------------------------------------+
|?????10?|?{"emp_1001":?100.00,?"emp_1002":?200.00}?|
|?????20?|?{"emp_1003":?300.00,?"emp_1004":?400.00}?|
+--------+------------------------------------------+
2?rows?in?set?(0.00?sec)
JSON_ARRAYAGG(col_or_expr)
將列的值聚合成 JSON 數(shù)組，注意，JSON 數(shù)組中元素的順序是隨機(jī)的。
mysql>?select?json_arrayagg(ename)?from?emp;
+--------------------------------------------------+
|?json_arrayagg(ename)?????????????????????????????|
+--------------------------------------------------+
|?["emp_1001",?"emp_1002",?"emp_1003",?"emp_1004"]?|
+--------------------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?deptno,json_arrayagg(ename)?from?emp?group?by?deptno;
+--------+--------------------------+
|?deptno?|?json_arrayagg(ename)?????|
+--------+--------------------------+
|?????10?|?["emp_1001",?"emp_1002"]?|
|?????20?|?["emp_1003",?"emp_1004"]?|
+--------+--------------------------+
2?rows?in?set?(0.00?sec)
JSON_PRETTY(json_val)
將 JSON 格式化輸出。
mysql>?select?json_pretty("[1,3,5]");
+------------------------+
|?json_pretty("[1,3,5]")?|
+------------------------+
|?[
??1,
??3,
??5
]??????|
+------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_pretty('{"a":"10","b":"15","x":"25"}');
+---------------------------------------------+
|?json_pretty('{"a":"10","b":"15","x":"25"}')?|
+---------------------------------------------+
|?{
??"a":?"10",
??"b":?"15",
??"x":?"25"
}???|
+---------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_STORAGE_FREE(json_val)
MySQL 8.0 新增的，與 Partial Updates 有關(guān)，用于計(jì)算 JSON 文檔在進(jìn)行部分更新后的剩余空間。
JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于計(jì)算 JSON 文檔的空間使用情況。
JSON_DEPTH(json_doc)
返回 JSON 文檔的最大深度。對(duì)于空數(shù)組，空對(duì)象，標(biāo)量值，其深度為 1。
mysql>?select?json_depth('{}'),json_depth('[10,?20]'),json_depth('[10,?{"a":?20}]');
+------------------+------------------------+-------------------------------+
|?json_depth('{}')?|?json_depth('[10,?20]')?|?json_depth('[10,?{"a":?20}]')?|
+------------------+------------------------+-------------------------------+
|????????????????1?|??????????????????????2?|?????????????????????????????3?|
+------------------+------------------------+-------------------------------+
1?row?in?set?(0.00?sec)
JSON_LENGTH(json_doc[, path])
返回 JSON 文檔的長(zhǎng)度，其計(jì)算規(guī)則如下：
如果是標(biāo)量值，其長(zhǎng)度為 1。 如果是數(shù)組，其長(zhǎng)度為數(shù)組元素的個(gè)數(shù)。 如果是對(duì)象，其長(zhǎng)度為對(duì)象元素的個(gè)數(shù)。 不包括嵌套數(shù)據(jù)和嵌套對(duì)象的長(zhǎng)度。
mysql>?select?json_length('"abc"');
+----------------------+
|?json_length('"abc"')?|
+----------------------+
|????????????????????1?|
+----------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_length('[1,?2,?{"a":?3}]');
+---------------------------------+
|?json_length('[1,?2,?{"a":?3}]')?|
+---------------------------------+
|???????????????????????????????3?|
+---------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_length('{"a":?1,?"b":?{"c":?30}}');
+-----------------------------------------+
|?json_length('{"a":?1,?"b":?{"c":?30}}')?|
+-----------------------------------------+
|???????????????????????????????????????2?|
+-----------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_length('{"a":?1,?"b":?{"c":?30}}',?'$.a');
+------------------------------------------------+
|?json_length('{"a":?1,?"b":?{"c":?30}}',?'$.a')?|
+------------------------------------------------+
|??????????????????????????????????????????????1?|
+------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_TYPE(json_val)
返回 JSON 值的類(lèi)型。
mysql>?select?json_type('123');
+------------------+
|?json_type('123')?|
+------------------+
|?INTEGER??????????|
+------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_type('"abc"');
+--------------------+
|?json_type('"abc"')?|
+--------------------+
|?STRING?????????????|
+--------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_type(cast(now()?as?json));
+--------------------------------+
|?json_type(cast(now()?as?json))?|
+--------------------------------+
|?DATETIME???????????????????????|
+--------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_type(json_extract('{"a":?[10,?true]}',?'$.a'));
+-----------------------------------------------------+
|?json_type(json_extract('{"a":?[10,?true]}',?'$.a'))?|
+-----------------------------------------------------+
|?ARRAY???????????????????????????????????????????????|
+-----------------------------------------------------+
1?row?in?set?(0.00?sec)
JSON_VALID(val)
判斷給定值是否是有效的 JSON 文檔。
mysql>?select?json_valid('hello'),?json_valid('"hello"');
+---------------------+-----------------------+
|?json_valid('hello')?|?json_valid('"hello"')?|
+---------------------+-----------------------+
|???????????????????0?|?????????????????????1?|
+---------------------+-----------------------+
1?row?in?set?(0.00?sec)
JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
從 JSON 文檔中提取數(shù)據(jù)并以表格的形式返回。
該函數(shù)的完整語(yǔ)法如下：
JSON_TABLE(
????expr,
????path?COLUMNS?(column_list)
)???[AS]?alias
column_list:
????column[,?column][,?...]
column:
????name?FOR?ORDINALITY
????|??name?type?PATH?string_path?[on_empty]?[on_error]
????|??name?type?EXISTS?PATH?string_path
????|??NESTED?[PATH]?path?COLUMNS?(column_list)
on_empty:
????{NULL?|?DEFAULT?json_string?|?ERROR}?ON?EMPTY
on_error:
????{NULL?|?DEFAULT?json_string?|?ERROR}?ON?ERROR
其中，
expr：可以返回 JSON 文檔的表達(dá)式。可以是一個(gè)標(biāo)量（ JSON 文檔 ），列名或者一個(gè)函數(shù)調(diào)用（ JSON_EXTRACT(t1.json_data,'$.post.comments') ）。 path：JSON 的路徑表達(dá)式， column：列的類(lèi)型，支持以下四種類(lèi)型： name FOR ORDINALITY：序號(hào)。name 是列名。 name type PATH string_path [on_empty] [on_error]：提取指定路徑（ string_path ）的元素。name 是列名，type 是 MySQL 中的數(shù)據(jù)類(lèi)型。 name type EXISTS PATH string_path：指定路徑（ string_path ）的元素是否存在。 NESTED [PATH] path COLUMNS (column_list)：將嵌套對(duì)象或數(shù)組與來(lái)自父對(duì)象或數(shù)組的 JSON 值扁平化為一行輸出。
select?*
?from
???json_table(
?????'[{"x":2,?"y":"8",?"z":9,?"b":[1,2,3]},?{"x":"3",?"y":"7"},?{"x":"4",?"y":6,?"z":10}]',
?????"$[*]"?columns(
???????id?for?ordinality,
???????xval?varchar(100)?path?"$.x",
???????yval?varchar(100)?path?"$.y",
???????z_exist?int?exists?path?"$.z",
???????nested?path?'$.b[*]'?columns?(b?INT?PATH?'$')
?????)
???)?as?t;
+------+------+------+---------+------+
|?id???|?xval?|?yval?|?z_exist?|?b????|
+------+------+------+---------+------+
|????1?|?2????|?8????|???????1?|????1?|
|????1?|?2????|?8????|???????1?|????2?|
|????1?|?2????|?8????|???????1?|????3?|
|????2?|?3????|?7????|???????0?|?NULL?|
|????3?|?4????|?6????|???????1?|?NULL?|
+------+------+------+---------+------+
5?rows?in?set?(0.00?sec)
JSON_SCHEMA_VALID(schema,document)
判斷 document （ JSON 文檔 ）是否滿(mǎn)足 schema （ JSON 對(duì)象）定義的規(guī)范要求。完整的規(guī)范要求可參考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不滿(mǎn)足，可通過(guò) JSON_SCHEMA_VALIDATION_REPORT() 獲取具體的原因。
以下面這個(gè) schema 為例。
set?@schema?=?'{
???"type":?"object",
???"properties":?{
?????"latitude":?{
???????"type":?"number",
???????"minimum":?-90,
???????"maximum":?90
?????},
?????"longitude":?{
???????"type":?"number",
???????"minimum":?-180,
???????"maximum":?180
?????}
???},
???"required":?["latitude",?"longitude"]
}';
它的要求如下：
document 必須是 JSON 對(duì)象。 JSON 對(duì)象必需的兩個(gè)屬性是 latitude 和 longitude。 latitude 和 longitude 必須是數(shù)值類(lèi)型，且兩者的大小分別在 -90 ～ 90，-180 ～ 180 之間。
下面通過(guò)具體的 document 來(lái)測(cè)試一下。
mysql>?set?@document?=?'{"latitude":?63.444697,"longitude":?10.445118}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_schema_valid(@schema,?@document);
+---------------------------------------+
|?json_schema_valid(@schema,?@document)?|
+---------------------------------------+
|?????????????????????????????????????1?|
+---------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?set?@document?=?'{"latitude":?63.444697}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_schema_valid(@schema,?@document);
+---------------------------------------+
|?json_schema_valid(@schema,?@document)?|
+---------------------------------------+
|?????????????????????????????????????0?|
+---------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_pretty(json_schema_validation_report(@schema,?@document))\G
***************************?1.?row?***************************
json_pretty(json_schema_validation_report(@schema,?@document)):?{
??"valid":?false,
??"reason":?"The?JSON?document?location?'#'?failed?requirement?'required'?at?JSON?Schema?location?'#'",
??"schema-location":?"#",
??"document-location":?"#",
??"schema-failed-keyword":?"required"
}
1?row?in?set?(0.00?sec)
mysql>?set?@document?=?'{"latitude":?91,"longitude":?0}';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?json_schema_valid(@schema,?@document);
+---------------------------------------+
|?json_schema_valid(@schema,?@document)?|
+---------------------------------------+
|?????????????????????????????????????0?|
+---------------------------------------+
1?row?in?set?(0.00?sec)
mysql>?select?json_pretty(json_schema_validation_report(@schema,?@document))\G
***************************?1.?row?***************************
json_pretty(json_schema_validation_report(@schema,?@document)):?{
??"valid":?false,
??"reason":?"The?JSON?document?location?'#/latitude'?failed?requirement?'maximum'?at?JSON?Schema?location?'#/properties/latitude'",
??"schema-location":?"#/properties/latitude",
??"document-location":?"#/latitude",
??"schema-failed-keyword":?"maximum"
}
1?row?in?set?(0.00?sec)
八、總結(jié)
如果要使用 JSON 類(lèi)型，推薦使用 MySQL 8.0。相比于 MySQL 5.7，Partial update 帶來(lái)的性能提升還是十分明顯的。
Partial update 在存儲(chǔ)引擎層是默認(rèn)開(kāi)啟的，binlog 中是否開(kāi)啟取決于 binlog_row_value_options 。該參數(shù)默認(rèn)為空，不會(huì)開(kāi)啟 Partial update，建議設(shè)置為 PARTIAL_JSON。
注意使用 Partial update 的前提條件。
當(dāng)我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進(jìn)行數(shù)組相關(guān)的操作時(shí)，可使用 MySQL 8.0.17 引入的多值索引來(lái)加快查詢(xún)。
九、參考資料
JSON：https://zh.wikipedia.org/wiki/JSON
The JSON Data Type：https://dev.mysql.com/doc/refman/8.0/en/json.html
JSON Functions：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
Upgrading JSON data stored in TEXT columns：https://dev.mysql.com/blog-archive/upgrading-json-data-stored-in-text-columns/
Indexing JSON documents via Virtual Columns：https://dev.mysql.com/blog-archive/indexing-json-documents-via-virtual-columns/
Partial update of JSON values：https://dev.mysql.com/blog-archive/partial-update-of-json-values/
MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates：https://dev.mysql.com/blog-archive/mysql-8-0-innodb-introduces-lob-index-for-faster-updates/
評(píng)論
圖片
表情