• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
    •
    位置： MySQL 8 中文手册 -> MySQL 函数和运算符 -> JSON函数

    JSON实用程序功能

    本节介绍了作用于JSON值的实用程序函数，或可以解析为JSON值的字符串。JSON_PRETTY()以易于阅读的格式输出JSON值。JSON_STORAGE_SIZE()和JSON_STORAGE_FREE()分别显示给定JSON值使用的存储空间量和JSON部分更新后列中剩余的空间量。

    • JSON_PRETTY(json_val)

      提供漂亮的JSON值打印，类似于用PHP以及其他语言和数据库系统实现的JSON值。提供的值必须是JSON值或JSON值的有效字符串表示形式。此值中存在多余的空格和换行符对输出没有影响。对于NULL值，函数返回NULL。如果该值不是JSON文档，或者无法将其解析为一个文档，则该函数将失败并显示错误。

      此函数的输出格式遵循以下规则：

      • 每个数组元素或对象成员都显示在单独的行上，与其父级相比缩进了一个附加级别。
      • 每个缩进级别都会添加两个前导空格。
      • 在分隔两个元素或成员的换行符之前，将逗号分隔单个数组元素或对象成员打印出来。
      • 对象成员的键和值用冒号分隔，后跟一个空格（':'）。
      • 空对象或数组将打印在一行上。左括号和右括号之间没有印刷空间。
      • 字符串标量和键名中的特殊字符使用该JSON_QUOTE()函数使用的相同规则进行转义。
      mysql> SELECT JSON_PRETTY('123'); # scalar
+--------------------	+
| JSON_PRETTY('123') 	|
+--------------------	+
| 123                	|
+--------------------	+

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------	+
| JSON_PRETTY("[1,3,5]") 	|
+------------------------	+
| [
  1,
  3,
  5
]      |
+------------------------	+

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------	+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') 	|
+---------------------------------------------	+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------	+

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]

    • JSON_STORAGE_FREE(json_val)

      对于JSON列值，该功能可显示有多少存储空间，在其二进制表示被释放它是使用到位更新后JSON_SET()，JSON_REPLACE()或JSON_REMOVE()。该参数也可以是有效的JSON文档或可以解析为1的字符串（作为文字值或作为用户变量的值），在这种情况下，该函数返回0。如果返回0，则返回正非零值。参数是JSON已如前所述更新的列值，因此其二进制表示所占用的空间比更新前要少。为一个JSON已更新的列，使其二进制表示形式与以前相同或更大，或者如果更新不能利用部分更新，则返回0；否则，返回0。NULL如果参数为，则返回NULL。

      如果json_val不是NULL，并且不是有效的JSON文档，也无法成功将其解析为一个文档，则会导致错误。

      在此示例中，我们创建一个包含JSON列的表，然后插入包含JSON对象的行：

      mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO jtable VALUES
->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM jtable;
+----------------------------------------------	+
| jcol                                         	|
+----------------------------------------------	+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} 	|
+----------------------------------------------	+
1 row in set (0.00 sec)

      现在，我们使用来更新列值JSON_SET()，从而可以执行部分更新。在这种情况下，我们用占用更少空间（整数）的c键替换键（数组[true, false]）指向的值1：

      mysql> UPDATE jtable
->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM jtable;
+--------------------------------	+
| jcol                           	|
+--------------------------------	+
| {"a": 10, "b": "wxyz", "c": 1} 	|
+--------------------------------	+
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------	+
| JSON_STORAGE_FREE(jcol) 	|
+-------------------------	+
|                      14 	|
+-------------------------	+
1 row in set (0.00 sec)

      连续部分更新对该可用空间的影响是累积的，如本示例所示，JSON_SET()用于减少具有键的值b（并且不进行其他更改）所占用的空间：

      mysql> UPDATE jtable
->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------	+
| JSON_STORAGE_FREE(jcol) 	|
+-------------------------	+
|                      16 	|
+-------------------------	+
1 row in set (0.00 sec)

      在不使用JSON_SET()，JSON_REPLACE()或的情况下更新列，JSON_REMOVE()意味着优化器无法就地执行更新。在这种情况下，JSON_STORAGE_FREE()返回0，如下所示：

      mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------	+
| JSON_STORAGE_FREE(jcol) 	|
+-------------------------	+
|                       0 	|
+-------------------------	+
1 row in set (0.00 sec)

      JSON文档的部分更新只能在列值上执行。对于存储JSON值的用户变量，即使使用JSON_SET()以下命令执行更新，也始终会完全替换该值：

      mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------	+------	+
