從5.7.8開始，MySQL開始支持JSON類型，用于存儲JSON數據。

JSON類型的加入模糊了關系型數據庫與NoSQL之間的界限，給日常開發也帶來了很大的便利。

這篇文章主要介紹一下MySQL中JSON類型的使用，主要參考MySQL手冊：https://dev.mysql.com/doc/refman/8.0/en/

1. 為什么要用JSON

自從MySQL添加對JSON的支持之后，一些表結構變更的操作就變得簡單了一些。

1.1 JSON的使用場景

雖然關系型數據庫一直很有效，但是面對需求的不斷變化，文檔型數據庫更加靈活方便。

MySQL支持JSON之后，模糊了關系型與文檔型數據庫之間的界限。

在開發過程中經常會遇見下面幾種情況：

• 表中僅僅小部分數據需要新添加的字段；
• 當這個新添加的字段很有可能只是臨時使用后續會廢棄的時候；
• 當后面還不知道要新添加什么字段但大概率要添加的時候。

這些時候，使用一個JSON進行存儲比較合適，不用更改表結構，非常方便。

1.2 字符串還是JSON類型

在還不支持JSON的MySQL 5.7版本之前，沒有選擇只能使用一個字符串類型存儲JSON數據了。

但是如果數據庫支持JSON類型，那么就還是使用JSON類型吧。

JSON類型相比與使用字符串存儲JSON數據有如下的好處：

• 可以對存儲的JSON數據自動校驗，不合法的JSON數據插入時會報錯；
• 優化的存儲結構。JSON類型將數據轉化為內部結構進行存儲，使得可以對JSON類型數據進行搜索與局部變更；而對于字符串來說，需要全部取出來再更新。

2. JSON的增刪改查

這里將簡單介紹一下JSON類型的使用，主要是增刪改查等操作。

MySQL中使用utf8mb4字符集以及utf8mb4_bin字符序來處理JSON中的字符串，因此JSON中的字符串時大小寫敏感的。

2.1 創建JSON列

創建一個JSON類型的列很簡單：

CREATE TABLE videos (id int NOT NULL AUTO_INCREMENT,ext json NOT NULL,PRIMARY KEY (id)
);

我們構建了一個表videos，里面有一個JSON類型的ext字段，用于存儲一些擴展數據。

2.2 插入JSON值

和其它類型一樣，使用INSERT來插入數據：

INSERT INTO videos
VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}'),(2, '{"vid": "vid2", "tags":[], "title": "title2", "logo": false}'),(3, '{"vid": "vid3", "title": "title3"}');

來看一下現在表里的數據：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "title": "title3"}                                          |
+----+-----------------------------------------------------------------------------+

每一個ext都是一個JSON數據。

2.3 校驗JSON

使用JSON類型的一個好處就是MySQL可以自動檢查數據的有效性，避免插入非法的JSON數據。

2.3.1 JSON合法性校驗

首先需要校驗一個值是否是一個合法的JSON，否則插入會失敗：

mysql> insert into videos values (1, '{');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'videos.ext'.

同時還可以使用JSON_VALID()函數查看一個JSON值是否合法：

mysql> select json_valid('{');
+-----------------+
| json_valid('{') |
+-----------------+
|               0 |
+-----------------+mysql> select json_valid('{"vid": "vid1"}');
+-------------------------------+
| json_valid('{"vid": "vid1"}') |
+-------------------------------+
|                             1 |
+-------------------------------+

2.3.2 JSON模式校驗

如果更進一步，除了值是否是合法JSON外，還需要校驗模式，比如JSON值要包含某些字段等。

這時可以定義一個模式（schema），然后使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()函數來校驗。

JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()兩個函數是8.0.17版本引入的，5.7版本還沒有。

定義一個模式：

{"id": "schema_for_videos","$schema": "http://json-schema.org/draft-04/schema#","description": "Schema for the table videos","type": "object","properties": {"vid": {"type": "string"},"tags": {"type": "array"},"logo": {"type": "boolean"},"title": {"type": "string"}},"required": ["title", "tags"]
}

字段含義：

• id: 模式的唯一ID；
• $schema: JSON模式校驗的標準，應該是這個值保持不變；
• description: 模式的描述；
• type: 根元素的類型，MySQL中JSON的根元素還可以是數組（array）；
• properties: JSON元素的列表，每一個元素都應該描述出來，里面列出了對應的類型；
• required: 必要的元素。

