• SQL-mysql操作json


    一 前言

    MySQL 5.7.8 之后 支持 JSON (由rfc7159规定)数据类型,其能在字段中使用json 类型,做到了自动校验是否为json类型数据,否则插入数据会报异常;其次,储存json数据内部做到了优化储存,能够快速读取json类型数据,比如无需将二进制json转为文本形式后读取;

    《SQL-你真的了解什么SQL么?》

    《SQL-小白最佳入门sql查询一》

    《SQL-小白最佳入门sql查询二》

    《SQL- 多年开发人员都不懂的插入与更新删除操作注意点》

    《SQL-SQL事物操作》

    《SQL-Mysql数据类型》

    《SQL-mysql视图的前世今生》

    《SQL-mysql储存过程》

    《SQL-mysql游标与触发器》

    《SQL-mysql用户权限管理》

    《SQL-mysql架构入门》

    《SQL-mysql锁等待与死锁》

    公众号:知识追寻者

    知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)

    二 操作 JOSN

    建表语句如下,为 area 字段 声明为 JSON 类型;

    CREATE TABLE `order` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
      `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `year` year(4) DEFAULT NULL COMMENT '年份',
      `area` json DEFAULT NULL COMMENT '地区',
      PRIMARY KEY (`id`),
      UNIQUE KEY `order_name` (`order_name`,`create_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
    

    MySQL 中支持 json 对象 和json 数组,他们之间可以相互嵌套;json类似map,在java中json就是map得实现类,初学者若不懂何为json,就当作map来用,即储存 key - value 形式得数据结构;注意点是 json 数据 得key 必须是字符串,可以有key无value

    2.1 插入 josn数据

    插入 json 对象,east 得值 为 50 , sourth 值为65 得 json对象;

    INSERT INTO `order`(`order_name`, `year`, `area`) 
    VALUES ('荷小花的订单', 2020, '{"east": "50", "south": "65"}');
    

    等效于使用 JSON_OBJECT 函数, 示例如下

    INSERT INTO `order`(`order_name`, `year`, `area`) 
    VALUES ('荷小花的订单', 2020, JSON_OBJECT("east", "50", "south", "65"));
    

    插入 json 数组

    INSERT INTO `order`(`order_name`, `year`, `area`) 
    VALUES ('荷小花的订单', 2020, '[{"east": "50", "south": "65"}]');
    

    等效于使用 JSON_ARRAY 函数,示例如下

    INSERT INTO `order`(`order_name`, `year`, `area`) 
    VALUES ('荷小花的订单', 2020, JSON_ARRAY("east", "50", "south", "65"));
    

    2.2 查询json数据

    使用 column - path 路径符 -> 查询 指定key 得值,

    select order_name, area -> '$.east' from `order`
    

    注意,如果json 数据中不存在 east 得键,则列出为null值

    输出如下

    ---------   ------
    荷小花的订单	"50"
    荷小花的订单	
    

    也可以使用 ->> 符号, 不同之处是使用 ->> 更加直观,输出得json数据最外层不会携带双引号,内层数据中存在双引号不会有反斜杠转义;

    select order_name, area ->> '$.east' from `order`
    

    使用 单引号 代替 双引号

    select order_name, area ->> "$.east" from `order`
    
    

    我们 也可以使用 JSON_EXTRACT 函数 达到同样得效果;

    SELECT order_name,JSON_EXTRACT(area, '$.east') from `order`;
    
    

    2.3 修改json数据

    使用 JSON_SET 设置 json key 得 值

    id = 1 得 area 数据如下

    {"north": "55", "south": "66"}
    

    现在将south 值改为 60 的语句示例如下

    update `order` set area = json_set(area, '$[0].south', '60') where id = '1'
    

    其中 $[0] 代表 json 中的第一个对象 ,以此类推 $[1] 为 josn 中的第二个对象;

    示例

    ["6","2",{"east": "50", "south": "65"}]
    

    $[0] 为"6",$[1] 为 "2" , $[2] 为 {"east": "50", "south": "65"}; $[2].east 为 "50" , 或者 $[2][1];

    如果上面$[*] 表达式式理解困难也可以使用如下方式

    update `order` set area = json_set(area, '$.south', '60') where id = '1'
    

    tip: 如果更改整个json值 与 平时的更新数据方式一致

    2.4 删除json中的数据

    使用 json_remove 可以达到效果;

    示例: 删除json 中的 south 键

    update `order` set area =json_remove(area, '$.south') where id = '1'
    

    三 jsom函数

    3.1 cast

    cast 函数 是特殊函数,可以使用 CAST(expr AS type) 函数进行数据类型得转换,此函数 与 convert 用法 类似 ,即 期望得表达式转为期望得类型;

    比如 将 字符串 知识追寻者从默认类型转为utf8类型

    SELECT CONVERT('知识追寻者' USING utf8);
    

    如下情况下查询是字符串,非json数据

    select '{"east": "50", "south": "65"}' as str
    
    

    再来看看 cast 函数使用,将字符串转为json 类型

    select cast( '{"east": "50", "south": "65"}' as json )
    

    3.2 JSON_TYPE

    JSON_TYPE 函数 会尝试 去解析 参数为json值,

    示例

    select JSON_TYPE('{"east": "50", "south": "65"}')
    

    得到结果为json 对象

    OBJECT
    

    示例

    select JSON_TYPE('[{"east": "50", "south": "65"}]')
    

    得到结果为json数组

    ARRAY
    

    示例

    select JSON_TYPE('hello')
    

    输出为异常,无效得json

    Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.
    

    3.3 JSON_MERGE

    JSON_MERGE 函数 即 将合并多个json文档; 合并规则如下

    • 如果都是json array,合并为json array;
    • 如果都是json object,合并为json object;
    • 如果有多种类型数据,则将非json array的元素封装成json array再按照如上规则进行合并;

    示例

    select json_merge('["west","20"]', '{"east": "50", "south": "65"}')
    
    

    输出

    ["west", "20", {"east": "50", "south": "65"}]
    

    3.4 JSON_VALID

    JSON_VALID 函数 为 校验是否是json 函数,是返回 1 ,否则 返回0;

    示例如下,返回1;

    SELECT JSON_VALID('{"east": "50", "south": "65"}')
    

    示例如下,返回 0;

    SELECT JSON_VALID('hello')
    
    

    3.5 JSON_INSERT

    JSON_INSERT 函数 向 json 中添加新的值,不会改变已经存在的值;

    id = 1 的数据如下

    {"north": "55"}
    

    更新语句如下

    update `order` set area =JSON_INSERT(area, '$.north', 55 ,'$.south', "60") where id = '1'
    

    更改结果如下

    {"north": "55", "south": "60"}
    

    3.6 JSON_REPLACE

    JSON_REPLACE 替换现有的值,如果存在新的值不会添加;

    id = 1 的数据如下

    {"north": "55", "south": "60"}
    

    更新语句如下

    update `order` set area =JSON_REPLACE (area, '$.north', "50" ,'$.east', "60")
    where id = '1'
    
    

    更改结果如下

    {"north": "50", "south": "60"}
    

    JSON_SEARCH , 返回路径,支持返回单个和返回多个;

    id = 1 的数据如下

    [{"north": "50", "south": "60"}, {"north": "50", "south": "70"}]
    

    查询 一个值为 50 的key路径

    select JSON_SEARCH(area, 'one', '50')  from `order`  where id = '1'
    
    

    输出

    "$[0].north"
    

    查询 所有值为 50 的key路径

    select JSON_SEARCH(area, 'all', '50')  from `order`  where id = '1'
    

    输出

    ["$[0].north", "$[1].north"]
    

    3.8 JSON_KEYS

    返回json顶级值底下所有key

    id = 1 的数据如下

    [{"north": "50", "south": "60"}, {"north": "50", "south": "70"}]
    

    查询json数据中第一个json对象所有的key

    select JSON_KEYS(area, '$[0]')  from `order`  where id = '1'
    

    输出

    ["north", "south"]
    
  • 相关阅读:
    S2T40,第五章
    S2T40,第四章,简答5
    sqlmap扫描发现注入点示例
    使用jenkins部署.net项目
    在线预览PDF插件
    Visual Studio 2019 License Key
    asp.net core mvc 中 ModelState.IsValid 值是fasle
    sql操作
    sql server 查找与替换 正则表达式 匹配
    asp.net redis 帮助类封装
  • 原文地址:https://www.cnblogs.com/zszxz/p/13277430.html
Copyright © 2020-2023  润新知