MySQL5.7中的JSON基本操作指南
作者:南丞_ 发布时间:2024-01-27 20:38:34
标签:mysql,json,查询
前言
因为项目需要,存储字段存储成了JSON格式,在项目中是将查询出来的值通过jackson转成相应的bean进行处理的,觉得不够简单方便。
MySQL从5.7版本开始就支持JSON格式的数据,操作用起来挺方便的。
建表
在新建表时字段类型可以直接设置为json类型,比如我们创建一张表:
mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);
json类型字段可以为NULL
插入数据:
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');
json类型的字段必须时一个有效的json字符串
可以使用JSON_OBJECT()
函数构造json对象:
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));
使用JSON_ARRAY()
函数构造json数组:
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));
现在查看test_user表中的数据:
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列->'$.键', 数组为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_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_INSERT()
插入新值,但不会覆盖已经存在的值
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": 1, "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)
来源:https://segmentfault.com/a/1190000018612255
0
投稿
猜你喜欢
- 在python中操作文件算是一个基本操作,但是选对了模块会让我们的效率大大提升。本篇整理了两种模块的常用方法,分别是os模块和shutil模
- mac用起来还是有很多不方便的地方,app很局限也都不是很好用,mac自带的截图工具,格式是tiff,需要转成jpg才能在代码中使用,利用p
- 前言本文主要介绍的是Python如何使用zip函数同时遍历多个迭代器,文中的版本为Python3,zip函数是Python内置的函数。下面话
- 本文研究的主要内容是Python中装饰器相关学习总结,具体如下。装饰器(decorator)功能引入日志函数执行时间统计执行函数前预备处理执
- 叨叨几句哈喽兄弟们,今天实现一下人脸识别。先问大家一个问题什么是百度Aip模块?百度AI平台提供了很多的API接口供开发者快速的调用运用在项
- Python tkinter中label控件动态改变值最近在用tkinter做大作业,因为是第一次接触tkinter,所以会遇到很多问题。比
- 友情提示,您阅读本篇博文的先决条件如下:1、本文示例基于Microsoft SQL Server 2008 R2调测。2、具备 Transa
- 1.怎么样查看数据库字符集 [A]数据库服务器字符集select * from nls_database_parameters,其来源于pr
- 该程序通过绘制树干(最初是树;后来是树枝)并递归地添加树来绘制“树”。 使用Pillow。利用递归函数绘制分形树(fractal tree)
- 因为python打包的时候需要用到ico图片格式,网上看好的图片又没有ico,于是自己寻找了一下python转换图片格式的方法,彻底解决这个
- <%If(Request.QueryString("Page")="") ThenPage=1
- 简单介绍HTTP是超文本传输协议,其定义了客户端与服务器端之间文本传输的规范。HTTP默认使用80端口,这个端口指的是服务端的端口,而客户端
- web2.0教程4:如何调用css样式表 这些技巧主要讲meta标签设置的,其实与符合web标准关系不大,只要注意在最后加"/&q
- 简介:MongoEngine 是一个Document-Object Mapper (想一下ORM, 但它是针对文档型数据库),Python通
- 什么是1433端口 1433端口,是SQL Server默认的端口,SQL Server服务使用两个端口:TCP-1433、UDP-1434
- 主要内容所谓RPC,是远程过程调用(Remote Procedure Call)的简写,网上解释很多,简单来说,就是在当前进程调用其他进程的
- 相信大家都想把自己完成的项目打包成EXE应用文件,然后就可以放在桌面随时都能运行了,下面来分享利用pytinstaller这个第三方库来打包
- 网上学习的时候总会遇到一些好的文章,分享给大家,也谢谢作者的分享。Python 简介Python 是一个高层次的结合了解释性、编译性、互动性
- Python使用类(class)和对象(object),进行面向对象(object-oriented programming,简称OOP)的
- 这篇文章主要介绍了Python命令行click参数用法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要