SQL中的JSON数据类型

2019-03-25  本文已影响0人  FoxLayla

SQL中的JSON数据类型

概述

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

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

JSON值的局部更新

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

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

创建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

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数组

合并JSON对象

合并非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值的比较和排序

JSON值的比较

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

JSON在比较时分为两步：

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

    JSON类型的优先级如下：

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

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

    • 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值排序时遵循以下规则：

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

参考

上一篇 下一篇

猜你喜欢

热点阅读