MySQL5.7特性：JSON数据类型学习-低调大师

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

2019-05-07 631

概述

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

微信关注我们

原文链接：https://yq.aliyun.com/articles/701662

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道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...

2019-05-08

750

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://...

2019-05-08

831

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。