美文网首页数据库
MySQL JSON类型

MySQL JSON类型

作者: 竹鼠不要中暑 | 来源:发表于2019-03-21 21:34 被阅读0次

    MySQL支持JSON数据类型。相比于Json格式的字符串类型，JSON数据类型的优势有：

    • 存储在JSON列中的JSON文档的会被自动验证。无效的文档会产生错误；
    • 最佳存储格式。存储在JSON列中的JSON文档会被转换为允许快速读取文档元素的内部格式。

    存储在JSON列中的任何JSON文档的大小都受系统变量max_allowed_packet的值的限制，可以使用JSON_STORAGE_SIZE()函数获得存储JSON文档所需的空间。

    JSON值的局部更新

    在MySQL8.0中，优化器可以执行JSON列的局部就地更新，而不用删除旧文档再将整个新文档写入该列。局部更新的条件：

    • 正在更新的列被声明为JSON；
    • 该UPDATE语句使用任一的三个函数 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列；
    • 输入列和目标列必须是同一列；
    • 所有更改都使用新值替换现有数组或对象值，并且不向父对象或数组添加任何新元素；
    • 新值不能大于旧值；

    创建JSON值

    JSON数组包含在 字符[和]字符中，其中为一个由逗号分隔的值列表：

    ["abc", 10, null, true, false]

    JSON对象包含在字符{和}字符中，其中为一组由逗号分隔的键值对，键必须是字符串：

    {"k1": "value", "k2": 10}

    在JSON数组和JSON对象的值中允许嵌套：

    [99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

    下例中向创建一个只有一个JSON列的表格t_json，并向其中添加JSON值：

    mysql> CREATE TABLE t_json (jdoc JSON) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.73 sec)

mysql> INSERT INTO t_json VALUES('[1,2]');
Query OK, 1 row affected (0.17 sec

mysql> INSERT INTO t_json VALUES('{"key1":"value1","key2":"value2"}');
Query OK, 1 row affected (0.27 sec)

mysql> INSERT INTO t_json VALUES('"HELLO"');
Query OK, 1 row affected (0.20 sec)

    若添加的值为非JSON格式，则报错：

    mysql> INSERT INTO t_json VALUES("HELLO");
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't_json.jdoc'.

    查看t_json:

    mysql> SELECT * FROM t_json;
+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| [1, 2]                               |
| {"key1": "value1", "key2": "value2"} |
| "HELLO"                              |
+--------------------------------------+
3 rows in set (0.00 sec)
    • JSON_TYPE()函数尝试将传入的值其解析为JSON值。如果值有效，则返回值的JSON类型，否则产生错误：
    mysql> SELECT JSON_TYPE('["a","b",true,13]');
+--------------------------------+
| JSON_TYPE('["a","b",true,13]') |
+--------------------------------+
| ARRAY                          |
+--------------------------------+
1 row in set (0.04 sec)

mysql> SELECT JSON_TYPE('[a,"b",true,13]'); //注意 a
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 1.
    • JSON_ARRAY()接收传入的值列表（可以为空），返回包含这些值的JSON数组：
    mysql> SELECT JSON_ARRAY('ab',false,13);
+---------------------------+
| JSON_ARRAY('ab',false,13) |
+---------------------------+
| ["ab", false, 13]         |
+---------------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_ARRAY();
+--------------+
| JSON_ARRAY() |
+--------------+
| []           |
+--------------+
1 row in set (0.00 sec)
    • JSON_OBJECT() 接收传入的键值对列表（可以为空），并返回包含这些键值对的JSON对象：
    mysql> SELECT JSON_OBJECT('key1','a','key2','b');
+------------------------------------+
| JSON_OBJECT('key1','a','key2','b') |
+------------------------------------+
| {"key1": "a", "key2": "b"}         |
+------------------------------------+
1 row in set (0.03 sec)

    如果传入的参数不能组成键值对，则报错：

    mysql> SELECT JSON_OBJECT('key1','value1','key2');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'
    • JSON_MERGE_PRESERVE() 获取两个或多个JSON文档并返回组合结果：
    mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.03 sec)

    因此我们也可以使用以上三种方法向表中添加JSON值，可以一定程度地避免输入格式错误：

    mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array'));
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello'));
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2)));
Query OK, 1 row affected (0.14 sec)

