开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql json -> 正文阅读

[大数据]mysql json

对mysql中的 json字段 进行操作或筛选

官方文档 mysql JSON Functions

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

JSON 数据类型简介

mysql5.7 以上 提供了一种新的字段格式 json，大概是mysql 想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在 mysql8.0 解决了

JSON 值将 不再以字符串的形式存储 ，而是采用一种 允许快速读取 文本元素（document elements）的内部二进制（internal binary）格式；
在 JSON 列 插入或者更新 的时候将会 自动验证 JSON 文本，未通过验证的文本将产生一个错误信息；
JSON 文本采用标准的创建方式，可以使用 大多数的比较操作符进行比较操作 ，例如：=, <, <=, >, >=, <>, != 等

官网 api

在这里插入图片描述

使用

-> 和 ->>

在这里插入图片描述
-> 在 field 中使用的时候结果带引号，->> 的结果不带引号

'$'指的是info字段本身，也可以指定第几项

在这里插入图片描述

注意：-> 当做 where 查询是要注意类型的，->> 是不用注意类型的

需要注意的是，在 MySQL5.7.17 版本之后，JSON 中的元素搜索是严格区分变量类型的
比如整型和字符串是严格区分的（使用 -> 的进行），但如果使用 ->> 的形式的话就不区分

注意下图中 sql 最后的 18 引号

-- 能查到
SELECT * FROM `demo` WHERE `info`->'$.age' = 18;

-- 不能查到
SELECT * FROM `demo` WHERE `info`->'$.age' = '18';

-- 能查到
SELECT * FROM `demo` WHERE `info`->>'$.age' = 18;

-- 能查到
SELECT * FROM `demo` WHERE `info`->>'$.age' = '18';

在 order 中使用没有什么区别

select * from member order by info->"$.id" desc;
select * from member order by info->>"$.id" desc;

创建 JSON 字段

JSON 类型可以为 null 或 not null 但不能有默认值

CREATE TABLE `demo` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`info` JSON NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

插入 JSON 数据

INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

json 类型的字段必须时一个有效的json字符串
可以使用 JSON_OBJECT() 函数构造 json 对象：

INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));

使用 JSON_ARRAY() 函数构造 json 数组

INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

查看表中数据

mysql> select * from test_user; 
+----+-----------+--------------------------------------------+ 
| id | name      | info                                       |
+----+-----------+--------------------------------------------+ 
|  1 | xiaoming  | {"age": 18, "sex": 1, "nick_name": "小萌"} | 
|  2 | xiaohua   | {"age": 17, "sex": 0}                      |
|  3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]}   | 
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)

查询

json_extract(字段名,$.json字段名) 查询json中的某个属性/列表

查询表达式：对象为json 列 -> '$.键', 数组为 json列->'$.键[index]'

mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; 
+-----------+---------------------+---------------+------------------+ 
| name      | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming  | "小萌"              | 1             | NULL             | 
| xiaohua   | NULL                | 0             | NULL             | 
| xiaozhang | NULL                | 1             | 3                | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

查询表达式：对象为 JSON_EXTRACT(json列 , '$.键')，数组为 JSON_EXTRACT(json列 , '$.键[index]')

mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]')  from test_user;
 +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| name      | JSON_EXTRACT(info, '$.nick_name') | JSON_EXTRACT(info, '$.sex') | JSON_EXTRACT(info, '$.tag[0]') 
| +-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| xiaoming  | "小萌"                            | 1                           | NULL                           |
| xiaohua   | NULL                              | 0                           | NULL                           | 
| xiaozhang | NULL                              | 1                           | 3                              | 
+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

JSON_CONTAINS()：JSON格式数据是否在字段中包含特定对象

JSON_CONTAINS(target, candidate[, path])

想查询包含 deptName=部门5 的对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

去除双引号

看到上面"小萌"是带双引号的，这不是我们想要的，可以用 JSON_UNQUOTE 函数将双引号去掉

mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; 
+----------+-----------------------------------+ 
| name     | JSON_UNQUOTE(info->'$.nick_name') | 
+----------+-----------------------------------+ 
| xiaoming | 小萌                              | 
+----------+-----------------------------------+ 
1 row in set (0.05 sec)

