开发者社区> 技术小胖子> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL 5.7新支持--------Json类型实战

2017-11-16 1816

简介：
+关注继续查看

1. 背景

　 * 在MySQL 5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问.

　 * MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.

   * 优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式，允许对Json元素进行快速读取访问.

   * MySQL Json类型支持建立索引增加查询性能提升.


2. Json类型所需的存储空间和值范围

类型 占用字节 最大长度
Json 数据长度 + 4 bytes
 4G


3. Json相关函数操作

   * JSON_OBJECT(string1, string2...) 创建 key-value 类型 Json 对象

1
2
3
4
5
6
7
mysql> SELECT JSON_OBJECT('k1', 'v1', 'k2', 'v2');
+-------------------------------------+
| JSON_OBJECT('k1', 'v1', 'k2', 'v2') |
+-------------------------------------+
| {"k1": "v1", "k2": "v2"}            |
+-------------------------------------+
1 row in set (0.01 sec)


   * JSON_ARRAY(string1, string2...) 创建一个 Json 数组

1
2
3
4
5
6
7
mysql> SELECT JSON_ARRAY('a', 'b', 'c', 'd');
+--------------------------------+
| JSON_ARRAY('a', 'b', 'c', 'd') |
+--------------------------------+
| ["a", "b", "c", "d"]           |
+--------------------------------+
1 row in set (0.00 sec)


   * JSON_TYPE(object) 判断并显示数据类型 [ 值非法会报错显示 ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql>  SELECT JSON_TYPE('"lisea"');
+----------------------+
| JSON_TYPE('"lisea"') |
+----------------------+
| STRING               |
+----------------------+
1 row in set (0.01 sec)
 
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
1 row in set (0.01 sec)
 
mysql> SELECT JSON_TYPE('1');
+----------------+
| JSON_TYPE('1') |
+----------------+
| INTEGER        |
+----------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_TYPE('{"k1":"v1", "k2":"v2"}');
+-------------------------------------+
| JSON_TYPE('{"k1":"v1", "k2":"v2"}') |
+-------------------------------------+
| OBJECT                              |
+-------------------------------------+
1 row in set (0.00 sec)


   * JSON_MERGE(doc1,doc2....) 合并多个Json对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_MERGE('{"k1":"v1"}','{ "k2":"v2"}');
+------------------------------------------+
| JSON_MERGE('{"k1":"v1"}','{ "k2":"v2"}') |
+------------------------------------------+
| {"k1": "v1", "k2": "v2"}                 |
+------------------------------------------+
1 row in set (0.04 sec)
 
mysql> SELECT JSON_MERGE('["k1","k2"]','{ "k3":"v3"}');
+------------------------------------------+
| JSON_MERGE('["k1","k2"]','{ "k3":"v3"}') |
+------------------------------------------+
| ["k1", "k2", {"k3": "v3"}]               |
+------------------------------------------+
1 row in set (0.00 sec)


   * JSON_EXTRACT(object, key) 通过Json key方式获取Val值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_EXTRACT('{"k1":"v1","k2":"v2"}', '$.k1');
+-----------------------------------------------+
| JSON_EXTRACT('{"k1":"v1","k2":"v2"}', '$.k1') |
+-----------------------------------------------+
| "v1"                                          |
+-----------------------------------------------+
1 row in set (0.01 sec)
 
mysql> SELECT JSON_EXTRACT('{"k1":"v1","k2":"v2"}', '$.*');
+----------------------------------------------+
| JSON_EXTRACT('{"k1":"v1","k2":"v2"}', '$.*') |
+----------------------------------------------+
| ["v1", "v2"]                                 |
+----------------------------------------------+
1 row in set (0.00 sec)


   * JSON_SET(object, key, val, key, val....) 通过key修改val值

1
2
3
4
5
6
7
mysql> SELECT JSON_SET('{"k1":"v1","k2":"v2"}', '$.k1', 'lisea', '$.k2', 'hello');
+---------------------------------------------------------------------+
| JSON_SET('{"k1":"v1","k2":"v2"}', '$.k1', 'lisea', '$.k2', 'hello') |
+---------------------------------------------------------------------+
| {"k1": "lisea", "k2": "hello"}                                      |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)


   * JSON_INSERT(object, key, val)添加新值到对象中，如果key已存在，不替换val

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_INSERT('{"k1":"v1","k2":"v2"}', '$.k1', 'hello');
+-------------------------------------------------------+
| JSON_INSERT('{"k1":"v1","k2":"v2"}', '$.k1', 'hello') |
+-------------------------------------------------------+
| {"k1": "v1", "k2": "v2"}                              |
+-------------------------------------------------------+
1 row in set (0.03 sec)
 
mysql> SELECT JSON_INSERT('{"k1":"v1","k2":"v2"}', '$.k3', 'v3');
+----------------------------------------------------+
| JSON_INSERT('{"k1":"v1","k2":"v2"}', '$.k3', 'v3') |
+----------------------------------------------------+
| {"k1": "v1", "k2": "v2", "k3": "v3"}               |
+----------------------------------------------------+
1 row in set (0.02 sec)


   * JSON_REPLACE() 替换现有的值并忽略新的值

