【MySQL】——JSON类型

ゞ浴缸里的玫瑰 2023-02-14 03:15 24阅读 0赞

自从MySQL5.7之后，MySQL开始支持JSON类型，在此之前如果想在表中保存JSON格式类型的数据，则需要依靠varchar或者text之类的数据类型，但是这样并不利于属性的查询，支持json类型后有以下优势

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

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

关于json类型函数介绍

[https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html][https_dev.mysql.com_doc_refman_5.7_en_json-function-reference.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWRhamluZzI2Nw_size_16_color_FFFFFF_t_70][]

### 创建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]}

\-- create

\-- 插入数组

JSON\_ARRAY(\[val\[, val\] ...\])

\-- 插入对象

JSON\_OBJECT(\[key, val\[, key, val\] ...\])

\-- update

\-- 末尾添加数组元素，如果原有值是数值或json对 象，则转成数组后，再添加元素

JSON\_ARRAY\_APPEND(json\_doc, path, val\[, path, val\] ...)

\-- 插入数组元素,在数组的指定下标处插入元素

JSON\_ARRAY\_INSERT(json\_doc, path, val\[, path, val\] ...)

\-- 插入值（插入新值，但不替换已经存在的旧值）

JSON\_INSERT(json\_doc, path, val\[, path, val\] ...)

\-- 替换值（只替换已经存在的旧值）

JSON\_REPLACE(json\_doc, path, val\[, path, val\] ...)

\-- 提取json信息的函数

JSON\_KEYS(json\_doc\[, path\])

\-- 返回指定path的key 结果\["a","b"\]

select json\_keys('\{"a":1,"b":2\}')

\-- 结果null。数组没有key

select json\_keys('\{"a":1,"b":\[1,2,3\]\}','$.b')

\-- json\_merge 合并json数组或对象

\-- json\_remove 删除json数据

\-- json\_set 设置值（替换旧值，并插入不存在的新值）

\-- json\_unquote 去除json字符串的引号，将值转成string类型

以下内容中涉及到sql语句

INSERT INTO `tb_rule`(`id`,  `detail`) VALUES (1,'{\"day\": [1, 2, 3, 4, 5, 6, 7], \"rate\": 1, \"time\": [0, 1, 2, 3], \"stage\": 1, \"regular\": 1, \"stageMode\": {\"serialDays\": 7, \"intervalDays\": 0}, \"stageGroup\": {\"termAfter\": 2, \"termBefore\": 2, \"termAfterExt\": 1, \"termBeforeExt\": 1}, \"timeDetails\": [{\"id\": \"0\", \"name\": \"上午\", \"endTime\": \"12:00\", \"startTime\": \"08:00\"}, {\"id\": \"1\", \"name\": \"下午\", \"endTime\": \"18:00\", \"startTime\": \"12:00\"}, {\"id\": \"2\", \"name\": \"傍晚\", \"endTime\": \"20:30\", \"startTime\": \"18:00\"}, {\"id\": \"3\", \"name\": \"晚上\", \"endTime\": \"23:59\", \"startTime\": \"20:30\"}]}');
    INSERT INTO `tb_category_mapping_config`(`id`, `city_info`, `class_duration`) VALUES (84, '[\"020\", \"021\", \"022\", \"023\", \"024\", \"025\", \"027\", \"028\", \"029\", \"0351\", \"0371\", \"0512\", \"0531\", \"0532\", \"0571\", \"0731\", \"0755\", \"0757\", \"999\"]', '[\"120\"]');
    INSERT INTO `edu`.`tb_preferential_compensation`(`id`,`use_rule`, `course_ids`) VALUES (1, '[{\"type\": 0, \"endTime\": 1590076799000, \"quantity\": 2, \"waitDays\": 10, \"startTime\": 1589990400000, \"changeType\": 0, \"reductionAmount\": 1}, {\"type\": 0, \"endTime\": 1590767999000, \"quantity\": 3, \"waitDays\": 11, \"startTime\": 1589990400000, \"changeType\": 1, \"reductionAmount\": 2}]', '[\"9460201711031041523075588783\"]');

\-- json 对象 查其中包含即可

select \* from tb\_rule where JSON\_CONTAINS(detail, '\[1,3\]','$.day');

\-- json 对象 查包含全部内容

select \* from tb\_rule where detail->'$.day'= CAST('\[1,2,3,4,5,6,7\]' as json);

\-- json数组，有key，value的

select JSON\_UNQUOTE(JSON\_EXTRACT(use\_rule, '$\[1\].node')) from tb\_preferential\_compensation where JSON\_CONTAINS( use\_rule, JSON\_OBJECT('node',"考勤"));

\-- json 数组，纯数组 查其中包含单个值

select \* from tb\_category\_mapping\_config where JSON\_CONTAINS(class\_duration,'"120"','$');

\-- json数组，纯数组，查包含全部的内容

select \* from tb\_category\_mapping\_config where class\_duration=CAST('\["120","60"\]' as json);

\-- JSON\_EXTRACT查询mysql中的\[\{\},\{\}中的值\] 和｛｝

json\_extract(a.tag, '$\[\*\].tag\_name.cn') as tag, json\_extract(a.address,'$.en') as address\_name

\-- json对象 判断是否存在此节点 'all' 必须全部在 ，one 存在一个即可

select \* from tb\_rule where JSON\_CONTAINS\_PATH(detail, 'one', '$.day','$.week')

\-- json数组 判断是否存在此节点

select \* from tb\_preferential\_compensation where JSON\_CONTAINS\_PATH(use\_rule, 'all', '$\[\*\].node')

\-- 返回满足的节点

select json\_search('\{"a":"abc","b":\{"c":"dad"\}\}','all','%a%')

\-- json的数组的长度

select \* from tb\_preferential\_compensation where JSON\_LENGTH(use\_rule)=1

\-- 结果1,1,1 无论是否有数据，每对 \{\},\[\] 都会使深度加一，再加上实际的数据，也会加一

select json\_depth('\{\}')，json\_depth('\[\]'),json\_depth('123')

select \* from tb\_preferential\_compensation where JSON\_DEPTH(use\_rule)=3

检索json列为空的数据

json有二种存储形式，数组和对象，涉及到判断json列为空，就会出现三种可能性，null，空数组(\[\]),空对象(\{\})，这种情况的检索依托的MySQL中的cast函数

CAST函数语法规则是：Cast(字段名 as 转换的类型 )

select * from tb_teacher where teacher_class_type = CAST(NULL AS JSON);
    select * from tb_teacher where teacher_class_type = CAST('[]' AS JSON);
    select * from tb_teacher where teacher_class_type = CAST('{}' AS JSON);

[https_dev.mysql.com_doc_refman_5.7_en_json-function-reference.html]: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWRhamluZzI2Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200531232020230.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWRhamluZzI2Nw==,size_16,color_FFFFFF,t_70