或者直接使用操作符 ->>

mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming';
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.06 sec)

当然属性也可以作为查询条件
mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌'; 
+----------+----------------------+ 
| name     | info->>'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌                 | 
+----------+----------------------+ 
1 row in set (0.05 sec)

通过虚拟列实现快速查询

通过虚拟列对JSON类型的指定属性进行快速查询

创建虚拟列
mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;

注意用操作符->>

使用时和普通类型的列查询是一样

mysql> select name,nick_name from test_user where nick_name='小萌'; 
+----------+-----------+ 
| name     | nick_name | 
+----------+-----------+ 
| xiaoming | 小萌      | 
+----------+-----------+ 
1 row in set (0.05 sec)

JSON作为条件查询

JSON 不同于普通字符串，如果直接将字符串和 JSON 字段进行比较，不会查询到结果

-- 查询不到数据
SELECT * FROM `demo` WHERE `info` = '{"age": 18, "name": "andy"}';

使用 CAST 函数 将字符串转成 JSON 的形式

SELECT * FROM `demo` WHERE `info` = CAST('{"age": 18, "name": "andy"}' AS JSON);

JSON数据中的元素作为条件查询

指定 JSON 元素的方法为：column -> '$.path' 或 column -> '$[index]'

SELECT * FROM `demo` WHERE `info`->'$.name' = 'andy';

上面提到过 column->path 查询出来的字符串包含双引号
而 column->>path 不包含双引号，但作为条件查询时，-> 和 ->> 的查询结果都是一样包含双引号的

JSON_TYPE(）：查询某个json字段属性类型

想查询deptName的字段属性是什么

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept

在这里插入图片描述

json_valid : 是否是有效的json

select json_valid(id),json_valid(info) from member;

在这里插入图片描述

JSON_KEYS() ：JSON文档中的键数组

想查询json格式数据中的所有key

SELECT JSON_KEYS(json_value) FROM dept

在这里插入图片描述

json_depth 和 json_length

在这里插入图片描述

select json_depth(info) from member;
json是 空数组 或者 空对象 是返回的是1

在这里插入图片描述

select json_length(info) from member;

在这里插入图片描述

更新数据

更新整个JSON

UPDATE `demo` SET `info` = '{"name": "joe", "age": 15}' WHERE `id` = 3;

JSON_INSERT

插入新的元素值，但不会覆盖已存在的元素值

-- 只会新增sex nick_name元素，不会覆盖age元素
mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

JSON_SET

设置值，如果元素不存在则创建，如果已存在则覆盖旧的值：

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

mysql> select * from test_user where id=3; 
+----+-----------+---------------------------------------------------------------+-----------+ 
| id | name      | info                                                          | nick_name | 
+----+-----------+---------------------------------------------------------------+-----------+ 
|  3 | xiaozhang | {"age": 19, "sex": 0, "tag": [3, 5, 90], "nick_name": "小张"} | 小张      | 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

JSON_REPLACE

只会替换已存在的元素值

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name    | info                                       | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
|  2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花      | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

tag没有更新进去

JSON_REMOVE

删除JSON元素

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

mysql> select * from test_user where id=1; 
+----+----------+----------------------------------+-----------+ 
| id | name     | info                             | nick_name | 
+----+----------+----------------------------------+-----------+ 
|  1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌      | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)

json_array_append

在这里插入图片描述
向指定的位置后追加值

select json_array_append(info, '$', 1) from member;
'$'指的是info字段本身，也可以指定第几项

在这里插入图片描述

select json_array_append(info, '$[1]', 2) from member;

下标不能是负数，会报错，不能超过原本json数量，会被忽略

在这里插入图片描述

JSON_ARRAY_INSERT

向指定的位置前插入值

// UPDATE `demo` SET `info` = JSON_ARRAY_INSERT(`info`, '$[1]', 0) WHERE `id` = 2;

select json_array_insert(info, '$[1]', 100) from member;

下标同样不能是负数，但是可以超过json数量，超过就是插入到最后

在这里插入图片描述