MySQL5.7特性:JSON数据类型学习
概述
MySQL5.7的发行声明中,官方称之为里程碑式的版本,除了运行速度大幅度提升之外,还添加了之前版本没有的功能,如本文所述的原生JSON数据类型功能。
在此版本之前,MySQL所有的JSON数据类型,全部是使用text等文本类型来实现的,数据的处理只能在应用代码级来实现,十分不方便。
什么是JSON类型
作为DBA,可能会对这个概念稍微有点陌生,但是对于开发者来说,这是一个十分熟悉的事物。
JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。它基于 ECMAScript (欧洲计算机协会制定的js规范)的一个子集,采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。
MySQL原始JSON类型的优势在哪?
原生的JSON优势如下:
- 存储上类似text,可以存非常大的数据。
- 存储在JSON列中的JSON文档的自动验证 。无效的文档会产生错误。
- 优化的存储格式。存储在JSON列中的JSON文档将 转换为内部格式,以允许对文档元素进行快速读取访问。
- 相比于传统形式,不需要遍历所有字符串才能找到数据。
- 支持索引:通过虚拟列的功能可以对JSON中部分的数据进行索引。
MySQL的JSON类型
创建JSON类型表
创建一个基础的员工表,除了工号字段外,还有一个个人基础信息字段和一个个人能力信息字段
MySQL [test]> CREATE TABLE employee ( -> -> `empno` int(10) unsigned NOT NULL AUTO_INCREMENT, -> -> `basic_info` JSON NOT NULL, -> -> `skill_info` JSON NOT NULL, -> -> PRIMARY KEY (`empno`) -> -> ); Query OK, 0 rows affected (0.02 sec)
表的基础信息,其中JSON类型的字段,是不可以有默认值的,这点需要注意
MySQL [test]> desc employee; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | empno | int(10) unsigned | NO | PRI | NULL | auto_increment | | basic_info | json | NO | | NULL | | | skill_info | json | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
试着插入几条数据
我们手动插入几条数据进这张表中,在前两条数据中,在个人能力信息上使用的是数组的方式,,后面两条则是使用对象的形式。在MySQL5.7.8版本后的JSON类型中,这两种都是可以的
INSERT INTO `employee` VALUES (1,'{"name": "wangyiyi", "age": "23" ,"from": "hangzhou"}', '["java", "go", "python"]'); INSERT INTO `employee` VALUES (2,'{"name": "linxue", "age": 24 ,"from": "shanghai"}', '["mysql", "oracle", "python"]'); INSERT INTO `employee` VALUES (3,'{"name": "zhaoqing", "age": 24 ,"from": "shanghai"}', '{"system": "linux","database": "mysql", "language": "python"}'); INSERT INTO `employee` VALUES (4,'{"name": "zhouxixi", "age": 30 ,"from": "nanjing"}', '{"system": ["linux","windows"],"database": ["mysql","oracle","postgresql"], "language": ["python","java","go"]}');
插入多个数据后,表中内容为如下
MySQL [test]> select * from employee; +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] | | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | | 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} | | 4 | {"age": 30, "from": "nanjing", "name": "zhouxixi"} | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} | +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
json数据查询方式
在插入了json类型的数据之后,可以针对JSON类型做一些特定的查询,如查询年龄大于20的记录
在SQL的语句中使用 字段->.键名 就可以查询出所对应的键值
MySQL [test]> select * from employee WHERE basic_info->'$.age'> 20; +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] | | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | | 3 | {"age": 28, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} | | 4 | {"age": 30, "from": "nanjing", "name": "zhouxixi"} | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} | +-------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) MySQL [test]> select * from employee WHERE basic_info->'$.age'< 20; Empty set (0.00 sec)
除了使用上述方式外,也可使用 提取json值的 函数 json_extract (使用函数的方式)
MySQL [test]> select * from employee where json_extract(basic_info,'$.age') = 24; +-------+-----------------------------------------------------+----------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-----------------------------------------------------+----------------------------------------------------------------+ | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | | 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} | +-------+-----------------------------------------------------+----------------------------------------------------------------+ 2 rows in set (0.00 sec)
对于数值查询也可做一个范围内查询,如下:
MySQL [test]> select * from employee WHERE basic_info->'$.age' in (23, 24); +-------+-------------------------------------------------------+----------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-------------------------------------------------------+----------------------------------------------------------------+ | 1 | {"age": "23", "from": "hangzhou", "name": "wangyiyi"} | ["java", "go", "python"] | | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | | 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "python"} | +-------+-------------------------------------------------------+----------------------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
因为 JSON 不同于字符串,所以如果用字符串和 JSON 字段比较,是不会相等的:
如下,直接使用字符串查询,查询不出来内容
MySQL [test]> select * from employee where basic_info = '{"age": 24, "from": "shanghai", "name": "linxue"}'; Empty set (0.00 sec)
可以通过 CAST 将字符串转成 JSON 的形式,如下:
MySQL [test]> select * from employee where basic_info = CAST('{"age": 24, "from": "shanghai", "name": "linxue"}' AS JSON); +-------+---------------------------------------------------+-------------------------------+ | empno | basic_info | skill_info | +-------+---------------------------------------------------+-------------------------------+ | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | +-------+---------------------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
查看单纯数组类型的函数JSON_CONTAINS
MySQL [test]> select * from employee where JSON_CONTAINS (skill_info,'"mysql"'); +-------+---------------------------------------------------+-------------------------------+ | empno | basic_info | skill_info | +-------+---------------------------------------------------+-------------------------------+ | 2 | {"age": 24, "from": "shanghai", "name": "linxue"} | ["mysql", "oracle", "python"] | +-------+---------------------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
JSON_PRETTY函数: 以易于阅读的格式打印出JSON值
便于在一些外部应用引用数据时,更方便的使用它
MySQL [test]> select JSON_PRETTY(basic_info) from employee; +---------------------------------------------------------------+ | JSON_PRETTY(basic_info) | +---------------------------------------------------------------+ | { "age": "23", "from": "hangzhou", "name": "wangyiyi" } | | { "age": 24, "from": "shanghai", "name": "linxue" } | | { "age": 28, "from": "shanghai", "name": "zhaoqing" } | | { "age": 30, "from": "nanjing", "name": "zhouxixi" } | +---------------------------------------------------------------+ 4 rows in set (0.00 sec)
MySQL 5.7.22中添加了此功能,此函数返回用于存储JSON文档的二进制表示的字节数,用于查看当前JSON字段的存储大小
MySQL [test]> select skill_info,JSON_STORAGE_SIZE(skill_info) AS Size from employee; +-----------------------------------------------------------------------------------------------------------------------+------+ | skill_info | Size | +-----------------------------------------------------------------------------------------------------------------------+------+ | ["java", "go", "python"] | 29 | | ["mysql", "oracle", "python"] | 34 | | {"system": "linux", "database": "mysql", "language": "go"} | 63 | | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} | 137 | +-----------------------------------------------------------------------------------------------------------------------+------+ 4 rows in set (0.00 sec)
查询JSON字段的长度
MySQL [test]> select JSON_LENGTH(basic_info) from employee; +-------------------------+ | JSON_LENGTH(basic_info) | +-------------------------+ | 3 | | 3 | | 3 | | 3 | +-------------------------+ 4 rows in set (0.00 sec)
查看数据的类型:可以是对象,数组或标量类型
MySQL [test]> select JSON_TYPE(skill_info) from employee; +-----------------------+ | JSON_TYPE(skill_info) | +-----------------------+ | ARRAY | | ARRAY | | OBJECT | | OBJECT | +-----------------------+ 4 rows in set (0.00 sec)
json数据修改方式
如果是整个 json 更新的话,和一般类型插入是一样的
json_array_insert是在指定下标插入,这是插入一般数组类型时的操作
MySQL [test]> SELECT json_array_insert(skill_info, '$[1]', 'php') from employee; +-----------------------------------------------------------------------------------------------------------------------+ | json_array_insert(skill_info, '$[1]', 'php') | +-----------------------------------------------------------------------------------------------------------------------+ | ["java", "php", "go", "python"] | | ["mysql", "php", "oracle", "python"] | | {"system": "linux", "database": "mysql", "language": "python"} | | {"system": ["linux", "windows"], "database": ["mysql", "oracle", "postgresql"], "language": ["python", "java", "go"]} | +-----------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
替换操作,也就是修改update操作,使用的是 json_replace 函数
json_replace:只替换已经存在的旧值,不存在则忽略;
MySQL [test]> update employee set skill_info = json_replace(skill_info, "$.language", "go") where empno = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> select * from employee where empno = 3; +-------+-----------------------------------------------------+------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-----------------------------------------------------+------------------------------------------------------------+ | 3 | {"age": 24, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} | +-------+-----------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec)
json_set:替换旧值,并插入不存在的新值;
MySQL [test]> update employee set basic_info = json_set(basic_info, "$.age", 28,"$.sex" ,"man") where empno = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> MySQL [test]> select * from employee where empno = 3; +-------+-------------------------------------------------------------------+------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-------------------------------------------------------------------+------------------------------------------------------------+ | 3 | {"age": 28, "sex": "man", "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} | +-------+-------------------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec)
json_insert:插入新值,但不替换已经存在的旧值;
MySQL [test]> update employee set basic_info = json_insert (basic_info, "$.age", 30, "$.phone" ,"123456789") where empno = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> select * from employee where empno = 3; +-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+ | 3 | {"age": 28, "sex": "man", "from": "shanghai", "name": "zhaoqing", "phone": "123456789"} | {"system": "linux", "database": "mysql", "language": "go"} | +-------+-----------------------------------------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec)
json_remove() 删除元素函数。
MySQL [test]> update employee set basic_info = json_remove (basic_info, "$.sex", "$.phone") where empno = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> MySQL [test]> select * from employee where empno = 3; +-------+-----------------------------------------------------+------------------------------------------------------------+ | empno | basic_info | skill_info | +-------+-----------------------------------------------------+------------------------------------------------------------+ | 3 | {"age": 28, "from": "shanghai", "name": "zhaoqing"} | {"system": "linux", "database": "mysql", "language": "go"} | +-------+-----------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec)
结语
JSON数据类型是一个对开发十分友好的功能,有了它,MySQL的功能才更趋于完善。
经常使用,会发现还有许多便捷的JSON函数能够在特定情况下帮到我们。详细信息也可以查看 https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
云平-20190508
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
为中文API的简繁转换库添加迟到的持续集成
前两天在V2EX偶遇反馈帖第一次见以汉字命名的 Java 类 - V2EX, 于是复习了一下半年多没碰的项目program-in-chinese/zhconverter. 前文介绍了发布过程: 在Maven Central发布中文API的Java库. 发现没有持续(测试)集成(下图顶上的绿块), 特此加上. 之前在另一个项目设置过但未成文: 设置CI - Set up continuous integration to automatically test your code · Issue #10 · program-in-chinese/junit4_in_chinese 参考: Getting started - Travis CI. 首先用collaborator之一(权限需为admin)的github账号登录Travis.org 之前把.*文件都排除在git版本控制之外了(导致.travis.yml不能提交), 于是用gitignore.io重新生成.gitignore文件(https://www.gitignore.io/api/java,maven,macos,eclip...
- 下一篇
centos7误删除python2导致的python和yum不可用处理
centos7查看版本 cat /etc/redhat-release // 我这边是 CentOS Linux release 7.6.1810 (Core) 强制删除已安装程序及其关联 rpm -qa|grep python|xargs rpm -ev --allmatches --nodeps 删除所有残余文件 ##xargs,允许你对输出执行其他某些命令 whereis python |xargs rm -frv 验证删除,返回无结果 whereis python 从http://vault.centos.org/7.5.1804/os/x86_64/Packages/下载版本,大版本要对应,7.6.1810用的7.5.1804版本的 python包下载到/opt/software/python/中(wget已经不能用了,页面下载的直接拷贝文件即可,也可以在其他机器上wget) http://vault.centos.org/7.5.1804/os/x86_64/Packages/lvm2-python-libs-2.02.177-4.el7.x86_64.rpmhttp://...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
-
Docker使用Oracle官方镜像安装(12C,18C,19C)
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS8编译安装MySQL8.0.19
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- MySQL8.0.19开启GTID主从同步CentOS8
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
推荐阅读
最新文章
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- CentOS6,CentOS7官方镜像安装Oracle11G
- SpringBoot2整合Redis,开启缓存,提高访问速度
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- Hadoop3单机部署,实现最简伪集群
- MySQL8.0.19开启GTID主从同步CentOS8
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果