在MySQL中定義一個變量：

mysql> set @schema = '{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"title":{"type":"string"},"tags":{"type":"array"}},"required":["title","tags"]}';
Query OK, 0 rows affected (0.04 sec)

這樣就可以使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()校驗一個JSON是否滿足要求了：

mysql> select json_schema_valid(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      1 |
+--------+mysql> select json_schema_validation_report(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+-----------------+
| valid?          |
+-----------------+
| {"valid": true} |
+-----------------+

JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()的區別就是后者可以給出不滿足要求的地方：

mysql> select json_schema_valid(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      0 |
+--------+mysql> select json_schema_validation_report(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?'\G
*************************** 1. row ***************************
valid?: {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}

當然，這兩個函數的第二個參數要是一個合法的JSON，不然MySQL會報錯：

mysql> select json_schema_valid(@schema, '{') as 'valid?';ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_schema_valid: "Missing a name for object member." at position 1.

我們還可以將這個模式添加到表的定義上，這樣插入數據就可以使用這個模式進行校驗了：

ALTER TABLE videos
ADD CONSTRAINT CHECK (JSON_SCHEMA_VALID('{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"vid":{"type":"string"},"tags":{"type":"array"},"logo":{"type":"bool"},"title":{"type":"string"}},"required":["title","tags"]}', ext));

當然如果表里已經有數據了且不符合這個校驗模式，MySQL會報錯：

ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

應該修改原來的數據以滿足要求后再添加校驗。

添加之后，新增的數據就會進行校驗：

mysql> INSERT INTO videos VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}');Query OK, 1 row affected (0.04 sec)mysql> INSERT INTO videos VALUES (2, '{"vid": "vid2", "title": "title2"}');ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

2.4 JSON的格式化

使用JSON_PRETTY()函數進行美化輸出：

mysql> select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {"vid": "vid1","logo": true,"tags": ["news","china"],"title": "title1"
}

2.5 獲取JSON元素

JSON字段優于JSON字符串的一點就是JSON字段可以直接獲取內部的元素而不用獲取整個文檔。

MySQL中支持使用JSON_EXTRACT()函數以及->，->>操作符來獲取JSON內部的元素：

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.a') as a;
+------+
| a    |
+------+
| 9    |
+------+
1 row in set (0.04 sec)mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b') as b;
+-----------+
| b         |
+-----------+
| [1, 2, 3] |
+-----------+
1 row in set (0.04 sec)mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b[1]') as 'b[1]';
+------+
| b[1] |
+------+
| 2    |
+------+
1 row in set (0.04 sec)

使用->：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)mysql> select ext->'$.title' from videos;
+----------------+
| ext->'$.title' |
+----------------+
| "title1"       |
| "title2"       |
| "title3"       |
+----------------+
3 rows in set (0.04 sec)

->就是JSON_EXTRACT()函數的別名。

使用JSON_UNQUOTE()函數去掉引號：

mysql> select json_unquote(ext->'$.title') from videos;
+------------------------------+
| json_unquote(ext->'$.title') |
+------------------------------+
| title1                       |
| title2                       |
| title3                       |
+------------------------------+
3 rows in set (0.04 sec)

還可以使用->>達到同樣的效果（->>就是JSON_UNQUOTE(JSON_EXTRACT(...))的別名）:

mysql> select ext->>'$.title' from videos;
+-----------------+
| ext->>'$.title' |
+-----------------+
| title1          |
| title2          |
| title3          |
+-----------------+
3 rows in set (0.04 sec)

2.6 JSONPath

在獲取JSON元素的過程中，我們使用了類似$.title，$.b[1]這樣的結構來指定元素，這些就是JSONPath。

JSONPath使用$符號表示整個JSON文檔，后面可以跟著不同的符號表示不同的元素：

• 一個點號(.)加上key，可以獲取指定key的值；
• [N]獲取數組中下標為N的元素（0開始）；
• [N to M]數組元素還可以指定開頭結尾（都包含）；
• [last] last表示數組中的最后一個元素；
• [*]獲取數組中的所有元素；
• prefix**suffix獲取所有prefix開頭suffix結尾的JSONPath。

以下面的JSON為例：

{"a": "a_value","b": [1, 2, 3, 4, 5],"c": true,"d": {"a": "inner_a","b": [11, 22, "inner_b"]}
}

• '$'得到整個文檔；
• '$.a'就是"a_value"；
• '$.b'就是[1, 2, 3, 4, 5]；
• '$.b[*]'等同于'$.b'；
• '$.b[2]'得到數組b中的第三個元素3；
• '$.d.a'得到的就是"inner_a"；
• '$.d.b[2]'得到的就是"inner_b"；
• '$.b[1 to 2]'返回[2, 3]；
• '$.b[last]'返回5；
• '$.b[last-2 to last-1]'返回[3, 4]；
• '$**.a'返回的是所有以a結尾的元素組成的數組["a_value", "inner_a"]；
• '$**.b'就是數組的數組了[[1, 2, 3, 4, 5], [11, 22, "inner_b"]]。

JSONPath并不僅僅可以用來獲取JSON內的元素，涉及到JSON值增刪改查的函數基本上都需要一個JSONPath作為參數來指定要操作的元素。

2.7 搜索JSON元素

JSON類型的另一個優勢就是可以進行搜索。

搜索可以使用JSON_SEARCH()函數，返回匹配的JSONPath。

JSON_SEARCH()函數原型如下：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

其中前三個是必須參數：

• json_doc: 一個有效的JSON文檔；
• one_or_all: 字符串，必須是'one'或'all'，用于指定匹配返回的個數，如果是'one'的話只返回匹配的第一個，否則全部返回；
• search_str: 就是需要搜索的值，目前只支持字符串搜索，同時還可以添加%或_來模糊匹配；

后兩個是可選參數：

• escape_char: 轉義字符，默認是\；如果不指定或為NULL的話，也是\;否則，這個參數只能為空（此時還是\）或者一個字符（指定多個會報錯）；
• path: 指定了開始搜索的位置，如果沒有的話就是整個文檔。

接下來以下面這個JSON文檔為例看一下如何進行搜索：

{"a": "a_value","b": ["1", "2", "3", "4", "5"],"c": true,"d": {"a": "a_value","b": ["1", "2", "bvalue"]}
}

• json_search(@j, 'one', 'a_value')返回"$.a"；
• json_search(@j, 'all', 'a_value')返回["$.a", "$.d.a"]；
• json_search(@j, 'all', '1')返回["$.b[0]", "$.d.b[0]"]；
• json_search(@j, 'all', '%_value')返回["$.a", "$.d.a", "$.d.b[2]"]；
• json_search(@j, 'all', '%\_value')返回["$.a", "$.d.a"]，注意和上一個的區別；
• json_search(@j, 'all', '%|_value', '|')指定轉義符，返回["$.a", "$.d.a"]；
• json_search(@j, 'all', '%|_value', '|', '$.a')指定了開始搜索的位置，返回"$.a"，沒有匹配$.d.a；

接下來，我們就可以在WHERE中使用JSON_SEARCH()了。

還是之前的videos表：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)mysql> select * from videos where json_search(ext, 'all', 'title2');
+----+---------------------------------------------------------------+
| id | ext                                                           |
+----+---------------------------------------------------------------+
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"} |
+----+---------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)mysql> select * from videos where json_search(ext, 'all', 'food', '', '$.tags');
+----+---------------------------------------------------------------------+
| id | ext                                                                 |
+----+---------------------------------------------------------------------+
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"} |
+----+---------------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

