蜜桃av色欲a片精品一区,麻豆aⅴ精品无码一区二区,亚洲人成网站在线播放影院在线,亚洲 素人 字幕 在线 最新

  越晚搞懂 MySQL JSON 數(shù)據(jù)類(lèi)型，你就越吃虧

發(fā)布日期：2022/8/8 14:28:05      瀏覽量：

作者介紹

陳臣，甲骨文MySQL首席解決方案工程師，公眾號(hào)《MySQL實(shí)戰(zhàn)》作者，有大規(guī)模的MySQL，Redis，MongoDB，ES的管理和維護(hù)經(jīng)驗(yàn)，擅長(zhǎng)MySQL數(shù)據(jù)庫(kù)的性能優(yōu)化及日常操作的原理剖析。

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)文檔是否滿足 JSON 格式的要求。

優(yōu)化了存儲(chǔ)格式。無(wú)需讀取整個(gè)文檔就能快速訪問(wèn)某個(gè)元素的值。

在 JSON 類(lèi)型引入之前，如果我們想要獲取 JSON 文檔中的某個(gè)元素，必須首先讀取整個(gè) JSON 文檔，然后在客戶端將其轉(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)

一、什么是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)的增刪改查操作：

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、查詢操作

1）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)

2）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)

3）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)

3、修改操作

1）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)

2）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)

3）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)

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è)查詢，雖然兩個(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)行查詢，很少會(huì)基于整個(gè) JSON 文檔。

對(duì)文檔中的元素進(jì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)查詢，都可以利用上索引。

注意，在創(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ù)找出不滿足 JSON 格式要求的文檔。

mysql> select * from t where json_valid(c1) = 0; +----+---------------------+ | id | c1 | +----+---------------------+ | 4 | {"id", "name": "d"} | +----+---------------------+ 1 row in set (0.00 sec)

2）處理不滿足 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)需要注意：

1）在 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ì)提示 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

2）不允許直接創(chuàng)建索引，可創(chuàng)建函數(shù)索引。

3）JSON 列的最大大小和 LONGBLOB（LONGTEXT）一樣，都是 4G。

4）插入時(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)看看。

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 需滿足以下條件：

1）被更新的列是 JSON 類(lèi)型。

2）使用 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;

3）輸入列和目標(biāo)列必須是同一列，如，

update t set c1=json_replace(c1,’$.id’,10) where id=1;

否則的話，就不會(huì)進(jìn)行部分更新，如，

update t set c1=json_replace(c2,’$.id’,10) where id=1;

4）變更前后，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ǔ)空間。

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，只需滿足存儲(chǔ)引擎層使用 Partial Updates 的前三個(gè)條件，無(wú)需考慮變更前后，JSON 文檔的空間使用是否會(huì)增加。

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 的查詢時(shí)間作為基準(zhǔn)：

• MySQL 8.0 只開(kāi)啟存儲(chǔ)引擎層的 Partial Updates，查詢時(shí)間比 MySQL 5.7 快 1.94 倍。

• MySQL 8.0 同時(shí)開(kāi)啟存儲(chǔ)引擎層和 binlog 中的 Partial Updates，查詢時(shí)間比 MySQL 5.7 快 4.87 倍。

• 如果在 2 的基礎(chǔ)上，同時(shí)將 binlog_row_image 設(shè)置為 MINIMAL，查詢時(shí)間更是比 MySQL 5.7 快 102.22 倍。

當(dāng)然，在生產(chǎn)環(huán)境，我們一般很少將 binlog_row_image 設(shè)置為 MINIMAL。

但即使如此，只開(kāi)啟存儲(chǔ)引擎層和 binlog 中的 Partial Updates，查詢時(shí)間也比 MySQL 5.7 快 4.87 倍，性能提升還是比較明顯的。

七、其它 JSON 函數(shù)

1、查詢相關(guān)

1）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)

2）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)

3）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)

4）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)

5）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’

6）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)

7）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)加快查詢。

2、修改相關(guān)

1）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)

2）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)

3）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)

4）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)

5）JSON_MERGE(json_doc, json_doc[, json_doc] ...)

與 JSON_MERGE_PRESERVE 作用一樣，從 MySQL 8.0.3 開(kāi)始不建議使用，后續(xù)會(huì)移除。

3、其它輔助函數(shù)

1）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)換。

2）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)判斷。

3）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)

4）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)

5）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)

6）JSON_STORAGE_FREE(json_val)

MySQL 8.0 新增的，與 Partial Updates 有關(guān)，用于計(jì)算 JSON 文檔在進(jìn)行部分更新后的剩余空間。

7）JSON_STORAGE_SIZE(json_val)

MySQL 5.7.22 引入的，用于計(jì)算 JSON 文檔的空間使用情況。

8）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)

9）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)

10）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)

11）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)

12）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)

13）JSON_SCHEMA_VALID(schema,document)

判斷 document （ JSON 文檔 ）是否滿足 schema （ JSON 對(duì)象）定義的規(guī)范要求。完整的規(guī)范要求可參考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不滿足，可通過(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)加快查詢。

>>>>

參考資料

• 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/

作者丨陳臣

來(lái)源丨公眾號(hào)：MySQL實(shí)戰(zhàn)（ID：MySQLInAction）