| @j                               	| Free 	|
+----------------------------------	+------	+
| {"a": 10, "b": "wxyz", "c": "1"} 	|    0 	|
+----------------------------------	+------	+
1 row in set (0.00 sec)

      对于JSON文字，此函数始终返回0：

      mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------	+
| Free 	|
+------	+
|    0 	|
+------	+
1 row in set (0.00 sec)

    • JSON_STORAGE_SIZE(json_val)

      此函数返回用于存储JSON文档的二进制表示形式的字节数。当参数是JSON列时，这是用于存储JSON文档（插入到列中之后）的空间，此之后可能对其进行任何部分更新。json_val必须是有效的JSON文档或可以解析为一个字符串。如果是字符串，该函数将通过将字符串解析为JSON并将其转换为二进制来创建JSON二进制表示形式，以返回存储空间量。NULL如果参数为，则返回NULL。

      当json_val不是NULL，并且不是或无法成功解析为JSON文档时，将导致错误。

      为了说明此函数在以JSON列作为参数时的行为，我们创建了一个jtable包含JSON列jcol的表，在表中插入JSON值，然后使用来获得该列使用的存储空间JSON_STORAGE_SIZE()，如下所示：

      mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
->     jcol,
->     JSON_STORAGE_SIZE(jcol) AS Size,
->     JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+-----------------------------------------------	+------	+------	+
| jcol                                          	| Size 	| Free 	|
+-----------------------------------------------	+------	+------	+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} 	|   47 	|    0 	|
+-----------------------------------------------	+------	+------	+
1 row in set (0.00 sec)

      根据的输出JSON_STORAGE_SIZE()，插入到该列中的JSON文档占用47个字节。我们还使用来检查列的任何先前部分更新释放的空间量JSON_STORAGE_FREE()。由于尚未执行任何更新，因此按预期该值为0。

      接下来，我们UPDATE在表格上执行，这将导致存储在中的文档的部分更新jcol，然后测试结果，如下所示：

      mysql> UPDATE jtable SET jcol = 
->     JSON_SET(jcol, "$.b", "a");
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
->     jcol,
->     JSON_STORAGE_SIZE(jcol) AS Size,
->     JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+--------------------------------------------	+------	+------	+
| jcol                                       	| Size 	| Free 	|
+--------------------------------------------	+------	+------	+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} 	|   47 	|    3 	|
+--------------------------------------------	+------	+------	+
1 row in set (0.00 sec)

      JSON_STORAGE_FREE()上一个查询中返回的值指示已执行JSON文档的部分更新，并且此操作释放了3个字节的存储空间。JSON_STORAGE_SIZE()部分更新未更改所返回的结果。

      对于使用，或进行的更新JSON_SET()，支持部分更新。将值直接分配给列不能部分更新。进行此类更新后，始终会显示用于新设置值的存储空间：JSON_REPLACE()JSON_REMOVE()JSONJSON_STORAGE_SIZE()

      mysql> UPDATE jtable
mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
->     jcol,
->     JSON_STORAGE_SIZE(jcol) AS Size,
->     JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
+------------------------------------------------	+------	+------	+
| jcol                                           	| Size 	| Free 	|
+------------------------------------------------	+------	+------	+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} 	|   56 	|    0 	|
+------------------------------------------------	+------	+------	+
1 row in set (0.00 sec)

      JSON用户变量无法部分更新。这意味着该函数将始终在用户变量中显示当前用于存储JSON文档的空间：

      mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------	+------	+
| @j                                 	| Size 	|
+------------------------------------	+------	+
| [100, "sakila", [1, 3, 5], 425.05] 	|   45 	|
+------------------------------------	+------	+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------	+------	+
| @j                               	| Size 	|
+----------------------------------	+------	+
| [100, "json", [1, 3, 5], 425.05] 	|   43 	|
+----------------------------------	+------	+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+---------------------------------------------	+------	+
| @j                                          	| Size 	|
+---------------------------------------------	+------	+
| [100, "json", [[10, 20, 30], 3, 5], 425.05] 	|   56 	|
+---------------------------------------------	+------	+
1 row in set (0.00 sec)

      对于JSON文字，此函数始终返回当前使用的存储空间：

      mysql> SELECT
->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----	+----	+----	+----	+
| A  	| B  	| C  	| D  	|
+----	+----	+----	+----	+
| 45 	| 44 	| 47 	| 56 	|
+----	+----	+----	+----	+
1 row in set (0.00 sec)

    上篇：JSON模式验证功能