SQL-mysql操作json

2 年前 · 来自专栏 java学习之路

知识追寻者

一前言

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

本套教程

- [MYSQL介绍(1)]( 知识追寻者 )

- [MYSQL检索(2)]( 知识追寻者 )

- [MYSQL检索(3)]( 知识追寻者 )

- [MYSQL插入更新删除(4)]( 知识追寻者 )

- [MYSQL事物(5)]( 知识追寻者 )

- [MYSQL数据类型(6)]( 知识追寻者 )

- [MYSQL表结构操作(7)]( 知识追寻者 )

- [MYSQL视图(8)]( 知识追寻者 )

- [MYSQL储存过程(9)]( 知识追寻者 )

- [MYSQL游标与触发器(10)]( 知识追寻者 )

- [MYSQL用户权限管理(11)]( 知识追寻者 )

- [MYSQLl架构入门(12)]( 知识追寻者 )

- [MYSQL锁等待与死锁(13)]( 知识追寻者 )

- [MYSQLl操作json(14)]( 知识追寻者 )

- [MYSQL执行计划(15)]( 知识追寻者 )

- [MYSQL索引(16)]( 知识追寻者 )

公众号： 知识追寻者

知识追寻者(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

一 前言

二 操作 JOSN