mysql> SELECT * FROM t_json;
+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| [1, 2]                               |
| {"key1": "value1", "key2": "value2"} |
| "HELLO"                              |
| ["json_array"]                       |
| {"key": "hello"}                     |
| [{"key": "hello"}, 1, 2]             |
+--------------------------------------+
6 rows in set (0.00 sec)

    JSON值的规范化，合并和自动包装

    解析字符串并发现字符串是有效的JSON文档时，它在被解析时也会被规范化。对于重复的键（key）,后面的值（value）会覆盖前面的值。如下：

    mysql> SELECT JSON_OBJECT('x',1,'y',2,'x','a','x','b');
+------------------------------------------+
| JSON_OBJECT('x',1,'y',2,'x','a','x','b') |
+------------------------------------------+
| {"x": "b", "y": 2}                       |
+------------------------------------------+
1 row in set (0.07 sec)

    这种“覆盖”在向JSON列添加值时也会发生。
    在MySQL8.0.3之前的版本中，与此相反，对于被重复的键，它的第一个值会被保留，后添加的值则会被抛弃。

    合并JSON值

    MySQL8.0.3及更高版本中，有两种合并函数：JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH()。下面具讨论它们的区别。

    • 合并数组：
    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');
+-------------------------------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') |
+-------------------------------------------------------------------------+
| [true, false]                                                           |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');
+----------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') |
+----------------------------------------------------------------------------+
| [1, 2, "a", "b", "c", 1, 2, true, false]                                   |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

    合并数组时，JSON_MERGE_PRESERVE只保留最后传入的数组参数，而JSON_MERGE_PRESERVE则按传入顺序将数组参数连接。

    • 合并对象
    mysql> SELECT JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}');
+------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') |
+------------------------------------------------------------------------------+
| {"a": 4, "b": 2, "c": 5, "d": 3}                                             |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

    合并对象时，对于重复键，JSON_MERGE_PRESERVE只保留最后传入的键值，而JSON_MERGE_PRESERVE重复键的所有值保留为数组。

    搜索和修改JSON值

    在了解搜索和修改JSON值之前，先来看看JSON的路径语法。

    路径语法

    • .keyName：JSON对象中键名为keyName的值；
    • 对于不合法的键名（如有空格），在路径引用中必须用双引号"将键名括起来，例，."key name"；
    • [index]：JSON数组中索引为index的值，JSON数组的索引同样从0开始；
    • [index1 to index2]：JSON数组中从index1到index2的值的集合；
    • .*: JSON对象中的所有value；
    • [*]: JSON数组中的所有值；
    • prefix**suffix: 以prefix开头并以suffix结尾的路径；
    • **.keyName为多个路径，如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}','$**.b'指路径$.a.b和$.c.b；
    • 不存在的路径返回结果为NULL；
    • 前导$字符表示当前正在使用的JSON文档
    • 例子：对于数组[3, {"a": [5, 6], "b": 10}, [99, 100]]
      • $[1]为{"a": [5, 6], "b": 10}。
      • [1].a为[5, 6]。
      • $[1].a[1]为 6。
      • $[1].b为 10。
      • $[2][0]为 99。

    搜索

    JSON_EXTRACT提取JSON值，直接看例子：

    • JSON对象
    mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name');
+--------------------------------------------------------+
| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') |
+--------------------------------------------------------+
| "Taylor"                                               |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*');
+-----------------------------------------------------+
| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') |
+-----------------------------------------------------+
| [29, "Taylor"]                                      |
+-----------------------------------------------------+
1 row in set (0.00 sec)
    • JSON数组
    mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');
+-----------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[1]') |
+-----------------------------------------+
| "b"                                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]');
+----------------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') |
+----------------------------------------------+
| ["b", "c"]                                   |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]');
+-----------------------------------------+
| JSON_EXTRACT('["a", "b", "c"]', '$[*]') |
+-----------------------------------------+
| ["a", "b", "c"]                         |
+-----------------------------------------+
1 row in set (0.00 sec)

    修改

    • JSON_REPLACE 替换值（只替换已经存在的旧值）
    • JSON_SET 设置值（替换旧值，并插入不存在的新值）
    • JSON_INSERT 插入值（插入新值，但不替换已经存在的旧值）
    • JSON_REMOVE 删除JSON数据，删除指定值后的JSON文档

    JSON_REPLACE与JSON_SET的区别：

    // 旧值存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');
+----------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+----------------------------------------------------------------+
| {"id": 29, "name": "Mere"}                                     |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");
+------------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+------------------------------------------------------------+
| {"id": 29, "name": "Mere"}                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