還可以使用->操作符來搜索：

mysql> select ext from videos where ext->'$.logo' = true;
+------------------------------------------------------------------------------------------------+
| ext                                                                                            |
+------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1", "protected": true} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

2.8 JSON中插入新元素

MySQL中有幾個函數可以支持向JSON中新增元素：

• JSON_INSERT()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()

這幾個函數支持就地更新，而不是取出JSON文檔更改后全量覆蓋。

使用JSON_INSERT()函數新增元素：

update videos set ext = json_insert(ext, '$.protected', true);

如果要增加的元素已經有了的話，則沒有變化。

JSON_ARRAY_APPEND()函數可以向數組中追加元素：

update videos set ext = json_array_append(ext, '$.tags', 'tech') where json_search(ext, 'all', 'title2', '', '$.title');

這里同時使用了JSON_SEARCH()進行匹配。

JSON_ARRAY_INSERT()函數可以在數組的指定位置中添加元素：

update videos set ext=json_array_insert(ext, '$.tags[1]', 'beijing') where ext->'$.vid' = 'vid1';

結果：

mysql> select ext from videos where ext->'$.vid' = 'vid1';
+-----------------------------------------------------------------------------------------------------------+
| ext                                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "beijing", "china"], "title": "title1", "protected": true} |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

2.9 更新JSON元素

