JSON 資料型別是 MySQL 5.7.8 開始支持的，在此之前，只能通過字符型別（CHAR，VARCHAR 或 TEXT ）來保存 JSON 檔案，

相對字符型別，原生的 JSON 型別具有以下優勢：

在插入時能自動校驗檔案是否滿足 JSON 格式的要求，
優化了存盤格式，無需讀取整個檔案就能快速訪問某個元素的值，

在 JSON 型別引入之前，如果我們想要獲取 JSON 檔案中的某個元素，必須首先讀取整個 JSON 檔案，然后在客戶端將其轉換為 JSON 物件，最后再通過物件獲取指定元素的值，

下面是 Python 中的獲取方式，

import json

# JSON 字串:
x =  '{ "name":"John", "age":30, "city":"New York"}'

# 將 JSON 字串轉換為 JSON 物件:
y = json.loads(x)

# 讀取 JSON 物件中指定元素的值:
print(y["age"])

這種方式有兩個弊端：一、消耗磁盤 IO，二、消耗網路帶寬，如果 JSON 檔案比較大，在高并發場景，有可能會打爆網卡，

如果使用的是 JSON 型別，相同的需求，直接使用 SQL 命令就可搞定，不僅能節省網路帶寬，結合后面提到的函式索引，還能降低磁盤 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 欄位的增刪改查操作，
如何對 JSON 欄位創建索引，
如何將存盤 JSON 字串的字符欄位升級為 JSON 欄位，
使用 JSON 時的注意事項，
Partial Updates，
其它 JSON 函式，

一、什么是 JSON

JSON 是 JavaScript Object Notation（JavaScript 物件表示法）的縮寫，是一個輕量級的，基于文本的，跨語言的資料交換格式，易于閱讀和撰寫，

JSON 的基本資料型別如下：

數值：十進制數，不能有前導 0，可以為負數或小數，還可以為 e 或 E 表示的指數，
字串：字串必須用雙引號括起來，
布林值：true，false，
陣列：一個由零或多個值組成的有序序列，每個值可以為任意型別，陣列使用方括號[] 括起來，元素之間用逗號,分隔，譬如，
```
[1, "abc", null, true, "10:27:06.000000", {"id": 1}]
```
物件：一個由零或者多個鍵值對組成的無序集合，其中鍵必須是字串，值可以為任意型別，

物件使用花括號{}括起來，鍵值對之間使用逗號,分隔，鍵與值之間用冒號:分隔，譬如，
```
{"db": ["mysql", "oracle"], "id": 123, "info": {"age": 20}}
```
空值：null，

二、JSON 欄位的增刪改查操作

下面我們看看 JSON 欄位常見的增刪改查操作：

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)

也可使用函式，常用的有 JSON_ARRAY() 和 JSON_OBJECT()，前者用于構造 JSON 陣列，后者用于構造 JSON 物件，如，

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)

對于 JSON 檔案，KEY 名不能重復，

如果插入的值中存在重復 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原則，會保留第一個 KEY，后面的將被丟棄掉，

從 MySQL 8.0.3 開始，遵循的是 last duplicate key wins 原則，只會保留最后一個 KEY，

下面通過一個具體的示例來看看兩者的區別，

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

JSON_EXTRACT(json_doc, path[, path] ...)

其中，json_doc 是 JSON 檔案，path 是路徑，該函式會從 JSON 檔案提取指定路徑（path）的元素，如果指定 path 不存在，會回傳 NULL，可指定多個 path，匹配到的多個值會以陣列形式回傳，

下面我們結合一些具體的示例來看看 path 及 JSON_EXTRACT 的用法，

首先我們看看陣列，

陣列的路徑是通過下標來表示的，第一個元素的下標是 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)

除此之外，還可通過 [M to N] 獲取陣列的子集，

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 代表最后一個元素的下標
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)

也可通過 [*] 獲取陣列中的所有元素，

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)

接下來，我們看看物件，

物件的路徑是通過 KEY 來表示的，

mysql> set @j='{"a": 1, "b": [2, 3], "a c": 4}';
Query OK, 0 rows affected (0.00 sec)

# 如果 KEY 在路徑運算式中不合法（譬如存在空格），則在參考這個 KEY 時，需用雙引號括起來，
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)

除此之外，還可通過 .* 獲取物件中的所有元素，

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，都是語法糖，在實際使用的時候都會轉化為 JSON_EXTRACT，

column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一個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 類似，只不過其回傳的是字串，以下三者是等價的，

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] ...)

插入新值，