1
2
3
4
5
6
7
mysql> SELECT JSON_REPLACE('{"k1":"v1","k2":"v2"}', '$.k1', 'hello', '$.k3', 'v3');
+----------------------------------------------------------------------+
| JSON_REPLACE('{"k1":"v1","k2":"v2"}', '$.k1', 'hello', '$.k3', 'v3') |
+----------------------------------------------------------------------+
| {"k1": "hello", "k2": "v2"}                                          |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)


   * JSON_REMOVE() 通过key移除

1
2
3
4
5
6
7
mysql> SELECT JSON_REMOVE('{"k1":"v1","k2":"v2"}', '$.k1');
+----------------------------------------------+
| JSON_REMOVE('{"k1":"v1","k2":"v2"}', '$.k1') |
+----------------------------------------------+
| {"k2": "v2"}                                 |
+----------------------------------------------+
1 row in set (0.00 sec)


   * JSON_KEYS() 获取所有key

1
2
3
4
5
6
7
mysql> SELECT JSON_KEYS('{"k1":"v1","k2":"v2"}');
+------------------------------------+
| JSON_KEYS('{"k1":"v1","k2":"v2"}') |
+------------------------------------+
| ["k1", "k2"]                       |
+------------------------------------+
1 row in set (0.03 sec)


   * JSON_UNQUOTE() 去掉值的引号

1
2
3
4
5
6
7
mysql> SELECT JSON_UNQUOTE('"hello"');
+-------------------------+
| JSON_UNQUOTE('"hello"') |
+-------------------------+
| hello                   |
+-------------------------+
1 row in set (0.01 sec)


   * JSON_DEPTH() 获取Json对象的深度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_DEPTH('{"k1":"v1","k2":"v2"}');
+-------------------------------------+
| JSON_DEPTH('{"k1":"v1","k2":"v2"}') |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_DEPTH('{}');
+------------------+
| JSON_DEPTH('{}') |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)


   * JSON_VALID() 判断是否为有效的json格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_VALID('{"k1":"v1"}');
+---------------------------+
| JSON_VALID('{"k1":"v1"}') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_VALID('{"k1":"v1"');
+--------------------------+
| JSON_VALID('{"k1":"v1"') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)


   * JSON_LENGTH() 获取指定路径下的长度

      长度的计算规则:

            标量的长度为1

            json array的长度为元素的个数

            json object的长度为key的个数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_LENGTH('[1, 2, 3]');
+--------------------------+
| JSON_LENGTH('[1, 2, 3]') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_LENGTH('{"k1":"v1", "k2":"v2"}');                                                            
+---------------------------------------+
| JSON_LENGTH('{"k1":"v1", "k2":"v2"}') |
+---------------------------------------+
|                                     2 |
+---------------------------------------+
1 row in set (0.00 sec)


   * JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])  

　　　查询包含指定字符串的paths，并作为一个json array返回　

　　　one_or_all："one"表示查询到一个即返回；"all"表示查询所有。

　　　search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。

　　　path：在指定path下查。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_SEARCH('{"k1":"v1", "k2":"v2", "k3":"v2"}', 'one', 'v2%');
+----------------------------------------------------------------+
| JSON_SEARCH('{"k1":"v1", "k2":"v2", "k3":"v2"}', 'one', 'v2%') |
+----------------------------------------------------------------+
| "$.k2"                                                         |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_SEARCH('{"k1":"v1", "k2":"v2", "k3":"v2"}', 'all', 'v2%');
+----------------------------------------------------------------+
| JSON_SEARCH('{"k1":"v1", "k2":"v2", "k3":"v2"}', 'all', 'v2%') |
+----------------------------------------------------------------+
| ["$.k2", "$.k3"]                                               |
+----------------------------------------------------------------+
1 row in set (0.01 sec)


4. 总结


以需求驱动技术，技术本身没有优略之分，只有业务之分。




      本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943339，如需转载请自行联系原作者




版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

