聊城手机网站建设解决方案,怎么做贝店式的网站,虚拟主机网站301跳转,建设部网站是什么网站从5.7.8开始#xff0c;MySQL开始支持JSON类型#xff0c;用于存储JSON数据。
JSON类型的加入模糊了关系型数据库与NoSQL之间的界限#xff0c;给日常开发也带来了很大的便利。
这篇文章主要介绍一下MySQL中JSON类型的使用#xff0c;主要参考MySQL手册#xff1a;https…从5.7.8开始MySQL开始支持JSON类型用于存储JSON数据。
JSON类型的加入模糊了关系型数据库与NoSQL之间的界限给日常开发也带来了很大的便利。
这篇文章主要介绍一下MySQL中JSON类型的使用主要参考MySQL手册https://dev.mysql.com/doc/refman/8.0/en/
1. 为什么要用JSON
自从MySQL添加对JSON的支持之后一些表结构变更的操作就变得简单了一些。
1.1 JSON的使用场景
虽然关系型数据库一直很有效但是面对需求的不断变化文档型数据库更加灵活方便。
MySQL支持JSON之后模糊了关系型与文档型数据库之间的界限。
在开发过程中经常会遇见下面几种情况
表中仅仅小部分数据需要新添加的字段当这个新添加的字段很有可能只是临时使用后续会废弃的时候当后面还不知道要新添加什么字段但大概率要添加的时候。
这些时候使用一个JSON进行存储比较合适不用更改表结构非常方便。
1.2 字符串还是JSON类型
在还不支持JSON的MySQL 5.7版本之前没有选择只能使用一个字符串类型存储JSON数据了。
但是如果数据库支持JSON类型那么就还是使用JSON类型吧。
JSON类型相比与使用字符串存储JSON数据有如下的好处
可以对存储的JSON数据自动校验不合法的JSON数据插入时会报错优化的存储结构。JSON类型将数据转化为内部结构进行存储使得可以对JSON类型数据进行搜索与局部变更而对于字符串来说需要全部取出来再更新。
2. JSON的增删改查
这里将简单介绍一下JSON类型的使用主要是增删改查等操作。
MySQL中使用utf8mb4字符集以及utf8mb4_bin字符序来处理JSON中的字符串因此JSON中的字符串时大小写敏感的。
2.1 创建JSON列
创建一个JSON类型的列很简单
CREATE TABLE videos (id int NOT NULL AUTO_INCREMENT,ext json NOT NULL,PRIMARY KEY (id)
);我们构建了一个表videos里面有一个JSON类型的ext字段用于存储一些扩展数据。
2.2 插入JSON值
和其它类型一样使用INSERT来插入数据
INSERT INTO videos
VALUES (1, {vid: vid1, title: title1, tags: [news, china], logo: true}),(2, {vid: vid2, tags:[], title: title2, logo: false}),(3, {vid: vid3, title: title3});来看一下现在表里的数据
mysql select * from videos;
---------------------------------------------------------------------------------
| id | ext |
---------------------------------------------------------------------------------
| 1 | {vid: vid1, logo: true, tags: [news, china], title: title1} |
| 2 | {vid: vid2, logo: false, tags: [], title: title2} |
| 3 | {vid: vid3, title: title3} |
---------------------------------------------------------------------------------每一个ext都是一个JSON数据。
2.3 校验JSON
使用JSON类型的一个好处就是MySQL可以自动检查数据的有效性避免插入非法的JSON数据。
2.3.1 JSON合法性校验
首先需要校验一个值是否是一个合法的JSON否则插入会失败
mysql insert into videos values (1, {);
ERROR 3140 (22032): Invalid JSON text: Missing a name for object member. at position 1 in value for column videos.ext.同时还可以使用JSON_VALID()函数查看一个JSON值是否合法
mysql select json_valid({);
-----------------
| json_valid({) |
-----------------
| 0 |
-----------------mysql select json_valid({vid: vid1});
-------------------------------
| json_valid({vid: vid1}) |
-------------------------------
| 1 |
-------------------------------2.3.2 JSON模式校验
如果更进一步除了值是否是合法JSON外还需要校验模式比如JSON值要包含某些字段等。
这时可以定义一个模式schema然后使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()函数来校验。 JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()两个函数是8.0.17版本引入的5.7版本还没有。 定义一个模式
{id: schema_for_videos,$schema: http://json-schema.org/draft-04/schema#,description: Schema for the table videos,type: object,properties: {vid: {type: string},tags: {type: array},logo: {type: boolean},title: {type: string}},required: [title, tags]
}字段含义
id: 模式的唯一ID$schema: JSON模式校验的标准应该是这个值保持不变description: 模式的描述type: 根元素的类型MySQL中JSON的根元素还可以是数组arrayproperties: JSON元素的列表每一个元素都应该描述出来里面列出了对应的类型required: 必要的元素。
在MySQL中定义一个变量
mysql set schema {id:schema_for_videos,$schema:http://json-schema.org/draft-04/schema#,description:Schema for the table videos,type:object,properties:{title:{type:string},tags:{type:array}},required:[title,tags]};
Query OK, 0 rows affected (0.04 sec)这样就可以使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()校验一个JSON是否满足要求了
mysql select json_schema_valid(schema, {title: , vid: , logo: false, tags: []}) as valid?;
--------
| valid? |
--------
| 1 |
--------mysql select json_schema_validation_report(schema, {title: , vid: , logo: false, tags: []}) as valid?;
-----------------
| valid? |
-----------------
| {valid: true} |
-----------------JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()的区别就是后者可以给出不满足要求的地方
mysql select json_schema_valid(schema, {vid: , logo: false, tags: []}) as valid?;
--------
| valid? |
--------
| 0 |
--------mysql select json_schema_validation_report(schema, {vid: , logo: false, tags: []}) as valid?\G
*************************** 1. row ***************************
valid?: {valid: false, reason: The JSON document location # failed requirement required at JSON Schema location #, schema-location: #, document-location: #, schema-failed-keyword: required}当然这两个函数的第二个参数要是一个合法的JSON不然MySQL会报错
mysql select json_schema_valid(schema, {) as valid?;ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_schema_valid: Missing a name for object member. at position 1.我们还可以将这个模式添加到表的定义上这样插入数据就可以使用这个模式进行校验了
ALTER TABLE videos
ADD CONSTRAINT CHECK (JSON_SCHEMA_VALID({id:schema_for_videos,$schema:http://json-schema.org/draft-04/schema#,description:Schema for the table videos,type:object,properties:{vid:{type:string},tags:{type:array},logo:{type:bool},title:{type:string}},required:[title,tags]}, ext));当然如果表里已经有数据了且不符合这个校验模式MySQL会报错
ERROR 3819 (HY000): Check constraint videos_chk_1 is violated.应该修改原来的数据以满足要求后再添加校验。
添加之后新增的数据就会进行校验
mysql INSERT INTO videos VALUES (1, {vid: vid1, title: title1, tags: [news, china], logo: true});Query OK, 1 row affected (0.04 sec)mysql INSERT INTO videos VALUES (2, {vid: vid2, title: title2});ERROR 3819 (HY000): Check constraint videos_chk_1 is violated.2.4 JSON的格式化
使用JSON_PRETTY()函数进行美化输出
mysql select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {vid: vid1,logo: true,tags: [news,china],title: title1
}2.5 获取JSON元素
JSON字段优于JSON字符串的一点就是JSON字段可以直接获取内部的元素而不用获取整个文档。
MySQL中支持使用JSON_EXTRACT()函数以及--操作符来获取JSON内部的元素
mysql select json_extract({a: 9, b:[1,2,3]}, $.a) as a;
------
| a |
------
| 9 |
------
1 row in set (0.04 sec)mysql select json_extract({a: 9, b:[1,2,3]}, $.b) as b;
-----------
| b |
-----------
| [1, 2, 3] |
-----------
1 row in set (0.04 sec)mysql select json_extract({a: 9, b:[1,2,3]}, $.b[1]) as b[1];
------
| b[1] |
------
| 2 |
------
1 row in set (0.04 sec)使用-
mysql select * from videos;
---------------------------------------------------------------------------------
| id | ext |
---------------------------------------------------------------------------------
| 1 | {vid: vid1, logo: true, tags: [news, china], title: title1} |
| 2 | {vid: vid2, logo: false, tags: [], title: title2} |
| 3 | {vid: vid3, logo: false, tags: [food], title: title3} |
---------------------------------------------------------------------------------
3 rows in set (0.04 sec)mysql select ext-$.title from videos;
----------------
| ext-$.title |
----------------
| title1 |
| title2 |
| title3 |
----------------
3 rows in set (0.04 sec)-就是JSON_EXTRACT()函数的别名。
使用JSON_UNQUOTE()函数去掉引号
mysql select json_unquote(ext-$.title) from videos;
------------------------------
| json_unquote(ext-$.title) |
------------------------------
| title1 |
| title2 |
| title3 |
------------------------------
3 rows in set (0.04 sec)还可以使用-达到同样的效果-就是JSON_UNQUOTE(JSON_EXTRACT(...))的别名:
mysql select ext-$.title from videos;
-----------------
| ext-$.title |
-----------------
| title1 |
| title2 |
| title3 |
-----------------
3 rows in set (0.04 sec)2.6 JSONPath
在获取JSON元素的过程中我们使用了类似$.title$.b[1]这样的结构来指定元素这些就是JSONPath。
JSONPath使用$符号表示整个JSON文档后面可以跟着不同的符号表示不同的元素
一个点号(.)加上key可以获取指定key的值[N]获取数组中下标为N的元素0开始[N to M]数组元素还可以指定开头结尾都包含[last] last表示数组中的最后一个元素[*]获取数组中的所有元素prefix**suffix获取所有prefix开头suffix结尾的JSONPath。
以下面的JSON为例
{a: a_value,b: [1, 2, 3, 4, 5],c: true,d: {a: inner_a,b: [11, 22, inner_b]}
}$得到整个文档$.a就是a_value$.b就是[1, 2, 3, 4, 5]$.b[*]等同于$.b$.b[2]得到数组b中的第三个元素3$.d.a得到的就是inner_a$.d.b[2]得到的就是inner_b$.b[1 to 2]返回[2, 3]$.b[last]返回5$.b[last-2 to last-1]返回[3, 4]$**.a返回的是所有以a结尾的元素组成的数组[a_value, inner_a]$**.b就是数组的数组了[[1, 2, 3, 4, 5], [11, 22, inner_b]]。
JSONPath并不仅仅可以用来获取JSON内的元素涉及到JSON值增删改查的函数基本上都需要一个JSONPath作为参数来指定要操作的元素。
2.7 搜索JSON元素
JSON类型的另一个优势就是可以进行搜索。
搜索可以使用JSON_SEARCH()函数返回匹配的JSONPath。
JSON_SEARCH()函数原型如下
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])其中前三个是必须参数
json_doc: 一个有效的JSON文档one_or_all: 字符串必须是one或all用于指定匹配返回的个数如果是one的话只返回匹配的第一个否则全部返回search_str: 就是需要搜索的值目前只支持字符串搜索同时还可以添加%或_来模糊匹配
后两个是可选参数
escape_char: 转义字符默认是\如果不指定或为NULL的话也是\;否则这个参数只能为空此时还是\或者一个字符指定多个会报错path: 指定了开始搜索的位置如果没有的话就是整个文档。
接下来以下面这个JSON文档为例看一下如何进行搜索
{a: a_value,b: [1, 2, 3, 4, 5],c: true,d: {a: a_value,b: [1, 2, bvalue]}
}json_search(j, one, a_value)返回$.ajson_search(j, all, a_value)返回[$.a, $.d.a]json_search(j, all, 1)返回[$.b[0], $.d.b[0]]json_search(j, all, %_value)返回[$.a, $.d.a, $.d.b[2]]json_search(j, all, %\_value)返回[$.a, $.d.a]注意和上一个的区别json_search(j, all, %|_value, |)指定转义符返回[$.a, $.d.a]json_search(j, all, %|_value, |, $.a)指定了开始搜索的位置返回$.a没有匹配$.d.a
接下来我们就可以在WHERE中使用JSON_SEARCH()了。
还是之前的videos表
mysql select * from videos;
---------------------------------------------------------------------------------
| id | ext |
---------------------------------------------------------------------------------
| 1 | {vid: vid1, logo: true, tags: [news, china], title: title1} |
| 2 | {vid: vid2, logo: false, tags: [], title: title2} |
| 3 | {vid: vid3, logo: false, tags: [food], title: title3} |
---------------------------------------------------------------------------------
3 rows in set (0.04 sec)mysql select * from videos where json_search(ext, all, title2);
-------------------------------------------------------------------
| id | ext |
-------------------------------------------------------------------
| 2 | {vid: vid2, logo: false, tags: [], title: title2} |
-------------------------------------------------------------------
1 row in set, 1 warning (0.04 sec)mysql select * from videos where json_search(ext, all, food, , $.tags);
-------------------------------------------------------------------------
| id | ext |
-------------------------------------------------------------------------
| 3 | {vid: vid3, logo: false, tags: [food], title: title3} |
-------------------------------------------------------------------------
1 row in set, 1 warning (0.04 sec)还可以使用-操作符来搜索
mysql select ext from videos where ext-$.logo true;
------------------------------------------------------------------------------------------------
| ext |
------------------------------------------------------------------------------------------------
| {vid: vid1, logo: true, tags: [news, china], title: title1, protected: true} |
------------------------------------------------------------------------------------------------
1 row in set (0.04 sec)2.8 JSON中插入新元素
MySQL中有几个函数可以支持向JSON中新增元素
JSON_INSERT()JSON_ARRAY_APPEND()JSON_ARRAY_INSERT()
这几个函数支持就地更新而不是取出JSON文档更改后全量覆盖。
使用JSON_INSERT()函数新增元素
update videos set ext json_insert(ext, $.protected, true);如果要增加的元素已经有了的话则没有变化。
JSON_ARRAY_APPEND()函数可以向数组中追加元素
update videos set ext json_array_append(ext, $.tags, tech) where json_search(ext, all, title2, , $.title);这里同时使用了JSON_SEARCH()进行匹配。
JSON_ARRAY_INSERT()函数可以在数组的指定位置中添加元素
update videos set extjson_array_insert(ext, $.tags[1], beijing) where ext-$.vid vid1;结果
mysql select ext from videos where ext-$.vid vid1;
-----------------------------------------------------------------------------------------------------------
| ext |
-----------------------------------------------------------------------------------------------------------
| {vid: vid1, logo: true, tags: [news, beijing, china], title: title1, protected: true} |
-----------------------------------------------------------------------------------------------------------
1 row in set (0.04 sec)2.9 更新JSON元素
使用JSON_REPLACE()或JSON_SET()函数来更新JSON中的元素。
JSON_REPLACE()函数可以用来更新元素的值
update videos set ext json_replace(ext, $.protected, false) where ext-$.vid vid1;不过如果JSON中没有要更新的key那么就什么也不做。
JSON_SET()除了可以更新元素的值之外如果指定的元素不存在还可以添加
update videos set ext json_set(ext, $.size, 100) where ext-$.vid vid1;2.10 删除JSON元素
使用JSON_REMOVE()函数可以删除JSON中的元素
update videos set ext json_remove(ext, $.size) where ext-$.vid vid1;update videos set ext json_remove(ext, $.tags[1]) where ext-$.vid vid1;JSON_REMOVE()函数可以指定多个JSONPath来删除多个元素这时MySQL是从左到右一个个删除的。
这样即使是相同的JSONPath但是顺序不一样结果就会不一样
mysql select json_remove({a: [1,2,3,4,5]}, $.a[2], $.a[3]);
-------------------------------------------------------
| json_remove({a: [1,2,3,4,5]}, $.a[2], $.a[3]) |
-------------------------------------------------------
| {a: [1, 2, 4]} |
-------------------------------------------------------
1 row in set (0.04 sec)mysql select json_remove({a: [1,2,3,4,5]}, $.a[3], $.a[2]);
-------------------------------------------------------
| json_remove({a: [1,2,3,4,5]}, $.a[3], $.a[2]) |
-------------------------------------------------------
| {a: [1, 2, 5]} |
-------------------------------------------------------
1 row in set (0.04 sec)2.11 JSON合并
MySQL中支持将两个JSON文档合并成一个文档。可以通过下面的两个函数来完成
JSON_MERGE_PATCH()相当于第二个参数更新第一个参数JSON_MERGE_PRESERVE()尽可能地保留两个参数的元素。
这两个函数有很大的不同使用的时候一定要注意。
2.11.1 JSON_MERGE_PATCH
函数接收至少两个参数如果多于两个参数的话那么就前两个合并的结果与后一个进行合并。
下面假设有两个参数进行讨论多于两个的也是类似的。
如果有一个参数是NULL那么结果就是NULL
mysql select json_merge_patch({a: 1, b: [1,2]}, null);
------------------------------------------------
| json_merge_patch({a: 1, b: [1,2]}, null) |
------------------------------------------------
| NULL |
------------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch(null, {b: null});
---------------------------------------
| json_merge_patch(null, {b: null}) |
---------------------------------------
| NULL |
---------------------------------------
1 row in set (0.04 sec)如果第一个参数不是object那么结果就相当于一个空的object和第二个参数合并其实就是第二个参数
mysql select json_merge_patch({}, {a: a});
--------------------------------------
| json_merge_patch({}, {a: a}) |
--------------------------------------
| {a: a} |
--------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({}, a);
-------------------------------
| json_merge_patch({}, a) |
-------------------------------
| a |
-------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch([], a);
-------------------------------
| json_merge_patch([], a) |
-------------------------------
| a |
-------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch([1, 2, 3], {a: a});
---------------------------------------------
| json_merge_patch([1, 2, 3], {a: a}) |
---------------------------------------------
| {a: a} |
---------------------------------------------
1 row in set (0.04 sec)如果第二个参数是数组array那么结果还是第二个参数
mysql select json_merge_patch({a: a}, []);
--------------------------------------
| json_merge_patch({a: a}, []) |
--------------------------------------
| [] |
--------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({a: a}, [1]);
---------------------------------------
| json_merge_patch({a: a}, [1]) |
---------------------------------------
| [1] |
---------------------------------------
1 row in set (0.04 sec)接下来就是两个参数都是object的情况了。
合并的结果包含第一个参数有而第二个参数没有的那些元素
mysql select json_merge_patch({a: 1}, {});
------------------------------------
| json_merge_patch({a: 1}, {}) |
------------------------------------
| {a: 1} |
------------------------------------
1 row in set (0.04 sec)也包含第一个参数没有而第二个有的元素除了值是null的
mysql select json_merge_patch({a: 1}, {b: 2});
------------------------------------------
| json_merge_patch({a: 1}, {b: 2}) |
------------------------------------------
| {a: 1, b: 2} |
------------------------------------------
1 row in set (0.04 sec)如果两个参数里都有那么合并的结果就是两个值递归合并的结果
mysql select json_merge_patch({a: 1}, {a: 2});
------------------------------------------
| json_merge_patch({a: 1}, {a: 2}) |
------------------------------------------
| {a: 2} |
------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({a: [1,2]}, {a: 2});
----------------------------------------------
| json_merge_patch({a: [1,2]}, {a: 2}) |
----------------------------------------------
| {a: 2} |
----------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({a: [1,2]}, {a: [3]});
------------------------------------------------
| json_merge_patch({a: [1,2]}, {a: [3]}) |
------------------------------------------------
| {a: [3]} |
------------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({a: [1,2]}, {a: {c: 1}});
-----------------------------------------------------
| json_merge_patch({a: [1,2]}, {a: {c: 1}}) |
-----------------------------------------------------
| {a: {c: 1}} |
-----------------------------------------------------
1 row in set (0.04 sec)这三个例子中结果就像是第二个参数的值覆盖了第一个这是因为这几个例子中两个参数a所对应的值不都是object结果就是第二个参数a的值。
下面的例子展示了递归合并的结果
mysql select json_merge_patch({a: {c: [1,2]}}, {a: {d: 9}});
------------------------------------------------------------
| json_merge_patch({a: {c: [1,2]}}, {a: {d: 9}}) |
------------------------------------------------------------
| {a: {c: [1, 2], d: 9}} |
------------------------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_patch({a: {c: [1,2]}}, {a: {c: 9}});
------------------------------------------------------------
| json_merge_patch({a: {c: [1,2]}}, {a: {c: 9}}) |
------------------------------------------------------------
| {a: {c: 9}} |
------------------------------------------------------------
1 row in set (0.04 sec)如果第二个参数的元素值是null那么结果里是不含这个元素的
mysql select json_merge_patch({a: 1}, {b: null});
---------------------------------------------
| json_merge_patch({a: 1}, {b: null}) |
---------------------------------------------
| {a: 1} |
---------------------------------------------
1 row in set (0.04 sec)使用这个特性可以删除第一个参数的元素就像JSON_REMOVE()一样
mysql select json_merge_patch({a: 1, b: [1,2]}, {b: null});
---------------------------------------------------------
| json_merge_patch({a: 1, b: [1,2]}, {b: null}) |
---------------------------------------------------------
| {a: 1} |
---------------------------------------------------------
1 row in set (0.04 sec)2.11.2 JSON_MERGE_PRESERVE
JSON_MERGE_PRESERVE()函数也是合并两个或多个JSON但是和JSON_MERGE_PATCH()不同在于第二个参数的元素并不会覆盖第一个参数的元素。
首先如果有一个参数是NULL那么救过就是NULL。
相邻的数组合并成一个数组
mysql select json_merge_preserve([1,2], [a, b]);
--------------------------------------------
| json_merge_preserve([1,2], [a, b]) |
--------------------------------------------
| [1, 2, a, b] |
--------------------------------------------
1 row in set (0.04 sec)相邻的两个object合并成一个object
mysql select json_merge_preserve({a: [1]}, {b: 1});
-----------------------------------------------
| json_merge_preserve({a: [1]}, {b: 1}) |
-----------------------------------------------
| {a: [1], b: 1} |
-----------------------------------------------
1 row in set (0.04 sec)标量值会包装成数组然后按照数组的方式合并
mysql select json_merge_preserve({a: 1}, {a: 2});
---------------------------------------------
| json_merge_preserve({a: 1}, {a: 2}) |
---------------------------------------------
| {a: [1, 2]} |
---------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_preserve({a: 1}, {a: [2]});
-----------------------------------------------
| json_merge_preserve({a: 1}, {a: [2]}) |
-----------------------------------------------
| {a: [1, 2]} |
-----------------------------------------------
1 row in set (0.04 sec)mysql select json_merge_preserve({a: 1, b: 3}, {a: 2, d: 4});
-------------------------------------------------------------
| json_merge_preserve({a: 1, b: 3}, {a: 2, d: 4}) |
-------------------------------------------------------------
| {a: [1, 2], b: 3, d: 4} |
-------------------------------------------------------------
1 row in set (0.04 sec)相邻的数组和object合并先将object包装成一个数组然后两个数组合并
mysql select json_merge_preserve([1, 2], {id: 47});
---------------------------------------------
| json_merge_preserve([1, 2], {id: 47}) |
---------------------------------------------
| [1, 2, {id: 47}] |
---------------------------------------------
1 row in set (0.04 sec)2.12 其它
MySQL还有很多有用的函数用于操作JSON类型这里简单介绍JSON_TYPE, JSON_LENGTH, 和JSON_STORAGE_SIZE等函数其余函数可以参考MySQL文档https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
JSON_TYPE返回JSON元素的类型包括object, array, null以及其他的标量类型
mysql select json_type({}), json_type([]), json_type(true), json_type(null), json_type(a);
----------------------------------------------------------------------------------------
| json_type({}) | json_type([]) | json_type(true) | json_type(null) | json_type(a) |
----------------------------------------------------------------------------------------
| OBJECT | ARRAY | BOOLEAN | NULL | STRING |
----------------------------------------------------------------------------------------
1 row in set (0.04 sec)JSON_LENGTH函数返回元素的个数
mysql select json_length([1]), json_length({a: [1,2]}, $.a), json_length({a: [1,2]}, $.a[1]);
-----------------------------------------------------------------------------------------------
| json_length([1]) | json_length({a: [1,2]}, $.a) | json_length({a: [1,2]}, $.a[1]) |
-----------------------------------------------------------------------------------------------
| 1 | 2 | 1 |
-----------------------------------------------------------------------------------------------
1 row in set (0.03 sec)JSON_STORAGE_SIZE函数返回JSON数据所占用的字节数
mysql select json_storage_size({a: true}), char_length({a: true});
--------------------------------------------------------------
| json_storage_size({a: true}) | char_length({a: true}) |
--------------------------------------------------------------
| 13 | 11 |
--------------------------------------------------------------
1 row in set (0.04 sec)JSON类型所占用的空间大致和LONGBLOB或LONGTEXT一样。不过由于有一些元数据可能会稍微大一些。
3. JSON的高级用法
前面我们介绍了MySQL中JSON类型的一些基本操作MySQL中对JSON类型的支持还可以有一些更高级的玩法比如关系型数据与JSON数据的相互转换甚至可以把MySQL当做一个文档型数据库来使用。
3.1 关系型数据转JSON
MySQL中有一些函数支持将关系型数据转换成JSON数据
JSON_OBJECTJSON_ARRAYJSON_OBJECTAGGJSON_ARRAYAGG
JSON_OBJECT函数可以将多个键值对拼装成一个object
mysql select json_pretty(json_object(a, 1, b, true, null, null))\G
*************************** 1. row ***************************
json_pretty(json_object(a, 1, b, true, null, null)): {a: 1,b: true,null: null
}
1 row in set (0.04 sec)如果键值对数量不对的话会报错
mysql select json_pretty(json_object(a, 1, b, true, null))\G
ERROR 1582 (42000): Incorrect parameter count in the call to native function json_objectJSON_ARRAY函数将所有的参数合并成一个数组
mysql select json_array(1,1,a,null,true,curtime());
--------------------------------------------
| json_array(1,1,a,null,true,curtime()) |
--------------------------------------------
| [1, 1, a, null, true, 17:38:39.000000] |
--------------------------------------------
1 row in set (0.04 sec)两个函数组合使用就可以构建一个复杂的JSON数据了
mysql select json_pretty(json_object(example, a complex example, user, json_object(name, valineliu, tags, json_array(1,2)), books, json_array(a,b))) as r\G
*************************** 1. row ***************************
r: {user: {name: valineliu,tags: [1,2]},books: [a,b],example: a complex example
}
1 row in set (0.04 sec)JSON_OBJECTAGG和JSON_ARRAYAGG两个函数可以通过GROUP BY返回更高级的数据。
JSON_OBJECTAGG可以指定一个关系型表的两个字段作为构建JSON的键值对。
比如一个表是这样的
mysql select * from r_videos;
-------------------
| id | size | title |
-------------------
| 1 | 100 | video 1 |
| 2 | 200 | video 2 |
| 3 | 300 | video 3 |
-------------------
3 rows in set (0.03 sec)指定title为keysize为value构建一个JSON
mysql select json_pretty(json_objectagg(title, size)) as size from r_videos\G
*************************** 1. row ***************************
size: {video 1: 100,video 2: 200,video 3: 300
}
1 row in set (0.04 sec)JSON_ARRAYAGG函数可以将一对多的关系转换成一个JSON数组。比如下面的表
mysql select * from r_videos;
----------------------------
| id | user_id | title | size |
----------------------------
| 1 | 100 | title 1 | 1000 |
| 2 | 100 | title 2 | 2000 |
| 3 | 200 | title 3 | 3000 |
| 4 | 300 | title 4 | 4000 |
| 5 | 300 | title 5 | 5000 |
| 6 | 300 | title 6 | 6000 |
----------------------------
6 rows in set (0.03 sec)下面的语句可以将这个关系型表转换成一个user_id为keytitle和size构成的object数组为value的JSON
mysql select json_pretty(json_object(user_id, user_id, videos, json_arrayagg(json_object(title, title, size, size)))) as videos from r_videos group by user_id\G
*************************** 1. row ***************************
videos: {videos: [{size: 1000,title: title 1},{size: 2000,title: title 2}],user_id: 100
}
*************************** 2. row ***************************
videos: {videos: [{size: 3000,title: title 3}],user_id: 200
}
*************************** 3. row ***************************
videos: {videos: [{size: 4000,title: title 4},{size: 5000,title: title 5},{size: 6000,title: title 6}],user_id: 300
}
3 rows in set (0.04 sec)3.2 JSON转表格
可以使用JSON_TABLE函数将一个JSON转换成关系型数据。
先看一个简单的例子
mysql select * from json_table({null: null, title: hello json, size: 1}, $ columns(title varchar(32) path $.title error on error, size int path $.size)) as jt;
------------------
| title | size |
------------------
| hello json | 1 |
------------------
1 row in set (0.03 sec)JSON_TABLE函数有两个参数第一个参数是一个JSON文档第二个参数就是列定义。
列定义前面的JSONPath指定了开始解析的位置列定义里每一个列都指定了列名、类型以及要获取值的JSONPath多个列定义用,分割。
下面的例子将一个含有数组的JSON展开成一个一对多的关系型数据。
原始数据如下
mysql select id, json_pretty(ext) as ext from videos\G
*************************** 1. row ***************************id: 1
ext: {vid: vid1,logo: true,tags: [news,china],title: title1,protected: false
}
*************************** 2. row ***************************id: 2
ext: {vid: vid2,logo: false,tags: [tech],title: title2,protected: true
}
*************************** 3. row ***************************id: 3
ext: {vid: vid3,logo: false,tags: [food,asian,china],title: title3,protected: true
}
3 rows in set (0.03 sec)其中每一行数据中都有一个数组类型的tags。现在想把这个一对多的数据展开成多行数据
mysql select v.id, jt.* from videos v, json_table(v.ext, $ columns (title varchar(32) path $.title, nested path $.tags[*] columns (tag varchar(32) path $))) as jt;
-------------------
| id | title | tag |
-------------------
| 1 | title1 | news |
| 1 | title1 | china |
| 2 | title2 | tech |
| 3 | title3 | food |
| 3 | title3 | asian |
| 3 | title3 | china |
-------------------
6 rows in set (0.04 sec)这里对于tag字段的定义使用了nested path。
3.3 通过JSON将MySQL作为文档型数据库
通过MySQL Shell甚至可以将MySQL当做一个文档型数据库。
可以参考https://dev.mysql.com/doc/mysql-shell/8.0/en/ 了解更多关于MySQL Shell的信息。
本质上还是使用表来存储数据的比如下面的表
CREATE TABLE MyCollection ( doc json DEFAULT NULL, _id varbinary(32) GENERATED ALWAYS AS (json_unquote( json_extract(doc,_utf8mb4$._id))) STORED NOT NULL, _json_schema json GENERATED ALWAYS AS (_utf8mb4{type:object}) VIRTUAL, PRIMARY KEY (_id), CONSTRAINT $val_strict_2190F99D7C6BE98E2C1EFE4E110B46A3D43C9751 CHECK (json_schema_valid(_json_schema,doc)) /*!80016 NOT ENFORCED */
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;通过mysqlsh连接数据库之后通过X DevAPI可以像操作MongoDB一样操作上面这个表
MyCollection session.getDefaultSchema().getCollection(MyCollection)X DevAPI中的Collection类定义了add, find, modify, remove等函数支持增删改查。
添加数据
MyCollection.add({document: one}).add([{document: two}, {document: three}]).add(mysqlx.expr({document: four}))像MongoDB一样find
MyCollection.find()
{_id: 000060d5ab750000000000000012,document: one
}
{_id: 000060d5ab750000000000000013,document: two
}
{_id: 000060d5ab750000000000000014,document: three
}
{_id: 000060d5ab750000000000000015,document: four
}
4 documents in set (0.0007 sec)这里仅仅是简单介绍了一下有意思的玩法更多关于X DevAPI的信息请参考https://dev.mysql.com/doc/x-devapi-userguide/en/
4. orm对JSON的支持
上面都是从mysql client的角度来使用JSON类型的在我们的程序中使用更多的还是各种orm。
这里简单介绍一下orm对于MySQL JSON类型的支持由于个人原因这里仅仅列出go语言的两个ormxorm和gorm。
不过好像对JSON的支持都不是很丰富。
其余orm以及其余语言参考各自的文档。
4.1 xorm
目前我还没有发现xorm支持JSON类型也可能是我漏掉了如果哪位大佬知道的话感谢补充~
4.2 gorm
gorm通过额外的包datatypes来支持JSON类型
import gorm.io/datatypestype User struct {gorm.ModelName stringAttributes datatypes.JSON
}db.Create(User{Name: jinzhu,Attributes: datatypes.JSON([]byte({name: jinzhu, age: 18, tags: [tag1, tag2], orgs: {orga: orga}})),
}// Query user having a role field in attributes
db.First(user, datatypes.JSONQuery(attributes).HasKey(role))
// Query user having orgs-orga field in attributes
db.First(user, datatypes.JSONQuery(attributes).HasKey(orgs, orga))但datatypes目前对JSON类型的支持还是很弱仅仅支持查找与搜索更加有用的更新还是没有的
import gorm.io/datatypestype UserWithJSON struct {gorm.ModelName stringAttributes datatypes.JSON
}DB.Create(User{Name: json-1,Attributes: datatypes.JSON([]byte({name: jinzhu, age: 18, tags: [tag1, tag2], orgs: {orga: orga}})),
}// Check JSON has keys
datatypes.JSONQuery(attributes).HasKey(value, keys...)db.Find(user, datatypes.JSONQuery(attributes).HasKey(role))
db.Find(user, datatypes.JSONQuery(attributes).HasKey(orgs, orga))
// MySQL
// SELECT * FROM users WHERE JSON_EXTRACT(attributes, $.role) IS NOT NULL
// SELECT * FROM users WHERE JSON_EXTRACT(attributes, $.orgs.orga) IS NOT NULL// PostgreSQL
// SELECT * FROM user WHERE attributes::jsonb ? role
// SELECT * FROM user WHERE attributes::jsonb - orgs ? orga// Check JSON extract value from keys equal to value
datatypes.JSONQuery(attributes).Equals(value, keys...)DB.First(user, datatypes.JSONQuery(attributes).Equals(jinzhu, name))
DB.First(user, datatypes.JSONQuery(attributes).Equals(orgb, orgs, orgb))
// MySQL
// SELECT * FROM user WHERE JSON_EXTRACT(attributes, $.name) jinzhu
// SELECT * FROM user WHERE JSON_EXTRACT(attributes, $.orgs.orgb) orgb// PostgreSQL
// SELECT * FROM user WHERE json_extract_path_text(attributes::json,name) jinzhu
// SELECT * FROM user WHERE json_extract_path_text(attributes::json,orgs,orgb) orgb参考https://gorm.io/docs/v2_release_note.html#DataTypes-JSON-as-example , https://github.com/go-gorm/datatypes
5. 一些思考
目前来看orm对于JSON的支持还不是很丰富而上面的绝大多数篇幅都是mysql客户端中操作JSON字段的方法在我们的程序中通过orm操作JSON字段还不是很方便。
在使用JSON类型的时候我更多地是把里面的元素当做一个候选字段。
比如今天来了一个需求需要添加一个字段我会将这个字段添加到JSON类型字段中满足可以将数据保存在一条记录中增加数据局部性而不用在别的地方获取这些数据。
随着产品的进化与需求的变更之前添加的字段有的变得没用了那么就可以后续删除这个元素而有的字段由于变得更加重要可以把它提升为一个关系型的字段。
到底是放在JSON中还是添加一个字段这个就看具体的使用了。如果这个字段经常使用读取写入还有搜索那么添加到一个新的字段还是比较方便的。不过好像添加为一个虚拟字段也是很有用。
当JSON变得巨大的时候没准可以考虑使用真正的文档型数据库了比如MongoDB。