使用JSON_REPLACE()或JSON_SET()函數來更新JSON中的元素。

JSON_REPLACE()函數可以用來更新元素的值：

update videos set ext = json_replace(ext, '$.protected', false) where ext->'$.vid' = 'vid1';

不過如果JSON中沒有要更新的key，那么就什么也不做。

JSON_SET()除了可以更新元素的值之外，如果指定的元素不存在，還可以添加：

update videos set ext = json_set(ext, '$.size', 100) where ext->'$.vid' = 'vid1';

2.10 刪除JSON元素

使用JSON_REMOVE()函數可以刪除JSON中的元素：

update videos set ext = json_remove(ext, '$.size') where ext->'$.vid' = 'vid1';update videos set ext = json_remove(ext, '$.tags[1]') where ext->'$.vid' = 'vid1';

JSON_REMOVE()函數可以指定多個JSONPath來刪除多個元素，這時MySQL是從左到右一個個刪除的。

這樣即使是相同的JSONPath但是順序不一樣，結果就會不一樣：

mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]') |
+-------------------------------------------------------+
| {"a": [1, 2, 4]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]') |
+-------------------------------------------------------+
| {"a": [1, 2, 5]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)

2.11 JSON合并

MySQL中支持將兩個JSON文檔合并成一個文檔。可以通過下面的兩個函數來完成：

• JSON_MERGE_PATCH()：相當于第二個參數更新第一個參數；
• JSON_MERGE_PRESERVE()：盡可能地保留兩個參數的元素。

這兩個函數有很大的不同，使用的時候一定要注意。

2.11.1 JSON_MERGE_PATCH

函數接收至少兩個參數，如果多于兩個參數的話，那么就前兩個合并的結果與后一個進行合并。

下面假設有兩個參數進行討論，多于兩個的也是類似的。

如果有一個參數是NULL，那么結果就是NULL：

mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', null);
+------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', null) |
+------------------------------------------------+
| NULL                                           |
+------------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch(null, '{"b": null}');
+---------------------------------------+
| json_merge_patch(null, '{"b": null}') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set (0.04 sec)

如果第一個參數不是object，那么結果就相當于一個空的object和第二個參數合并，其實就是第二個參數：

