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

iVictor發表於2022-05-05
MySqlJSON資料型別

JSON 資料型別是 MySQL 5.7.8 開始支援的。在此之前，只能通過字元型別（CHAR，VARCHAR 或 TEXT ）來儲存 JSON 文件。

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

  1. 在插入時能自動校驗文件是否滿足 JSON 格式的要求。
  2. 優化了儲存格式。無需讀取整個文件就能快速訪問某個元素的值。

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

本文將從以下幾個方面展開：

  1. 什麼是 JSON。
  2. JSON 欄位的增刪改查操作。
  3. 如何對 JSON 欄位建立索引。
  4. 如何將儲存 JSON 字串的字元欄位升級為 JSON 欄位。
  5. 使用 JSON 時的注意事項。
  6. Partial Updates。
  7. 其它 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 型別，有以下幾點需要注意：

  1. 在 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

  2. 不允許直接建立索引，可建立函式索引。

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

  4. 插入時，單個文件的大小受到 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 需滿足以下條件：

  1. 被更新的列是 JSON 型別。

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

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

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

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

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

  4. 變更前後，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);

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

  1. MySQL 5.7.36
  2. MySQL 8.0.27
  3. MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON
  4. MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL

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

最後的測試結果如下：

圖片

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

  1. MySQL 8.0 只開啟儲存引擎層的 Partial Updates，查詢時間比 MySQL 5.7 快 1.94 倍。
  2. MySQL 8.0 同時開啟儲存引擎層和 binlog 中的 Partial Updates，查詢時間比 MySQL 5.7 快 4.87 倍。
  3. 如果在 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 文件。其合併規則如下：

  1. 如果兩個文件不全是 JSON 物件，則合併後的結果是第二個文件。
  2. 如果兩個文件都是 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 有所不同。

  1. 兩個文件中，只要有一個文件是陣列，則另外一個文件會合併到該陣列中。
  2. 兩個文件都是 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. 如果是標量值，其長度為 1。
  2. 如果是陣列，其長度為陣列元素的個數。
  3. 如果是物件，其長度為物件元素的個數。
  4. 不包括巢狀資料和巢狀物件的長度。
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"]
}';

它的要求如下：

  1. document 必須是 JSON 物件。
  2. JSON 物件必需的兩個屬性是 latitude 和 longitude。
  3. 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 引入的多值索引來加快查詢。

 

九、參考資料

  1. JSON

  2. The JSON Data Type

  3. JSON Functions

  4. Upgrading JSON data stored in TEXT columns

  5. Indexing JSON documents via Virtual Columns

  6. Partial update of JSON values

  7. MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates

相關文章