// 旧值不存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+---------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+---------------------------------------------------------------+
| {"id": 29, "name": "Taylor"}                                  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+-----------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+-----------------------------------------------------------+
| {"id": 29, "cat": "Mere", "name": "Taylor"}               |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

    JSON_INSERT和JSON_SET:

    // 旧值存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[1]', 4) |
+-------------------------------------+
| [1, 2, 3]                           |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[1]', 4) |
+----------------------------------+
| [1, 4, 3]                        |
+----------------------------------+
1 row in set (0.00 sec)

//旧值不存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[4]', 4) |
+-------------------------------------+
| [1, 2, 3, 4]                        |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[4]', 4) |
+----------------------------------+
| [1, 2, 3, 4]                     |
+----------------------------------+
1 row in set (0.00 sec)

    JSON_REMOVE:

    mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[1]');
+----------------------------------+
| JSON_REMOVE('[1, 2, 3]', '$[1]') |
+----------------------------------+
| [1, 3]                           |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[4]');
+----------------------------------+
| JSON_REMOVE('[1, 2, 3]', '$[4]') |
+----------------------------------+
| [1, 2, 3]                        |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name');
+-------------------------------------------------------+
| JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name') |
+-------------------------------------------------------+
| {"id": 29}                                            |
+-------------------------------------------------------+
1 row in set (0.00 sec)

    JSON值的比较和排序

    可以使用=，<，<=，>，>=，<>，!=，和 <=>对JSON值进行比较。
    JSON值的比较先比较值的类型。如果类型不同，则直接 返回类型的优先级的比较结果；如果类型相同，再进行值的内容的比较。

    • JSON中值的类型的优先级从高到低为：
    BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

    OPAQUE值是不属于其他类型的值。

    • JSON值的内容的比较规则（因类型不同而有差别）：
      • BLOB
        比较两个值的前N个字节，其中N为较短的值的字节数。如果前N个字节相同，则较短的值较小。BIT和OPAQUE与BLOB的规则相同。
      • DATETIME
        较早时间点的值较小。如果两个值分别为 MySQL DATETIME and TIMESTAMP类型且表示的是相同的时间点，则这两个值相等。
      • TIME
        较少的是时间值较小。
      • DATE
        较早的日期值较小。
      • ARRAY
        较短的数组较小。
        如果两个数组长度相同，且相同索引处的值相同，则两个数组相等。
        对于不行等的数组，它们的大小顺序由两数组中第一个不同的元素决定。
        例子：
      [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
      • BOOLEAN
        false < true
      • OBJECT
        具有完全相同的键值对的两对象相等。如
      {"a": 1, "b": 2} = {"b": 2, "a": 1}
      • STRING
        与BLOB比较规则相似。区分大小写。
        如：
      "A"<"a" < "ab" < "b" < "bc"
      • INTEGER， DOUBLE
        • 如果进行INTEGER列和DOUBLE列的比较，则integer数会被转为double数，即精确值转为近似值，再进行比较；
        • 如果查询比较包含数字的两个JSON列，则无法预先知道数字是INTEGER还是DOUBLE，比较时会将近似值转为精确值，在进行比较。
        • INTEGER比较
        9223372036854775805 < 9223372036854775806 < 9223372036854775807
        • DOUBLE比较
        9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18
      • 任何JSON值与NULL比较的结果为UNKOWN
      • JSON值与非JSON值比较时，非JSON值会被转为JSON值

    JSON值和非JSON值转换

    转换规则为：

    other type CAST(other type AS JSON) CAST(JSON AS other type)
    JSON 没变化 没变化
    utf8字符类型(utf8mb4,utf8,ascii) 字符串被解析为JSON值 JSON值被序列化为utf8mb4字符串
    其他字符类型 其他字符编码被隐式转换为utf8mb4，并按utf8字符类型进行处理 JSON值被序列化为utf8mb4字符串，然后再被转换为其他字符编码。结果可能没有意义。
    NULL 结果为JSON类型的NULL值 不适用
    Geometry类型 ST_AsGeoJSON()将Geometry值转换为JSON文档 非法操作。解决办法: 将CAST(JSON AS other type)的结果传递给CHAR)ST_GeomFromGeoJSON()
    所有其他类型 转换结果是由单个标量值组成的JSON文档 如果JSON文档由目标类型的单个标量值组成，并且标量值可以强制转换为目标类型，则成功转换。否则，返回NULL 并发出警告。

    相关文章

      网友评论

        本文标题：MySQL JSON类型

        本文链接：https://www.haomeiwen.com/subject/pfptvqtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        数据库

        关于我们|服务条款|联系我们|MySQL JSON类型|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！