This section documents utility functions that act on JSON values, or strings that can be parsed as JSON values. 本节介绍作用于JSON值的实用程序函数，或者可以解析为JSON值的字符串。JSON_PRETTY() prints out a JSON value in a format that is easy to read. JSON_PRETTY()以易于阅读的格式打印JSON值。JSON_STORAGE_SIZE() and JSON_STORAGE_FREE() show, respectively, the amount of storage space used by a given JSON value and the amount of space remaining in a JSON column following a partial update.JSON_STORAGE_SIZE()和JSON_STORAGE_FREE()分别显示给定JSON值使用的存储空间量和部分更新后JSON列中剩余的空间量。
Provides pretty-printing of JSON values similar to that implemented in PHP and by other languages and database systems. 提供类似于PHP和其他语言和数据库系统实现的JSON值的漂亮打印。The value supplied must be a JSON value or a valid string representation of a JSON value. 提供的值必须是JSON值或JSON值的有效字符串表示形式。Extraneous whitespaces and newlines present in this value have no effect on the output. 此值中存在的无关空格和换行符对输出没有影响。For a 对于NULL value, the function returns NULL. NULL值，函数返回NULL。If the value is not a JSON document, or if it cannot be parsed as one, the function fails with an error.如果该值不是JSON文档，或者无法将其解析为JSON文档，则函数将失败并出现错误。
Formatting of the output from this function adheres to the following rules:此函数的输出格式遵循以下规则：
Each array element or object member appears on a separate line, indented by one additional level as compared to its parent.每个数组元素或对象成员显示在单独的行上，与其父级相比，缩进了一个额外的级别。
Each level of indentation adds two leading spaces.每一级缩进添加两个前导空格。
A comma separating individual array elements or object members is printed before the newline that separates the two elements or members.分隔单个数组元素或对象成员的逗号打印在分隔两个元素或成员的换行符之前。
The key and the value of an object member are separated by a colon followed by a space ('对象成员的键和值由冒号和空格（: ').': '）分隔。
An empty object or array is printed on a single line. 空对象或数组打印在一行上。No space is printed between the opening and closing brace.在左大括号和右大括号之间不打印空格。
Special characters in string scalars and key names are escaped employing the same rules used by the 字符串标量和键名中的特殊字符使用与JSON_QUOTE() function.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" ]
For a 对于JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). JSON列值，此函数显示在使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()就地更新二进制表示形式后释放的存储空间。The argument can also be a valid JSON document or a string which can be parsed as one — either as a literal value or as the value of a user variable — in which case the function returns 0. 参数也可以是有效的JSON文档或字符串，可以将其解析为1，或者作为文本值，或者作为用户变量的值，在这种情况下，函数返回0。It returns a positive, nonzero value if the argument is a 如果参数是一个JSON column value which has been updated as described previously, such that its binary representation takes up less space than it did prior to the update. JSON列值，它返回一个正的非零值，该列值已按前面所述进行了更新，因此它的二进制表示比更新之前占用的空间少。For a 对于已更新的JSON column which has been updated such that its binary representation is the same as or larger than before, or if the update was not able to take advantage of a partial update, it returns 0; it returns NULL if the argument is NULL.JSON列，其二进制表示形式与以前相同或更大，或者如果更新无法利用部分更新，则返回0；如果参数为NULL，则返回NULL。
If 如果json_val is not NULL, and neither is a valid JSON document nor can be successfully parsed as one, an error results.json_val不为NULL，并且既不是有效的json文档，也不能成功地将其解析为json文档，则会产生错误。
In this example, we create a table containing a 在本例中，我们创建一个包含JSON column, then insert a row containing a JSON object: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)
Now we update the column value using 现在我们使用JSON_SET() such that a partial update can be performed; in this case, we replace the value pointed to by the c key (the array [true, false]) with one that takes up less space (the integer 1):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)
The effects of successive partial updates on this free space are cumulative, as shown in this example using 连续部分更新对此可用空间的影响是累积的，如本例所示，使用JSON_SET() to reduce the space taken up by the value having key b (and making no other changes):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)
Updating the column without using 在不使用JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0, as shown here: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)
Partial updates of JSON documents can be performed only on column values. 只能对列值执行JSON文档的部分更新。For a user variable that stores a JSON value, the value is always completely replaced, even when the update is performed using 对于存储JSON值的用户变量，即使使用JSON_SET():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)
For a JSON literal, this function always returns 0:对于JSON文本，此函数始终返回0:
mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
| 0 |
+------+
1 row in set (0.00 sec)This function returns the number of bytes used to store the binary representation of a JSON document. When the argument is a 此函数返回用于存储JSON文档二进制表示形式的字节数。当参数是一个JSON column, this is the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards. JSON列时，这是用于存储JSON文档的空间，当它被插入到列中时，在随后对它执行任何部分更新之前。json_val must be a valid JSON document or a string which can be parsed as one. json_val必须是有效的json文档或可以解析为一个文档的字符串。In the case where it is string, the function returns the amount of storage space in the JSON binary representation that is created by parsing the string as JSON and converting it to binary. 在字符串的情况下，函数返回JSON二进制表示形式中的存储空间量，该表示形式是通过将字符串解析为JSON并将其转换为二进制来创建的。It returns 如果参数为NULL if the argument is NULL.NULL，则返回NULL。
An error results when 当json_val is not NULL, and is not—or cannot be successfully parsed as—a JSON document.json_val不为NULL，并且不是或无法成功解析为JSON文档时，会产生一个错误。
To illustrate this function's behavior when used with a 为了说明此函数在使用JSON列作为参数时的行为，我们创建了一个名为JSON column as its argument, we create a table named jtable containing a JSON column jcol, insert a JSON value into the table, then obtain the storage space used by this column with JSON_STORAGE_SIZE(), as shown here: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)
According to the output of 根据JSON_STORAGE_SIZE(), the JSON document inserted into the column takes up 47 bytes. JSON_STORAGE_SIZE()的输出，插入到列中的JSON文档占用47字节。We also checked the amount of space freed by any previous partial updates of the column using 我们还使用JSON_STORAGE_FREE(); since no updates have yet been performed, this is 0, as expected.JSON_STORAGE_FREE()检查了以前部分更新列所释放的空间量；由于尚未执行任何更新，因此这是0，如预期的那样。
Next we perform an 接下来，我们对表执行一个更新，该更新将导致对UPDATE on the table that should result in a partial update of the document stored in jcol, and then test the result as shown here: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)
The value returned by 在前面的查询中，JSON_STORAGE_FREE() in the previous query indicates that a partial update of the JSON document was performed, and that this freed 3 bytes of space used to store it. JSON_STORAGE_FREE()返回的值表示对JSON文档执行了部分更新，并释放了3个字节的存储空间。The result returned by JSON_STORAGE_SIZE() is unchanged by the partial update.JSON_STORAGE_SIZE()返回的结果不会因部分更新而改变。
Partial updates are supported for updates using 使用JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). JSON_SET()、JSON_REPLACE()或JSON_REMOVE()的更新支持部分更新。The direct assignment of a value to a 将值直接赋值给JSON列不能部分更新；更新之后，JSON column cannot be partially updated; following such an update, JSON_STORAGE_SIZE() always shows the storage used for the newly-set value:JSON_STORAGE_SIZE()始终显示用于新设置值的存储：
mysql>UPDATE jtablemysql>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)
A JSON user variable cannot be partially updated. 无法部分更新JSON用户变量。This means that this function always shows the space currently used to store a JSON document in a user variable:这意味着此函数始终显示当前用于在用户变量中存储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)
For a JSON literal, this function always returns the current storage space used:对于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)