对mysql
中的 json字段
进行操作或筛选
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数量,超过就是插入到最后