MySQL 9.0 发行说明
本节介绍对 JSON 值或可解析为 JSON 值的字符串进行操作的工具函数。 JSON_PRETTY() 以易于阅读的格式打印 JSON 值。 JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 分别显示给定 JSON 值使用的存储空间量以及部分更新后 JSON 列中剩余的空间量。
提供类似于 PHP 以及其他语言和数据库系统中实现的 JSON 值的漂亮打印。提供的值必须是 JSON 值或 JSON 值的有效字符串表示形式。此值中存在的额外空格和换行符对输出没有影响。对于
NULL值，该函数返回NULL。如果该值不是 JSON 文档，或者无法将其解析为 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列值，此函数显示在使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()就地更新后，其二进制表示形式中释放了多少存储空间。该参数也可以是有效的 JSON 文档或可以解析为 JSON 文档的字符串（作为文字值或用户变量的值），在这种情况下，该函数返回 0。如果该参数是一个JSON列值，并且已经按照前面所述进行了更新，使得其二进制表示形式占用的空间比更新之前少，则该函数返回一个正的非零值。对于已更新的JSON列，如果其二进制表示形式与更新前相同或更大，或者更新无法利用部分更新，则该函数返回 0；如果该参数为NULL，则返回NULL。如果
json_val不是NULL，并且既不是有效的 JSON 文档，也无法成功解析为 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 文档的二进制表示形式的字节数。当参数为
JSON列时，这是将 JSON 文档插入列中时使用的空间，在对其执行任何部分更新之前。json_val必须是有效的 JSON 文档或可以解析为 JSON 文档的字符串。如果是字符串，则该函数返回 JSON 二进制表示形式中使用的存储空间量，该空间是通过将字符串解析为 JSON 并将其转换为二进制来创建的。如果参数为NULL，则返回NULL。当
json_val不是NULL，并且不是 JSON 文档或无法成功解析为 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()进行更新时，支持部分更新。无法对将值直接赋值给JSON列的操作进行部分更新；在此类更新之后，JSON_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)