僅當指定位置或指定 KEY 的值不存在時，才執行插入操作，另外，如果指定的 path 是陣列下標，且 json_doc 不是陣列，該函式首先會將 json_doc 轉化為陣列，然后再插入新值，

下面我們看幾個示例，

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] ...)

插入新值，并替換已經存在的值，

換言之，如果指定位置或指定 KEY 的值不存在，會執行插入操作，如果存在，則執行更新操作，

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] ...)

替換已經存在的值，

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)

最后一個查詢，雖然兩個 path 都是 '$[1]' ，但作用物件不一樣，第一個 path 的作用物件是 '["a", ["b", "c"], "d", "e"]' ，第二個 path 的作用物件是洗掉了 '$[1]' 后的陣列，即 '["a", "d", "e"]' ，

三、如何對 JSON 欄位創建索引

同 TEXT，BLOB 欄位一樣，JSON 欄位不允許直接創建索引，

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.

即使支持，實際意義也不大，因為我們一般是基于檔案中的元素進行查詢，很少會基于整個 JSON 檔案，

對檔案中的元素進行查詢，就需要用到 MySQL 5.7 引入的虛擬列及函式索引，

下面我們來看一個具體的示例，

# C2 即虛擬列
# index (c2) 對虛擬列添加索引，
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)

可以看到，無論是使用虛擬列，還是檔案中的元素來查詢，都可以利用上索引，

注意，在創建虛擬列時需指定 JSON_UNQUOTE，將 c1 -> "$.name" 的回傳值轉換為字串，

四、如何將存盤 JSON 字串的字符欄位升級為 JSON 欄位

在 MySQL 支持 JSON 型別之前，對于 JSON 檔案，一般是以字串的形式存盤在字符型別（VARCHAR 或 TEXT）中，

在 JSON 型別出來之后，如何將這些字符欄位升級為 JSON 欄位呢？

為方便演示，這里首先構建測驗資料，

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"}');

注意，最后一個檔案有問題，不是合格的 JSON 檔案，

如果使用 DDL 直接修改欄位的資料型別，會報錯，

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'.

下面，我們看看具體的升級步驟，

（1）使用 json_valid 函式找出不滿足 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 時的注意事項

對于 JSON 型別，有以下幾點需要注意：

在 MySQL 8.0.13 之前，不允許對 BLOB，TEXT，GEOMETRY，JSON 欄位設定默認值，從 MySQL 8.0.13 開始，取消了這個限制，

設定時，注意默認值需通過小括號()括起來，否則的話，還是會提示 JSON 欄位不允許設定默認值，
```
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
```
不允許直接創建索引，可創建函式索引，
JSON 列的最大大小和 LONGBLOB（LONGTEXT）一樣，都是 4G，
插入時，單個檔案的大小受到 max_allowed_packet 的限制，該引數最大是 1G，

六、Partial Updates

在 MySQL 5.7 中，對 JSON 檔案進行更新，其處理策略是，洗掉舊的檔案，再插入新的檔案，即使這個修改很微小，只涉及幾個位元組，也會替換掉整個檔案，很顯然，這種處理方式的效率較為低下，

在 MySQL 8.0 中，針對 JSON 檔案，引入了一項新的特性-Partial Updates（部分更新），支持 JSON 檔案的原地更新，得益于這個特性，JSON 檔案的處理性能得到了極大提升，

下面我們具體來看看，

6.1 使用 Partial Updates 的條件

為方便闡述，這里先構造測驗資料，

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

被更新的列是 JSON 型別，
使用 JSON_SET，JSON_REPLACE，JSON_REMOVE 進行 UPDATE 操作，如，
```
update t set c1=json_remove(c1,'$.id') where id=1;
```
不使用這三個函式，而顯式賦值，就不會進行部分更新，如，
```
update t set c1='{"id": 1, "name": "a"}' where id=1;
```

輸入列和目標列必須是同一列，如，

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

否則的話，就不會進行部分更新，如，

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

變更前后，JSON 檔案的空間使用不會增加，

關于最后一個條件，我們看看下面這個示例，

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)

示例中，用到了兩個函式：JSON_STORAGE_SIZE 和 JSON_STORAGE_FREE ，前者用來獲取 JSON 檔案的空間使用情況，后者用來獲取 JSON 檔案在執行原地更新后的空間釋放情況，

這里一共執行了三次 UPDATE 操作，前兩次是原地更新，第三次不是，同樣是 JSON_SET 操作，為什么第一次是原地更新，而第二次不是呢？

因為第一次的 JSON_SET 復用了 JSON_REMOVE 釋放的空間，而第二次的 JSON_SET 執行的是更新操作，且 'mysql' 比 3306 需要更多的存盤空間，

