AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

MySQL5.7特性：JSON数据类型学习

2019-05-08 2110
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： MySQL5.7的发行声明中，官方称之为里程碑式的版本，除了运行速度大幅度提升之外，还添加了之前版本没有的功能，如本文所述的原生JSON数据类型功能。

概述

MySQL5.7的发行声明中，官方称之为里程碑式的版本，除了运行速度大幅度提升之外，还添加了之前版本没有的功能，如本文所述的原生JSON数据类型功能。
在此版本之前，MySQL所有的JSON数据类型，全部是使用text等文本类型来实现的，数据的处理只能在应用代码级来实现，十分不方便。

什么是JSON类型

作为DBA，可能会对这个概念稍微有点陌生，但是对于开发者来说，这是一个十分熟悉的事物。

JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。它基于 ECMAScript (欧洲计算机协会制定的js规范)的一个子集，采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

MySQL原始JSON类型的优势在哪？

原生的JSON优势如下：

  1. 存储上类似text,可以存非常大的数据。
  2. 存储在JSON列中的JSON文档的自动验证 。无效的文档会产生错误。
  3. 优化的存储格式。存储在JSON列中的JSON文档将 转换为内部格式，以允许对文档元素进行快速读取访问。
  4. 相比于传统形式，不需要遍历所有字符串才能找到数据。
  5. 支持索引：通过虚拟列的功能可以对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

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
Java
Python
关系型数据库
Linux
存储
Go
MySQL
Oracle
数据格式
JSON
关键词：
云数据库 RDS MySQL 版特性
云数据库 RDS MySQL 版json
云数据库 RDS MySQL 版学习
云数据库 RDS MySQL 版数据类型
JSON数据类型
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
云平_Stephen
目录
相关文章
小王老师呀
|
26天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前，自增变量（`AUTO_INCREMENT`）的行为在服务器重启后可能会发生变化，导致意外结果。MySQL 8.0 引入了自增变量的持久化特性，将其信息存储在数据字典中，确保重启后的一致性。这提高了开发和管理的稳定性，减少了主键冲突和数据不一致的风险。默认情况下，MySQL 8.0 启用了这一特性，但在升级时需注意行为变化。
小王老师呀
41 1
武子康
|
2月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
武子康
51 3
java冯坚持
|
2月前
|
关系型数据库 MySQL Java
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库，创建模型实例，并自动或手动创建数据库表，以及对这些表进行操作的详细教程。
java冯坚持
79 0
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
小强在此
|
2月前
|
关系型数据库 MySQL 数据库
MySQL数据库基础（数据库操作，常用数据类型，表的操作）
MySQL数据库基础（数据库操作，常用数据类型，表的操作）
小强在此
37 5
java冯坚持
|
2月前
|
Java 关系型数据库 MySQL
springboot学习五：springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库，并进行基本的增删改查操作的教程。
java冯坚持
139 0
springboot学习五：springboot整合Mybatis 连接 mysql数据库
java冯坚持
|
2月前
|
Java 关系型数据库 MySQL
springboot学习四：springboot链接mysql数据库，使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
java冯坚持
47 0
springboot学习四：springboot链接mysql数据库，使用JdbcTemplate 操作mysql
sunrr
|
2月前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性？
【10月更文挑战第3天】MySQL8.2有哪些新特性？
sunrr
47 2
蓝易云
|
1月前
|
JSON 关系型数据库 MySQL
MySQL JSON数据存储结构与操作
通过本文的介绍，我们了解了MySQL中JSON数据类型的基本操作、常用JSON函数、以及如何通过索引和优化来提高查询性能。JSON数据类型为存储和操作结构化数据提供了灵活性和便利性，在现代数据库应用中具有广泛的应用前景。希望本文对您在MySQL中使用JSON数据类型有所帮助。
蓝易云
52 0
Codelinghu
|
2月前
|
关系型数据库 MySQL 数据库
mysql关系型数据库的学习
mysql关系型数据库的学习
Codelinghu
23 0
爱吃龙利鱼
|
2月前
|
Kubernetes 关系型数据库 MySQL
k8s学习--利用helm部署应用mysql，加深helm的理解
k8s学习--利用helm部署应用mysql，加深helm的理解
爱吃龙利鱼
239 0

热门文章

最新文章

  • 1
    业内首次融合数据仓库与数据湖 阿里云推出下一代大数据平台 “湖仓一体”
  • 2
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 3
    用好阿里云分析型数据库大存储实例，大幅降低大数据应用成本
  • 4
    AnalyticDB(原ADS)分区列的最佳实践
  • 5
    Hologres助力AliExpress双11实时数仓升级
  • 6
    面向未来的开源 OLAP 技术架构探讨以及选型实践
  • 7
    FFA 2023 「流式湖仓」专场：从技术原理到应用实践打造流式湖仓新架构
  • 8
    MaxCompute数仓构建流程基本概念
  • 9
    开源湖仓一体平台(一):LakeSoul
  • 10
    Flink+Hologres实时数仓在Lazada的建设及应用
  • 1
    【MySQL系列笔记】分库分表
    196
  • 2
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    157
  • 3
    服务器Linux系统配置mysql数据库主从自动备份
    212
  • 4
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    417
  • 5
    golang使用gorm操作mysql3，数据查询
    96
  • 6
    golang使用gorm操作mysql2
    57
  • 7
    golang使用gorm操作mysql1
    106
  • 8
    Hbase与MySQL对比，区别是什么？
    291
  • 9
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2297
  • 10
    navicat过期了，直接用idea连接mysql
    64

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月