更新时间:2026-10-21 GMT+08:00
分享

JSON函数

JSON函数差异说明:

  • 对于JSON函数和其他字符入参函数,如果输入中包含转义字符,默认情况下会与MySQL有一定差异。如果需要实现与MySQL的兼容,需要开启转义符开关(在GUC参数m_format_behavior_compat_options中添加enable_escape_string配置选项),但在\0和\uxxxx的场景与MySQL依然存在差异。
  • 当JSON函数的入参的字符集不一致时,MySQL均会将参数转换为utf8mb4字符集进行处理。GaussDB对于不同字符集的入参首先需要进行字符集合并推导,如果推导失败则报错。
    -- GaussDB行为
    m_db=# select json_array(_latin1 'b', _gbk 'a');
    ERROR:  Collation mismatch between collations "latin1_swedish_ci" and "gbk_chinese_ci".
    LINE 1: select json_array(_latin1 'b', _gbk 'a');
                                                ^
    -- MySQL行为
    mysql> select json_array(_latin1 'b', _gbk 'a');
    +-----------------------------------+
    | json_array(_latin1 'b', _gbk 'a') |
    +-----------------------------------+
    | ["b", "a"]                        |
    +-----------------------------------+
    1 row in set (0.00 sec)
表1 JSON函数列表

函数名

与MySQL的差异

JSON_APPEND()

-

JSON_ARRAY()

-

JSON_ARRAY_APPEND()

-

JSON_ARRAY_INSERT()

-

JSON_CONTAINS()

-

JSON_CONTAINS_PATH()

-

JSON_DEPTH()

-

JSON_EXTRACT()

-

JSON_INSERT()

-

JSON_KEYS()

-

JSON_LENGTH()

-

JSON_MERGE()

-

JSON_MERGE_PATCH()

-

JSON_MERGE_PRESERVE()

-

JSON_OBJECT()

-

JSON_QUOTE()

-

JSON_REMOVE()

-

JSON_REPLACE()

-

JSON_SEARCH()

-

JSON_SET()

-

JSON_TYPE()

-

JSON_UNQUOTE()

在转义字符中\0和\uxxxx的场景与MySQL有差异:

SELECT json_unquote('"\0"');

mysql> SELECT json_unquote('"\0"');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: "Missing a closing quotation mark in string." at position 1.

m_db=# SELECT json_unquote('"\0"');
ERROR:  invalid byte sequence for encoding "UTF8": 0x00

JSON_VALID()

-

JSON_PRETTY()

函数返回值的字符序与MySQL的存在差异。MySQL中固定为utf8mb4_bin,GaussDB中取决于GUC参数collation_connection的取值。

-- GaussDB
m_db=# SET collation_connection=gbk_bin;
SET
m_db=# SELECT COLLATION(JSON_PRETTY('{"1": "2"}'));
 collation 
-----------
 gbk_bin
(1 row)

-- MySQL
mysql> SET collation_connection=gbk_bin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COLLATION(JSON_PRETTY('{"1": "2"}'));
+--------------------------------------+
| COLLATION(JSON_PRETTY('{"1": "2"}')) |
+--------------------------------------+
| utf8mb4_bin                          |
+--------------------------------------+
1 row in set (0.00 sec)

JSON_STORAGE_SIZE()

  • 由于GaussDB的json类型内部存储方式与MySQL的存在差异,该函数返回的值与MySQL存在不一致。
  • 由于GaussDB的json类型未适配部分更新功能,会实时更新json类型的存储大小。
    -- GaussDB
    m_db=# CREATE TABLE jtable (jcol JSON);
    CREATE TABLE                        ^
    m_db=# INSERT INTO jtable VALUES  ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
    INSERT 0 1
    m_db=# SELECT  jcol, JSON_STORAGE_SIZE(jcol) AS Size FROM jtable;
    jcol                      | Size
    -----------------------------------------------+------
    {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   83
    (1 row)
    ^
    m_db=# UPDATE jtable SET jcol =JSON_SET(jcol, "$.b", "a");
    UPDATE 1
    m_db=# SELECT  jcol,JSON_STORAGE_SIZE(jcol) AS Size from jtable;
    jcol                    | Size
    --------------------------------------------+------
    {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   80
    (1 row)
    
    -- MySQL
    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)
    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)

相关文档