6.2 如何在 binlog 中開啟 Partial Updates

Partial Updates 不僅僅適用于存盤引擎層，還可用于主從復制場景，

主從復制開啟 Partial Updates，只需將引數 binlog_row_value_options（默認為空）設定為 PARTIAL_JSON，

下面具體來看看，同一個 UPDATE 操作，開啟和不開啟 Partial Updates，在 binlog 中的記錄有何區別，

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

不開啟

### UPDATE `slowtech`.`t`
### WHERE
###   @1=1
###   @2='{"id": "1", "name": "a"}'
### SET
###   @1=1
###   @2='{"id": 10, "name": "a"}'

開啟

### UPDATE `slowtech`.`t`
### WHERE
###   @1=1
###   @2='{"id": 1, "name": "a"}'
### SET
###   @1=1
###   @2=JSON_REPLACE(@2, '$.id', 10)

對比 binlog 的內容，可以看到，不開啟，無論是修改前的鏡像（before_image）還是修改后的鏡像（after_image），記錄的都是完整檔案，而開啟后，對于修改后的鏡像，記錄的是命令，而不是完整檔案，這樣可節省近一半的空間，

在將 binlog_row_value_options 設定為 PARTIAL_JSON 后，對于可使用 Partial Updates 的操作，在 binlog 中，不再通過 ROWS_EVENT 來記錄，而是新增了一個 PARTIAL_UPDATE_ROWS_EVENT 的事件型別，

需要注意的是，binlog 中使用 Partial Updates，只需滿足存盤引擎層使用 Partial Updates 的前三個條件，無需考慮變更前后，JSON 檔案的空間使用是否會增加，

6.3 關于 Partial Updates 的性能測驗

首先構造測驗資料，t 表一共有 16 個檔案，每個檔案近 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;

接下來，測驗下述 SQL

update t set json_col = json_set(json_col, '$.age', age + 1);

在以下四種場景下的執行時間：

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

分別執行 10 次，去掉最大值和最小值后求平均值，

最后的測驗結果如下：

以 MySQL 5.7.36 的查詢時間作為基準：

MySQL 8.0 只開啟存盤引擎層的 Partial Updates，查詢時間比 MySQL 5.7 快 1.94 倍，
MySQL 8.0 同時開啟存盤引擎層和 binlog 中的 Partial Updates，查詢時間比 MySQL 5.7 快 4.87 倍，
如果在 2 的基礎上，同時將 binlog_row_image 設定為 MINIMAL，查詢時間更是比 MySQL 5.7 快 102.22 倍，

當然，在生產環境，我們一般很少將 binlog_row_image 設定為 MINIMAL，

但即使如此，只開啟存盤引擎層和 binlog 中的 Partial Updates，查詢時間也比 MySQL 5.7 快 4.87 倍，性能提升還是比較明顯的，

七、其它 JSON 函式

7.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)

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

判斷指定的 path 是否存在，存在，則回傳 1，否則是 0，

函式中的 one_or_all 可指定 one 或 all，one 是任意一個路徑存在就回傳 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] ...])

回傳某個字串（search_str）在 JSON 檔案中的位置，其中，

one_or_all：匹配的次數，one 是只匹配一次，all 是匹配所有，如果匹配到多個，結果會以陣列的形式回傳，
search_str：子串，支持模糊匹配：% 和 _ ，
escape_char：轉義符，如果該引數不填或為 NULL，則取默認轉義符\，
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 對應元素最外層的 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）的元素，

該函式的完整語法如下：

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：回傳值的型別，不指定，則默認是 VARCHAR(512)，不指定字符集，則默認是 utf8mb4，且區分大小寫，
on_empty：如果指定路徑沒有值，會觸發 on_empty 子句，默認是回傳 NULL，也可指定 ERROR 拋出錯誤，或者通過 DEFAULT value 回傳默認值，
on_error：三種情況下會觸發 on_error 子句：從陣列或物件中提取元素時，會決議到多個值；型別轉換錯誤，譬如將 "abc" 轉換為 unsigned 型別；值被 truncate 了，默認是回傳 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 陣列的一個元素，如果是，則回傳 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 引入的，用來比較兩個 JSON 檔案是否有相同的鍵值對或陣列元素，如果有，則回傳 1，否則是 0，如果兩個引數都是標量，則判斷這兩個標量是否相等，

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 開始，InnoDB 支持多值索引，可用在 JSON 陣列中，當我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進行陣列相關的操作時，可使用多值索引來加快查詢，

7.2 修改相關

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

向陣列指定位置追加元素，如果指定 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] ...)

向陣列指定位置插入元素，

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 引入的，用來合并多個 JSON 檔案，其合并規則如下：

