SQL中的JSON数据类型

SQL中的JSON数据类型

MySQL支持原生 JSON 类型，使用 JSON 数据类型相较于将JSON格式的字符串存储在String型中的优势有：

  • 存储时会自动验证JSON文本；

  • 可以优化存储格式。存储在 JSON 型中的JSON文本会被转换成一个支持快速读取的文档元素，这样在使用时不需要再解析文本，并且可以直接通过键和索引访问其中的子对象而无需读取全部文本。

    • JSON 型中可存储的JSON文本的大小不会超过 mysql.ini 配置文件中设置的 max_allowed_packet 的值。

    JSON值的局部更新

    MySQL从8.0开始支持对 JSON 型中存储的数据进行局部更新，而不需要清除原有数据并写入新值。

    局部更新按照下述规则执行：

  • 数据列被声明为 JSON 型

    • UPDATE 语句使用 JSON_SET() 、 JSON_REPLACE() 、 JSON_REMOVE() 三个函数实现JSON的局部更新

    但对该列直接赋值不属于局部更新，例如： UPDATE myTable SET json1 = '{"a": 10, "b": 25}'

    局部更新操作可以实现在单个 UPDATE 语句中更新多个 JSON 列

    局部更新是针对同一列数据的操作，对不同列的操作不属于局部更新，例如： UPDATE myTable SET json1 = JSON_SET(json2, '$.a', 100)

    局部更新中可以使用上述三个函数的嵌套调用形式

  • 局部更新仅将现有JSON对象中的数组或子对象替换成新值，但不能给父对象或数组添加新元素

  • 所替换的新值不能比原值占据的存储空间更大，除非上一次更新留下了足够的存储空间

    • 创建JSON值

    JSON数组： ["abc", 10, null, true, false]

    JSON数组中可以存储数字量、字符串、null、布尔量、时间量

    JSON对象： {"key1": "value", "key2": 10}

    JSON对象中的键必须为字符串

    JSON数组和JSON对象中可以嵌套子JSON数组和对象。

    MySQL中的JSON编码格式为 CHARSET=utf8mb4 COLLATE=utf8mb4_bin 。

    使用字符串字面量创建JSON

    在MySQL中JSON值按照字符串的形式写入，在要求为JSON值的上下文中（例如将值插入 JSON 列，或调用输入参数为JSON的函数）MySQL会解析该字符串，若不符合JSON格式则报错。 

    mysql> SELECT JSON_TYPE('["abc", 1]');
+-------------------------+
| JSON_TYPE('["abc", 1]') |
+-------------------------+
| ARRAY                   |
+-------------------------+
mysql> SELECT JSON_TYPE('{"a": 1, "b": 2}');
+-------------------------------+
| JSON_TYPE('{"a": 1, "b": 2}') |
+-------------------------------+
| OBJECT                        |
+-------------------------------+
mysql> SELECT JSON_TYPE('"abc"');
+--------------------+
| JSON_TYPE('"abc"') |
+--------------------+
| STRING             |
+--------------------+
mysql> SELECT JSON_TYPE('abc');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

    使用函数创建JSON
    

    介绍三个常用的创建JSON的函数：
    

    JSON_ARRAY()函数会将传入其中的参数组成JSON数组，例如：
    

    mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2019-03-22 10:01:53.000000"] |
+----------------------------------------+

    JSON_OBJECT()会将传入的键值对转换为JSON对象，例如：
    

    mysql> SELECT JSON_OBJECT('a', 1, 'b', 2);
+-----------------------------+
| JSON_OBJECT('a', 1, 'b', 2) |
+-----------------------------+
| {"a": 1, "b": 2}            |
+-----------------------------+

    JSON_MERGE_PRESERVE()将多个JSON文本组合成一个JSON，例如：
    

    mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}') |
+-----------------------------------------------------+
| ["a", 1, {"a": 1, "b": 2}]                          |
+-----------------------------------------------------+
mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}');
+-------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}') |
+-------------------------------------------------------------+
| {"a": 1, "b": 2, "c": 3, "d": 4}                            |
+-------------------------------------------------------------+

    使用JSON
    

    将创建出的JSON值赋值给一个自定义的变量时，该JSON会被转换成字符串，因此自定义的变量属于字符串类型、而非JSON类型，例如：
    

    SET @j = JSON_OBJECT('key', 'value');

    创建的@j为字符串类型。
    

    JSON值在进行比较时会区分大小写，例如：
    

    mysql> SELECT JSON_ARRAY('X') = JSON_ARRAY('x');
