Mysql的JSON数据类型

https://dev.mysql.com/doc/refman/8.0/en/json.html

11.5 The JSON Data Type

Mysql支持服从RFC 7159标准的JSON文档存取。JSON类型的数据是已JSON-format strings的格式存储在string类型的字段中。在存储时，mysql会验证json格式，格式不符则报error；另外，会优化存储格式——JSON文档在存储到JSON字段时，会转换为中间格式，以便快速读写，当服务端必须读取以二进制形式存储的JSON值时，则该值在存储时不能存为text格式的，这样的二进制形式存储的JSON数据支持嵌套查值（即：在JSON结构中按照key或array index在subObject中进行嵌套查值），并且在该JSON字段未完全写入/读取前就能查值。

为支持HTTP Patch方法，Mysql 8.0新增了JSON_MERGE_PATCH() 函数以支持 RFC 7396标准的 JSON Merge Patch格式。

存储JSON类型数据的空间分配类似于 LONGBLOB 或 LONGTEXT类型的数据。需注意的是，Mysql中，JSON格式数据的最大值受系统的max_allowed_packet所限，默认是64MB，可用JSON_STORAGE_SIZE()函数自定义。

MySQL 8.0.13之前的版本中，JSON字段不能有非空的默认值。

有一系列SQL函数（“JSON Functions”）支持JSON类型数据的操作，如ceration, manipulation, searching等。

另有一系列函数（“Spatial GeoJSON Functions”）支持GeoJson类型数据的操作（GeoJson是用于描述地理空间信息的数据格式，其语法规范符合 JSON 格式）。

类似于其他二进制类型，JSON类型字段默认没有索引，但可以为其生成索引字段（参见Indexing a Generated Column to Provide a JSON Column Index）。MySQL 8.0.17之后的版本的innoDB存储引擎支持对JSON arrays的Multi-Valued Indexes。

---

在Mysql8.0中，可以对JSON类型字段进行部分更新，但需满足如下条件：

• 待更新的字段声明为JSON格式；
• UPDATE语句使用JSON_SET(), JSON_REPLACE(), JSON_REMOVE() 三个函数中的任意一个进行该字段的更新。对该字段进行直接赋值（如：UPDATE mytable SET jcol = ‘{“a”: 10, “b”: 25}’）则不能进行部分更新。只要用的是上述三个函数中的任意一个，可用一个UPDATE语句部分更新多个JSON类型的字段；
• 可以用同一个JSON类型字段的值为该字段的另一个JSON类型记录赋值，但如果两个JSON字段不是同一个字段（如：UPDATE mytable SET jcol1 = JSON_SET(jcol2, ‘$.a’, 100)），则不能部分更新。对相同JSON类型字段的UPDATE语句可以嵌套进行部分更新；
• 所有的更新只用新的JSON array或JSON Object部分替换已存在的部分值，并不会为父JSON array或JSON Object增加新的JSON元素；
• 更新后的JSON类型值的存储空间大小不能超过更新前的值，但如果前几次UPDATE空出的空间比这次UPDATE新增的空间大时例外，可用JSON_STORAGE_FREE()函数查看更新空出的存储空间大小。

部分更新的二进制类型日志可以用compact format存储以节省空间，可用 binlog_row_value_options启用该功能。

---

JSON类型字段存储的JSON arrays或JSON Objects的标量值可以是strings类型或numbers类型的，还可以存储literal的JSON null、JSON boolean true/false，以及date/time/datetime时间类型的值。JSON objects中keys必须是string类型的。

JSON array的元素和JSON Object的值允许嵌套。

可以用Mysql提供的函数构建JSON类型的值，或者将非JSON类型的值转换为JSON类型。

Mysql中，JSON类型的值是以string类型写入的（支持utf8mb4和utf8mb4_bin字符集，ascii或utf8是utf8mb4的子集），Mysql对JSON字符串进行解析，解析错误时会报错。

JSON类型的数据可用是某些函数的返回值，如对于JSON array，有：

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT()函数可返回JSON Object：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE_PRESERVE()可用合并多个JSON类型值：

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

(MySQL 8.0.3之后的版本支持JSON_MERGE_PATCH()函数)

可将JSON类型的值赋值给用户自定义的变量：

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

但，用户自定义的变量不能是JSON类型的，例如@j虽然有JSON类型的值，但是字符串类型，而非JSON类型的。可使用JSON_OBJECT()讲字符串转变为JSON类型的。

---

特殊字符:（注意：关闭NO_BACKSLASH_ESCAPES server SQL mode）

如果JSON Object的值中有特殊字符，例如对于

mysql> CREATE TABLE facts (sentence JSON);

其键值对的值是：mascot: The MySQL mascot is a dolphin named “Sakila”。

可以用Mysql的JSON_OBJECT()函数对该值进行封装,特殊字符前用转义字符\：

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

以防万一，最好连用两个转义字符\\：

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

上例中，要用mascot作为key，可以使用操作符->：

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

注意：取到的值中仍然有转义字符\和引号。如果不想要最外层的引号或空白字符，可以使用操作符->>：

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

---

Normalization, Merging, and Autowrapping of JSON Values

Mysql在解析JSON类型数据时，从左到右读取字符，如果遇到重复的key，则抛弃，即：只保存第一次读到的key及其值，如下所示：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

另外，Mysql在解析JSON类型数据时，还会自动去除多余的空白字符，并且对JSON Object的元素按照key进行排序。

---

Merging JSON Values

MySQL 8.0.3之后版本中有两个合并函数：JSON_MERGE_PRESERVE() （即MySQL 8.0.3之前版本中的JSON_MERGE()函数）和 JSON_MERGE_PATCH()，这两个函数对重复key的处理方式不同： JSON_MERGE_PRESERVE() 返回的是首次出现的键值对，而JSON_MERGE_PATCH()返回的是最后出现的键值对。

​ Merging arrays

当将多个JSON array合并成一个时，JSON_MERGE_PRESERVE()函数将其他JSON array的键值对拼接到第一个JSON Array的末尾；而JSON_MERGE_PATCH()函数则是将各个JSON Array的元素从头开始分别取出来，拼接成一个新的JSON Array，如果遇到多个键值对有相同的key时，取最后一对键值对。如下所示：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

​ Multiple objects

当将多个JSON Object合并成一个时，JSON_MERGE_PRESERVE()函数如果遇到重复的key，则将所有相同key对应的值拼接为一个数组；而JSON_MERGE_PATCH()函数则是只保留重复键值对的最后一对键值对的值。如下所示：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    ->   JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

---

Searching and Modifying JSON Values

JSON path表达式可用于检索JSON类型数据，Path表达式可以很方便地用于提取JSON类型数据的部分信息或修改JSON类型数据。如下所示，可以很方便的取到key名为name的值：

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Path语法以$符号开头，表示要处理的JSON数据，还可以使用一些通配符之类的。