存储 JSON 关系型数据库 MySQL 数据格式 SQL 索引 JavaScript 前端开发
JSON类型 JSON MySQL 云数据库 MySQL 版实战 云数据库 MySQL 版json 云数据库 MySQL 版json类型
开发者社区 > 数据库 > 文章
数据库备份
作者高分内容
更多
SQLOS's memory manager and SQL Server's Buffer Pool 3507 python开发环境搭建 15621 Android 动态加载 （一） 态加载机制 案例一 7862 FragmentTransaction.replace（） 你不知道的坑 3606 基于Rebound制造绚丽的动画效果-入门篇 3268
相关文章
go-Jack
postman测试post请求参数为json类型
postman测试post请求参数为json类型
0 0
高久峰
fastadmin设置默认返回json类型
fastadmin设置默认返回json类型
0 0
xaubllxwtvaqiu
开发指南—数据类型—Json类型
PolarDB-X支持JSON类型。 与MySQL不同，PolarDB-X支持的JSON类型暂不支持作为分区键。 详细信息请参见MySQL Json类型。
0 0
不会压弯的小飞侠
SpringMVC五种类型参数传递和json数据传递参数
SpringMVC五种类型参数传递和json数据传递参数
0 0
十八岁讨厌编程
[SpringMVC]请求与响应③(JSON数据传输参数、日期类型参数传递)
请求与响应③(JSON数据传输参数、日期类型参数传递)
0 0
IT咸鱼干
MySQL Json类型的一个坑
MySQL中JSON类型有个隐藏的坑，底层会对json结构重排序后保存，目的是为了优化搜索效率；但是针对结构不能改变的业务来说，就是一个大坑
0 0
陈卸甲
【MySQL】 json类型的增删改查
【MySQL】 json类型的增删改查
0 0
痴者工良
Newtonsoft.Json C#Json序列化和反序列化工具的使用、类型方法大全
Newtonsoft.Json C#Json序列化和反序列化工具的使用、类型方法大全
0 0
兮动人
【mysql】JSON 类型
【mysql】JSON 类型
0 0
小蓝枣
Python字符串和json类型的相互转换实例演示，python字符串转json、json转字符串
Python字符串和json类型的相互转换实例演示，python字符串转json、json转字符串
0 0
+关注
技术小胖子
文章
问答
作者高分内容
更多
SQLOS's memory manager and SQL Server's Buffer Pool 3507 python开发环境搭建 15621 Android 动态加载 （一） 态加载机制 案例一 7862 FragmentTransaction.replace（） 你不知道的坑 3606 基于Rebound制造绚丽的动画效果-入门篇 3268
文章排行榜
最热
最新
1 承上启下：基于全域漏斗分析的主搜深度统一粗排 91532
2 复杂链表的复制 90642
3 【Transform3D】转换详解（看完就会） 88190
4 【flexbox弹性布局学习指南】CSS热门布局方案 68854
5 如何构建标签画像工程体系及实现方案 63157
6 DCM：中间件家族迎来新成员 61169
7 系统的混乱并业务本身之复杂，我们并不擅长处理『简单』 48276
8 超越10倍开发者 48152
9 ⚡Three.js-在场景中导入3D动画模型 46113
10 Request&Response 39098
11 《JavaScript高级程序设计》__ 作用域&内存 37896
12 影响成长的关键思考 34911
13 K8S学习-当我们部署应用的时候都发生了什么？（第一篇） 30696
14 【从零开始学习深度学习】12. 什么是模型的训练误差？基于三阶多项式的欠拟合与过拟合训练过程演示 27899
15 阿里云物联网平台设备分发实战 33505
16 记录下考Apsara Clouder云计算专项技能认证：云服务器ECS入门过程 16330
17 ChatGPT 常见错误原因及解决方案：报错、回答不完整等 22211
18 Python3，9行代码，对比两个Excel数据差异，并把差异结果重新保存。 8592
19 深聊性能测试，从入门到放弃之： Windows系统性能监控(一) 性能监视器介绍及使用。 9582
20 学生速看！免费领取阿里云云服务器的详细攻略 3580
1 云时代，好用的数据迁移方案推荐 126
2 浅谈智能语音交互 116
3 【分享】宜搭集成自动化，Groovy节点一般用法，解析JSON数据 81
4 算法提升（二） 异或法 52
5 一封来自开发者社区的邀请函 135
6 【分享】在宜搭JS面板写了死循环如何解决 106
7 如何选择数据集成方式-离线&实时 119
8 网易游戏实时 HTAP 计费风控平台建设 199
9 Apache Tomcat 存在 JsonErrorReportValve 注入漏洞（CVE-2022-45143） 56
10 Grid网格布局学习笔记 53
11 【数据设计与实现】第5章：同步与互斥 79
12 基于钉钉宜搭开发的《连锁门店管理系统》功能演示 82
13 Python库函数pandas读取excel数据 97
14 谈谈《流浪地球2》中如何正确的“重启互联网” 2175
15 《阿里巴巴Java开发手册（终极版）》电子版地址 52
16 2023阿里云学生服务器免费申请流程 275
17 阿里云服务器ECS性能优势详解_选择云服务器的理由 263
18 阿里云服务器ECS介绍_云主机_服务器托管_弹性计算 171
19 阿里云服务器ECS云主机详细介绍说明 78
20 阿里云服务器ECS详解（性能/安全/速度） 1541
相关课程
更多
数据库及SQL/MySQL基础
4395
22
去学习
MySQL高级应用 - 索引和锁
15006
65
去学习
MySQL实战进阶
536
7
去学习
云数据库RDS MySQL从入门到高阶
1363
7
去学习
云数据库MySQL快速入门
2314
10
去学习
云数据库MySQL版快速上手教程
19680
13
去学习
推荐文章
2022藏经阁年度电子书榜单出炉！ 参与应用引擎 SAE评测，赢取CHERRY机械键盘等好礼 “第益课”大学生技术公益 乘风者计划邀您入驻社区，精彩权益即刻享
相关电子书
更多
好的 MySQL 兼容可以做到什么程度
立即下载
从理论到实践，深度解析MySQL Group Replication
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL，选择PgSQL
立即下载