+-----------------------------------+
| JSON_ARRAY('X') = JSON_ARRAY('x') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

    'x'和`'X'不相等，返回false（即0）。
    

    JSON中的null、true、false字面量必须为小写形式，例如：
    

    mysql> SELECT JSON_VALID('null'), JSON_VALID('NULL'), JSON_VALID('Null');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('NULL') | JSON_VALID('Null') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

    可以看到Null和NULL的形式是对JSON型来说非法的。
    

    但应注意，SQL中的null、true、false字面量不区分大小写。
    

    单引号''和双引号""的使用：
    

    使用JSON_OBJECT()等函数创建JSON时，字符串中出现的引号需要使用转义字符\来和标记字符串开始结束的引号作区分，否则会报错，例如：
    

    正确写法：
mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
Query OK, 1 row affected (0.22 sec)
mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named 'Sakila'."));
Query OK, 1 row affected (0.22 sec)
错误写法：
mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot2", "Our mascot is a dolphin named "Sakila"."));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"."))' at line 1

    使用JSON对象字面量创建JSON时，需要使用两个转义字符\\来指明MySQL不需要对引号进行转义，而保留其字面值，例如：
    

    两种正确写法：
mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \'Sakila\'."}');
Query OK, 1 row affected (0.07 sec)
三种错误写法：
mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column 'facts.sentence'.
mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named "Sakila"."}');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column 'facts.sentence'.
mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named 'Sakila'."}');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Sakila'."}')' at line 1

    使用->操作符，返回值会显示为带有包裹引号和转义字符的形式，例如：
    

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

    使用->>操作符，返回值会显示为无包裹引号和转义字符的形式，例如：
    

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

    JSON值的标准化、合并、和自动包装
    

    JSON值的标准化
    

    当使用JSON_OBJECT()函数创建JSON对象时，传入参数中的重复键会被忽略，即当出现重复键值对时，会对已存在键值对的值进行更新，例如：
    

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

    在使用INSERT()函数插入JSON对象时，也会忽略重复键，并更新该键对应的值，例如：
    

    mysql> CREATE TABLE t1 (c1 JSON);
mysql> INSERT INTO t1 VALUES
    -> ('{"x": 1, "x": "a"}'),
    -> ('{"x": 1, "x": "a", "x": [1, 2, 3]}');
mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": "a"}       |
| {"x": [1, 2, 3]} |
+------------------+

    （注意：在8.0.3之前版本的MySQL中，遇到重复出现的键时，不会用新值更新旧值，而仅忽略该键值对）
    

    JSON值的合并和自动包装
    

  • 使用JSON_MERGE_PRESERVE()函数合并多个JSON值时会保留重复的键
    • 

  • 使用JSON_MERGE_PATCH()函数时仅保留重复键的最后一个，即会发生JSON的标准化过程
    • 

    合并JSON数组
    

    JSON_MERGE_PRESERVE()函数会将多个数组串联
    

    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]');
+------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]') |
+------------------------------------------------------------------+
| [1, 2, "a", "b", "true", "false"]                                |
+------------------------------------------------------------------+

    JSON_MERGE_PATCH()函数仅保留传入的最后一个数组
    

    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]');
+---------------------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]') |
+---------------------------------------------------------------+
| ["true", "false"]                                             |
+---------------------------------------------------------------+

    合并JSON对象
    

    JSON_MERGE_PRESERVE()函数会将重复键对应的所有值组合成一个数组
    

    mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}');
+--------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') |
+--------------------------------------------------------------+
| {"a": [1, 10], "b": 2, "c": 3}                               |
+--------------------------------------------------------------+

    JSON_MERGE_PATCH()函数仅保留最后一个重复键对应的值
    

    mysql> SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') |
+-----------------------------------------------------------+
| {"a": 10, "b": 2, "c": 3}                                 |
+-----------------------------------------------------------+

    合并非JSON数组或对象的元素
    

    当待合并的元素既非JSON数组也非JSON对象时，会将传入的元素自动包装为长度为1的JSON数组，并按照合并数组的规则合并
    

    mysql> SELECT JSON_MERGE_PRESERVE('1', '2');
+-------------------------------+
| JSON_MERGE_PRESERVE('1', '2') |
+-------------------------------+
| [1, 2]                        |
+-------------------------------+
mysql> SELECT JSON_MERGE_PATCH('1', '2');
+----------------------------+
| JSON_MERGE_PATCH('1', '2') |
+----------------------------+
| 2                          |
+----------------------------+

    将JSON数组和JSON对象合并到一起
    

    当待合并的元素既有JSON数组也有JSON对象时，会将JSON对象自动包装成数组，并按照合并数组的规则合并
    

    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}');
+---------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}') |
+---------------------------------------------------+
| [1, 2, {"a": 1, "b": 2}]                          |
+---------------------------------------------------+
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}') |
+------------------------------------------------+
| {"a": 1, "b": 2}                               |
+------------------------------------------------+

    在JSON中查找和修改元素
    

    在JSON中查找和修改元素的语法为：$，该符号后跟随需要查找的键名或索引。
    

    JSON_EXTRACT()函数用于从JSON中提取元素，例如：
    

    mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.a');
+-----------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2}', '$.a') |
+-----------------------------------------+
| 1                                       |
+-----------------------------------------+
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[2]');
+-----------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[2]') |
+-----------------------------------+
| 3                                 |
+-----------------------------------+

    JSON_SET()函数用于修改JSON中对应的元素，例如：
    

    mysql> SELECT JSON_SET('{"a": 1, "b": 2}', '$.a', 3);
+----------------------------------------+
| JSON_SET('{"a": 1, "b": 2}', '$.a', 3) |
+----------------------------------------+
| {"a": 3, "b": 2}                       |
+----------------------------------------+
mysql> SELECT JSON_SET('[1, 2, 3]', '$[2]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[2]', 4) |
+----------------------------------+
| [1, 2, 4]                        |
+----------------------------------+

    当路径对应的元素不存在时，会添加新的元素
    

    mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

    JSON_INSERT()函数会给JSON添加新的元素，但是不会更改原有元素
    

    mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

    JSON_REPLACE()函数会修改现有元素，但不会添加新元素
    

    mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

    JSON_REMOVE()函数会删除路径对应的元素
    

    mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> SELECT JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]');
+-----------------------------------------+
| JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]') |
+-----------------------------------------+
| ["a", {"b": [false]}, [10, 20]]         |
+-----------------------------------------+

    当键名中包含空格时需要用双引号包含键名，即
    

    mysql> SELECT JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"');
+------------------------------------------------------------------------+
| JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"') |
+------------------------------------------------------------------------+
| "shark"                                                                |
+------------------------------------------------------------------------+

    当查找不到路径中的元素时，返回null
    

    mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[3]');
+-----------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[3]') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+

    可以使用'$[M to N]'的形式提取出某一范围内的元素，last关键字指代数组中最后一个元素
    

    （但不能用于修改元素）
    

    mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[1 to last]');
+-------------------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[1 to last]') |
+-------------------------------------------+
| [2, 3]                                    |
+-------------------------------------------+

    可以使用*和**通配符来提取元素（但不能用于修改元素）
    

    .*表示提取JSON对象中所有键的值，并返回一个数组
    

    mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.*');
+-----------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2}', '$.*') |
+-----------------------------------------+
| [1, 2]                                  |
+-----------------------------------------+

    [*]表示提取JSON数组中所有元素
    

    mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[*]');
+-----------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[*]') |
+-----------------------------------+
| [1, 2, 3]                         |
+-----------------------------------+

    prefix**suffix表示提取路径以prefix开始、以suffix结束的元素，其中prefix是可选的，但suffix是必须的
    

    mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b');
+-------------------------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b') |
+-------------------------------------------------------------------------+
| [1, 3]                                                                  |
+-------------------------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b');
+---------------------------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b') |
+---------------------------------------------------------------------------+
| [1]                                                                       |
+---------------------------------------------------------------------------+

    JSON值的比较和排序
    

    JSON值的比较
    

    JSON值使用<、>、=、<=、>=、<>、<=>、!=操作符进行比较。比较时会将JSON转换为MySQL的原生数值类型或字符串来比较。
    

    JSON在比较时分为两步：
    

    比较JSON类型（即JSON_TYPE()的返回值），若类型不同则按照类型的优先级顺序得出比较结果，优先级越高则越大，若类型相同则进行第二步；
    

    JSON类型的优先级如下：
    

    BLOB < BIT < OPAQUE < DATETIME < TIME < DATE < BOOLEAN < ARRAY < OBJECT < STRING < INTEGER = DOUBLE < NULL
    

    根据各类型具体的比较规则比较
    

    BLOB、BIT、OPAQUE、STRING：先比较两个值长度相同的部分，如果都相同，则长度较短的值排在长度较长的值之前。对STRING的比较是基于utf8mb4编码格式的。
    

    "a" < "ab" < "b" < "bc"
"A" < "a"

  • DATATIME、TIME、DATE：表示较早时间点的值排在表示较晚时间点的值之前。表示相同时间点的DATATIME值和TIMESTAMP值相等
    • 

    ARRAY：两JSON数组长度相同且对应元素相等时两数组相等。长度不同时，对应位置的元素的值较小的数组排在前面；对应元素都相同时，较短的数组排在前面
    

    [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]

  • BOOLEAN：false值排在true值之前
    • 

    OBJECT：当两个JSON对象具有相同的键，且各键对应的值也相等时，两个JSON对象相等。不相等的JSON对象的的排序不定
    

    {"a": 1, "b": 2} = {"b": 2, "a": 1}

  • INTEGER、DOUBLE：当两个比较对象一个是INT型、一个是DOUBLE型时，INT型会被转换为DOUBLE型；但当两个比较对象无法预先判断是INT型还是DOUBLE型时，会转换为INT型比较
    • 

  • JSON值和SQLNULL比较时，比较结果未知
    • 

  • JSON值和非JSON值比较时，非JSON值会被转换为JSON值
    • 

    JSON值的排序
    

    使用ORDER BY和GROUP BY对JSON值排序时遵循以下规则：
    

  • 按照前述比较规则排序
    • 

  • 升序时，SQLNULL排在所有JSON值之前（包括JSONnull）；降序时，SQLNULL排在所有JSON值之后
    • 

    推荐将JSON值转换为MySQL基本类型再进行排序。