mysql> select json_merge_patch('{}', '{"a": "a"}');
+--------------------------------------+
| json_merge_patch('{}', '{"a": "a"}') |
+--------------------------------------+
| {"a": "a"}                           |
+--------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{}', '"a"');
+-------------------------------+
| json_merge_patch('{}', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('[]', '"a"');
+-------------------------------+
| json_merge_patch('[]', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('[1, 2, 3]', '{"a": "a"}');
+---------------------------------------------+
| json_merge_patch('[1, 2, 3]', '{"a": "a"}') |
+---------------------------------------------+
| {"a": "a"}                                  |
+---------------------------------------------+
1 row in set (0.04 sec)

如果第二個參數是數組（array），那么結果還是第二個參數：

mysql> select json_merge_patch('{"a": "a"}', '[]');
+--------------------------------------+
| json_merge_patch('{"a": "a"}', '[]') |
+--------------------------------------+
| []                                   |
+--------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{"a": "a"}', '[1]');
+---------------------------------------+
| json_merge_patch('{"a": "a"}', '[1]') |
+---------------------------------------+
| [1]                                   |
+---------------------------------------+
1 row in set (0.04 sec)

接下來就是兩個參數都是object的情況了。

合并的結果包含第一個參數有而第二個參數沒有的那些元素：

mysql> select json_merge_patch('{"a": 1}', '{}');
+------------------------------------+
| json_merge_patch('{"a": 1}', '{}') |
+------------------------------------+
| {"a": 1}                           |
+------------------------------------+
1 row in set (0.04 sec)

也包含第一個參數沒有而第二個有的元素（除了值是null的）：

mysql> select json_merge_patch('{"a": 1}', '{"b": 2}');
+------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": 2}') |
+------------------------------------------+
| {"a": 1, "b": 2}                         |
+------------------------------------------+
1 row in set (0.04 sec)

如果兩個參數里都有，那么合并的結果就是兩個值遞歸合并的結果：

mysql> select json_merge_patch('{"a": 1}', '{"a": 2}');
+------------------------------------------+
| json_merge_patch('{"a": 1}', '{"a": 2}') |
+------------------------------------------+
| {"a": 2}                                 |
+------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{"a": [1,2]}', '{"a": 2}');
+----------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": 2}') |
+----------------------------------------------+
| {"a": 2}                                     |
+----------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{"a": [1,2]}', '{"a": [3]}');
+------------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": [3]}') |
+------------------------------------------------+
| {"a": [3]}                                     |
+------------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{"a": [1,2]}', '{"a": {"c": 1}}');
+-----------------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": {"c": 1}}') |
+-----------------------------------------------------+
| {"a": {"c": 1}}                                     |
+-----------------------------------------------------+
1 row in set (0.04 sec)

這三個例子中，結果就像是第二個參數的值覆蓋了第一個，這是因為這幾個例子中兩個參數a所對應的值不都是object，結果就是第二個參數a的值。

下面的例子展示了遞歸合并的結果：

mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": [1, 2], "d": 9}}                               |
+------------------------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": 9}}                                            |
+------------------------------------------------------------+
1 row in set (0.04 sec)

如果第二個參數的元素值是null，那么結果里是不含這個元素的：

mysql> select json_merge_patch('{"a": 1}', '{"b": null}');
+---------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": null}') |
+---------------------------------------------+
| {"a": 1}                                    |
+---------------------------------------------+
1 row in set (0.04 sec)

使用這個特性可以刪除第一個參數的元素，就像JSON_REMOVE()一樣：

mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}');
+---------------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}') |
+---------------------------------------------------------+
| {"a": 1}                                                |
+---------------------------------------------------------+
1 row in set (0.04 sec)

2.11.2 JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()函數也是合并兩個或多個JSON，但是和JSON_MERGE_PATCH()不同在于第二個參數的元素并不會覆蓋第一個參數的元素。

首先如果有一個參數是NULL，那么救過就是NULL。

相鄰的數組合并成一個數組：

mysql> select json_merge_preserve('[1,2]', '["a", "b"]');
+--------------------------------------------+
| json_merge_preserve('[1,2]', '["a", "b"]') |
+--------------------------------------------+
| [1, 2, "a", "b"]                           |
+--------------------------------------------+
1 row in set (0.04 sec)

相鄰的兩個object合并成一個object：

mysql> select json_merge_preserve('{"a": [1]}', '{"b": 1}');
+-----------------------------------------------+
| json_merge_preserve('{"a": [1]}', '{"b": 1}') |
+-----------------------------------------------+
| {"a": [1], "b": 1}                            |
+-----------------------------------------------+
1 row in set (0.04 sec)

標量值會包裝成數組，然后按照數組的方式合并：

mysql> select json_merge_preserve('{"a": 1}', '{"a": 2}');
+---------------------------------------------+
| json_merge_preserve('{"a": 1}', '{"a": 2}') |
+---------------------------------------------+
| {"a": [1, 2]}                               |
+---------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_preserve('{"a": 1}', '{"a": [2]}');
+-----------------------------------------------+
| json_merge_preserve('{"a": 1}', '{"a": [2]}') |
+-----------------------------------------------+
| {"a": [1, 2]}                                 |
+-----------------------------------------------+
1 row in set (0.04 sec)mysql> select json_merge_preserve('{"a": 1, "b": 3}', '{"a": 2, "d": 4}');
+-------------------------------------------------------------+
| json_merge_preserve('{"a": 1, "b": 3}', '{"a": 2, "d": 4}') |
+-------------------------------------------------------------+
| {"a": [1, 2], "b": 3, "d": 4}                               |
+-------------------------------------------------------------+
1 row in set (0.04 sec)

相鄰的數組和object合并，先將object包裝成一個數組，然后兩個數組合并：

mysql> select json_merge_preserve('[1, 2]', '{"id": 47}');
+---------------------------------------------+
| json_merge_preserve('[1, 2]', '{"id": 47}') |
+---------------------------------------------+
| [1, 2, {"id": 47}]                          |
+---------------------------------------------+
1 row in set (0.04 sec)

