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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

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,如需转载请自行联系原作者




相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
7月前
|
数据采集 JSON 数据可视化
JSON数据解析实战:从嵌套结构到结构化表格
在信息爆炸的时代,从杂乱数据中提取精准知识图谱是数据侦探的挑战。本文以Google Scholar为例,解析嵌套JSON数据,提取文献信息并转换为结构化表格,通过Graphviz制作技术关系图谱,揭示文献间的隐秘联系。代码涵盖代理IP、请求头设置、JSON解析及可视化,提供完整实战案例。
474 4
JSON数据解析实战:从嵌套结构到结构化表格
|
7月前
|
存储 SQL 关系型数据库
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**:在MySQL迁移到崖山环境时,若字段类型为char(2),而应用存储的数据仅为'0'或'1',查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式,导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求,可将崖山环境中的char类型改为varchar类型以规避补空格问题,适用于所有版本。
|
7月前
|
关系型数据库 MySQL Java
【YashanDB知识库】崖山BIT类型对MYSQL兼容问题
【YashanDB知识库】崖山BIT类型对MYSQL兼容问题
|
7月前
|
自然语言处理 关系型数据库 MySQL
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
|
9月前
|
机器学习/深度学习 关系型数据库 MySQL
mysql bit对gorm使用何种类型?
在GORM中使用MySQL的BIT类型时,通常使用 `bool`类型来处理BIT(1),使用 `[]byte`类型来处理BIT(N)(N > 1)。通过正确的类型映射和位操作,可以高效地处理位字段数据。确保在定义结构体字段时,明确指定字段类型,以便GORM能够正确地处理数据库交互。
204 18
|
8月前
|
存储 SQL 关系型数据库
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
|
10月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
451 6
|
11月前
|
JSON 前端开发 JavaScript
聊聊 Go 语言中的 JSON 序列化与 js 前端交互类型失真问题
在Web开发中,后端与前端的数据交换常使用JSON格式,但JavaScript的数字类型仅能安全处理-2^53到2^53间的整数,超出此范围会导致精度丢失。本文通过Go语言的`encoding/json`包,介绍如何通过将大整数以字符串形式序列化和反序列化,有效解决这一问题,确保前后端数据交换的准确性。
297 4
|
11月前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
225 8
|
11月前
|
JSON 关系型数据库 MySQL
MySQL JSON数据存储结构与操作
通过本文的介绍,我们了解了MySQL中JSON数据类型的基本操作、常用JSON函数、以及如何通过索引和优化来提高查询性能。JSON数据类型为存储和操作结构化数据提供了灵活性和便利性,在现代数据库应用中具有广泛的应用前景。希望本文对您在MySQL中使用JSON数据类型有所帮助。
1172 0

推荐镜像

更多