如果兩個檔案不全是 JSON 物件，則合并后的結果是第二個檔案，
如果兩個檔案都是 JSON 物件，且不存在著同名 KEY，則合并后的檔案包括兩個檔案的所有元素，如果存在著同名 KEY，則第二個檔案的值會覆寫第一個，

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)

# 如果第二個檔案存在 null 值，檔案合并后不會輸出對應的 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 引入的，用來代替 JSON_MERGE，也是用來合并檔案，但合并規則與 JSON_MERGE_PATCH 有所不同，

兩個檔案中，只要有一個檔案是陣列，則另外一個檔案會合并到該陣列中，
兩個檔案都是 JSON 物件，若存在著同名 KEY ，第二個檔案并不會覆寫第一個，而是會將值 append 到第一個檔案中，

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 開始不建議使用，后續會移除，

7.3 其它輔助函式

JSON_QUOTE(string)

生成有效的 JSON 字串，主要是對一些特殊字符（如雙引號）進行轉義，

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)

除此之外，也可通過 CAST(value AS JSON) 進行型別轉換，

JSON_UNQUOTE(json_val)

將 JSON 轉義成字串輸出，

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)

直觀地看，沒加 JSON_UNQUOTE 字串會用雙引號引起來，加了 JSON_UNQUOTE 就沒有，但本質上，前者是 JSON 中的 STRING 型別，后者是 MySQL 中的字符型別，這一點可通過 JSON_VALID 來判斷，

JSON_OBJECTAGG(key, value)

取表中的兩列作為引數，其中，第一列是 key，第二列是 value，回傳 JSON 物件，如，

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 陣列，注意，JSON 陣列中元素的順序是隨機的，

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 有關，用于計算 JSON 檔案在進行部分更新后的剩余空間，

JSON_STORAGE_SIZE(json_val)

MySQL 5.7.22 引入的，用于計算 JSON 檔案的空間使用情況，

JSON_DEPTH(json_doc)

回傳 JSON 檔案的最大深度，對于空陣列，空物件，標量值，其深度為 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 檔案的長度，其計算規則如下：

如果是標量值，其長度為 1，
如果是陣列，其長度為陣列元素的個數，
如果是物件，其長度為物件元素的個數，
不包括嵌套資料和嵌套物件的長度，

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 值的型別，

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 檔案中提取資料并以表格的形式回傳，

該函式的完整語法如下：

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 檔案的運算式，可以是一個標量（ JSON 檔案），列名或者一個函式呼叫（ JSON_EXTRACT(t1.json_data,'$.post.comments') ），
path：JSON 的路徑運算式，
column：列的型別，支持以下四種型別：

name FOR ORDINALITY：序號，name 是列名，
name type PATH string_path [on_empty] [on_error]：提取指定路徑（ string_path ）的元素，name 是列名，type 是 MySQL 中的資料型別，
name type EXISTS PATH string_path：指定路徑（ string_path ）的元素是否存在，
NESTED [PATH] path COLUMNS (column_list)：將嵌套物件或陣列與來自父物件或陣列的 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 檔案）是否滿足 schema （ JSON 物件）定義的規范要求，完整的規范要求可參考 Draft 4 of the JSON Schema specification ，如果不滿足，可通過 JSON_SCHEMA_VALIDATION_REPORT() 獲取具體的原因，

以下面這個 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 物件，
JSON 物件必需的兩個屬性是 latitude 和 longitude，
latitude 和 longitude 必須是數值型別，且兩者的大小分別在 -90 ～ 90，-180 ～ 180 之間，

下面通過具體的 document 來測驗一下，

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)

八、總結

如果要使用 JSON 型別，推薦使用 MySQL 8.0，相比于 MySQL 5.7，Partial update 帶來的性能提升還是十分明顯的，

Partial update 在存盤引擎層是默認開啟的，binlog 中是否開啟取決于 binlog_row_value_options ，該引數默認為空，不會開啟 Partial update，建議設定為 PARTIAL_JSON，

注意使用 Partial update 的前提條件，

當我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進行陣列相關的操作時，可使用 MySQL 8.0.17 引入的多值索引來加快查詢，

九、參考資料

JSON
The JSON Data Type
JSON Functions
Upgrading JSON data stored in TEXT columns
Indexing JSON documents via Virtual Columns
Partial update of JSON values
MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/469736.html

標籤：MySQL

上一篇：JavaWeb連接MySQL資料庫

下一篇：開源之夏 2022 重磅來襲，歡迎報名 RadonDB 社區專案

一文說透 MySQL JSON 資料型別（收藏）