2.12 其它

MySQL還有很多有用的函數用于操作JSON類型，這里簡單介紹JSON_TYPE, JSON_LENGTH, 和JSON_STORAGE_SIZE等函數，其余函數可以參考MySQL文檔：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

JSON_TYPE返回JSON元素的類型，包括object, array, null以及其他的標量類型：

mysql> select json_type('{}'), json_type('[]'), json_type('true'), json_type(null), json_type('"a"');
+-----------------+-----------------+-------------------+-----------------+------------------+
| json_type('{}') | json_type('[]') | json_type('true') | json_type(null) | json_type('"a"') |
+-----------------+-----------------+-------------------+-----------------+------------------+
| OBJECT          | ARRAY           | BOOLEAN           | NULL            | STRING           |
+-----------------+-----------------+-------------------+-----------------+------------------+
1 row in set (0.04 sec)

JSON_LENGTH函數返回元素的個數：

mysql> select json_length('[1]'), json_length('{"a": [1,2]}', '$.a'), json_length('{"a": [1,2]}', '$.a[1]');
+--------------------+------------------------------------+---------------------------------------+
| json_length('[1]') | json_length('{"a": [1,2]}', '$.a') | json_length('{"a": [1,2]}', '$.a[1]') |
+--------------------+------------------------------------+---------------------------------------+
|                  1 |                                  2 |                                     1 |
+--------------------+------------------------------------+---------------------------------------+
1 row in set (0.03 sec)

JSON_STORAGE_SIZE函數返回JSON數據所占用的字節數：

mysql> select json_storage_size('{"a": true}'), char_length('{"a": true}');
+----------------------------------+----------------------------+
| json_storage_size('{"a": true}') | char_length('{"a": true}') |
+----------------------------------+----------------------------+
|                               13 |                         11 |
+----------------------------------+----------------------------+
1 row in set (0.04 sec)

JSON類型所占用的空間大致和LONGBLOB或LONGTEXT一樣。不過由于有一些元數據，可能會稍微大一些。

3. JSON的高級用法

前面我們介紹了MySQL中JSON類型的一些基本操作，MySQL中對JSON類型的支持還可以有一些更高級的玩法，比如關系型數據與JSON數據的相互轉換，甚至可以把MySQL當做一個文檔型數據庫來使用。

3.1 關系型數據轉JSON

MySQL中有一些函數支持將關系型數據轉換成JSON數據：

• JSON_OBJECT
• JSON_ARRAY
• JSON_OBJECTAGG
• JSON_ARRAYAGG

JSON_OBJECT函數可以將多個鍵值對拼裝成一個object：

mysql> select json_pretty(json_object("a", 1, "b", true, "null", null))\G
*************************** 1. row ***************************
json_pretty(json_object("a", 1, "b", true, "null", null)): {"a": 1,"b": true,"null": null
}
1 row in set (0.04 sec)

如果鍵值對數量不對的話會報錯：

mysql> select json_pretty(json_object("a", 1, "b", true, "null"))\G
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'

JSON_ARRAY函數將所有的參數合并成一個數組：

mysql> select json_array(1,1,"a",null,true,curtime());
+--------------------------------------------+
| json_array(1,1,"a",null,true,curtime())    |
+--------------------------------------------+
| [1, 1, "a", null, true, "17:38:39.000000"] |
+--------------------------------------------+
1 row in set (0.04 sec)

兩個函數組合使用就可以構建一個復雜的JSON數據了：

mysql> select json_pretty(json_object('example', 'a complex example', 'user', json_object('name', 'valineliu', 'tags', json_array(1,2)), 'books', json_array('a','b'))) as r\G
*************************** 1. row ***************************
r: {"user": {"name": "valineliu","tags": [1,2]},"books": ["a","b"],"example": "a complex example"
}
1 row in set (0.04 sec)

JSON_OBJECTAGG和JSON_ARRAYAGG兩個函數可以通過GROUP BY返回更高級的數據。

JSON_OBJECTAGG可以指定一個關系型表的兩個字段作為構建JSON的鍵值對。

比如一個表是這樣的：

mysql> select * from r_videos;
+----+------+---------+
| id | size | title   |
+----+------+---------+
|  1 |  100 | video 1 |
|  2 |  200 | video 2 |
|  3 |  300 | video 3 |
+----+------+---------+
3 rows in set (0.03 sec)

指定title為key，size為value構建一個JSON：

mysql> select json_pretty(json_objectagg(title, size)) as size from r_videos\G
*************************** 1. row ***************************
size: {"video 1": 100,"video 2": 200,"video 3": 300
}
1 row in set (0.04 sec)

JSON_ARRAYAGG函數可以將一對多的關系轉換成一個JSON數組。比如下面的表：

mysql> select * from r_videos;
+----+---------+---------+------+
| id | user_id | title   | size |
+----+---------+---------+------+
|  1 |     100 | title 1 | 1000 |
|  2 |     100 | title 2 | 2000 |
|  3 |     200 | title 3 | 3000 |
|  4 |     300 | title 4 | 4000 |
|  5 |     300 | title 5 | 5000 |
|  6 |     300 | title 6 | 6000 |
+----+---------+---------+------+
6 rows in set (0.03 sec)

下面的語句可以將這個關系型表轉換成一個user_id為key，title和size構成的object數組為value的JSON：

mysql> select json_pretty(json_object('user_id', user_id, 'videos', json_arrayagg(json_object('title', title, 'size', size)))) as videos from r_videos group by user_id\G
*************************** 1. row ***************************
videos: {"videos": [{"size": 1000,"title": "title 1"},{"size": 2000,"title": "title 2"}],"user_id": 100
}
*************************** 2. row ***************************
videos: {"videos": [{"size": 3000,"title": "title 3"}],"user_id": 200
}
*************************** 3. row ***************************
videos: {"videos": [{"size": 4000,"title": "title 4"},{"size": 5000,"title": "title 5"},{"size": 6000,"title": "title 6"}],"user_id": 300
}
3 rows in set (0.04 sec)

3.2 JSON轉表格

可以使用JSON_TABLE函數將一個JSON轉換成關系型數據。

先看一個簡單的例子：

mysql> select * from json_table('{"null": null, "title": "hello json", "size": 1}', '$' columns(title varchar(32) path '$.title' error on error, size int path '$.size')) as jt;
+------------+------+
| title      | size |
+------------+------+
| hello json |    1 |
+------------+------+
1 row in set (0.03 sec)

JSON_TABLE函數有兩個參數，第一個參數是一個JSON文檔，第二個參數就是列定義。

列定義前面的JSONPath指定了開始解析的位置，列定義里每一個列都指定了列名、類型以及要獲取值的JSONPath，多個列定義用,分割。

下面的例子將一個含有數組的JSON展開成一個一對多的關系型數據。

原始數據如下：

mysql> select id, json_pretty(ext) as ext from videos\G
*************************** 1. row ***************************id: 1
ext: {"vid": "vid1","logo": true,"tags": ["news","china"],"title": "title1","protected": false
}
*************************** 2. row ***************************id: 2
ext: {"vid": "vid2","logo": false,"tags": ["tech"],"title": "title2","protected": true
}
*************************** 3. row ***************************id: 3
ext: {"vid": "vid3","logo": false,"tags": ["food","asian","china"],"title": "title3","protected": true
}
3 rows in set (0.03 sec)

其中每一行數據中都有一個數組類型的tags。現在想把這個一對多的數據展開成多行數據：

mysql> select v.id, jt.* from videos v, json_table(v.ext, '$' columns (title varchar(32) path '$.title', nested path '$.tags[*]' columns (tag varchar(32) path '$'))) as jt;
+----+--------+-------+
| id | title  | tag   |
+----+--------+-------+
|  1 | title1 | news  |
|  1 | title1 | china |
|  2 | title2 | tech  |
|  3 | title3 | food  |
|  3 | title3 | asian |
|  3 | title3 | china |
+----+--------+-------+
6 rows in set (0.04 sec)

這里對于tag字段的定義使用了nested path。

3.3 通過JSON將MySQL作為文檔型數據庫

通過MySQL Shell甚至可以將MySQL當做一個文檔型數據庫。

可以參考https://dev.mysql.com/doc/mysql-shell/8.0/en/ 了解更多關于MySQL Shell的信息。

本質上還是使用表來存儲數據的，比如下面的表：

CREATE TABLE `MyCollection` ( `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote( json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL, PRIMARY KEY (`_id`), CONSTRAINT `$val_strict_2190F99D7C6BE98E2C1EFE4E110B46A3D43C9751` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

通過mysqlsh連接數據庫之后，通過X DevAPI可以像操作MongoDB一樣操作上面這個表：

MyCollection = session.getDefaultSchema().getCollection('MyCollection')

X DevAPI中的Collection類定義了add, find, modify, remove等函數支持增刪改查。

添加數據：

MyCollection.add({"document": "one"}).add([{"document": "two"}, {"document": "three"}]).add(mysqlx.expr('{"document": "four"}'))

像MongoDB一樣find：

MyCollection.find()
{"_id": "000060d5ab750000000000000012","document": "one"
}
{"_id": "000060d5ab750000000000000013","document": "two"
}
{"_id": "000060d5ab750000000000000014","document": "three"
}
{"_id": "000060d5ab750000000000000015","document": "four"
}
4 documents in set (0.0007 sec)

這里僅僅是簡單介紹了一下有意思的玩法，更多關于X DevAPI的信息，請參考https://dev.mysql.com/doc/x-devapi-userguide/en/

4. orm對JSON的支持

上面都是從mysql client的角度來使用JSON類型的，在我們的程序中使用更多的還是各種orm。

這里簡單介紹一下orm對于MySQL JSON類型的支持，由于個人原因，這里僅僅列出go語言的兩個orm：xorm和gorm。

不過好像對JSON的支持都不是很豐富。

其余orm以及其余語言參考各自的文檔。

4.1 xorm

目前我還沒有發現xorm支持JSON類型，也可能是我漏掉了，如果哪位大佬知道的話，感謝補充~

4.2 gorm

gorm通過額外的包datatypes來支持JSON類型：

import "gorm.io/datatypes"type User struct {gorm.ModelName       stringAttributes datatypes.JSON
}db.Create(&User{Name:       "jinzhu",Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}// Query user having a role field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("role"))
// Query user having orgs->orga field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))

但datatypes目前對JSON類型的支持還是很弱，僅僅支持查找與搜索，更加有用的更新還是沒有的：

import "gorm.io/datatypes"type UserWithJSON struct {gorm.ModelName       stringAttributes datatypes.JSON
}DB.Create(&User{Name:       "json-1",Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}// Check JSON has keys
datatypes.JSONQuery("attributes").HasKey(value, keys...)db.Find(&user, datatypes.JSONQuery("attributes").HasKey("role"))
db.Find(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))
// MySQL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.role') IS NOT NULL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.orgs.orga') IS NOT NULL// PostgreSQL
// SELECT * FROM "user" WHERE "attributes"::jsonb ? 'role'
// SELECT * FROM "user" WHERE "attributes"::jsonb -> 'orgs' ? 'orga'// Check JSON extract value from keys equal to value
datatypes.JSONQuery("attributes").Equals(value, keys...)DB.First(&user, datatypes.JSONQuery("attributes").Equals("jinzhu", "name"))
DB.First(&user, datatypes.JSONQuery("attributes").Equals("orgb", "orgs", "orgb"))
// MySQL
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.name') = "jinzhu"
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.orgs.orgb') = "orgb"// PostgreSQL
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'name') = 'jinzhu'
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'orgs','orgb') = 'orgb'

參考https://gorm.io/docs/v2_release_note.html#DataTypes-JSON-as-example , https://github.com/go-gorm/datatypes

5. 一些思考

目前來看，orm對于JSON的支持還不是很豐富，而上面的絕大多數篇幅都是mysql客戶端中操作JSON字段的方法，在我們的程序中通過orm操作JSON字段還不是很方便。

在使用JSON類型的時候，我更多地是把里面的元素當做一個候選字段。

比如今天來了一個需求需要添加一個字段，我會將這個字段添加到JSON類型字段中，滿足可以將數據保存在一條記錄中增加數據局部性，而不用在別的地方獲取這些數據。

隨著產品的進化與需求的變更，之前添加的字段有的變得沒用了，那么就可以后續刪除這個元素；而有的字段由于變得更加重要，可以把它提升為一個關系型的字段。

到底是放在JSON中還是添加一個字段，這個就看具體的使用了。如果這個字段經常使用，讀取寫入還有搜索，那么添加到一個新的字段還是比較方便的。不過好像添加為一個虛擬字段也是很有用。

當JSON變得巨大的時候，沒準可以考慮使用真正的文檔型數據庫了，比如MongoDB。